Using PHP/MySQL, how do I display all group items by each group?

Question

The following code lists each of the caritems each line at a time - displaying the carname on each line: (echo mysql_result($result, $i, 'carname') . " > ";

Instead of displaying the carname on each line, how would I make it so it groups the caritems together by carname and then displays the carname once at the top with each of the caritems associated with that carname displayed below... It would then move on to the next carname, grouping items...

    $result = mysql_query("SELECT * FROM cars LEFT JOIN caritems ON cars.carid = caritems.carid ORDER BY cars.carsortorder ASC");

    $total_results = mysql_num_rows($result);       


    for ($i = 0; $i < 1500; $i++)

    {

    if ($i == $total_results) { break; }

    $rowid = mysql_result($result, $i, 'caritems.caritemid'); 

    echo mysql_result($result, $i, 'carname') . "&nbsp; > ";

    echo "<div class='carved' />";

    echo "<strong class='caritemheader'>" . mysql_result($result, $i, 'caritemname') . "&nbsp;" . mysql_result($result, $i, 'caritemprice') . "</strong><br />";

    /* CHECK IF BLOB EXISTS AND IF SO DISPLAY IMAGE */

    if (strlen(mysql_result($result, $i, 'caritempicture'))>0) { echo '<br />' . '<img src="data:image/png;base64,' . base64_encode(mysql_result($result, $i, 'caritempicture')) . '"/>' . '<br />'; }

    echo mysql_result($result, $i, 'caritemdescription') . "<br /><br />";

    echo "</div>";

    }

Please, [don't use `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php), They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://us1.php.net/pdo) or [MySQLi](http://us1.php.net/mysqli). [This article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide. — Jay Blanchard, Oct 22 '14 at 18:27
You have to echo the carname and store that information. Then check it during each loop, if it changes you echo out the new one. — Jay Blanchard, Oct 22 '14 at 18:29
@JayBlanchard Exactly. As long as your "sort by cars.carsortorder" groups the car names. — jiy, Oct 22 '14 at 18:47

score 0 · Answer 1 · answered Oct 22 '14 at 19:14

Here's a basic structure using PDO, and trapping unique carnames as others have suggested. I didn't integrate all of your existing code.

try {       
    $db = new PDO("mysql:host=localhost;charset=utf8", "root", "root");
    $cmd = $db->prepare("
        SELECT * FROM cars 
        LEFT JOIN caritems ON cars.carid = caritems.carid 
        ORDER BY cars.carsortorder ASC;
    ");
    if ($cmd->execute()) {
        $carname = "";
        while ($row = $cmd->fetch(PDO::FETCH_ASSOC)) {
            if ($carname != $row['carname']) {
                echo $row['carname'];
            }
            echo /* everything else */
            $carname = $row['carname'];
        }
    }
} catch (Exception $e) { echo $e->getMessage(); return; }

score -2 · Answer 2 · answered Oct 22 '14 at 18:33

-2

You can first select distinct carids as following:

select distinct carid from cars

Then in a for loop query every car's items individually as following:

select * from caritems where carid=$carid

Here $carids are from the first query.

answered Oct 22 '14 at 18:33

Turdaliev Nursultan

2,538
1
22
28

1

Why would you run a query over and over when you can get all of the data at once? – Jay Blanchard Oct 22 '14 at 18:49
This might be a problem if there are dozens or hundreds of carnames. Multiple calls to the Database should be avoided. – jiy Oct 22 '14 at 18:49
Could you possibly utilize my code to demonstrate your solution? It would be very helpful. – reversehalo Oct 22 '14 at 18:50
Else sort by carid asc then keep echoing items as first car while carids are equal. When carid changes it means new car items has started so you can start next cars items separately. – Turdaliev Nursultan Oct 22 '14 at 18:55
Could you possibly utilize my code to demonstrate your solution? It would be very helpful. – reversehalo Oct 22 '14 at 19:01
Nested queries like this is a terrible anti-pattern and like to kill your database if done in particularly bad cases. – Mike Brant Oct 22 '14 at 19:21

Using PHP/MySQL, how do I display all group items by each group?

2 Answers2