How to use an array of values from PHP in the 'IN' clause of mysql query?

Question

//get all id's of ur friend that has installed your application
 $friend_pics=$facebook->api( array( 'method' => 'fql.query', 'query' => "SELECT uid FROM user WHERE uid   IN(SELECT uid2 from friend WHERE uid1='$user') AND is_app_user = 1" ) ); // this query work fine
//your top10 friends in app
$result="SELECT * FROM fb_user WHERE user_id IN($friend_pics) ORDER BY oldscore DESC LIMIT 0,10";
db_execute($result);

i want to retrive ten top scorer from my database stored in oldscore but in my second query the array name $friend_pics is not working i guess,plz help me thanks

You'll need to post more of your code, and / or your db schema. There could be a lot of problems here. This isn't enough information. — bonez, Mar 06 '11 at 00:10
Hopefully one of the placeholder-enabled access methods (mysqli or pdo) support a construct like this (they should generally be used anyway). — , Mar 06 '11 at 00:17

score 3 · Answer 1 · answered Mar 06 '11 at 00:25

There is no native support for that. Even the bound parameter APIs do not allow to use arrays for IN clauses. You have to construct the query with some helper code:

$friend_pics = array_map("mysql_real_escape_string", $friend_pics);
$friend_pics = "'" . implode("', '", $friend_pics) . "'";
"SELECT * WHERE user_id IN ($friend_pics)              "

A simpler alternative would be mysqls FIND_IN_SET() however:

$friend_pics = mysql_real_escape_string(implode(",", $friend_pics));
"SELECT * FROM fb_user WHERE find_in_set(user_id,'$friend_pics')     "

score 1 · Answer 2 · answered Mar 06 '11 at 00:21

1

Why don't you convert that array into a string of long list of your values unless array is huge size.

    $ids    = array(2, 4, 6, 8);
$params = implode(",", array_fill(0, count($ids), "?"));

    $result="SELECT * FROM fb_user WHERE user_id IN($params) ORDER BY oldscore DESC LIMIT 0,10";
db_execute($result);

answered Mar 06 '11 at 00:21

yogsma

10,142
31
97
154

What should it do can i get wt i want? – Fawad Ghafoor Mar 06 '11 at 00:25
$friendsAp = implode(",",$friendpics); i already did this but not working – Fawad Ghafoor Mar 06 '11 at 00:26
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home5/roohware/ – Fawad Ghafoor Mar 06 '11 at 00:31
wt should i put in "?" i in my array i have now 30 ids how to put it in "?" thanking u in anticipation – Fawad Ghafoor Mar 06 '11 at 00:42
Did you try what mario told? "?" is just for what value to fill in array. Somehow I feel $result is not getting any values in result of the query you are executing. – yogsma Mar 06 '11 at 00:49
yep i tried but its giving "access denied for user"xxxxxxx n blaw blaw blaw i guess i havent include mysql_real_escape_string in my connection m i rite? – Fawad Ghafoor Mar 06 '11 at 01:13
Probably problem with your mysql ..you don't need different access for mysql_real_escape_string. – yogsma Mar 06 '11 at 01:27

score 1 · Answer 3 · answered Mar 06 '11 at 00:24

1

$friend_pics should be a string containing a comma separated list i.e. "1, 2, 3, 4". If it is not, then your second query will always fail.

answered Mar 06 '11 at 00:24

this work perfectly$friend_pics=$facebook->api( array( 'method' => 'fql.query', 'query' => "SELECT uid FROM user WHERE uid IN(SELECT uid2 from friend WHERE uid1='$user') AND is_app_user = 1" ) );foreach($friend_pics as $status_a) { $status_sql[] = '\''.$status_a['uid'].'\''; } $status = implode(',',$status_sql);$result= "SELECT * FROM fb_user WHERE user_id IN($status) ORDER BY oldscore DESC LIMIT 0,10"; db_execute($result); this is damn rite thaks 4 ur help – Fawad Ghafoor Mar 06 '11 at 23:26

How to use an array of values from PHP in the 'IN' clause of mysql query?

3 Answers3

Linked

Related