CODEWITHSUNDEEP
phpmysql
RobAtStackOverflow
RobAtStackOverflow

Reputation: 525

Mysql IN clause only retrieving 1 row

Edit: Thank you Baylor Rae and JvBerg - Your answers/comments helped solve the problem. I had to remove the quotes - Which I didn't realise. Thank you all for your assistance.

I'm trying to select all data from the 'alerts' table where ownerid='$friend_ids' using the IN clause. The problem I'm having (despite numerous googling and code changing) is it's only retrieving one row.

$fid=$ClassUsers->Friendids($user_id);
echo $fid;

Which returns (as expected) 1,2,3.

However when trying:

$sql="SELECT * FROM alerts WHERE ownerid IN('$fid')";
echo mysql_num_rows($db->query($sql));

Returns "1" row only.

My alerts table:

Alerts table

Does anyone know why? Or how to resolve this? I've literally spent all night yesterday, the day before and a few hours today googling and trying different ways to query this. Thanks.

Upvotes: 2

Views: 1778

Answers (3)

Mike Brant
Mike Brant

Reputation: 71422

If $fid is an array, you will need to do something like

$fid_sql = implode(','. $fid);
$sql="SELECT * FROM alerts WHERE ownerid IN( $fid_sql )";

Note you shouldn't need single-quotes around each value here if they are integer fields in the DB.

If $fid is a string = "1, 2, 3", then all you need to do is get rid of the single-quotes around $fid in your query.

Upvotes: 1

Phil
Phil

Reputation: 420

Try quoting your values

IN ('1', '2', '3')

Otherwise it looks for the value that is literally '1, 2, 3' not '1' or '2' or '3'.

Upvotes: 1

JvdBerg
JvdBerg

Reputation: 21866

It depends on the value of $fid:

$fid = '1, 2, 3';
$sql="SELECT * FROM alerts WHERE ownerid IN( $fid )";
echo mysql_num_rows($db->query($sql));

Will return all 3 rows.

Upvotes: 1

Related Questions