mysql_num_rows problem

[corporateboy]

My problem

I have a page, on that page are sql statements, these statements return results.

I echo them to the page. They all return results in phpmyadmin, there is a database connection running and no sql errors. mysql_num_rows seems to be just plain wrong! But even if I comment out the mysql_num_rows I still get the first 3 results not displaying - this is totally bizarre - does anyone know what is going on?

the page this is running on is http://law.blunt.com.au/2003/index.php?page=events&sub=photos have a look - if the page is displaying properly it means I fixed the problem somehow.

here is the code:

$i3 = date('Y');
$i2 = 2001;

?><font face="Arial, Helvetica, sans-serif"><b>MULS Events From <? echo $i2; ?> to <? echo $i3; ?></b></font><br><br>
<br>


<?


for ($i = $i3; $i >= $i2; $i--)
{
?><font face="Arial, Helvetica, sans-serif"><b><? echo $i;?></b></font><br>

<?
$sql = "SELECT 
event_id, 
name, 
class_id, 
date_format(year,\"%W, %D of %M, %Y\") as date 
FROM 
event 
WHERE 
include = 'Y' 
AND 
YEAR(year) = $i 
ORDER BY date DESC";
echo "$sql<p>";
	if ($result = mysql_query($sql))
	{

	$num = mysql_num_rows($result);
	echo"$num events found for this year<br>";
	if($num != 0)
	{
		while($myrow = mysql_fetch_array($result))
		{
		$name = $myrow[name];
		$name2 = urlencode($name);
		$event_id = $myrow[event_id];
		$date = $myrow['date'];

		?><a href = "index.php?page=events&sub=photos&event_id=<? echo$event_id; ?>"><? echo $name; ?></a> - <? echo $date; ?><br><?

		}
	} // close else
}
else
{
echo"error in $sql".mysql_error();
}
?><br>
<?
}

[groovything]

try removing each of these conditions one at a time:

WHERE

include = 'Y'
AND

YEAR(year) = $i

If you are getting results for one record it seems like it should work for all of them...maybe include = "y" or year is like "02"

I dont know...Im grasping at straws without being able to see the db

[corporateboy]

b4t the queries work on the db! and the query works fine - its like its a bug in php!

I can give you the results for each query if you like.

2003
event_id name class_id date

101 Law Society Cruise 2003 6 Wednesday, 26th of March, 2003
103 Law Society Camp 2003 4 Saturday, 5th of April, 2003

2002
event_id name class_id date

57 Law Society Cruise 2002 6 Wednesday, 27th of March, 2002
87 Law Com Dinner 2002 6 Thursday, 21st of November, 2002
81 Law Ball 2002 6 Thursday, 10th of October, 2002
64 First Year Law Camp 6 Friday, 3rd of May, 2002

2001
event_id name class_id date

45 Law Ball 2001 6 Friday, 19th of October, 2001

note that the 'First Year Law Camp' record is the only one that displays on the page all the others don't - so the problem is NOT in the SQL - tricky eh?

[HalfaBee]

why don't you try taking out the test for $num==0 and replace it with TRUE and see what the loop outputs.

HalfaBee

[corporateboy]

ok i tried that - the exact same result occurred - now this is getting totally bizarre - $num still returns 0 for 2003 and 2001 and only 1 for 2002 - so it seems to be chopping the first 3 records off the query result

this is so bizarre

[LordShryku]

Not if mysql_num_rows is also wrong. Stabbing in the dark here, but try naming this differently

date_format(year,\"%W, %D of %M, %Y\") as date  

date is a reserved SQL word. Try saying instead

date_format(year,\"%W, %D of %M, %Y\") as dateFormatted

Lemme know what happens there....

[HalfaBee]

I think this is a SQL problem still.

What type are you storing year as?

HalfaBee

[corporateboy]

you know what - im a total tool, I was running the queries on the WRONG DATABASE!

seems to be fixed now - apologies for wasting your time!

[groovything]

ha ha thats kind of funny. I thought it might be a data issue because you were getting one good result.

If it were a synax error that would not even have happened