将逗号分隔的数据从MySql转换为php数组

Question

What would be a direct way to turn comma separated data from a mysql table, into a simple php array, other than doing it like I'm doing now?

I current do this in a very messy way by selecting data, then concatenating it into a string, which I later use.

I'm using PDO here.

Data is simple

locations
-------------------
US,UK,SE,DE
DE,SE,CA
GB,US,DE,SE
AU,NZ,GB

pdo

$d = $db->prepare('select locations a from destinations');

$d->execute();

$d->bindColumn('a',$a);

$count = $d->rowCount();

if($count >= 1) {
    $b = '';
    while($row = $d->fetch()) {
        $b .= $a.',';
    }
} else {
    $b = 'No records to display';
}

echo $b;

The output I'm hoping to achieve is just a simple array like below, which is all the rows joined together.

Desired output

array('US','UK','SE','DE','DE','SE','CA','GB','US','DE','SE','AU','NZ','GB')

Can you help?

Answer 1

You're almost there. The easiest thing you can do is use explode(',', $b) for the complete string you have now, and frankly, I'm a bit surprised that you couldn't figure that part out after writing all that code.

Instead of concating everying in PHP, you can also concat it in the query using the GROUP_CONCAT function. This will introduce a limit, though, since the result of GROUP_CONCAT can be at most 32k, if I remember correctly.

Below is an alternative approach. Here, each row is fetched and exploded to an array immediately. That array is merged with (appended to, actually) to one big resulting array.

The advantage is that you are not concatting a big string first, which is relatively inefficient, but in practice, I doubt if you can tell the difference in performance.

$d = $db->prepare('select locations a from destinations');

$d->execute();

// An array for results.
$results = array();

$d->bindColumn('a',$a);

$count = $d->rowCount();

if($count >= 1) {
    $b = '';
    while($row = $d->fetch()) {
        // explode each row and append it to the array.
        $results = array_merge($results, explode(',', $a));
    }
} else {
    $b = 'No records to display';
}

// Display the complete array.
print_r($results);