How can I merge values of existing rows

Question

I have to get all results and push to HTML table, but existing rows have to be combine to 1 row.

This is my table

id   domain       php_version
-----------------------------
1    localhost    5.5.30
2    live         7.05
3    localhost    5.5.30
4    localhost    5.5.30

and the code which output the html table is:

// Prepare query
$stmt = $mysqli->prepare("SELECT * FROM domains ORDER BY domain ASC LIMIT 10");
// Execute the query
$stmt->execute();
// Bind Parameters
$stmt->bind_result($id, $domain, $php_version);

<?php while ($stmt->fetch()) : ?>
<tr class="row-id-<?php echo $id; ?>">
    <td class="id"><?php echo $id; ?></td>
    <td class="domain"><?php echo $domain; ?></td>
    <td class="php_version"><?php echo $php_version; ?></td>
</tr>
<?php endwhile; ?>

The output looks like this:

And I just want to be like this:

I just want to combine values of dublicated domains in one row/column

Thank you very much!

If you order by `domain`, then your real output differs from what you show us. — u_mulder, Sep 16 '16 at 15:08
http://stackoverflow.com/questions/13451605/how-to-use-group-concat-in-a-concat-in-mysql may help. — lshettyl, Sep 16 '16 at 15:17

score 0 · Answer 1 · answered Sep 16 '16 at 15:29

First get the results out of mysql and into a regular PHP array (called $array in my code), and then this snippet will do what you want:

function sort_by_php_version($a, $b)
{
    if ($a["php_version"] == $b["php_version"]) {
        return 0;
    }
    return ($a["php_version"] < $b["php_version"]) ? -1 : 1;
}

$array = [
["id"=>1, "domain"=>"localhost", "php_version"=>"5.5.30"], 
["id"=>2, "domain"=>"live", "php_version"=>"7.05"], 
["id"=>3, "domain"=>"localhost", "php_version"=>"5.5.30"], 
["id"=>4, "domain"=>"localhost", "php_version"=>"5.5.30"], 
];

usort($array, "sort_by_php_version");

$in_domain = null;
$output_array = array();

for ($i=0; $i<count($array); $i++)
{
    $thisRow = $array[$i];
    $domain = $thisRow["domain"];
    if ($domain == $in_domain) {
        $output_array[count($output_array) - 1]["php_versions"][] = $thisRow["php_version"];
    } else {
        $thisRow["php_versions"] = array($thisRow["php_version"]);
        unset($thisRow["php_version"]);
        $output_array[] = $thisRow;

        $in_domain  = $domain;
    }
}

var_dump($output_array);

You should be constructing your `$array' dynamically from database as opposed it being hardcoded with values here. In which case, when you add more columns, just change the iteration loop to include those columns and do whatever you want with them. It cannot be predict predicted what kind of column changes you will make, so the scope of that is to broad to answer here. — coderodour, Sep 16 '16 at 18:43

score 0 · Accepted Answer · answered Sep 19 '16 at 07:36

0

I Think that I managed to do it with group_concat:

SELECT domain,
       GROUP_CONCAT(DISTINCT php_version SEPARATOR '\n' ) php_versions,
       ...
FROM domains GROUP BY domain

answered Sep 19 '16 at 07:36

tweb

123
3
12

How can I merge values of existing rows

2 Answers2