select row from one table where it equals another table's row and count

SpecialFX

In this script it does many things but I need it to count the number of comments from the comment table where showid='$showid' but the information for this isn't coming from another page So I can't use $_REQUEST or anything. So I need a way to get the showid variable for line 12.

<?php
require ('dbconnect.php');
$today= date('m/d/Y');
$query = "SELECT showid FROM shows WHERE today='$today'";
$myresult = mysql_query($query) or die(mysql_error());
$amount = mysql_num_rows($myresult);
mysql_close();
require ('dbconnect.php');
$query = "SELECT showid FROM comment";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)) {
$showid = $row['showid'];
}
mysql_close();
require ('dbconnect.php');
$query = "SELECT * FROM comment WHERE showid='$showid'";
$theresult = mysql_query($query) or die(mysql_error());
$countcomment = mysql_num_rows($theresult);
mysql_close();
require ('dbconnect.php');
$day= date('m/d/Y');
$today = strtotime($day);
$query = "SELECT showid FROM shows WHERE unixtime = '$today'";
$myresult = mysql_query($query) or die(mysql_error());
$showtoday = mysql_num_rows($myresult);
mysql_close();
echo '<font face="Tahoma" size="2">Noiselive is user-driven and dedicated to uniting musicians with the </br>fans of New York!  Bands touring through New York post their shows here so </br>you know whose playing and where.  Check back often for new posts and </br>don\'t forget to comment shows and add your favorite bands as your myspace friend!</font>';
echo '</br>';
echo '</br>';
echo '<font face="Tahoma" size="2" color="#D30D0E">'.$amount.' show(s) were posted today.</font>';
echo '</br>';
if($showtoday != 0) {
if($showtoday == 1) {
echo '<font face="Tahoma" size="2" color="#D30D0E">'.$showtoday.' show is happening <a href="today.php">today</a>.</font>';
}
elseif($showtoday > 1) {
echo '<font face="Tahoma" size="2" color="#D30D0E">'.$showtoday.' shows are happening <a href="today.php">today</a>.</font>';
}
}
require ('dbconnect.php');
$yesterday = time() - (60*60*24);  

$query = "SELECT * FROM shows ORDER BY showid DESC LIMIT 5";
$myresult = mysql_query($query) or die(mysql_error());
$number = mysql_num_rows($myresult); 
echo "<table>\n";
echo '<tr><td><font face="Tahoma" size="2">The five most recently posted shows.</font></td></tr>';
echo'<tr><td>.................................</td></tr>';
echo '<tr><td></br></td></tr>';
while($row = mysql_fetch_array($myresult)) {
$row['venue'] = stripslashes($row['venue']);
$row['address'] = stripslashes($row['address']);
$row['city'] = stripslashes($row['city']);
$row['time'] = stripslashes($row['time']);
$row['bands'] = stripslashes($row['bands']);
$row['price'] = stripslashes($row['price']);
$trimband = wordwrap($row['bands'], 40, "<br />\n");
echo '<tr align="left" valign="top"><td><font face="Tahoma" size="2"><b>' . $trimband. '</b></br>' . $row['time'] . ' on ' . $row['date']. ' @ '.$row['venue'].'</font></td></tr>
    <tr align="left" valign="top"><td><font face="Tahoma" size="2">' . $row['address'] . ', ' . $row['city'] . '</font></td></tr>
<tr align="left" valign="top"><td><font face="Tahoma" size="2">Price ' . $row['price'] . '</font></td></tr>';
if(!empty($row['myspace'])){
echo '<tr align="left" valign="top"><td><font face="Tahoma" size="2"><a href="'.$row['myspace'].'">Myspace Calendar Event Link</a></font></td></tr>';
echo'<tr><td><a href="invite.php?showid='.$row['showid'].'"><font face="Tahoma" size="2"><img src="invite2.gif" border="0"> Invite A Friend </font></a></td></tr>';
if($countcomment ==0) {
echo'<tr><td><font face="Tahoma" size="2"><a href="comment.php?showid='.$row['showid'].'"><img src="comment2.gif" border="0"> 0 Comments </font></a></td></tr>';
}
elseif($countcomment ==1){
echo'<tr><td><font face="Tahoma" size="2"><a href="comment.php?showid='.$row['showid'].'"><img src="comment2.gif" border="0"> 1 Comment </font></a></td></tr>';
}
elseif($countcomment > 1) {
echo'<tr><td><font face="Tahoma" size="2"><a href="comment.php?showid='.$row['showid'].'"><img src="comment2.gif" border="0"> '.$countcomment.' Comments </font></a></td></tr>';
}
echo'<tr><td><font face="Tahoma" size="2"><a href="http://www.mapquest.com/maps/map.adp?searchtype=address&country=US&addtohistory=&searchtab=home&formtype=address&popflag=0&latitude=&longitude=&name=&phone=&level=&cat=&address=' . $row['address'] . '&city='.$row['city'].'&state=NY&zipcode=">Map</a> | <a href="javascript:popUp(\'printshow.php?showid='.$row['showid'].'\')" >Print</a> | <a href="spam.php?showid='.$row['showid'].'">Report Spam</a></font></td></tr>';
echo'<tr><td>.................................</td></tr>';
} else {
echo'<tr><td><a href="invite.php?showid='.$row['showid'].'"><font face="Tahoma" size="2"><img src="invite2.gif" border="0"> Invite A Friend </font></a></td></tr>';
if($countcomment ==0) {
echo'<tr><td><font face="Tahoma" size="2"><a href="comment.php?showid='.$row['showid'].'"><img src="comment2.gif" border="0"> 0 Comments </font></a></td></tr>';
}
elseif($countcomment ==1){
echo'<tr><td><font face="Tahoma" size="2"><a href="comment.php?showid='.$row['showid'].'"><img src="comment2.gif" border="0"> 1 Comment </font></a></td></tr>';
}
elseif($countcomment > 1) {
echo'<tr><td><font face="Tahoma" size="2"><a href="comment.php?showid='.$row['showid'].'"><img src="comment2.gif" border="0"> '.$countcomment.' Comments </font></a></td></tr>';
}
echo'<tr><td><font face="Tahoma" size="2"><a href="http://www.mapquest.com/maps/map.adp?searchtype=address&country=US&addtohistory=&searchtab=home&formtype=address&popflag=0&latitude=&longitude=&name=&phone=&level=&cat=&address=' . $row['address'] . '&city='.$row['city'].'&state=NY&zipcode=">Map</a> | <a href="javascript:popUp(\'printshow.php?showid='.$row['showid'].'\')">Print</a> | <a href="spam.php?showid='.$row['showid'].'">Report Spam</a></font></td></tr>';
echo'<tr><td>.................................</td></tr>';
}
echo '<tr><td></br></td></tr>';
}
echo "\n</table>";
?>

Piranha

I can't help you with the original problem, but I saw another problem in your code:

$query = "SELECT * FROM comment WHERE showid='$showid'";

If you only use the result to count how many rows are returned it is better to do that in the database:

$query = "SELECT COUNT(*) FROM comment WHERE showid='$showid'";

Then you only get a number instead of ever comment to that show. With a big database it will really be an improvement.

SpecialFX

Good call so I made the change.

this is what the page looks like http://noiselive.com/index3.php It doesn't return a number it says like "resource id #9".