Page 1 of 1
[SOLVED]How do I get a Row Count of rows that have been se..
Posted: Wed Dec 10, 2003 2:03 am
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?
Posted: Wed Dec 10, 2003 2:55 am
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.
-Nay
Posted: Wed Dec 10, 2003 4:22 am
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.
Posted: Wed Dec 10, 2003 8:01 am
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...
Thaks for your speedy reply
Posted: Wed Dec 10, 2003 8:52 am
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.
This one is solved
Posted: Thu Dec 11, 2003 3:59 am
by dbudde
The answers given did solve my question on this one if you want to mark it as SOLVED.