mysql num rows returns the wrong number of rows

[ro1960]

I have the following code and it returns the incorrect number of rows. In this particular case, there is one match (I tested the query directly in phpMyAdmin and it returns one match.) and mysql_num_rows returns 2.

I am using the number of rows to format the html that follows, so it is important that the number be correct.

Any idea what is causing this?

$query_thisweekend = "SELECT id, holiday, event, city, state, country, 
DATE_FORMAT(date_start, '%m/%d') AS Fdate_holiday_start, 
DATE_FORMAT(date_end, '%m/%d') AS Fdate_holiday_end, url 
FROM calendar
WHERE (date_start >= CURDATE() AND date_start <= (CURDATE() + INTERVAL 5 DAY) OR (date_end >= CURDATE() AND date_end <= (CURDATE() + INTERVAL 5 DAY)) ) 
ORDER BY date_start 
";

$result_thisweekend = mysql_query($query_thisweekend)
or die ("Couldn't execute query.");

$numrows_thisweekend = mysql_num_rows($result_thisweekend); 

[Anyango]

did you print both the records values it returns to see if that tells any obvious signs of whats wrong where?

[ro1960]

Maybe I wasn't clear enough, there is only one match, so only one record to print. It's the count returned by mysql_num_rows that is wrong, it says there are two records, but there is only one. This is confirmed by the result of the query ran directly in phpMyAdmin. So I assume that mysql_num_rows should return "1", not "2" as a value.

[Anyango]

Interesting, i might be totaly wrong here and i dont mean to insist on something that you dont believe to be the case but i think if mysql_num_rows is returning 2 then it must be having 2 records which mysql_fetch_array or mysql_fetch_assoc can show, maybe the query is returning same record twice. That might help sort it out

[LifeinAsia]

Try outputting $result_thisweekend and not just the number of rows. What do you see?

[ro1960]

Thanks for your inputs. There is definitely something strange. I modified the query and see what the result would be. Every time I run the query directly in phpMyAdmin to compare the results. I even hard coded a date for which I knew there was no match (2010-09-30) or 3 matches (2010-10-28 to 2010-10-31). Both phpMyAdmin and my html output show the correct number of records (0 or 3 in my tries) but the value returned by mysql_num_rows was again 2.

Could it be a compatibility issue between the mysql_num_rows fucntion and my server settings/versions?

[ro1960]

I solved the problem, but I am not sure what caused it. I'm posting the whole code including parts that where not in my original post. It seems that I was doing something wrong with my if statement, probably bad syntax.


original code:

$query_thisweekend = "SELECT id, holiday, event, city, state, country, 
DATE_FORMAT(date_start, '%m/%d') AS Fdate_holiday_start, 
DATE_FORMAT(date_end, '%m/%d') AS Fdate_holiday_end, url 
FROM calendar
WHERE (date_start >= CURDATE() AND date_start <= (CURDATE() + INTERVAL 5 DAY) OR (date_end >= CURDATE() AND date_end <= (CURDATE() + INTERVAL 5 DAY)) ) 
ORDER BY date_start 
";

$result_thisweekend = mysql_query($query_thisweekend)
or die ("Couldn't execute query.");
// counting the rows so I can display nothing if no results //////////////
$numrows_thisweekend = mysql_num_rows($result_thisweekend); 

if ($numrows_thisweekend = "1") { $cell_width = "100%"; }
if ($numrows_thisweekend = "2") { $cell_width = "50%"; }
if ($numrows_thisweekend > "2") { $cell_width = "33%"; }
// echo "nbrows:".$numrows_thisweekend."<br />";
if ($numrows_thisweekend > "0")
{
...

New code:

$query_thisweekend = "SELECT id, holiday, event, city, state, country, 
DATE_FORMAT(date_start, '%m/%d') AS Fdate_holiday_start, 
DATE_FORMAT(date_end, '%m/%d') AS Fdate_holiday_end, url 
FROM calendar
WHERE (date_start >= CURDATE() AND date_start <= (CURDATE() + INTERVAL 5 DAY) OR (date_end >= CURDATE() AND date_end <= (CURDATE() + INTERVAL 5 DAY)) ) 
ORDER BY date_start 
";

$result_thisweekend = mysql_query($query_thisweekend)
or die ("Couldn't execute query.");
// counting the rows so I can display nothing if no results //////////////
$numrows_thisweekend = mysql_num_rows($result_thisweekend); 

if ($numrows_thisweekend == 1) :
$cell_width = "100%";
elseif ($numrows_thisweekend == 2) :
$cell_width = "50%";
else:
$cell_width = "33%";
endif;

echo "<font color=\"white\">nbrows:".$numrows_thisweekend."</font><br />";
if ($numrows_thisweekend > 0)
{

You'll see my code using the number of rows to set the value of $cell_width is different. I am not sure what was wrong with the original statement but this seems to solve the problem, the number of rows returned is now correct.

I'm happy to hear what I was doing wrong.

Thank you.

[Anyango]

Very easy to figure that one out ;)


if ($numrows_thisweekend = "1") { $cell_width = "100%"; }
if ($numrows_thisweekend = "2") { $cell_width = "50%"; }

you have to have two = signs for comparisons, not 1, that single = sign in an if statement is causing an assignment, not a comparison

Cheers :)

[Anyango]

That is actualy why i was insisting that lets print the records and it will tell exactly whats wrong, if we had printed the resultset we would know that you were getting only 1 record still, but due to execution of this statement

if ($numrows_thisweekend = "2") { $cell_width = "50%"; }

You would always get the value of $numrows_thisweekend as 2 in the lines below, even if the result had 100 rows

[ro1960]

Thank you, this was such a stupid mistake on my end.

[Anyango]

Glad its sorted, all mistakes help us learn more