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
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