MySQL Num Rows Lies! - Webmaster Forum - Web Design, Programming and SEO forums

02-16-2007, 11:57 PM

WinSrev

Status: Junior Member
Join date: Oct 2005
Location:
Expertise:
Software:

Posts: 38

iTrader: 0 / 0%

WinSrev is on a distinguished road

MySQL Num Rows Lies!

Hi everybody,

i recently made a PM system so i was adding the function to check if the user has any PM's if not show a message if so do whatever, but for some reasons even with an empty field mysql still reckons theres some records in there, and yeh, i truncated and its all lies or it could be my inefficent code

but either way heres my code: i spent ages trying to figure out what the problem is and got no where:

PHP Code:


			
        $total_pms = "SELECT count(*) FROM `message` WHERE `myid` = '$sdvariable'";

    $result_total_pms = mysql_query($total_pms) or die(mysql_error());

        

        $unread_pms = mysql_query("SELECT count(*) FROM `message` WHERE `myid` = '$sdvariable' AND `flag` = '1'");

        

        echo("You currently have <strong>" .mysql_num_rows($result_total_pms). "</strong> messages and <strong>" .mysql_num_rows($unread_pms). "</strong> unread messages");

        

        if(mysql_num_rows($total_pms) == 0)

        {

            echo("You currently have no PM's in your inbox!");

        }

        else

        {

... etc ...

can anyone help? thanks

02-17-2007, 01:07 AM

Andrew R

Status: Request a custom title
Join date: Dec 2005
Location: Arizona
Expertise:
Software:

Posts: 5,200

iTrader: 17 / 95%

Andrew R is on a distinguished road

Change:

PHP Code:


			
if(mysql_num_rows($total_pms) == 0)

To:

PHP Code:


			
if(mysql_num_rows($result_total_pms) == 0)

02-17-2007, 01:58 AM

WinSrev

Status: Junior Member
Join date: Oct 2005
Location:
Expertise:
Software:

Posts: 38

iTrader: 0 / 0%

WinSrev is on a distinguished road

i get the same problem, i really dont know whats wrong

02-17-2007, 02:22 AM

Andrew R

Status: Request a custom title
Join date: Dec 2005
Location: Arizona
Expertise:
Software:

Posts: 5,200

iTrader: 17 / 95%

Andrew R is on a distinguished road

Actually, instead of using count(), just use mysql_num_rows() earlier in the script to determine if there is any.

Hint: Use something like this:

PHP Code:


			
if(mysql_num_rows($query) > 0) {
// do this
} else {
// do this
}

02-17-2007, 12:57 PM

WinSrev

Status: Junior Member
Join date: Oct 2005
Location:
Expertise:
Software:

Posts: 38

iTrader: 0 / 0%

WinSrev is on a distinguished road

thanks,

got it working in the end

02-28-2007, 09:09 PM

bluesaga

Status: Member
Join date: Feb 2007
Location:
Expertise:
Software:

Posts: 137

iTrader: 1 / 100%

bluesaga is on a distinguished road

Count() is faster than selecting all the rows you want to know the number of rows for. However you can't simply use mysql_num_rows() to find out how many rows were returned, you will have to read it in an array something like.

Code:

<?php
$query = mysql_query("SELECT count(*) as count FROM t1");
//As count() will never return more than one row (in this instance, you need to read how many are "counted" in the count variable.
if(mysql_num_rows($query) != 0)
{
    $result = mysql_fetch_array($query);
    print "Count found".$result['count']."Rows";
}

?>

Hope that help you understand