How to build a WHERE condition with multiple values from checkbox?

Question

I have a form which has a checkbox with three options i.e. 'A','B' and 'C'. I need to build a SELECT statement to get data from a database table X, for which the WHERE clause could be WHERE field1 IN ('A','B','C') or it could be any one of the values i.e. WHERE field1 = 'A' etc.

I am kind of stuck as to how to proceed with the SELECT statement.

Please let me know if any more details are required to understand the problem.

The code for my checkbox in the form:

<input type="checkbox" name="gender[]" value="Male" id="Male"> Male
<input type="checkbox" name="gender[]" value="Female" id="Female"> Female
<input type="checkbox" name="gender[]" value="Other" id="Other"> Other

The SELECT statement would basically be like :

SELECT field1,field2,field3
FROM X 
WHERE field1 IN('A','B','C')

or it could be

SELECT field1,field2,field3
FROM X
WHERE field1 ='A' -- Basically this could be a single value.

@WilliamGunawan the problem is building the WHERE clause based on the values of the checkbox values selected i.e. either one value or all values. — David D'Lima, Aug 04 '19 at 18:43
You query already correct nothing wrong with it. so after you execute the quest it's not same as you expected? can you tell us more? — William Gunawan, Aug 04 '19 at 18:50
https://stackoverflow.com/questions/772913/how-do-you-use-in-clauses-with-mysqli-prepared-statements — lufc, Aug 04 '19 at 19:26
https://stackoverflow.com/questions/327274/mysql-prepared-statements-with-a-variable-size-variable-list — lufc, Aug 04 '19 at 20:11

score 0 · Answer 1 · answered Aug 04 '19 at 19:57

I'm not quite understanding what you actually want to do there, but as the comments already say, don't put user-inputs directly into mysql query. As far as I understand you, this might help:

If you don't know how to establish a connection with pdo : https://www.php.net/manual/en/pdo.connections.php

$stmt = $pdo->prepare(SELECT field1,field2,field3 FROM X WHERE field1 = :a OR field1 = :b OR field1 = :c);
$stmt->execute(['a'=> $aVariable,'b'=> $bVariable,'c'=>$cVariable]); //Here you bind the data from your form to the parameters of the select query.
$row = $stmt->fetch(); //This is the result of the mysql query with the data of the table

François Huppé · Accepted Answer · 2019-08-04T19:28:37.423

-2

Try this:

$sql = "SELECT * FROM table WHERE field IN ('".implode("','", $_POST['gender'])."')";

This is how you could build your query, but it is strongly recommended you sanitize the content of the $_POST array before building the query, because you could be vulnerable to SQL injection.

edited Aug 04 '19 at 19:28

answered Aug 04 '19 at 19:23

François Huppé

2,006
1
6
15

1

Don't put user inputs directly into SQL queries. – lufc Aug 04 '19 at 19:23
well this is a whole other question, but of course, you are right ! – François Huppé Aug 04 '19 at 19:26
The more times people put answers like this on SO, the more vulnerable websites get built... – lufc Aug 04 '19 at 19:27
@lufc I agree, my bad ! Still, it does answer the OP, so shouldn't it be enough to mention the vulnerability like I did in my edit ? – François Huppé Aug 04 '19 at 19:30
Any comment on the downvotes ? Did i miss understood the question ? – François Huppé Aug 05 '19 at 02:10
Guys this is just an example. I am taking care of SQL injection. No issues with that. I just need to know how to handle the multiple values of the checkbox in the WHERE clause . All other precautions are taken care of in my code. – David D'Lima Aug 05 '19 at 05:03
good to know ! at first i didn’t mention the vulnerability, this is what probably got me the downvotes :) still i understand, it is important to mention, you seem to know what you are doing but it’s not necessarily the case for anyone who could use this post in the future ! – François Huppé Aug 05 '19 at 05:43
@DavidD'Lima, did it solved your problem finally ? Was not sure if you meant you wanted strictly either 1 or all checkboxes at a time… Anyway i can edit if there is something missing, or if you were waiting to see if something else comes up before accepting an answer, it's totally cool too. – François Huppé Aug 05 '19 at 07:01
@FrançoisHuppé it could be either one value or multiple values. Just trying out your solution. – David D'Lima Aug 05 '19 at 07:11
@DavidD'Lima - the best way to take care of SQL injection is to use prepared statements. This approach will not work with prepared statements. – lufc Aug 05 '19 at 20:12
@lufc yes I am aware that this will not work with prepared statements. – David D'Lima Aug 08 '19 at 07:56
@lufc The OP does not mention anything about « The best way to avoid SQL injection » You’re being completely out of line here. It was more than enough to add a warning about the vulnerability in my answer. I think it’s important you realize that prepared statement are for security purposes, which also means that if you want to optimize the performance of your code, you must avoid them whenever it is possible. – François Huppé Aug 08 '19 at 17:23

How to build a WHERE condition with multiple values from checkbox?

2 Answers2