Page 1 of 1

counting number of rows in database. which method?

Posted: Fri Aug 17, 2007 7:00 pm
by Dave2000
For example, I have a users table, and I want to find if a particular userID is of a valid user.

Two methods that I know of...

Code: Select all

$result = mysql_query("SELECT userID FROM users WHERE userID = '$userID'");
$rows = mysql_num_rows($result);
if ( $rows == 1 ) { return true; } else { return false; }

Code: Select all

$result = mysql_query("SELECT COUNT(userID) AS rows FROM users WHERE userID = '$userID'");
$row = mysql_fetch_assoc($result);
if ( $row['rows'] == 1 ) { return true; } else { return false; }
So which is recommended I use? (And in other similar situations.) This question has been annoying me since I started with PHP. Is it best to count with PHP or count with SQL ?

Thank you

Posted: Fri Aug 17, 2007 7:37 pm
by Theory?
Well, if you need the associative array elsewhere in your script, then you might as well do it the second way. If you're just using the result set to find out how many rows are in the result set, then the first one should be sufficient.

Posted: Sat Aug 18, 2007 2:05 am
by RhapX

Code: Select all

$query  = mysql_query('SELECT * FROM dev_shear WHERE user_id = ' . $user_id . '');
    $rows   = mysql_num_rows($query);
    $result = ($rows > 0) ? true : false;
    
    echo $result;
That's a simple clean way of what you're wanting to do. It's the same as what you did above, but would actually parse quicker.

Which query to use also depends on what you're wanting to use it for. If you're going to get an array of data about the user, then you would use what I have posted above which is similar to yours. If you're only trying to find how many rows, then you would just replace the asteric with the user_id and obviously change around the mysql_num_rows() portion.

Try to minimize your code so it parses faster.