Problem with sort data

bumbar

Hallo!

Here is a little tricky script:

<?php
mysql_connect ("localhost", "", "");

mysql_select_db ("books");

$select = "
SELECT
a.name AS author,
b.name AS book,
a.id AS author_id,
b.name AS book_id

FROM
		author AS a,
		book AS b,
		book_author AS ba

WHERE 
		a.id = ba.author_id 
		AND
		b.id = ba.book_id

$query = mysql_query ("$select") or die (mysql_error());

echo "<table border=1>";

while ($row = mysql_fetch_array ($query)) {
echo "<tr><td>" . $row[0] . "</td><td>" . $row[1] . "</td></tr>";
}

echo "</table>";

This output:

Brad Phillips - MySQL in a bucket
Don Charles - MySQL in a bucket
Bob Silver - Databases for Delinquents
Brad Phillips - PHP Professional
Brad Phillips - Design Patterns

How can I sort output like this:

Brad Phillips - MySQL in a bucket , PHP Professional, Design Patterns

Don Charles - MySQL in a bucket

Bob Silver - Databases for Delinquents

Thanks!

Piranha

There are 2 good ways that I know to do this. The first is to order the result by author and then handle the results in PHP. The second is to do a list in the database query, but I don't know how to do this so I can't show that one.

Note that this is not a finished code, it is just to let you understand how to do it.

$sql = "SELECT id, name FROM tables WHERE something ORDER BY table.id";
$query = mysql_query ("$sql") or die (mysql_error());

$oldid = 0; // Using this variable to know if it is the same id or not. Setting oldid to 0 to have a starting value that won't be in the database.

while ($row = mysql_fetch_array ($query)) {
    if ($row['id'] != $oldid)  // If it is not the same id we should do some things
    {
        echo "<br /> $row['name'];
        $oldid = $row['id'];
    }
    else // If it is the same id we should only echo the name
    {
        echo ", $row['name'];
    }
}

You might have to do some things before going into the while-loop and some things after (new row after for example), use mysql_num_rows to see if there is a result and if there is then do these things.