count how many times the same username is in the database

[CBG]

Hi,

 

I currently have the below code.

 

$query = "select * from db WHERE username LIKE '$username'";
$result = mysql_query($query);
while ($r = mysql_fetch_array($result)) {
$user = $r["username"];
$user_seen = $r["username_seen"];
}

 

Now what I would like to do is count and return the number how many times the same username is in the database, as well as return username

 

How would I do this with the above code?

[the182guy]

Use a SELECT COUNT() query when you want to do something like this.

 

$query = "SELECT COUNT(*) AS num FROM db WHERE username = '$username'";
$result = mysql_query($query);

$numberOfUsernames = mysql_result($result, 0, 0);

// or

list($numberOfUsernames) = mysql_fetch_row($result);

 

You alreay have the username in $username

 

[JAY6390]

You could also just use

$number_of_rows = mysql_num_rows($result);

[trq]

You could also just use

$number_of_rows = mysql_num_rows($result);

 

Not with a COUNT() query as it only ever returns 1 row.

[JAY6390]

Huh? Why would it only return 1 row? There's no limit on the query. It will return all rows that have the username supplied, then mysql_num_rows will give the number of records returned. I've just tried this locally too and it's fine

[trq]

$query = "SELECT COUNT(*) AS num FROM db WHERE username = '$username'";

 

Would only ever return 1 row containing the count of the number of users that have $username as there name. Did you read my reply?

[JAY6390]

I would have assumed that since I said "instead" the OP would realise he should keep his original query since trying to get the number twice would be pretty stupid

[trq]

I would have assumed that since I said "instead" the OP would realise he should keep his original query since trying to get the number twice would be pretty stupid

 

You didn't mention the word 'instead' (not that that would have made your post any clearer) anywhere in your post, nor did you bother to read mine obviously.

[JAY6390]

Well theres really no need to be pedantic about it. No I didn't re-read my post. That said, it was still pretty obvious that I meant use that instead so I've copied and pasted the two words that define it just in case you need the exact words

"just use"

better?

[trq]

Mate, I really don't care. It was you who decided to prove me wrong without reading what I had written, lets just move on.

[ignace]

Use a SELECT COUNT() query when you want to do something like this.

 

$query = "SELECT COUNT(*) AS num FROM db WHERE username = '$username'";
$result = mysql_query($query);

$numberOfUsernames = mysql_result($result, 0, 0);

// or

list($numberOfUsernames) = mysql_fetch_row($result);

 

You alreay have the username in $username

 

 

The while makes me believe he wants to know how many times each username occurs which leads to:

 

SELECT count(*) AS username_count, username
FROM db
GROUP BY username

[CBG]

Thanks for all your replies

 

I went with this one in the end.

You could also just use

$number_of_rows = mysql_num_rows($result);