problem accessing 2 tables

[johnny_o_neill]

Hey everyone, I'm having some trouble with this code i got to access some info from my database, the problem is that i can't access the table i'm querying during the while function. I'm new to php and mysql, but i can't figure out why it won't query the second table, everything else comes out fine. I appreciate any help you can offer

Johnny O

Here's my code:

PHP Code:

<?php
 include("dbinfo.php");

 mysql_connect($host, $username, $password);
 @mysql_select_db($database) or die("Unable to select database from index.php");

 $query="SELECT s_id, song_title, views, a_id FROM songs ORDER BY views DESC";
 $result=mysql_query($query);
 mysql_close();

 include("scripts/title.php");

 echo "<script>
 function hilight(obj, Color) {
     obj.style.backgroundColor=Color
 }
 </script>\n";

 include("scripts/header.php");
 include("scripts/menu_side.php");
 include("scripts/menu_list.php");

echo "<h2 align=\"center\">Welcome to Johnny O's Guitar Tab Database</h2>
  <center>
  <table border=\"0\" cellpadding=\"0\" cellspacing=\"1\" style=\"border-collapse: collapse\">
    <tr>
      <td colspan=\"2\" background=\"http://guitar-tabs.johnny-o.net/images/bg_4.gif\"><h6>TOP TEN VIEWED SONGS</h6></td>
    </tr>
    <tr>
      <td width=\"400\"><b>ARTIST - SONG</b></td>
      <td width=\"100\" align=\"right\"><b>VIEWS</b></td>
    </tr>
    ";

 $num=0;
 while($num < 10) {
   $s_id=mysql_result($result,$num,"s_id");
   $song_title=mysql_result($result,$num,"song_title");
   $views=mysql_result($result,$num,"views");
   $a_id=mysql_result($result ,$num,"a_id");

   $query_new="SELECT artist_name FROM artists WHERE a_id = '$a_id'";
   $result_new=mysql_query($query_new);
   mysql_close();

   $name=mysql_result($result_new,0,"artist_name");

   echo "<tr onMouseOver=\"hilight(this, '404040')\" onMouseOut=\"hilight(this, '')\">
      <td><a href=\"artist.php?a_id=$a_id\">$name</a> - <a href=\"song.php?s_id=$s_id\">$song_title</a></td>
      <td align=\"right\">$views</td>
     </tr>
   ";

   $num++;
 }

echo "</table></center>";

include("../scripts/footer.php"); ?>

[JGross]

pull your information out from both tables in the same query:

$query = "SELECT s.s_id, s.song_title, s.views, s.a_id, a.artist_name FROM " . songs . " s, " . artists . " a WHERE a.a_id = '$a_id' ORDER BY s.views DESC"

something of that nature should make things less complicated for you, cant guarentee it will work tho, im not expert myself.

[Pig]

You use mysql_close(), so you no longer have a connection to mysql. If you want to combine it all into 2 queries, make sure you use a join.

[mitchind]

With what you you're trying to do you can do an implicit join by using something like this ...

SELECT songs.s_id, songs.song_title, songs.views, songs.a_id, artists.artist_name FROM songs, artists WHERE songs.a_id = a_id ORDER BY songs.views DESC