How to return specific SQL results

ok. I have written a function for my private messaging system that I have written. It's supposed to return the total number of PM's a user has in his inbox.


function getTotalPM($id)
{
$pm_query = "SELECT * FROM pm WHERE reciever='$id'";
echo mysql_num_rows(mysql_query($pm_query));
}


What I want to know is if I can limit the results that this function returns to ones that only have the value of 1(in this case, PM's that have not been read) so I can then make a new function(getNewPM()) to return the number of unread PM's the user has. How would I modify this function to return the intended results?

<?php
function getreadpm($id){
$pm_query = "SELECT * FROM pm WHERE reciever='$id' AND read='yes'";
echo mysql_num_rows(mysql_query($pm_query));
}

function getnewpm($id){
$pm_query = "SELECT * FROM pm WHERE reciever='$id' AND read='no'";
echo mysql_num_rows(mysql_query($pm_query));
}
?>


jus edit it a bit depending on how you record if a PM has been read

wtf.

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 'read='1'' at line 1

and

mysql_num_rows(): supplied argument is not a valid MySQL result resource in .... on line 44

How am i getting this error..i'm using the same syntax as a a different function and that one works just fine...all im doing is adding the AND read = '1'

probably a wrong sql query

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 'read='1'' at line 1

Check what you really have. That error looks like you may have missed a single quote after the one 'read='1''

Paste your whole code in (of course though change your mysql_connect password when you paste in)

Functions:


function getReadPM($id)
{
$pm_query = "SELECT * FROM pm WHERE reciever='$id' AND read='1'";
echo mysql_num_rows(mysql_query($pm_query)) or die(mysql_error());
}
//--------------------------------------------
//
// function that returns how many new PM's the user has
//
//--------------------------------------------
function getNewPM($id)
{
$pm_query = "SELECT * FROM pm WHERE reciever='$id' AND read='0'";
echo mysql_num_rows(mysql_query($pm_query));
}


the connection info


$db_user = 'root';
$db_pass = '***';
$db_host = 'localhost';
$db_name = 'site';

$conn = mysql_connect($db_host,$db_user,$db_pass);
mysql_select_db($db_name) or die(mysql_error());


i made sure there was atleast one row where read='1' in the database, and still no luck.

Note: when posting PHP use the PHP tags instead of the CODE tags

does the read='0' query work?

The functions can't "see" your database connection variable based on what you posted. Try adding

global $conn;

to both of them and see what you get.


function getReadPM($id)
{
global $conn;
$pm_query = "SELECT * FROM pm WHERE reciever='$id' AND read='1'";
echo mysql_num_rows(mysql_query($pm_query)) or die(mysql_error());
}

Note: when posting PHP use the PHP tags instead of the CODE tags

does the read='0' query work?

ok, sorry, it said to post incomplete PHP into code tags, this is by no means my whole file so yea, sorry.

as for the read='0' query, no it doesn't work, still returns the same error.

and as for adding the global var into the function, that didn't help either. I tried inputting it directly into the link identifier in the (mysql_query(query, $conn)), and that didn't work either.

Funny thing is, I have another function that returns the total number of PM's the user has total, using almost the same exact methods, and it works fine. If I add an AND statement in the query, thats where its getting all mixed up.


//--------------------------------------------
//
// function that returns how PM's the user has
//
//--------------------------------------------
function getTotalPM($id)
{
$pm_query = "SELECT * FROM pm WHERE reciever = '$id'";
echo mysql_num_rows(mysql_query($pm_query));
}


That function works just fine, the other two don't.

Is there a field named "read" in your pm table?

lol, yes, im sure their is. its of type int, but im pretty sure that has nothing to do with why its not working.

if it helps any, here is an SQL dump


no use anymore, thanks everyuone.

I believe read is a reserved word? i dont know, i didn't find it on the list, BUT, I did change the coilumn name of read to is_read, and everything seems to be working like a charm. thanks everyone for your help.