Sql And An Array

[ebolt007]

I have a query where I need to select an ID from a row where a column has an array.

for instance I have

ID Name photos

1 My Name 300,302,303,

2 2nd Name 304,405,309,

 

How would I grab the ID where the "photos" contains 405,?

But it can't select where it contains 1405 or 4054, or anything like that so I can't use LIKE %405% to select this.

 

$sql = "SELECT ID FROM user_images WHERE photos LIKE '%405%'";
					 $sql_query = mysql_query($sql);
					 $true_row = mysql_fetch_assoc($sql_query);

[Jessica]

Normalize your data. You should never store an array of values in one column. You need a separate table for that.

[ebolt007]

Naw, I have a ton of other tables for things like that, there has to be a way to pull an array out this way. Anyone with any ideas? I know how to easily pull it by grabbing it, exploding it by the , and using a foreach. I also put them in other tables with these id's, but for commenting and other purposes to keep them together I have to do it this way for this table.

 

It's as easy as this to grab them, but what about searching right inside the query?

$uploads= $true_row['photos'];
$s = explode(",", $uploads);
foreach($s as $a)
{
echo "$s";
}

Edited October 5, 2012 by ebolt007

[PFMaBiSmAd]

http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_find-in-set

[ebolt007]

Awesome thanks! And yes I know it's not always ideal and never should be used for beginers to keep an array inside a column but in a very complex program sometimes it's a necassary evil to do for organization.

[Pikachu2000]

On the contrary, storing multiple pieces of data in a single field is something done almost exclusively by beginners, and nearly never necessary.

Edited October 5, 2012 by Pikachu2000

[Jessica]

There's always a better way.

[fenway]

Actually, there are many reasons to store serialized data -- but very few beginners have *good* reasons to do so.

 

Short answer: if you're even going to need to query, join on, or change, an individual piece of this 'array', then it should not be an array.