[SOLVED]How do I get a Row Count of rows that have been se..

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
dbudde
Forum Commoner
Posts: 25
Joined: Mon Dec 01, 2003 4:02 pm
Location: Chicago, IL

[SOLVED]How do I get a Row Count of rows that have been se..

Post by dbudde »

I'm using Mysql and PHP of course and I just need to know how I return a row count of an SQL statement like this?

Code: Select all

$result = mysql_query("SELECT * FROM users WHERE user = $user AND password = $password")
All I need to know is how many rows are returned by this query, so how do I get a row count from it?
Nay
Forum Regular
Posts: 951
Joined: Fri Jun 20, 2003 11:03 am
Location: Brisbane, Australia

Post by Nay »

First off, doing it this way is more advisible (and common):

Code: Select all

$query = "SELECT * FROM users WHERE user = $user AND password = $password";
$result = mysql_query($query, $link) or die(mysql_error());
$link should be the variable your connection is stored at. Anyhow, they're a few ways you can do it either with:

Code: Select all

$rows = mysql_num_rows($result);

// or

$rows = mysql_affected_rows($result);
Both of them gives the number, no difference. It's your choice. 8)

-Nay
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

also you can:

Code: Select all

$query = "SELECT count(*) FROM users WHERE user = $user AND password = $password"; 
$result = mysql_query($query, $link) or die(mysql_error());
list($num)=mysql_fetch_row($result);
echo $num;
2Nay
This way is even more advisable as it reduces traffic between web-server and mysql database and works pretty well if you need to know only the count of users with particular name-password pair.
User avatar
aquila125
Forum Commoner
Posts: 96
Joined: Tue Dec 09, 2003 10:39 am
Location: Belgium

Post by aquila125 »

If we'r going for efficiency, change the code to this:

Code: Select all

$query = "SELECT count(user) FROM users WHERE user = ".$user." AND password = ".$password; 
$result = mysql_query($query, $link) or die(mysql_error()); 
list($num)=mysql_fetch_row($result); 
echo $num;
Count works faster if it only has to count one column... and don't put your variables between quotes.. easier to read this way..

and to be even more exact, you don't want your script to end if mysql generates an error.. so don't use the 'or die' syntax...

Code: Select all

if (!$result=mysql_query($query,$link) echo "<br>".mysql_error."<br>";
is much nicer to the users...
dbudde
Forum Commoner
Posts: 25
Joined: Mon Dec 01, 2003 4:02 pm
Location: Chicago, IL

Thaks for your speedy reply

Post by dbudde »

Hey, thanks for your help. I saw the mysql_affected_rows before but I was led to beleive it only works with inserted, updated, or deleted rows. I could have sworn that I looked at all the other mysql functions, but alas you showed me the mysql_num_rows and it is in the mysql documentation, so thanks for opening my eyes everyone.
dbudde
Forum Commoner
Posts: 25
Joined: Mon Dec 01, 2003 4:02 pm
Location: Chicago, IL

This one is solved

Post by dbudde »

The answers given did solve my question on this one if you want to mark it as SOLVED.
Post Reply