Mysql count rows function

Question

I have created a simple function which takes user id and show total no. of post by the user id

function totalpost($user_id){
$sql = 'SELECT * FROM posts WHERE u_id='.$user_id;
$total = mysql_num_rows(mysql_query($sql)) or die(mysql_errno());
return $total;

}

Its working fine, but when 0 record found, it not returns anything.

I want to return 0 if there are no record found

Please help

Answer 1

Try this query:

SELECT COUNT(*) FROM posts WHERE u_id=xx

By using the COUNT function you will guarantee a 0 will be returned even if no rows match the WHERE clause.

Answer 2

function totalpost($user_id){
  $sql = 'SELECT count(*) FROM posts WHERE u_id='.intval($user_id);
  $res = mysql_query($sql)) or trigger_error(mysql_error().$sql);
  $row = mysql_fetch_row($res);
  return $row[0];
}

not because you need 0, but because you have to always use count() instead of selectiong all users posts which can be big load of data.

Answer 3

Try this:

$SQL = 'Select Count(u_id) from posts where u_id='.$user_id;

Or test for is_null with PHP

if( is_null(mysql_num_rows(mysql_query($sql)) or die(mysql_errno())) {
$total =0;
} else { $total = mysql_num_rows(mysql_query($sql)) or die(mysql_errno()) ; }

Please note, I would do this in SQL