Display data from DB - Comparison Issue

[woldie]

Hi,

I'm currently building a time booking system, and what I have done is created a few tables in my database and populated these with some test data.

The problem I'm having is that I know for a fact that when I click on the 1st Sept for example the first couple of time slots have been taken, so for a test I've used the word 'booked', this works, if the time slot is not taken then it should say 'available', this doesn't do it.

I have echoed the MySQL query and I've test this on the MySQL console and it works, but yet it says 'booked' when its really not.

Many Thanks

Here's the code:

<?
if (isset($eventid))
{
?>
<table cellpadding="0" cellspacing="0" border="1">
<tr>
<td>Time</td>
<td>Availability</td>
</tr>

<?
$start=mktime(10,0,0,date('m'),date('d'),date('Y'));
for($i=$start;$i<=$start+32400;$i+=900)
{
$time=date("H:i",$i);
mysql_connect("localhost","","") or die ("could not find file");
mysql_select_db ("dbname") or exit();

$result=mysql_query("select count(booking_times.btid)
from booking_times,streamline_shows,cmsengineers,booking_webassess
where streamline_shows.shid=booking_times.shid
AND showname='$showname'
AND booking_times.engineernum=cmsengineers.engineernum
AND firstname='$firstname'
AND surname='$surname'
AND booking_times.btid=booking_webassess.btid
AND bktimes='$time'
AND bkdate='$eventid'");

echo "select count(booking_times.btid)
from booking_times,streamline_shows,cmsengineers,booking_webassess
where streamline_shows.shid=booking_times.shid
AND showname='$showname'
AND booking_times.engineernum=cmsengineers.engineernum
AND firstname='$firstname'
AND surname='$surname'
AND booking_times.btid=booking_webassess.btid
AND bktimes='$time'
AND bkdate='$eventid'";

list($DBcount)=mysql_fetch_row($result);
$num=mysql_num_rows($result);

// This is where it does the comparison
if ($num==1)
{
echo "<tr><td>$time</td><td>Booked</td></tr>";
}
else
{
echo "<tr><td>$time</td><td><a href=assessment.php?showtime=$time>Available</a></td></tr>";
}
}
?>
</table>
<?
}
?>

[jusdrum]

It looks like in your SQL query that you ask for a count of a field, then at the end, you ask mysql how rows it returned. It will always return one row: the count of the booked items. You have to get the value of the count, not the number of rows returned.

[woldie]

Hi jusdrum,

Thanks for the response.

I see what you mean, however this is what I've changed:

You mention 'You have to get the value of the count, not the number of rows returned.' I've changed to count(*), is this what you meant? But that didn't work, I've tried to set $num to 0, but that didn't work as well.

Thanks.

<?
if (isset($eventid))
{
?>
<table cellpadding="0" cellspacing="0" border="1">
<tr>
<td>Time</td>
<td>Availability</td>
</tr>

<?
$start=mktime(10,0,0,date('m'),date('d'),date('Y'));
for($i=$start;$i<=$start+32400;$i+=900)
{

$time=date("H:i",$i);
mysql_connect("localhost","webbie","support") or die ("could not find file");
mysql_select_db ("tracking") or exit();

$result=mysql_query("select count(*)
from booking_times,streamline_shows,cmsengineers,booking_webassess
where streamline_shows.shid=booking_times.shid
AND showname='$showname'
AND booking_times.engineernum=cmsengineers.engineernum
AND firstname='$firstname'
AND surname='$surname'
AND booking_times.btid=booking_webassess.btid
AND bktimes='$time'
AND bkdate='$eventid'");
/*echo "select count(*)
from booking_times,streamline_shows,cmsengineers,booking_webassess
where streamline_shows.shid=booking_times.shid
AND showname='$showname'
AND booking_times.engineernum=cmsengineers.engineernum
AND firstname='$firstname'
AND surname='$surname'
AND booking_times.btid=booking_webassess.btid
AND bktimes='$time'
AND bkdate='$eventid'";*/

list($DBcount)=mysql_fetch_row($result);
$num=mysql_num_rows($result);
if ($num==1)
{
echo "<tr><td>$time</td><td>Booked</td></tr>";
}
else
{
echo "<tr><td>$time</td><td><a href=web-assessment.php?showtime=$time>Available</a></td></tr>";
}

}
?>
</table>
<?
}
?>

[jusdrum]

Ah, try doing this:

$num = mysql_result($result,0);

That grabs the value of count(*) into $num.