How to get comma separated values from database

Question

I have the following mysqli query, and I want to implode the array that's outputted into a (1,2,3,4) type format.

Here's the query and the asoc array code:

$user_categories = mysqli_query($connect, "SELECT sub_cat FROM subscriptions WHERE sub_user_id = '$user_id'");

$category_ids = mysqli_fetch_all($user_categories,MYSQLI_NUM);

print_r($category_ids);

$category_ids = implode(", ",$category_ids);

I then get the following output, and I can't seem to isolate the values...

Array ( 
[0] => Array ( [0] => 5 ) 
[1] => Array ( [0] => 8 ) 
[2] => Array ( [0] => 4 ) 
[3] => Array ( [0] => 2 ) 
)

Apologies if I'm missing something really obvious here. I've been trying to fix this for a while, and due to my lack of PHP experience, I'm not 100% sure what to search for.

I've also tried a simple implode using the results of the $user_categories query, following the instructions of other StackExchange answers I've seen on the topic, but got nothing (code below):

$user_categories = mysqli_query($connect, "SELECT sub_cat FROM subscriptions WHERE sub_user_id = '$user_id'");

$category_ids = implode(", ",$user_categories);

echo $category_ids;

I'm not sure what you expect as output, can you please write an example? — Martin Hučko, Jul 10 '16 at 14:26

score 4 · Accepted Answer · answered Jul 10 '16 at 14:26

4

$category_ids is an array of arrays (rows), so you can't just implode it. You need to fetch the first value from each row and implode that.

PHP 5.5+ solution:

Using array_column():

$category_ids = implode(', ', array_column($category_ids, 0));

echo $category_ids;

Output:

5, 8, 4, 2

PHP 5.3+ solution:

Subtitute array_map() for array_column():

$category_ids = implode(', ', array_map(function ($row) { return $row[0]; }, $category_ids));

echo $category_ids;

Output:

5, 8, 4, 2

answered Jul 10 '16 at 14:26

Shira

6,392
2
25
27

Thank you, you're a life saver! Accepted and +1 for clear 5.3 + 5.5 answers :) – tristanojbacon Jul 10 '16 at 14:36
Sometimes I find answers who do not have the deserved upvotes. Nice solutions Shira – John Oct 26 '16 at 04:19

score 2 · Answer 2 · answered Jul 10 '16 at 14:28

$category_ids that you get from mysqli_fetch_all is array of arrays and the desired result cannot be retrieved just by passing it to implode. However, this should do the job:

$category_ids = mysqli_fetch_all($user_categories,MYSQLI_NUM);

$category_ids_imploded = implode(', ', array_map(function ($entry) {
  return $entry['0'];
}, $category_ids));

Alternatively, in case you're using PHP 5.5 or higher, you can make it a bit less messy by using array_column:

$category_ids_imploded = implode(', ', array_column($category_ids, 0));

score 0 · Answer 3 · answered Jul 10 '16 at 15:26

Mysql solution

$sql = "SELECT group_concat(sub_cat) FROM subscriptions WHERE sub_user_id = '$user_id'"
$result = mysqli_query($connect, $sql);
$category_ids = mysqli_fetch_row($user_categories)[0];

PDO solution (preferred)

$sql = "SELECT group_concat(sub_cat) FROM subscriptions WHERE sub_user_id = ?"
$stmt = $connect->prepare($sql);
$stmt->execute([$user_id]);
$array = $stmt->fetchAll(PDO::FETCH_COLUMN);
$category_ids = implode(",", $array);

How to get comma separated values from database

3 Answers3

PHP 5.5+ solution:

PHP 5.3+ solution:

Mysql solution

PDO solution (preferred)

Linked

Related