Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 14 of 14
  1. #1
    New Coder
    Join Date
    Mar 2006
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

    Code:
    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?

  • #2
    Regular Coder goughy000's Avatar
    Join Date
    Nov 2005
    Location
    England
    Posts
    415
    Thanks
    0
    Thanked 0 Times in 0 Posts
    PHP Code:
    <?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
    PHP Freelancing!
    ByteForums!
    Scriptlance.comNeed a job done quick? post it on scriptlance! OR! your a programmer? sign up and get paid for these jobs!

  • #3
    New Coder
    Join Date
    Mar 2006
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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'

  • #4
    Registered User
    Join Date
    Sep 2005
    Posts
    315
    Thanks
    0
    Thanked 0 Times in 0 Posts
    probably a wrong sql query

  • #5
    Senior Coder djm0219's Avatar
    Join Date
    Aug 2003
    Location
    Wake Forest, North Carolina
    Posts
    1,293
    Thanks
    4
    Thanked 202 Times in 199 Posts
    Quote Originally Posted by asavenged
    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''
    Dave .... HostMonster for all of your hosting needs

  • #6
    Regular Coder goughy000's Avatar
    Join Date
    Nov 2005
    Location
    England
    Posts
    415
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Paste your whole code in (of course though change your mysql_connect password when you paste in)
    PHP Freelancing!
    ByteForums!
    Scriptlance.comNeed a job done quick? post it on scriptlance! OR! your a programmer? sign up and get paid for these jobs!

  • #7
    New Coder
    Join Date
    Mar 2006
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Functions:

    Code:
    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

    Code:
        $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.

  • #8
    Regular Coder goughy000's Avatar
    Join Date
    Nov 2005
    Location
    England
    Posts
    415
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Note: when posting PHP use the PHP tags instead of the CODE tags

    does the read='0' query work?
    PHP Freelancing!
    ByteForums!
    Scriptlance.comNeed a job done quick? post it on scriptlance! OR! your a programmer? sign up and get paid for these jobs!

  • #9
    Senior Coder djm0219's Avatar
    Join Date
    Aug 2003
    Location
    Wake Forest, North Carolina
    Posts
    1,293
    Thanks
    4
    Thanked 202 Times in 199 Posts
    The functions can't "see" your database connection variable based on what you posted. Try adding

    PHP Code:
    global $conn
    to both of them and see what you get.

    PHP Code:
    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());

    Dave .... HostMonster for all of your hosting needs

  • #10
    New Coder
    Join Date
    Mar 2006
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by goughy000
    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.

    PHP Code:
    //--------------------------------------------
    //
    //  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.

  • #11
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts
    Is there a field named "read" in your pm table?

  • #12
    New Coder
    Join Date
    Mar 2006
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    lol, yes, im sure their is. its of type int, but im pretty sure that has nothing to do with why its not working.

  • #13
    New Coder
    Join Date
    Mar 2006
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    if it helps any, here is an SQL dump

    Code:
    no use anymore, thanks everyuone.
    Last edited by asavenged; 03-13-2006 at 03:32 AM.

  • #14
    New Coder
    Join Date
    Mar 2006
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •