www.webdeveloper.com
Results 1 to 4 of 4

Thread: Building WHERE clause based on checkboxes

  1. #1
    Join Date
    Feb 2011
    Location
    Waterloo, Ontario, Canada
    Posts
    80

    Building WHERE clause based on checkboxes

    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.

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

  2. #2
    Join Date
    Oct 2007
    Location
    Western Massachusetts, USA
    Posts
    387
    You can make one large query, many small ones (joining data in memory in PHP), or you can do a different concept:
    - Each genre has it's own unique bit (1, 2, 4, 8, 16, ...)
    - A search checks for bitwise AND of those genres vs your search mask.
    A similar system (if bitwise AND not supported) would be to use a char field and compare with a regex. E.g. to be "metal" and "progressive", could be "YY", while "metal" without "progressive" would be "YN". Then regex could match only Y's.

  3. #3
    Join Date
    Jul 2013
    Posts
    29
    I would use "select * from atists where genreId in ($in)", where "$in = implode(',', $_REQUEST['genreId'])". In this case the HTML code for checkboxes should look like input name="genreId[]" value="11", not name="genreId11" value="on". This is called array.

  4. #4
    Join Date
    Feb 2011
    Location
    Waterloo, Ontario, Canada
    Posts
    80
    Quote Originally Posted by tishetsky View Post
    I would use "select * from atists where genreId in ($in)", where "$in = implode(',', $_REQUEST['genreId'])". In this case the HTML code for checkboxes should look like input name="genreId[]" value="11", not name="genreId11" value="on". This is called array.
    This worked for me. Thanks!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles