Multiple sort using SQL SELECT Query using PHP and Form

Question

I use a html form with 6 criterias, using $_POST lat's convert criterias in variables like here:

Case 1 - All criterias are default
$core = null; $mhz = null; $ram = null; $cam = null; $mAh = null $screen = null
The correct sql query is this :
$sql = "SELECT * FROM $tbl_name ORDER BY performanta_cpu_core DESC, performanta_cpu DESC, performanta_rami DESC LIMIT $start, $limit";

Case 2 - Only one criteria is set
$core = null; $mhz = "performanta_cpu=1400"; $ram = null; $cam = null; $mAh = null $screen = null
The corect query is this :
$sql = "SELECT * FROM $tbl_name WHERE $mhzz ORDER BY performanta_cpu_core DESC, performanta_cpu DESC, performanta_rami DESC LIMIT $start, $limit";

Case 3 - Here is the problem All or more than one criterias ore setted:
$core = 2; $mhz = "performanta_cpu=1400"; $ram = "performanta_rami=1024"; $cam = "camera_spate=3.2"; $mAh = "baterie_mAh=2250"; $screen = "densitate=441";

I understand that i have need to make "WHERE" to be dinamic and visible just when any variable is set and also I have need an "AND" also dinamically like:

$sql = "SELECT * FROM $tbl_name WHERE $core AND $mhzz ORDER BY performanta_cpu_core DESC, performanta_cpu DESC, performanta_rami DESC LIMIT $start, $limit";
I am stressed for a week with this and i cant advance without help...

Thanks in advance

If you dont use special framework. in this part WHERE $mhzz you dont set a value to comparison. Please read the following text for better understanding dynamically query creating. link — Mahmut Ali ÖZKURAN
– Mahmut Ali ÖZKURAN, Commented Mar 8, 2013 at 22:30
please escape any untrusted values within your query with mysql_real_escape_string at least (don't forget to pass the current link) — m02ph3u5
– m02ph3u5, Commented Mar 8, 2013 at 22:55

Matt · Accepted Answer · 2013-03-08 23:29:39Z

2

Disclaimer: This is terrible code and there are a million better ways to do this, but, this is the simplest explanation.

$parameters = array();
if(!empty($core)){
$parameters['core'] = $core;
}
if(!empty($mhz)){
$parameters['mhz'] = $mhz;
}
if(!empty($ram)){
$parameters['ram'] = $ram;
}
if(!empty($cam)){
$parameters['cam'] = $cam;
}
if(!empty($mAh)){
$parameters['mAh'] = $mAh;
}
if(!empty($screen)){
$parameters['screen'] = $screen;
}

$sql = "SELECT * FROM $tbl_name WHERE 1=1 ";
foreach($parameters as $k=>$v){
 $sql .= " AND ".$k."='".$v."'";
}
$sql .=  " ORDER BY performanta_cpu_core DESC, performanta_cpu DESC, performanta_rami DESC LIMIT $start, $limit";

// All of those parameters should be sanitized to prevent SQL injection.
// mysql_* is deprecated, use mysqli_* or PDO.

edited Mar 8, 2013 at 23:29

answered Mar 8, 2013 at 22:32

Matt

5,4381 gold badge34 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

NiTrO Over a year ago

how to put $sql? like

$sql = "SELECT * FROM $tbl_name ORDER BY performanta_cpu_core DESC, performanta_cpu DESC, performanta_rami DESC LIMIT $start, $limit";

or else?

Matt Over a year ago

Append it after. I've updated my response to reflect the change.

NiTrO Over a year ago

Parse error: syntax error, unexpected ';', expecting ']' in E:\server\htdocs\performanta.php on line 26

NiTrO Over a year ago

this is line $parameters['core'] = $core;

Matt Over a year ago

I typoed all the brackets, I've updated my response to correct.

|

chip · Accepted Answer · 2013-03-08 22:33:31Z

2

Excellently formatted question.. well done.

I may well be misinterpreting the question, however I think you're asking how to construct a query dynamically. Perhaps you are not aware you concat strings ?

Eg.

if ($core != null) {$query.= 'AND core ='.$core;}

I hope this puts you in the right direction.

answered Mar 8, 2013 at 22:33

chip

6091 gold badge10 silver badges21 bronze badges

Comments

Ed Gibbs · Accepted Answer · 2013-03-08 22:41:10Z

Start with the beginning of your query, and to make the command easier to build add an "always true" WHERE condition:

$sql = "SELECT * FROM $tbl_name WHERE 1=1";

Then go through your variables and add to the WHERE condition as needed (the space before the AND is really important):

if ($core) $sql .= " AND performanta_cpu_core = '$core'";
if ($mhz) $sql .= " AND whatever = '$mhz'";
... and so on for the other four variables

Then append your ORDER BY and LIMIT and you're done (the space before the ORDER BY is really important):

$sql .= " ORDER BY performanta_cpu_core DESC, performanta_cpu DESC, performanta_rami DESC LIMIT $start, $limit";

Also, as Mahmut pointed out, you can't have WHERE $mhz, you've got to have WHERE your-column-name = $mhz.

Make sure you try out the query from the MySQL command line or WorkBench first. That will help you with syntax, including which columns need single quotes around them and which ones don't.

This isn't the ideal way to assemble a query but it'll work, and it looks like you're just getting started with PHP/MySQL so no need to throw you too much at once.

Olaf Dietsche · Accepted Answer · 2013-03-08 22:47:19Z

2

You can use array_filter and implode to build your where clause

$conditions = array_filter(array($core, $mhz, $ram, $cam, $mAh, $screen));
$clause = implode(' and ', $conditions);

This keeps all non null elements as $conditions and then concatenates these with and. You can then use this as

'... where ' . $clause . '...'

answered Mar 8, 2013 at 22:47

Olaf Dietsche

74.4k9 gold badges113 silver badges214 bronze badges

Comments

Barmar · Accepted Answer · 2013-03-08 23:14:46Z

2

$parameters = array();
if(!empty($core)){
    $parameters[] = "core = '$core'";
}
if(!empty($mhz)){
    $parameters[] = "mhz = '$mhz'";
}
if(!empty($ram)){
    $parameters[] = "ran = '$ram'";
}
if(!empty($cam)){
    $parameters[] = "cam = '$cam'";
}
if(!empty($mAh)){
    $parameters[] = "mAh = '$mAh'";
}
if(!empty($screen)){
    $parameters[] = "screen = $screen";
}

if (empty($parameters)) {
   $whereclause = "";
} else {
   $whereclause = "WHERE " . join(' AND ', $parameters);
}

$sql = "SELECT * FROM $tbl_name $whereclause ORDER BY performanta_cpu_core DESC, performanta_cpu DESC, performanta_rami DESC LIMIT $start, $limit";

edited Mar 8, 2013 at 23:14

answered Mar 8, 2013 at 22:42

Barmar

789k57 gold badges555 silver badges669 bronze badges

4 Comments

NiTrO Over a year ago

Parse error: syntax error, unexpected ';', expecting ']' in E:\server\htdocs\performanta.php on line 49

Barmar Over a year ago

I fixed a missing parenthesis in if(empty($parameters)), does that fix it?

NiTrO Over a year ago

Parse error: syntax error, unexpected '{' in E:\server\htdocs\performanta.php on line 44

Barmar Over a year ago

I just checked, and there's no syntax error in my code. It must be elsewhere in your script. If you use a good IDE, it should help you find it.

Collectives™ on Stack Overflow

Multiple sort using SQL SELECT Query using PHP and Form

5 Answers 5

12 Comments

Comments

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

12 Comments

Comments

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related