Geek asks and answers

Multiple sorting using SQL SELECT query using PHP and form

I am using an html form with 6 criteria, using $_POSTlat convert criteria in variables, like here:

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

Case 2 - only one criterion is specified. $core = null; $mhz = "performanta_cpu=1400"; $ram = null; $cam = null; $mAh = null $screen = null
Request corect: $sql = "SELECT * FROM $tbl_name WHERE $mhzz ORDER BY performanta_cpu_core DESC, performanta_cpu DESC, performanta_rami DESC LIMIT $start, $limit";

Case 3 is the problem. All or more of the criteria: $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 need to make " WHERE" dynamic and visible only when some variable is set, and I also need " AND" dynamically:

$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 have been tense for a week with this, and I cannot advance without help ...

Thanks in advance

+5
source share
5 answers

: , , .

$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.
+2

.. .

, , , , , . , , ?

Eg.

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

, .

+2

, " " WHERE:

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

WHERE ( ):

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

ORDER BY LIMIT, ( ORDER BY ):

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

, , WHERE $mhz, WHERE your-column-name = $mhz.

MySQL WorkBench. , , , .

This is not an ideal way to compile a query, but it will work, and it looks like you are just starting with PHP / MySQL, so you don’t need to throw you too much.

+2
source 
$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";
+2
source

You can use array_filterand implodeto create your proposalwhere

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

This saves all nonzero elements as $conditions, and then combines them with and. Then you can use this as

'... where ' . $clause . '...'
+2
source

More articles:


All Articles