0

Not sure if i labeled this question clearly, however, i have a MySQL table with a column zones. This column holds the values of a checkbox array that has been run through PHP's implode like so:

if(isset($_POST['checkbox'])) {
    $zones = implode(',', $_POST['checkbox']);
}

This saves the data in a string format like 2,3,4,5.

I'd like to create a query to basically match any number in that individual column. So far my query looks like:

$zoneId = '2';
SELECT * FROM $alert_table WHERE `zones` LIKE $zoneId AND `time_created` < DATE_SUB(NOW(), INTERVAL 2 HOUR) ORDER BY `id` ASC LIMIT 1;

It works perfectly if the column only has 1 number. But fails if the column has the string 2,3,4,5.

Any help is appreciated, i am not opposed to a change in method of storing the checkbox values if their is a better alternative.

DLzer
  • 159
  • 11
  • Do **NOT** combine multiple values in a single field. This is a very bad design. – PM 77-1 Apr 26 '18 at 17:08
  • Nooooo dont store things in something seperated lists in a single column. ___It causes you to have to ask these kind of questions___ as it makes the data ___almost___ impossible to process – RiggsFolly Apr 26 '18 at 17:08
  • This post should be very helpful to you .. You need a table that just stores the checkbox value and a **seperate** pivot table to link those values to other items. This is a `one-to-many` relationship: https://stackoverflow.com/questions/7296846/how-to-implement-one-to-one-one-to-many-and-many-to-many-relationships-while-de?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa – Zak Apr 26 '18 at 17:11
  • I understand the flaw in my table now, thank you. I need to store the zone data separately ( Preferably a separate table ). – DLzer Apr 26 '18 at 17:40
  • @ShateelAhmed See my comment. I understand the flaw in table design and understand the fix. Regardless performing the query with a `LIKE`search would return 2, however the `ORDER BY "id" LIMIT 1 would return a single item which is what i need. – DLzer Apr 26 '18 at 17:46
  • Looks like you need to use regexp (RLIKE): "SELECT ... WHERE zones RLIKE '(^|,)$zoneID(,|$)' ..." – Yuri Lachin Apr 27 '18 at 16:50

0 Answers0