Passing String Array in sql query using PHP

Question

I have two tables is sql 1) friends_details and 2) user_info. Now using the below query am getting the list of friends of that particular using $number = is coming from app

 $sql = "select friends from user_info WHERE user_number ='$number' ";;
$result = mysqli_query($conn, $sql) or die("Error in Selecting " . mysqli_error($conn));

//create an array
$emparray = array();
while($row =mysqli_fetch_assoc($result))
{
    $emparray[] = $row;
}

now $emparray have names of friends in String fromat. Now i want to pass this array into another query so that i can find the details of these friends. And Can't find the way to do. I have tried this code.

$friendsArray2 = "'" .implode("','", $emparray  ) . "'"; 
$query120      = "SELECT * FROM friends_deataisl WHERE name IN  ( $friendsArray2 )";
echo $query120;

and the result of echo $query120 is SELECT * FROM friends_deatails WHERE name IN ( 'Array','Array' )

So this means values are not going in the query. Any help would be appreciated. And i have already checked $emparray is not empty it contains the name that means first query is right but the problem is in second query.

Because mysqli_fetch_assoc returns array, you must fill emparray like $emparray[] = $row['friends'];. — maximkou
– maximkou, Commented May 25, 2017 at 7:36

Barmar · Accepted Answer · 2017-05-25 07:45:05Z

$emparray is a 2-dimensional array, not an array of strings, because $row in the first loop is an associative array.

You need to change the first loop to do:

$emparray[] = $row['friends'];

But you could just combine the two queries into a JOIN:

SELECT DISTINCT fd.*
FROM friend_details AS fd
JOIN user_info AS ui ON fd.name = ui.friends
WHERE ui.user_number = '$number'

Also, the column name friends makes me suspect that this is a comma-separated list of names. Using = or IN won't work with that -- it will try to match the entire list with friend_details.name. It's best to normalize your database so you don't have lists in a column. If you can't fix that, you need to use FIND_IN_SET to join the tables:

SELECT DISTINCT fd.*
FROM friend_details AS fd
JOIN user_info AS ui ON FIND_IN_SET(fd.name, ui.friends)
WHERE ui.user_number = '$number'

And in your original code, you'll need to explode $row['friends']:

$emparray = array_merge($emparray, explode(',', $row['friends']));

Collectives™ on Stack Overflow

Passing String Array in sql query using PHP

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related