Return all rows from an array

I’m trying to return a comma separated list of location names from a database.

So far I have the SQL statement that I know works because it returns the correct results when tested in phpMyAdmin.

I’m now trying to get the results returned and printed on screen in php.

The code I have bellow only seems to return the first result.

 $sql = "SELECT user_locations.location_id, location.name AS LocName FROM user_locations INNER JOIN location ON user_locations.location_id=location.id WHERE user_id = '" . $id . "'";
 $query = mysqli_query($conn, $sql);
 while ($row = $query->fetch_assoc()) {
    $array[] = $row['LocName'];
    $comma_separated = implode(",", $array);
    return $comma_separated;
 }  

Answer

You need to remove the implode and return outside your while loop, otherwise your loop will stop after the first value:

$sql = "SELECT user_locations.location_id, location.name AS LocName 
        FROM user_locations 
        INNER JOIN location ON user_locations.location_id=location.id 
        WHERE user_id = '" . $id . "'";
$query = mysqli_query($conn, $sql);
while ($row = $query->fetch_assoc()) {
    $array[] = $row['LocName'];
}  
$comma_separated = implode(",", $array);
return $comma_separated;

Note that you can achieve this result directly in your query using GROUP_CONCAT:

$sql = "SELECT GROUP_CONCAT(location.name) AS LocName
        FROM user_locations 
        INNER JOIN location ON user_locations.location_id=location.id
        WHERE user_id = '" . $id . "'";
$query = mysqli_query($conn, $sql);
$row = $query->fetch_assoc();
$comma_separated = $row['LocName'];
return $comma_separated;

Note that dependent on the source of your $id value, you may need to look into prepared statements to protect yourself from SQL injection.