Variables into a different SELECT

Anon

I have a select statement:

$artist = mysql_query("SELECT DISTINCT db_artist from music WHERE db_artist LIKE 'A%'");

I want to use that variable in another select statement where it would select the album for that artist:

$album = mysql_query("SELECT DISTINCT db_artist from music WHERE db_artist <VARIABLE FROM ARTIST SELECT>");

and then use that variable in to get the songs:

$album = mysql_query("SELECT DISTINCT db_artist from music WHERE db_artist <VARIABLE FROM ALBUM SELECT>");

Am I 1/2 way close to it?
Your help is very much appreciated.
Thanks.

Anon

Yep, yer almost there, it's just a matter of converting the returned values from the query to a usable form, you'd do this by either using mysql_fetch_row() or mysql_fetch_array() or one other the other fetch functions.

I'll toss in an example, this example will use the first item in the list from each of the previous queries in the next, in real life you'd probably want to go through a set of selection boxes or something to narrow this down:

~example~
$ret = mysql_query("SELECT DISTINCT db_artist from music WHERE db_artist LIKE 'A%'");

$row = mysql_fetch_array($ret);

$ret = mysql_query("SELECT DISTINCT db_artist from music WHERE db_artist='".$row["db_artist"]."'");

The heart of the whole thing is the mysql_fetch_array() function wich converts the returned data into an easy to reference array consisting over every item that was requested from the database for the current row. Each time the function is called it advances the current row by one, so to display all results you would call it in a while loop like so:

$ret = mysql_query("SELECT DISTINCT db_artist from music WHERE db_artist LIKE 'A%'");

while($row = mysql_fetch_array($ret))
{
echo "Artist name: ".$row["db_artist"]."<br>";
}

Barring from me making any typo's this is exactly how the big boys do it.

Anon

This is the code that I have:
<?php

$x=1;
$artist = mysql_query("SELECT DISTINCT music_artist from music WHERE
music_artist LIKE 'A%'");
while ($myrow=mysql_fetch_array($artist))
{

$art_info = $myrow["music_artist"];
printf('<param name = menu1i1i%s value = "%s">
', $x, $art_info);

$album = mysql_query("SELECT DISTINCT music_album from music WHERE
music_artist = '".$myrow["music_artist"]."'");
$y=1;
while ($myrow=mysql_fetch_array($album))
{

$alb_info = $myrow["music_album"];
printf('<param name = menu1i1i%si%s value = "%s">
', $x, $y, $alb_info);

$y++;
printf('
');
}

$x++;
printf('
');
}
?>

the code is used to populate an applet.
the printf's are used to print the applet tags and to space it out when the html is created.

I inserted the code that you sudgested to me but it never made any differance to what it was before.

I get the artist to come out in order ok...but I get the cd's of both artist for each artist and all the songs from the cd's for each cd.

So I would get Beatles and Beasty Boys fine.
but under Beatles I would get their albums and the Beasty Boys albums. The same holds true for the Beasty Boys. Then I would get all the songs from those albums under each of the Beatles cd's and the Beasty Boys cd's.

I'm starting to get into a panic now...I need this done by the end of the week. lol

Thanks,
Jaymz

Anon

Oh, heh, so it's all in the same database then, ok, now that makes more sense, I thought that was just a typo or something. Sorry about that.

Anyways my bains not too clear right now, still recovering from my B-day stuff lastnight so I'll look at your code again tomorow afternoon with a clear head and see what I can come up with.

Have you ever thought of separating the diferent types of information in to separate databases? You'd save on space and database size by cross referencing the whole system using 3 tables instead of 1, you also would then not have to type each atists information more than once, or each album name/information.