mysql_num_rows

[niranjnn01]

Hello,

i am using mysql_num_rows to find the number of rows in a result set,
but when there are now rows in the resultset, then , instead of returning zero, the program is halting.

what can i do to get a 0 as a return value?

$query="select date from view_diary";
$result=mysql_query($query);
$num_rows=mysql_num_rows($result); // execution stops here if no rows to return!

[YAOMK]

You could use it based on a condition whether there were any affected rows.

<?php
$query="select date from view_diary";
$result=mysql_query($query);
if (mysql_affected_rows($result))
{
$num_rows=mysql_num_rows($result);
echo $num_rows;
?>

[laserlight]

but when there are now rows in the resultset, then , instead of returning zero, the program is halting.

That should not be the case. Are you sure that your query is correct?

You could use it based on a condition whether there were any affected rows.

Using mysql_affected_rows() does not make sense for a select query.

[niranjnn01]

Hello,
YAOMKs sugession worked for me. thank you.

the query is correct. everything runs correctly when there are some fields to return. i experienced this problem as soon as there is zero rows to return,

the return type of this fuction is flase on failure.. mayb thats y this problem.

newyas the problem is fixed now,
Thank you all for your interest and help 🙂

[laserlight]

the return type of this fuction is flase on failure.. mayb thats y this problem.

No, zero rows returned is not a failure. It is simply that the query returns no rows. A failure would be something like an incorrect query, or no connection to the database server.

newyas the problem is fixed now,

I have my doubts. What are you using now? Have you tried it with a query that returns at least one row?

EDIT:
Ah, a test reveals that MySQL5 at least returns the number of rows selected when mysql_affected_rows() is called. On the other hand, I would caution that there is no guarantee that you will actually get the number of rows selected. Read the PHP manual on [man]mysql_affected_rows/man and PDOStatement's rowCount() for more details.

[niranjnn01]

have my doubts. What are you using now? Have you tried it with a query that returns at least one row?

EDIT:
Ah, a test reveals that MySQL5 at least returns the number of rows selected when mysql_affected_rows() is called. On the other hand, I would caution that there is no guarantee that you will actually get the number of rows selected. Read the PHP manual on mysql_affected_rows() and PDOStatement's rowCount() for more details.

Hi,
i previously used this code
$query="select date from view_diary";
$result=mysql_query($query);
$num_rows=mysql_num_rows(); // execution stopped here will not go to the next line of code (i.e wont return from the function "mysql_num_rows")

this is the case when there are no rows in the table. (I had thought it would return a zero.)
when i checked the doumentation of mysql_num_rows, i learned that it will eiter return number of rows, or a flase on failure.

maybe it treats a zero- rows(nothing to return) as a failure even though it could have returned a 0

then i used this code

---

$query="select date from view_diary";
$result=mysql_query($query);
$num_rows=mysql_affected_rows();

		if($num_rows==0)
                       {$j=0;}
                    else{
			for($k=0;$k< $num_rows;++$k)
			{
			$array=mysql_fetch_row($result);
				if($array[0]==$date)
				{
				++$j;
				}

			}
		}

this code is to check if the value of $date is already present in the table.(and if not present, add it to the table) i used a for loop to seach through the table values.(this is where i needed the number of rows). but in the case of an empty table, the code does not run, as it halts at the line where number of rows are found out!.

Do tell me if there is an easier way of doing this same operation.

Thank you.

[Piranha]

It should return 0 if there are 0 rows returned, and false if there is some error (the connection to the database have been lost for example).

It all depends on how you handle the information afterwards. Unfortunatley more values than false are regarded as false in PHP, as seen in the manual.

[niranjnn01]

Please note that i was not asking more questions without reading all posts,
but i was giving clarifications on a comment made by another user.

and thank you for the extra information. 🙂

[laserlight]

this code is to check if the value of $date is already present in the table.(and if not present, add it to the table)

So, the date is unique? If so, the best way is to declare the date column as unique, and then just attempt to insert $date. If there is already a row with $date in the table, the insert statement will fail, but you can suppress the error and check for it, if necessary. If not, it will just be added to the table.

Alternatively, instead of using mysql_num_rows() (or mysql_affected_rows(), for that matter), just select the count of the number of rows in the table with $date as the date. If the count is 0, insert $date:

$query = "SELECT COUNT(*) FROM view_diary WHERE `date`='$date'";
$result = mysql_query($query);
if (mysql_result($result, 0) == 0)
{
    // No such date in the table, so insert it.
    mysql_query("INSERT INTO view_diary (`date`) VALUES ('$date')");
}

[Piranha]

Note that if you use mysql_num_rows, mysql_affected_rows, count or any other way from PHP it could be that you check if it exists, get the result that it doesn't exist and insert it. But another person might have inserted it between the check and you inserting it. Timeline:

User 1                User 2
run select query      -------
unique = true         run select query
------                unique = true
insert row            ------
------                insert row

The result is that when user 1 checked it was unique. So it was when user 2 checked it. The result is that the row gets inserted twice. To avoid this do as laserlight says, define the date column as unique.