count data in mysql

[shirvo]

first i will show example,

table messages containes user id, message, read.

now i want to get all the messages from user id 1 and see how many of them have read as true.

How can i do this, i'm using MYSQL so really i need a count thing

[Cagecrawler]

For all of the messages:
[code]SELECT * FROM table WHERE id = '$id' //In this case, $id is 1[/code]

For all the ones that have been read:
[code]SELECT * FROM table WHERE id = '$id' AND read = 'true' //Or whatever the value to show a message has been stored as true.[/code]

You can then do this to output the number:
[code]<?php
$query = "mysql_query(SELECT * FROM table WHERE id = '$id' AND read = 'true')";
$numrows = "mysql_num_rows($query)"
echo $numrows." messages have been read."
?>[/code]

Hope this is what you want.

[shirvo]

yes it was and i feel so stupid with how simple that was. i didnt really think about it as im working so hard on other parts of the site.

[hitman6003]

If you are only wanting the number of records, and not the data itself, use a count query.

[code]SELECT COUNT(*) WHERE WHERE id = '$id' AND read = 'true'[/code]

Then use mysql_result to get your answer:

[code]$result = mysql_query("SELECT COUNT(*) WHERE WHERE id = '$id' AND read = 'true'");
$count = mysql_result($result, 0);[/code]

The reason for doing this is because if you use Cagecrawler's method you are actually retrieving the data and storing it in a variable.  If there is a large amount of data retrieved by the query, it will take up a large amount of memory as well.  Additionally, if your web server and database server are different machines, that data will have to traverse the network, even if you aren't going to use it for anything other than a "mysql_num_rows" function.

[visitafsar@yahoo.com]

Hello,

I have a problem in php count  below code.

 

$people= mysql_query("select * from record");

$result = mysql_num_rows($people);

<?php echo $result;?>

 

it also count the empty record from 'record' table how I skipp the empty record.