CODEWITHSUNDEEP
phpmysqlarraysstring
DobotJr
DobotJr

Reputation: 4049

Get comma separated string from Mysql

I'm saving a string from PHP to MySQL like this..

$groupid = "13, 14, 15, 16"
$write = mysql_query("INSERT INTO table VALUES ('','$groupid')");

I'm then trying to extract data from the table if $a = "15" 

$extract = mysql_query("SELECT * FROM table WHERE ustaffid='$ustaffid' AND groupid='$a'");

How can I easily match what's in $groupid to $a, while extracting it? Can I do that with MySQL?

Upvotes: 0

Views: 231

Answers (5)

gen_Eric
gen_Eric

Reputation: 227190

Expanding on @ecchymose's answer, you can use a regex instead of LIKE.

SELECT *
FROM table
WHERE ustaffid='$ustaffid'
AND groupid RLIKE '(^|, )$a(, |$)'

Note: You may need to escape the last $ with a \.

Note 2: This isn't the optimal solution. You should really have a separate row for each ID (with the ID being the PRIMARY key).

Upvotes: 1

Xint0
Xint0

Reputation: 5379

I don't know if having spaces in the groupid columns would affect the find_in_set MySQL function, and as @PeeHaa notes searching inside a string is slower and is hard to optimize with indexes.

You should really consider modifying the database schema and normalize it so that instead of saving multiple values in a column, use a dependent table and relate multiple rows to the main table for each groupid value. That's what relational databases are for.

Upvotes: 2

ecchymose
ecchymose

Reputation: 673

SELECT * 
FROM table 
WHERE ustaffid='$ustaffid' 
AND groupid LIKE '%$a,%'

But you have to end your string with a comma at the end.

Upvotes: 0

PeeHaa
PeeHaa

Reputation: 72652

If $a contains only one id I would suggest seperating it into multiple records, because in string search (/ select) is slower.

Upvotes: 2

Joe Stefanelli
Joe Stefanelli

Reputation: 135729

You can use the find_in_set function here.

SELECT * 
    FROM table 
    WHERE ustaffid='$ustaffid' 
        AND FIND_IN_SET('$a', groupid) > 0

Upvotes: 2

Related Questions