PHP combo boxes + MySQL query

wildest_jjk

wildest_jjk

wildest_jjk

Soldato
Joined
6 Jun 2011
Posts
2,741
Hey guys,

So I am currently trying to construct a mysql query that searches through a database.

The problem I have is that I am trying to use multiple checkbox values to construct the WHERE query.

Anybody know a good way of doing this?

Thanks :)
 
Without knowing the significance of the checkboxes it would be a bit hard. Are you using them to select data from different columns/tables, or is it to select from a range of items that are to be found all in a single column?

If the latter, then each checkbox has a name attribute, this will come into the $_POST[] key/value hash that you can see if it has been checked via isset(), and if so, put the relevant criteria into a comma delimited string.

You'd then include that into your query something like: "... WHERE thiscolumn in(" . $yourCommaDelimitedValues . ")".

(Whilst validating the inputs are valid of course and not tricking you with injected SQL or the like).

Might be able to suggest something better if there are more details to share though.
 
Last edited:
topdog@OC said:
Without knowing the significance of the checkboxes it would be a bit hard. Are you using them to select data from different columns/tables, or is it to select from a range of items that are to be found all in a single column?

If the latter, then each checkbox has an ID, this will come into the $_POST[] key/value hash that you can see if it has been checked via isset(), and if so, put the relevant criteria into a comma delimited string.

You'd then include that into your query something like: "... WHERE thiscolumn in(" . $yourCommaDelimitedValues . ")".

(Whilst validating the inputs are valid of course and not tricking you with injected SQL or the like).

Might be able to suggest something better if there are more details to share though.
Click to expand...

Thanks for the response. Silly me, I meant combo boxes! But I know they have a similar principle.

The thing is I am going to have multiple combo boxes.

So if I run something like:


The first line does what you said but also checks to see if the combo box is set to 'all' for instance. Then if it is true it will add the current value into a variable which can then be used in an SQL query. The problem is I have multiple boxes which need to be used in the same query.

Hopefully this makes sense!

Thanks :)
 
Last edited:
So you have multiple combo boxes/dropdowns and you want to put them in a SQL query?

Perhaps i'm missing something but it's a simple case of pulling in the values, $_REQUEST['element name here'] (use the POST/GET equivalent though) etc, do any necessary 'checking' and then stringing it together in the query using AND/OR operators -
Code: 
SELECT `columns` FROM `table` WHERE `column1` = '$value1' AND `column2` = '$value2' OR `column3` = '$value3'

Obviously encase the lot in a IF statement with a condition checking that the user submitted the data.
 
Thanks for the response.

The problem is that not all of the combo boxes have to be selected. The default option for all the combo boxes basically have a value which is "".

So then any or all of the combo boxes can be set to other than "" and each one corresponds to a column in the table.

Constructing the SQL select query would be something like that I guess. But what happens when the variables for a column box are set to default?

Cheers :)
 
What I tend to do (though I do this inside stored procedures / functions in MSSQL) is something like this which means that if a value, e.g. $c2 is blank then it just skips that where part:

PHP: 
SELECT * FROM table
WHERE ('$c1' = '' OR column1='$c1')
AND ('$c2' = '' OR column2='$c2')
AND ('$c3' = '' OR column3='$c3')
AND ('$c4' = '' OR column4='$c4')

You're probably putting ever so sightly more of a hit on the DB server doing it this way but it's quick and easy to build your filter queries.
 
Thanks very much for your response. Do you mind explaning it a bit more? I am a beginner really and don't really understand how the bit in brackets works!

Thanks a lot :)
 
Last edited:
The brackets test whether the condition A OR B is true. So if either the part on the left or right of the OR is true the bracket its-self is true. It's important you don't omit the brackets when using an OR because it could work different to how you expect.

If have values of:
PHP: 
$C1 = 'Apple';
$C2 = 'Banana';
$C3 = 'Pear';
$C4 = 'Cake';

The query becomes:
PHP: 
SELECT * FROM table
WHERE ('Apple' = '' OR column1='Apple')
AND ('Banana' = '' OR column2='Banana')
AND ('Pear' = '' OR column3='Pear')
AND ('Cake' = '' OR column4='Cake')

