SQL Setup

[ecabrera]

I want to be able to search various manufacturers by selecting checkboxes, how would I do this?

What if they choose 7 to search how would i get it to work with the sql string right now i have to enter it manually like this LIKE '%BMW' but I want it to come from the loop:

if(!empty($_POST['check_list'])) {

// Loop to store and display values of individual checked checkbox.

foreach($_POST['check_list'] as $selected) {

echo $selected."</br>";

}

}


$sql = "SELECT * FROM `Cars` WHERE `manufacturer` LIKE '%BMW' OR

`manufacturer` LIKE '%Audi' ORDER BY `tier` ASC";

[mac_gyver]

you would dynamically build the WHERE part of the $sql = " ... "; string using php code.

 

for something like (no pun intended) a repeated like term, it is easiest to build each term as entries in an array, then just implode the array with the OR keyword.

 

you should have an array with the possible choices that you are using to both build the form and to build the WHERE part of the query (blindly looping over the submitted form data is not a good idea as hackers can submit hundreds of form fields.)

 

as you loop over the defining array, if that particular form input isset(), to indicate the checkbox was checked, add the `manufacturer` LIKE '%abc' portion of the query into an array.

 

when you get done looping over the choices, if the array is not empty, just implode it to form that part of the query (note: implode will produce the correct result even if there is only one entry in the array.)

 

edit: for the example you posted, the code would look like this - 

$cars[] = 'BMW';
$cars[] = 'Audi';

$terms = array();
if(!empty($_POST['check_list'])) {
    foreach($cars as $choice){
        if(isset($_POST['check_list'][$choice])){
            $terms[] = "`manufacturer` LIKE '%$choice'";
        }
    }
}

$where_clause = '';
if(!empty($terms)){
    $where_clause = "WHERE ".implode(' OR ',$terms);
}

$sql = "SELECT * FROM `Cars` $where_clause ORDER BY `tier` ASC";

Edited May 12, 2015 by mac_gyver

[Psycho]

Never run queries in loops - it puts a lot of overhead on the server and, will eventually, result is significant performance issues. Also, if the user is selecting from predetermined values, you should not be using a LIKE condition. The options should be a lookup list using a unique if as a foreign key in the table you are searching. So, based on what you have provided, you should have another table such as 'makes' something like this:

 

makes

=================

id | make

1   BMW

2   Audi

3   Ford

4   Buick

 

Then, in the 'Cars' table you would have a column for make_id using the numeric values above. You would not put 'BWM' as a value in the 'Cars' table, just the id.

 

Then, you could dynamically create the checkboxes using the 'makes' table like so

 

$query = "SELECT id, make FROM makes ORDER by make";
$result = mysqli_query($link, $query);
 
$makeOptions = '';
while ($row = )
{
    $makeOptions .= "<input type='checkbox' name='makes[]' value='{$row['id']}'> {$row['make']}<br>\n";
}
 
echo $makeOptions;

 

Lastly, on the page that receives the form post, you would use those selected values to create a query using the IN operator

 

//Get the passed make IDs
$makeIDs = isset($_POST['makes']) ? $_POST['makes'] : array();
//Force to ints to prevent SQL injection
$makeIDs = array_map('intval', $_POST['makes']);
//Create variable for WHERE clause
$WHERE = ""; //Used as if if no makes selected
//If there are values, create an IN condition
if($makeIDs)
{
    $WHERE = " WHERE make_id IN (" . implode(',', $makeIDs) .   ")";
}
 
$query = "SELECT * FROM `Cars` {$WHERE}";

[ecabrera]

I am trying to do this 

$sql = "SELECT * FROM `cars` WHERE `manufacturer` IN (".implode(",",$_POST['check_list']).")";

$query = mysqli_query($db,$sql);

//get the rows
while($rows = mysqli_fetch_assoc($query)){

$mid = $rows['id'];
$dbm = $rows['manufacturer'];
$tier = $rows['tier'];
$name = $rows['name'];
$email = $rows['email'];

echo "";
echo "<br><br>Tier: $tier <br>Manufacturer: $dbm<br>Contact Name: $name <br> Email: $email ";

}

 but it's not working. 

[Barand]

Using LIKE '%BMW'in a where clause will prevent the query from using an index and will be slow.

 

The IN with join() that you are using will only work with numeric values - strings need to be enclosed in quotes. So you can have

IN (1,2,3) but not IN (BMW,Audi).

The latter would have to be

IN ('BMW', 'Audi')

 

Therefore the code is

"WHERE manufacturer IN ('". implode("','", $checklist) . "')";

Don't use POST values directly in queries - sanitize first or use prepared statements

[ecabrera]

What im i doing wrong ? 

$sql = "SELECT * FROM `cars` WHERE manufacturer IN ('". implode("','", $checklist) . "') AND tier IN ('". implode("','", $tier) . "')";

[Barand]

echo $sql;

and see what you have

[ecabrera]

i get this SELECT * FROM `cars` WHERE manufacturer IN ('Audi','BMW') AND tier IN ('')

 

i don't get it. Isn't it the same as the one before?

[Barand]

IN ('') will look for those with an empty string. If you do not have any values in $tier then omit that condition from the query. See reply #2