Calculate number of rows

[Gibbs]

Hello,

I have a database named "mail" which has 5 different fields. These are the fields:

UserTo, UserFrom, Subject, Message, status, SentDate and mail_id.

What I want to do is get the total number of rows that have status set to "unread" in them for a certain username. I would like to have a variable with a number so users know how many unread mails they have.

Any help would be great.

Thanks!

[michaellunsford]

you can use PHP's [code=php:0]$variable = mysql_num_rows($result);[/code] or MySQL's [color=blue]SELECT COUNT()[/color]

[code]SELECT COUNT('field_name') FROM `table_name` AS total WHERE ...[/code]

[Gibbs]

[code]$get_mail = mysql_query("SELECT COUNT('status') FROM $tblnamer AS TOTAL WHERE UserTo='$justr' ORDER BY SentDate") or die ("<br><br>Can't execute $sql: " .mysql_error());[/code]

This works (thanks to your help). When I run it through PHPMyAdmin is gives me the number 2.

When I run $inboxcount = mysql_num_rows($get_mail); through PHP I get the number 1.

Number 2 is correct. How can I get that number into a PHP string?

Thanks by the way :)

[Gibbs]

The edit buttons gone again  :o

I figured it out. Thanks for your much needed help! :)

[Gibbs]

Actually I do have a problem with this (dont want to create another thread).

I need to read only rows that have "status" set to unread. I've tried this but it doesn't work....
[code]SELECT COUNT(status='unread') FROM $tblnamer AS TOTAL WHERE UserTo='$justr' ORDER BY SentDate[/code]

Ah figured it out. SORRY! :P

[michaellunsford]

[code] SELECT COUNT('status') FROM $tblnamer AS TOTAL WHERE UserTo='$justr' AND `status` = 'unread' ORDER BY SentDate[/code]

the reason mysql_num_rows is only returning "1" is because you're running it against the SELECT COUNT() query (which is only returning one row, with one record that has the count in it). mysql_num_rows is supposed to be used with your real query. so...

[code]<?php
$result=mysql_query("SELECT * FROM `$tblnamer` WHERE `UserTo` = '$justr' ORDER BY `SentDate`");
$total_rows=mysql_num_rows($result);
while(mysql_fetch_assoc($result)) {
... blah blah
[/code]

[fenway]

Just remember not to run the query if you only need the count!