('Banana' = '' OR column2='Banana') checks whether the string 'Banana'='' (it doesn't) OR filters rows where the value in column2 is 'Banana'.

So it returns rows where column1 is Apple, column2 is Banana, column3 is Pear and column4 is Cake.


But, if I have values of:
PHP: 
$C1 = 'Apple';
$C2 = '';
$C3 = 'Pear';
$C4 = '';

The query becomes:
PHP: 
SELECT * FROM table
WHERE ('Apple' = '' OR column1='Apple')
AND ('' = '' OR column2='')
AND ('Pear' = '' OR column3='Pear')
AND ('' = '' OR column4='')

This time we omitted the column2 filter (banana) and that where clause is now:
('' = '' OR column2='')

So now we're checking whether ''='' (it does!) and because of this the column2='' part is skipped (it's an OR query, only A OR B needs to be true, not both) therefore this pair of brackets is essentially skipped.

The query is now in effect because of the blank checks:
PHP: 
SELECT * FROM table
WHERE ('Apple' = '' OR column1='Apple')
AND ('Pear' = '' OR column3='Pear')

Which is essentially:

PHP: 
SELECT * FROM table
WHERE column1='Apple'
AND column3='Pear'

Now it returns rows where column1 is Apple and column3 is Pear and not filtering on column2/column4 at all.
 
Thanks very much for that. That is very kind!

The only thing is I am not sure if this will work in my instance?

If I am adding the values into variables on submit like this:

That is just for one combo box. So if the value is equal to ''. Then searching this in the database, using the filter method you gave will not work will it?

Or am I looking at this wrong?

Thanks :)
 
Last edited:
You'd do something like this, giving you just one query for all checkboxes:

PHP: 
$choice = $_POST['abc'];
$choice2 = $_POST['def'];
$choice3 = $_POST['ghi'];

$sql = "SELECT * FROM table WHERE ('$choice' = '' OR column='$choice') AND ('$choice2' = '' OR column2='$choice2') AND ('$choice3' = '' OR column3='$choice3')";
 
Yes that's what I had actually just got to. Thanks so much, that is a great help.

One last thing that I do not understand. Is because it is in an SQL query why does the '$choice' = '' not cause a problem for instance?

Is it not looking in the table somewhere for this? I understand that when that is true it will not look at the other condition in the brackets.

Hopefully that makes sense!

Thanks :)
 
wildest_jjk said:
One last thing that I do not understand. Is because it is in an SQL query why does the '$choice' = '' not cause a problem for instance?

Is it not looking in the table somewhere for this? I understand that when that is true it will not look at the other condition in the brackets.
Click to expand...

When your query is ran $choice is substituted for whatever the PHP value is (e.g. 'Apple').

This is simply a string comparison (does 'str1'='str2'?) - it's not referencing any columns. If the comparison is true the left-hand-side of the OR clause is satisfied and so it doesn't then bother/need to execute the right-hand-side of the OR clause which actually goes off and checks the rows' column values. If you changed OR for AND it would have to check both the left and right sides and both would need to be true for the row to be included in the result.

wildest_jjk said:
Just to add,

Or is the '$choice' = '' part looking in the table? It just does not find anything that matches?
Click to expand...

No this happens outside of the table which is why it works. Well, actually, that comparison does happen for each row in your table (as it needs to check each row to see if it satisfies your WHERE conditions) but it doesn't check the row's column value if the left-side is satisfied.

Make sense? Quite difficult to explain but I know in my head what I mean :p,


You could (without too much effort) build your query from PHP which only includes the WHERE for columns you've selected but it's a bit more faff I find.
 
Last edited:
Code: 
$sql1 = 'SELECT * FROM table'
$sql_clause = ' WHERE '

if(isset($_POST['abc']) && $_POST['abc'] != ''){
$choice = $_POST['abc'];
$sql1 = $sql1 . $sql_clause . 'column1 = \'' . $choice '\'';
$sql_clause = ' AND ';
}

if(isset($_POST['def']) && $_POST['def'] != ''){
$choice = $_POST['def'];
$sql1 = $sql1 . $sql_clause . 'column2 = \'' . $choice '\'';
$sql_clause = ' AND ';
}

.. etc. ..

This is probably what Pho means by more faff, but it's correct and fairly normal to build SQL from multiple string fragments like this.
 
Thanks for the response,

Would that work in this case though? Using the filter in the where statement allows it to skip the column search, if the variable is empty. Would this be possible using the other way that you have posted?

Thanks again :)
 
topdog@OC's way checks whether the value of a selectbox exists and is not blank [if(isset($_POST['abc']) && $_POST['abc'] != '')], if it does exist and it does have a value then the relevant WHERE clause is added to your query. So yeah it should do what you want it to do also.

Alternatively you could look at using frameworks, e.g. Zend or roll your own query building class which cuts down the amount of code you need to write manually to build a query.
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom