Building an SQL query based on checkboxes

Question

Let's say I have a form that has 30 checkboxes that correspond to music genres (it submits to a PHP form handler).

I have an artists table that has a genre field. What are the best practices on building a query that would behave along the lines of:

Select name FROM artists WHERE genreId = 1 OR genreId = 2 OR . . . etc etc. Where the genreId's are chosen from checkboxes.

I know I can dynamically build my WHERE clause in PHP, but I'm in the habit of using prepared statements and stored procedures. What should I do?

Mike Brant · Accepted Answer · 2013-07-03 22:41:09Z

2

You would likely want to use IN() in your where clause like this:

WHERE genreId IN (1,2,3)

Unfortunately, there really isn't a good solution to use this in a parameterized way. You just have to go old-school:

$query = 'SELECT ... WHERE genreId IN (' . implode(',', $genreId_array) . ')';

I suppose though you could do something like this to build a parametrized approach:

$query = 'SELECT ... WHERE genreId IN (';
$array_length = count($genreId_array);
for ($i = 0; $i < $array_length; $i++) {
    $query .= '?,';
}
$query = rtrim(',',$query) . ')';

edited Jul 3, 2013 at 22:41

answered Jul 3, 2013 at 22:33

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

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

3 Comments

Dan Bracuk Over a year ago

Make sure you acknowledge that the user might submit the form without selecting any checkboxes.

Mike Brant Over a year ago

@Galway Certainly that validation should be done before you even get to the point of trying to form a query.

Galway Over a year ago

@MikeBrant Alright. Thanks gents. It's too bad there isn't much in terms of best practices. Never hurts to ask.

Collectives™ on Stack Overflow

Building an SQL query based on checkboxes

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related