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ᴏʀʏ
– Cᴏʀʏ, Commented Sep 30, 2014 at 14:55
Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi. This article will help you decide which. — Jay Blanchard
– Jay Blanchard, Commented Sep 30, 2014 at 14:59
Your error isn't on line $array = $idFriend',';? Change to $array = $idFriend.','; — Carlos
– Carlos, Commented Sep 30, 2014 at 15:00

Kevin · Accepted Answer · 2014-09-30 15:07:03Z

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, 2014 at 15:07

answered Sep 30, 2014 at 14:56

Kevin

41.9k12 gold badges57 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mitch Satchwell Over a year ago

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.

Kunal Gupta · Accepted Answer · 2014-09-30 14:54:38Z

0

You made a small error:

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

answered Sep 30, 2014 at 14:54

Kunal Gupta

4493 silver badges22 bronze badges

4 Comments

RNK Over a year ago

still, it'll be wrong. there shouldn't be comma after last word. You might need to trim your string before putting in query.

Steve Over a year ago

This is one of many erros, both syntax and logic. Pointing out just this error wont help

Jordanbaggs Over a year ago

Thanks for your reply! it now kind of works im getting four 1s whats the best way to count the ones up?

Kunal Gupta Over a year ago

Actually, there shouldn't be a comma after all.

izpoo · Accepted Answer · 2014-09-30 14:58:21Z

0

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))";

answered Sep 30, 2014 at 14:58

izpoo

112 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related