How do I use an array from an sql database in an sql statement

Question

I'm probably going in the complete wrong direction but I want to get an array of data from the database and then use that array in another SQL statement.

Here is my current code:

  $result = mysql_query($query_friend_club_count) or die(mysql_error());

  while($row = mysql_fetch_array($result)){
  $row['idPerson']. " - ". $row['idFriend'];
  $idFriend = $row['idFriend'];
  $array = $idFriend',';

  $query_friends = "SELECT * FROM whosout WHERE idPerson IN ('$array')";
  $query_friends_run = mysql_query($query_friends);
  $friendCounter = mysql_num_rows($query_friends_run);

  echo $friendCounter;
  }

I'm getting a error of:

syntax error, unexpected T_CONSTANT_ENCAPSED_STRING

Don't know if that helps.

Any suggestions would be really helpful as I've been stuck on this for ages!!

Are the two tables you're querying in the same database? This seems like it should be a single query with a JOIN instead of monkeying around with building arrays and performing multiple queries. Also, is that second query supposed to be *inside* the `while` loop? — Cᴏʀʏ, Sep 30 '14 at 14:55
Please, [don't use `mysql_*` functions in new code](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). *They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation)*. See the [red box](http://uk.php.net/manual/en/function.mysql-connect.php)? Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://us1.php.net/pdo) or [MySQLi](http://us1.php.net/mysqli). [This article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide which. — Jay Blanchard, Sep 30 '14 at 14:59
Your error isn't on line `$array = $idFriend',';`? Change to `$array = $idFriend.',';` — Carlos, Sep 30 '14 at 15:00

Kevin · Answer 1 · 2014-09-30T15:07:03.563

4

You can also group them first instead of quering them each:

$array = array();
while($row = mysql_fetch_array($result)){
    $array[] = mysql_escape_string($row['idFriend']); // escape just to be sure
}

$array = "'".implode("','", $array) . "'"; // comma separated values

$query_friends = "SELECT * FROM whosout WHERE idPerson IN ($array)";
$query_friends_run = mysql_query($query_friends);
$friendCounter = mysql_num_rows($query_friends_run);
echo $friendCounter;

Or if this column is an INT, no need for quotes:

$array = implode(', ', $array);

edited Sep 30 '14 at 15:07

answered Sep 30 '14 at 14:56

Kevin

41,694
12
53
70

1

Good answer. It's also worth noting that if the ID is not numeric the values in the array should be escaped. Complex injection attacks could use input that is escaped to get into the DB, then exploit queries like this. Alternatively it could just cause an error if an innocent value contains the string delimiter. – Mitch Satchwell Sep 30 '14 at 15:00

score 0 · Answer 2 · answered Sep 30 '14 at 14:54

0

You made a small error:

 $array = $idFriend . ','; //There should be a period here.

answered Sep 30 '14 at 14:54

Kunal Gupta

449
3
22

still, it'll be wrong. there shouldn't be comma after last word. You might need to `trim` your string before putting in query. – RNK Sep 30 '14 at 14:56
This is one of many erros, both syntax and logic. Pointing out just this error wont help – Steve Sep 30 '14 at 14:56
Thanks for your reply! it now kind of works im getting four 1s whats the best way to count the ones up? – Jordanbaggs Sep 30 '14 at 14:57
Actually, there shouldn't be a comma after all. – Kunal Gupta Sep 30 '14 at 14:59

score 0 · Answer 3 · answered Sep 30 '14 at 14:58

Correct way is:

$arr = array();
while($row = mysql_fetch_array($result)){
  $idFriend = $row['idFriend'];
  $array[] = $idFriend;
}

// then implode that array using IN sql statement.
$query_friends = "SELECT * FROM whosout WHERE idPerson IN (implode(','$arr))";

How do I use an array from an sql database in an sql statement

3 Answers3