Filter SELECT query with PHP?

[sncdsl]

Hello

I have two tables in mySQL, cds and tracks, both connected by catalog number. I now try to create a webpage, which lists the output as follows:

Artist1: Title1

CD1.Track1
CD1.Track2
CD1.Track3...

Artist1: Title2

CD2.Track1
CD2.Track2
CD2.Track3...

I came up with this query string:

 
$sql = "SELECT
cds.catnr,
cds.artist,
cds.title,
tracks.catnr,
tracks.trackname
FROM
cds
LEFT JOIN
tracks
ON
tracks.catnr = cds.catnr
WHERE
cds.artist = '$artist'
GROUP BY
cds.catnr
";
$result = mysql_query($sql) OR die( mysql_error ()); 
while ($row = mysql_fetch_array($result))

But the Result only puts out the first track of each cd.
If I leave off the GROUP BY statement, I get each Artist and Title listed to every track. How would I filter this array, so I get the above mentioned result?

Thanks for your input.

[coopster]

$sql = "SELECT
cds.catnr,
cds.artist,
cds.title,
tracks.catnr,
tracks.trackname
FROM
cds
LEFT JOIN
tracks
ON
tracks.catnr = cds.catnr
WHERE
cds.artist = '$artist'
ORDER BY
cds.catnr,
cds.artist,
cds.title,
whatever_your_CD1-type_name_field_is,
tracks.trackname
";

[jatar_k]

Welcome to WebmasterWorld sncdsl

[sncdsl]

thanks for the fast reply. but the order was not the problem. if i print out the result, i get this:

artist1: title1 - cd1.track1
artist1: title1 - cd1.track2
artist1: title1 - cd1.track3

artist1:title2 - cd2.track1
artist1:title2 - cd2.track2...

but i want the tracks to be in a seperate list. how can i read out the query and only get the tracks according to one cd?

[coopster]

You now have the data set, you need to condition/select the data for display in a loop:

$result = mysql_query($sql) OR die( mysql_error ()); 
while ($row = mysql_fetch_array($result)) { 
if ($row[catnr]!= $catnr AND $row[artist]!= $artist AND $row[title]!= $title) { 
print "<b>$row[artist]</b>: $row[title]<br />"; 
} 
print "$row[trackname]<br />"; 
$catnr = $row[catnr]; 
$artist = $row[artist]; 
$title = $row[title]; 
} 

Careful with your query string though, looks like you are pulling the catnr field twice in your select statement.

[sncdsl]

coopster thank you very much.
this was exactly, what i was looking for.
and thanks for the hint, i already renamed the second catnr field.

[jatar_k]

if you have fields with the same name it doesn't really effect much.

cds.catnr
tracks.catnr

those are not the same and can be referenced by adding the table name or by usng a numeric index.

[coopster]

Yeah, that's true, jatar_k. Just redundant data in the dataset at that point.

Hey, sncdsl, what version of MySQL are you running? If you are running 4.1 or higher, I've always wanted to try this...

MySQL version 4.1 also has a GROUP_CONCAT [mysql.com] function that I always wanted to try. It will group data into a string separated by commas; you can specify a different separator if necessary. Here is another way I was thinking you could use, if you are on MySQL 4.1 or higher, but I've never tested it, so...

$sql = "SELECT
cds.catnr,
cds.artist,
cds.title,
GROUP_CONCAT(tracks.trackname ORDER BY tracks.trackname)
FROM
cds
LEFT JOIN
tracks
ON
tracks.catnr = cds.catnr
WHERE
cds.artist = '$artist'
ORDER BY
cds.catnr,
cds.artist,
cds.title";

$result = mysql_query($sql) OR die(mysql_error());
while ($row = mysql_fetch_array($result)) {
print "<b>$row['artist']</b>: $row['title']<br />";
$tracknames = array($row['trackname']);
foreach ($tracknames as $trackname) print "$trackname<br />";
}

[sncdsl]

unfortunately i'm running MySQL 4.0.15.
in my research i stumbled across the GROUP CONCAT function, but since it's only available in 4.1 i had no chance of using it.

it looks much better than the method i have to use now.

is MySQL 4.1 stable and usable already?

[jatar_k]

[mysql.com...]

MySQL 4.0 -- Production release (recommended)
MySQL 4.1 -- Alpha release (use this for new development)

doesn't look like it.