MySQL Count function not working right

Question

$req_user = trim($_GET['user']);
    mysql_connect("$host", "$username", "$password")or die("cannot connect");
    mysql_select_db("$db_name")or die("cannot select DB");
$get_data = "SELECT * FROM `users` WHERE uname = '$req_user'";
$result = mysql_query($get_data) OR die(mysql_error());
$rows = mysql_fetch_assoc($result);
$email = $rows['email'];
$gravatar = md5(strtolower(trim("$email")));
$user_likes = mysql_query("SELECT COUNT(*) FROM likes WHERE username = '$email'");

I'm trying to count all of the rows in the database likes with the email of the current user in their username field.
(It's a loop to get info from links like userinfo.php?user=xxx.)
When I echo $user_likes there is no output.

What is wrong with the code?
http://www.tutorialspoint.com/mysql/mysql-count-function.htm

Has this line $gravatar = md5(strtolower(trim("$email"))); anything to do with the question? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Aug 11, 2011 at 16:15

ypercubeᵀᴹ · Accepted Answer · 2011-08-11 17:42:53Z

2

Assuming that $gravatar = md5(strtolower(trim("$email"))); is not related to the question here and not needed, you could also use one query to the database, to get the count:

$req_user = trim($_GET['user']);
mysql_connect("$host", "$username", "$password") or die("cannot connect");
mysql_select_db("$db_name") or die("cannot select DB");
$get_count_query = 
    "SELECT COUNT(*)
     FROM likes 
     WHERE username = 
           ( SELECT email
             FROM users 
             WHERE uname = '$req_user'
           )
    ";
$result = mysql_query($get_count_query) or die(mysql_error());
$row = mysql_fetch_row($result);
$user_likes = $row[0];

edited Aug 11, 2011 at 17:42

answered Aug 11, 2011 at 16:29

ypercubeᵀᴹ

116k19 gold badges181 silver badges248 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Asaf Biton Over a year ago

Thanks but the output is literally "Array". using <?php echo $user_likes; ?>

ypercubeᵀᴹ Over a year ago

Counting means that you want a number though, or am I missing something?

Asaf Biton Over a year ago

That's right, I need a number. But instead, the output is "Array" and not a number. # of Likes Posted: Array

Asaf Biton Over a year ago

Nevermind! After taking a look at php.net/manual/en/function.mysql-fetch-row.php - I changed it to echo $user_likes[0];. It's working fine now - thank you! :)

ypercubeᵀᴹ Over a year ago

Yes, you found it. It's an array with one row only and not a number (because of the COUNT(*).)

Nemanja · Accepted Answer · 2011-08-11 15:56:54Z

1

You didn't fetch it...

Do it like this:

$result = mysql_query("SELECT COUNT(*) FROM likes WHERE username = '$email'");

$user_likes = mysql_fetch_row($result);

answered Aug 11, 2011 at 15:56

Nemanja

1,54512 silver badges24 bronze badges

1 Comment

Asaf Biton Over a year ago

Same as @ypercube 's solution, the output is "Array" and not a number.

Dalmas · Accepted Answer · 2011-08-11 16:03:52Z

1

First you need to properly compare the emails (case insensitive) :

"... WHERE UPPER(username) = '" . strtoupper($email) . "'"

Then $user_likes is a resource variable, it won't give you the count directly.

You need to fetch it first :

$row = mysql_fetch_row($user_likes);
echo 'Count: ' . $row[0];

edited Aug 11, 2011 at 16:03

answered Aug 11, 2011 at 15:58

Dalmas

26.6k9 gold badges71 silver badges82 bronze badges

1 Comment

ypercubeᵀᴹ Over a year ago

MySQL is case insensitive when using LIKE or = with CHAR fields.

Yann Saint-Dizier · Accepted Answer · 2011-08-11 16:14:28Z

You should do that :

$req_user = trim($_GET['user']);
mysql_connect("$host", "$username", "$password") or die("cannot connect");
mysql_select_db("$db_name") or die("cannot select DB");
$get_data = "SELECT * FROM `users` WHERE uname = '$req_user'";
$result = mysql_query($get_data) or die(mysql_error());
if ($rows = mysql_fetch_assoc($result)) {
    $email = $rows['email'];
    $gravatar = md5(strtolower(trim("$email")));
    $get_data = "SELECT COUNT(*) FROM likes WHERE username = '$email'";
    $user_likes = mysql_query($get_data) or die(mysql_error());
    if ($row = mysql_fetch_row($user_likes)) {
       $nbr = 1*$row[0]; 
    } else {
       // it could never happen ;-)
       $nbr = 0;
    }
} else {
    // no match with this user!
    $nbr = 0;
}
echo "This user likes $nbr times !";

Collectives™ on Stack Overflow

MySQL Count function not working right

4 Answers 4

5 Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related