-2

need some help with a MYSQL call. In one of my tables, I have an array (of sorts) of id's formatted likes this: 13,32,51,131,57 etc..

Every id is separated by a comma. Putting them in and getting them out I've figured out using explode and implode, but how would I go about searching through them direectly from the SQL call with php?

for example: I need to see if id# 13 is in that row.

$this_id = '13';
$sql="SELECT * FROM table WHERE this_id NOT IN ($this_id)";

So basically, I need to look through an array with NOT IN to see if the id number is in that array. Any ideas?

David
  • 2,094
  • 3
  • 30
  • 47
  • 3
    Without knowing anything about your DB structure, all I can say is this: **don't put more than one piece of data in each column**. Your database should be [normalized](http://en.wikipedia.org/wiki/Database_normalization). – Bojangles Sep 20 '11 at 21:30
  • 1
    What @JamWaffles said. Packing loads of data into one field is really baaaaaadd – David Snabel-Caunt Sep 20 '11 at 21:32
  • @DavidCaunt That Wikipedia page is getting worn out, I tell you! The amount of times I paste it around SO along with a comment. It's like the infamous multiple-ID issue people have with jQuery. – Bojangles Sep 20 '11 at 21:33
  • 4
    +1 to @JamWaffles; see also [Is storing a comma separated list in a database column really that bad?](http://stackoverflow.com/questions/3653462/) – Bill Karwin Sep 20 '11 at 21:35
  • @Bill I saw the link and instantly thought "yes". I'm a little bit confused as to why MySQL has aggregate functions to aid bad database design, however. – Bojangles Sep 20 '11 at 21:37
  • 8 good reasons not to rewrite a RDBMS in your application code. Great question to link to - thanks @BillKarwin – David Snabel-Caunt Sep 20 '11 at 21:39
  • 1
    @JamWaffles: MySQL is full of its share of WTF's, like any product. – Bill Karwin Sep 20 '11 at 21:42
  • @BillKarwin thanks for posting that, I'll have to rethink how this data is stored. – David Sep 20 '11 at 21:55

1 Answers1

7

As long as there are no more than 64 entries in your comma-separated list you can use the MySQL FIND_IN_SET() function.

SELECT * FROM table WHERE FIND_IN_SET( this_id, '$this_id' ) = 0

It's not clear to me whether the commas-separated array is your input data or stored in your table - so you may need to swap the arguments.

(I should point out that constructing a query string using possibly tainted input exposes you to SQL Injection attacks.)

martin clayton
  • 76,436
  • 32
  • 213
  • 198