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. Additionally, you need to stop using mysql_ functions as they are removed in PHP7 — Machavity
– Machavity ♦, Commented Sep 30, 2015 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
– crashtest, Commented Sep 30, 2015 at 12:26

AleOtero93 · Accepted Answer · 2015-09-30 18:28:21Z

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, 2015 at 18:28

answered Sep 30, 2015 at 13:34

AleOtero93

49812 silver badges33 bronze badges

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

2 Comments

crashtest Over a year ago

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);

AleOtero93 Over a year ago

My bad, forgot the . ... The idea was concatenate the clauses.

user2762134 · Accepted Answer · 2015-09-30 13:18:58Z

1

$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)

answered Sep 30, 2015 at 13:18

user2762134

Collectives™ on Stack Overflow

Altering mysql query based on variable

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related