Jump to content

SQL Setup


ecabrera
Go to solution Solved by Barand,

Recommended Posts

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

Link to comment
Share on other sites

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 by mac_gyver
Link to comment
Share on other sites

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}";
Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

  • Solution

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.