Selecting results as a comma-separated string

Question

The below code is working correctly, however, I am not sure if the combination of while, foreach and implode is really necessary here or if the same can be achieved much simpler.

What I do here is fetching some data (text) from a db and the only thing I need is the results as a comma separated string.

Now even though this is working I was wondering if there is a direct way to get the results as a comma separated string since the arrays here are not needed for anything else.

$stmt = $conn->prepare("SELECT " . $languageFrm . " FROM TranslationsMain WHERE location LIKE ? ORDER BY sortOrder, " . $languageFrm);
$stmt->bind_param("s", $location);
$stmt->execute();
$result = $stmt->get_result();
$strTranslations = array();
while($arrTranslations = $result->fetch_assoc()){
    $translations[] = $arrTranslations;
}
foreach($translations as $translation){
    array_push($strTranslations, $translation[$languageFrm]);
}
echo implode(",", $strTranslations);

Answer 1

Instead of two loops + an array + implode, you could use a single loop, appending to a string the elements and a comma. For a non empty result, there will be a trailing unnecessary comma, so chop it off at the end.

Trying to move the formatting logic into the query is not a great idea for two reasons:

• a database is not a formatting tool. Good separation of concerns suggests to avoid using the database for such purpose
• a query with string concatenation will most probably sacrifice portability. Your current SQL is nicely portable: it will work fine in probably any rdbms. It would be good to keep it that way

Answer 2

Using one of the below queries removes the need to iterate over the result sets to construct the CSV strings. When a single result is expected, a ->fetch() instead of ->fetchAll() would do.

Conclusion: implode goes away, while and for loops go away the only iteration needed would be in case of multiple CSV result sets.

SELECT GROUP_CONCAT(columnName) FROM Table1; # for single column

SELECT CONCAT(columnA,",",columnB) from Table2; # for multi cols

You might have issues creating prepared statements for this; I don't know.

example for the first query:

<?php

$conn = new PDO("mysql:host=localhost;dbname=test", "root", "");

$stmt = $conn->query("SELECT GROUP_CONCAT(columnName) FROM Table1");

$result = $stmt->fetch(PDO::FETCH_ASSOC);

var_dump($result);