How to ORDER a mysql_query from another database. ?

MichaelHe

I have a simple database with CD's and songs.

I have a CD base with info about the CD
I have a song base with info about the songs.

And then I have a tracklist base with info about which songs is on which CDs.

I have these codes in my Song file

$gettrack=mysql_query("select * from DiscographyTRACKLIST WHERE `Song`='$idsong'");
while ($row = mysql_fetch_array($gettrack)){
$cd=$row['CD'];

  $getlist=mysql_query("select * from DiscographyCD WHERE `CDid`='$cd' LIMIT 1");
  while ($row = mysql_fetch_array($getlist)){
  $cdid=$row['CDid'];
  $cdtitel=$row['Titel'];
  $release=$row['Release'];
  $year = substr($release, 0, 4);

?><tr> 
    <td width="200"><div align="right"><?echo $year;}?></div></td>
    <td width="300"><a href="DiscographyCD.php?ID=<?echo $cdid;?>"><?echo $cdtitel;?></a></td>
  </tr>
<?;};}

This works fine and it lists the CD's that contains the specific song.

But how do I list it in another order. ??

If I put a ORDER BY in the first mysql_query I can't order it by ex. year, since the year info is in the CD base and not in the tracklist base.
And I can't ORDER BY in the second mysql_query since this just gets the info from the one CD the first mysql_query finds.

Is it possible with some codes, or do I have to do it all over, with completelly different coding. ?

Roger_Ramjet

You need to learn the terminology: these are not 'bases' but tables in a database
You can easily select data from multiple tables by using JOINS : you do not have to select data from 1 table and then run umpteen queries on the second and third tables
You can order by as many columns as you like, they will process in the same order as the you list them: ORDER BY year, name would give you a list ordered by year and then alphabetically within each year.

Yes, you do have to do it over again, but then you will learn a lot and hopefully learn to 'think more and code less'.

MichaelHe

I am still new in php coding, and I'm learning all the time

The problem of being a newby, is that when you get the tools like the JOINS command, which I didn't knew, I can usually get it to work. But it's very difficult to find these commands. The php manual is not good for newbies. If you search for something in it, you never find the command to use. Then I ask in here, and I get a command like JOINS that does the trink. But this is not easy to find in the manual, if you don't know the name of the command to use.

But know I have the tool to go on, and I will read about this JOINS, and get it to work.
Thank a lot.

Roger_Ramjet

There are lots of resources around to help you. I suggest that you start with the free sample chapters from Kevin Yank's book . The have a look at some of the articles here at PHPBuilder . You can also google for tutorials on SQL or RELATIONAL DATABASES.