Axon Axon - 3 years ago 91
PHP Question

Getting a distinct column value and all values with same name in other column

I have this table here

name_relation names
[ id name head ] [ id - name ]
[ 1 X1 X ] [ 1 - X1 ]
[ 2 X2 X ] [ 2 - X2 ]
[ 3 X3 X ] [ 3 - X3 ]
[ 4 Y1 Y ] [ 4 - Y1 ]
[ 5 Y2 Y ] [ 5 - Y2 ]
[ 6 Y3 Y ] [ 6 - Y3 ]


What i'm trying to get is the following

X: X1X2X3
Y: Y1Y2Y3


so i made this loop

$pre = null;
$ed = "";
while($row = $stmt->fetch()){
$name = $row['name'];
$head = $row['head'];

if ($head != $pre) {
echo $ed;
echo "<div>";
echo "$head:";
}
$ed = "</div>";
$pre = $head;
echo $name;
}
echo $ed;
echo "</div>";


at an empty page it worked perfectly, But when i put it with another code structures i discovered that the web browser was filling and fixing it automatically, But now i get an extra
</div>
at the end of the loop, How can i solve this loop to become

<div>
X:X1X2X3
</div>
<div>
Y:Y1Y2Y3
</div>


The Query i used

SELECT a.name, b.head FROM name_relation p INNER JOIN names b ON a.name = b.name

Answer Source

While it is possible and not that difficult to achieve your desired result using PHP, the problem you have is easier solved in the database, using the appropriate query.

Try this one:

SELECT head, GROUP_CONCAT(name SEPARATOR '') AS all_names
FROM tbl
GROUP BY head

Replace tbl with your actual table name. You probably also want the results in a certain order. Add ORDER BY head for that.

The PHP code now becomes:

while ($row = $stmt->fetch()) {
    echo("<div>{$row['head']}:{$row['all_names']}</div>");
}

That's all.


The PHP-only solution is to keep track of the current value of head, output the closing tag </div> when the value of head changes but not before the first row, output the opening tag <div> also when the value of head changes. Also, display the value of head only once, when it changes but display the value of name for every row. Don't forget to output a closing tag ` after the loop ends but only if iterated at least once (the query returned something).

This sounds difficult but in fact, is quite easy:

// The current value of $['head']
// We assume here NULL is not a valid value for it in the database
// If it happens that you have NULLs in the database in column "head"
// then make sure the query converts it to an empty string. For example:
//     SELECT IFNULL(head, '') AS head ...
$head = NULL;
// Process one row at a time
while ($row = $stmt->fetch()) {
    if ($row['head'] !== $head) {
        // The value of 'head' just changed
        // Display '</div>' but not before the first row
        if ($head !== NULL) {
            // This is not the first row, there is a current value of 'head'
            echo("</div>\n");
        }
        // Display the opening tag '<div>' and new value of 'head', only once
        echo("<div>{$row['head']}: ");
        // Update the current value of 'head'
        $head = $row['head']
    }
    // Display 'name'; there is nothing special here
    echo($row['name']);
}

// After the loop, close the last <div> only if there is one ($head was changed)
if ($head !== NULL) {
    echo("</div>\n");
}

Instead of NULL in the code above you can use some "magic" string (it is called a "sentinel") that is not possible to appear in the head column in your database.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download