Unread Message Function

[imdead]

Hey, im trying to create a little function that displays how many unread messages a user has

this is my function

function unread() {
$sql_pm_r = mysql_query("SELECT COUNT(*) FROM messages WHERE reciever=" . $user . " AND recieved='0'");
$num_rows = mysql_num_rows($sql_pm_r);
$sql_pm_check = mysql_query("SELECT id FROM messages WHERE reciever=" . $user . " AND recieved='0' LIMIT 1");
$num_new_pm = mysql_num_rows($sql_pm_check);
if ($num_new_pm > 0) {
$pms = '<a href="inbox.php" >('.$num_rows.')</a>';
} else {
$pms = '<a href="inbox.php" >(0)</a>';
}
echo $pms;
}

and i'm calling it on the index page as normal like

unread();

 

However not matter how many unread messages i have, i always get (0), this is how my sql table looks

id reciever sender subject message recieved

[jotorres1]

Have you tried echoing out '$user'? Seeing what you get?

 

Also, when you do a SELECT COUNT, it should already return a number.  Try echoing out '$sql_pm_r' and see if it has a number.  This is assuming recieved = 0 mean 0 is unread. 

[Buddski]

$sql_pm_r will, assuming the query executed, will return a resource, not a number.

The first place to start is to see if your queries are in fact running as expected.

mysql_query("SELECT COUNT(*) FROM messages WHERE reciever=" . $user . " AND recieved='0'") or die(mysql_error());

[imdead]

Right just fixed a slight issue, $user was empty.

Working now

 

function unread() {
global $user;
mysql_query("SELECT COUNT(*) FROM messages WHERE reciever=" . $user . " AND recieved='0'") or die(mysql_error());
$num_rows = mysql_num_rows($sql_pm_r);
$sql_pm_check = mysql_query("SELECT id FROM messages WHERE reciever=" . $user . " AND recieved='0' LIMIT 1");
$num_new_pm = mysql_num_rows($sql_pm_check);
if ($num_new_pm > 0) {
$pms = '<a href="inbox.php" >('.$num_rows.')</a>';
} else {
$pms = '<a href="inbox.php" >(0)</a>';
}
echo $pms;
echo $sql_pm_r;
echo $user;
}

 

However i get

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USERNAME AND recieved='0'' at line 1

 

Which i fixed by changing

". $user ."

to

'". $user ."'

 

Now it's echo'ing

(0)USERNAME

 

Which means $sql_pm_r is empty right?

[Buddski]

Have you checked for errors in your second query?

And echoing $sql_pm_r will be of no use to you..

 

Try this.

function unread() {
global $user;
$sql_pm_r = mysql_query("SELECT `id`, COUNT(*) as `unread` FROM `messages` WHERE reciever='" . $user . "' AND recieved='0' GROUP BY `reciever`") or die(mysql_error());
$rows = mysql_fetch_assoc($sql_pm_r);

if ($rows['unread'] > 0) {
	$pms = '<a href="inbox.php" >('.$rows['unread'].')</a>';
} else {
	$pms = '<a href="inbox.php" >(0)</a>';
}
}

[trq]

Don't use globals inside functions. You have just broke all encapsulation benefits a function provides. Pass it to your function as an argument.

[imdead]

Thorpe how do you mean?

 

and using that function didn't do anything.

 

at the moment i have this echo'ing

(1)Resource id #11USERNAME

 

with

function unread() {
global $user;
$sql_pm_r = mysql_query("SELECT COUNT(*) FROM messages WHERE reciever='". $user ."' AND recieved='0'") or die(mysql_error());
$num_rows = mysql_num_rows($sql_pm_r);
$sql_pm_check = mysql_query("SELECT id FROM messages WHERE reciever='" . $user . "' AND recieved='0' LIMIT 1") or die(mysql_error());
$num_new_pm = mysql_num_rows($sql_pm_check);
if ($num_new_pm > 0) {
$pms = '<a href="inbox.php" >('.$num_rows.')</a>';
} else {
$pms = '<a href="inbox.php" >(0)</a>';
}
echo $pms;
echo $sql_pm_r;
echo $user;
}

[Buddski]

My function didnt do anything because I missed the echo statement.

I will explain your echos below:

(1) => This is because $num_rows is 1 and will always be 1 as you are using a COUNT(*);

Resource id #11 => This is because, as I already stated, $sql_pm_r contains a MySQL Resource, NOT actual results.

USERNAME => Pretty self explanatory.

 

thorpe is talking about your use of global $user;, instead of global you should pass in the variable into the function.

 

function unread($user) {
$sql_pm_r = mysql_query("SELECT `id`, COUNT(*) as `unread` FROM `messages` WHERE reciever='" . $user . "' AND recieved='0' GROUP BY `reciever`") or die(mysql_error());
$rows = mysql_fetch_assoc($sql_pm_r);

if ($rows['unread'] > 0) {
	$pms = '<a href="inbox.php" >('.$rows['unread'].')</a>';
} else {
	$pms = '<a href="inbox.php" >(0)</a>';
}
echo $pms;
}
// Then to call the function use:
unread($user);

[imdead]

Right got it working!

Cheers everyone

function unread($user) {
$sql_pm_r = mysql_query("SELECT id, COUNT(*) as unread FROM messages WHERE reciever='" . $user . "' AND recieved='0' GROUP BY reciever") or die(mysql_error());
$rows = mysql_fetch_assoc($sql_pm_r);
if ($rows['unread'] > 0) {
$pms = '<a href="inbox.php" >('.$rows['unread'].')</a>';
} else {
$pms = '<a href="inbox.php" >(0)</a>';
}
echo $pms;
}