Altering mysql query based on variable

Question

So I am making a click based search tool. Nothing fancy and it works great as long as you pick at least one thing from all the available categories. I want to change it so that there would be no mandatory categories to pick from, so if I only want to pick from category cpu, then that would give results aswell.

This is what i have as input:

<form action="search.php" method="GET"> 
<input value="i3"  name="cpu[]" type="checkbox">i3 
<input value="i5"  name="cpu[]" type="checkbox">i5 <br>
<input value="4gb"  name="ram[]" type="checkbox">4gb  
<input value="8gb"  name="ram[]" type="checkbox">8gb<br>
<input value="1tb"  name="storage[]" type="checkbox">1TB 
<input value="500gb"  name="storage[]" type="checkbox">500GB<br>
<input type="submit" value="search" class="submit" />

This is the part that gathers the clicked checkboxes (I left out the cpu and ram parts since its the same):

if(isset($_GET['storage'])){
  if (is_array($_GET['storage'])) {
    foreach($_GET['storage'] as $storage_value){
      $storage_term = trim($storage_value);
      $storage_term_array[] = $storage_term;
    if (!empty($storage_term)) {
        $storage_bits[] = "`storage` LIKE '%$storage_term%'";
    }
} } }
else $storage_bits="0";

And the mysql_query goes like this:

$res=mysql_query("SELECT * FROM laptop WHERE 
(".implode(' OR ', $cpu_bits).")  
AND (".implode(' OR ', $ram_bits).") 
AND (".implode(' OR ', $storage_bits).")
AND visibility=1");

This is what it returns:

(`cpu` LIKE '%i5%') AND (`ram` LIKE '%8gb%') AND (`storage` LIKE '%1tb%' OR `storage` LIKE '%500gb%')

This is the code that is working, but as I said before you need to pick at least 1 checkbox per category. How do I completely exclude lets say the AND (".implode(' OR ', $storage_bits)." part if $storage_bits="0"?

So far I have had no luck with case

$res=mysql_query("SELECT * FROM laptop WHERE 
(".implode(' OR ', $cpu_bits).")  
AND (".implode(' OR ', $ram_bits).") 
(case when ($storage_bits=0) THEN 'AND 1=1' ELSE 'AND (".implode(' OR ', $storage_bits)."' END )
AND visibility=1");

And not even the IF is working for me.

$res=mysql_query("SELECT * FROM laptop WHERE 
(".implode(' OR ', $cpu_bits).")  
AND (".implode(' OR ', $ram_bits).") 
IF($storage_bits=0,'1=1','AND (".implode(' OR ', $storage_bits).")
AND visibility=1");

It gives me no syntax errors whatsoever, it just doesn't give results. What gives? I feel like I am missing some quotation marks or something similarly pitiful but I just cant seem to wrap my head around it. Thanks!

Your code is [open to SQL injection attacks](http://bobby-tables.com/). Additionally, you need to [stop using mysql_ functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) as they are removed in PHP7 — Machavity, Sep 30 '15 at 12:24
yes I am aware that my code is stone age like, but before I can find some time to study PDO I am looking for a quick fix on this. Thanks! — crashtest, Sep 30 '15 at 12:26

score 1 · Answer 1 · answered Sep 30 '15 at 13:18

$query_parts = array();
foreach($_GET as $column => $values) {
    $query_parts[] = '(' . $column . ' LIKE \'%' . implode(' OR ' . $column . ' LIKE \'%', $values) . '%\')';
}
$query = 'SELECT * FROM Laptop';
if(count($query) > 0) {
    $query .= ' WHERE ' . implode(' AND ', $query_parts);
}
var_dump($query);

The query for /?cpu%5B%5D=i3&cpu%5B%5D=i5&ram%5B%5D=4gb

Results string 'SELECT * FROM Laptop WHERE (cpu LIKE '%i3 OR cpu LIKE '%i5%') AND (ram LIKE '%4gb%')' (length=85)

AleOtero93 · Accepted Answer · 2015-09-30T18:28:21.980

1

Try this:

$whereClause = "WHERE visibility = 1";
if($cpu_bits != '0'){
    $whereClause .= " AND (".implode(' OR ', $cpu_bits).")";
}
if($ram_bits != '0'){
    $whereClause .= " AND (".implode(' OR ', $ram_bits).")";
}
if($storage_bits != '0'){
    $whereClause .= "AND (".implode(' OR ', $storage_bits).")";
}
$res=mysql_query("SELECT * FROM laptop".$whereClause);

Doing this, you are preventing empty fields to go in there where clause.

edited Sep 30 '15 at 18:28

answered Sep 30 '15 at 13:34

AleOtero93

473
12
32

1

your method was a good start, but it needed some `array_push` in order to work and not to overwrite the `$whereClause` every time. thanks! `$whereClause = array( "WHERE visibility = 1" ); if($cpu_bits != ''){ array_push($whereClause, " AND (".implode(' OR ', $cpu_bits).")"); } if($ram_bits != ''){ array_push($whereClause, " AND (".implode(' OR ', $ram_bits).")"); } if($storage_bits != ''){ array_push($whereClause, " AND (".implode(' OR ', $storage_bits).")"); } echo implode($whereClause);` – crashtest Sep 30 '15 at 18:27
My bad, forgot the `.` ... The idea was concatenate the clauses. – AleOtero93 Sep 30 '15 at 18:28

Altering mysql query based on variable

2 Answers2