...

View Full Version : How to return specific SQL results



asavenged
03-12-2006, 05:29 AM
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?

goughy000
03-12-2006, 09:36 AM
<?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

asavenged
03-12-2006, 01:59 PM
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'

arne
03-12-2006, 02:03 PM
probably a wrong sql query

djm0219
03-12-2006, 02:09 PM
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''

goughy000
03-12-2006, 02:14 PM
Paste your whole code in (of course though change your mysql_connect password when you paste in)

asavenged
03-12-2006, 02:22 PM
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.

goughy000
03-12-2006, 02:38 PM
Note: when posting PHP use the PHP tags instead of the CODE tags

does the read='0' query work?

djm0219
03-12-2006, 02:47 PM
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());
}

asavenged
03-12-2006, 08:23 PM
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.

arnyinc
03-12-2006, 09:16 PM
Is there a field named "read" in your pm table?

asavenged
03-13-2006, 03:13 AM
lol, yes, im sure their is. its of type int, but im pretty sure that has nothing to do with why its not working.

asavenged
03-13-2006, 03:22 AM
if it helps any, here is an SQL dump



no use anymore, thanks everyuone.

asavenged
03-13-2006, 03:31 AM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum