Selecting results as a comma-separated string

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);

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

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);