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 10 of 10
  1. #1
    Regular Coder
    Join Date
    Jan 2011
    Posts
    120
    Thanks
    6
    Thanked 2 Times in 2 Posts

    My query isn't working right - help please

    My sql statement isn't working on my website, but it's working when I just put it into the sql area of phpmyadmin.

    $sqlName = mysql_query("SELECT first_name, last_name, points FROM users WHERE user_id='$value' ORDER BY points DESC LIMIT 5")

    On my website, it's pulling the first name and last name..but it is not ordering the results by points, or limiting it to 5 results.

    help please

    PHP Code:
    <?php
    // GATHER TOP SCORING FRIENDS
    $TopScoringDisplayList .= "";
    if(
    $friend_array != "") {  

        
    $friendArray explode(","$friend_array);
            
    $TopScoringDisplayList .='<div class="contentContainer topScoringFriends"> 
                <div class="header"> 
                  <p>Highest Scoring Friends</p> 
                </div>'
    ;
            
    $i 0// create a variable that will tell us how many items we looped over
            
    $TopScoringDisplayList .= '<div class="grayBox flatTop" style="padding: 0;">';
            foreach (
    $friendArray as $key => $value) {
    $i++; // increment $i by one each loop pass
        
    $sqlName mysql_query("SELECT first_name, last_name, points FROM users WHERE user_id='$value' ORDER BY `points` DESC LIMIT 5") or die ("Sorry, we had a mysql error.");
    while(
    $row mysql_fetch_array($sqlName)) {$friendFirstName $row["first_name"]; $friendLastName $row["last_name"]; $friendpoints $row["points"]; }

    $check_pic 'user_photos/' $value '/image01.jpg';
    if(
    file_exists($check_pic)) {
        
    $frnd_pic '<a href="profile.php?id=' $value '"><img src="' $check_pic '" width="25px" height="25px" border="1" /></a>';
    } else {
        
    $frnd_pic '<a href="profile.php?id=' $value '"><img src="user_photos/0/image01.jpg" width="25px" height="25px" border="1" /></a> &nbsp;';
    }

    $TopScoringDisplayList .='<div class="boxContainer"><div class="avatar">' $frnd_pic '</div><span class="friendName"><a href="profile.php?id=' $value '">' $friendFirstName ' ' $friendLastName '</a></span><span class="points">Points: ' $friendpoints '</span></div>';
    }        
    $TopScoringDisplayList .='</div></div>';
    } else {
    $TopScoringDisplayList .='<div class="contentContainer topScoringFriends"> 
                <div class="header"> 
                  <p>Highest Scoring Friends</p> 
                </div><div class="grayBox flatTop" style="padding: 0;">
                <div class="boxContainer"><strong>You haven\'t added any friends yet!</strong><br /<br /><br />
                <a href="search.php">Click here</a> to search for people you may know.
                </div></div></div>'
    ;
    }
    ?>

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,603
    Thanks
    80
    Thanked 4,500 Times in 4,464 Posts
    Humor me?

    Change this:
    Code:
        $sqlName = mysql_query("SELECT first_name, last_name, points FROM users WHERE user_id='$value' ORDER BY `points` DESC LIMIT 5") or die ("Sorry, we had a mysql error.");
    to this
    Code:
        $sql = "SELECT first_name, last_name, points FROM users WHERE user_id='$value' ORDER BY points DESC LIMIT 5"
        echo "DEBUG SQL: " . $sql . "<HR>\n";
        $sqlName = mysql_query( $sql ) or die ("Sorry, we had a mysql error.");
    Then copy/paste the SQL you see from that DEBUG into phpmysqladmin and see if indeed it still gives you the expected results.

    My guess is that you will always get *ONE* record from that query, because that query is only going to give you the data for *ONE* person. (How else could it?? You are asking for one single user_id!)

    But it's quite possible that I don't understand the structure of your users table.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder
    Join Date
    Jan 2011
    Posts
    120
    Thanks
    6
    Thanked 2 Times in 2 Posts
    give me one second and I will do that for you!

    BUT, what the query is doing is pulling ALL friends from the person's friend array. I have the person's friend list stored in an array, and I explode it.

    I have it limited to 1 to limit it to pulling 1 person's friends.

    On my site, it's pulling ALL of the users friends, not just the top 5 scoring, and it's not ordering them in any particular order.

    But in phpmyadmin it only pulls 5, and it orders by points so the top 5 scoring show up. One second, and i'll post the results for what you're asking.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,603
    Thanks
    80
    Thanked 4,500 Times in 4,464 Posts
    I don't use PHP, but I'm pretty sure that what you really want is this:
    Code:
    <?php
    // GATHER TOP SCORING FRIENDS
    $TopScoringDisplayList .= "";
    if($friend_array != "") 
    {  
        $TopScoringDisplayList .='<div class="contentContainer topScoringFriends"> 
                <div class="header"> 
                  <p>Highest Scoring Friends</p> 
                </div>';
        $i = 0; // create a variable that will tell us how many items we looped over
        $TopScoringDisplayList .= '<div class="grayBox flatTop" style="padding: 0;">';
        $sql = "SELECT first_name, last_name, points, user_id FROM users "
             . " WHERE user_id IN ( $friend_array ) ORDER BY points DESC LIMIT 5";
        $sqlName = mysql_query( $sql ) or die ("Sorry, we had a mysql error.");
        while($row = mysql_fetch_array($sqlName)) 
        {
            $i++;
            $friendFirstName = $row["first_name"]; 
            $friendLastName = $row["last_name"]; 
            $friendpoints = $row["points"]; 
            $value = $row["user_id"];
            $check_pic = 'user_photos/' . $value . '/image01.jpg';
            if(file_exists($check_pic)) {
                $frnd_pic = '<a href="profile.php?id=' . $value . '"><img src="' . $check_pic . '" width="25px" height="25px" border="1" /></a>';
            } else {
                $frnd_pic = '<a href="profile.php?id=' . $value . '"><img src="user_photos/0/image01.jpg" width="25px" height="25px" border="1" /></a> &nbsp;';
            }
    
            $TopScoringDisplayList .='<div class="boxContainer"><div class="avatar">' . $frnd_pic . '</div><span class="friendName"><a href="profile.php?id=' . $value . '">' . $friendFirstName . ' ' . $friendLastName . '</a></span><span class="points">Points: ' . $friendpoints . '</span></div>';
        }        
        $TopScoringDisplayList .='</div></div>';
    } else {
        $TopScoringDisplayList .='<div class="contentContainer topScoringFriends"> 
                <div class="header"> 
                  <p>Highest Scoring Friends</p> 
                </div><div class="grayBox flatTop" style="padding: 0;">
                <div class="boxContainer"><strong>You haven\'t added any friends yet!</strong><br /<br /><br />
                <a href="search.php">Click here</a> to search for people you may know.
                </div></div></div>';
    }
    Not to ask a dumbass questsion, but what is $i for??? You create it, you increment it, you never use it. I left it in, but I dunno why.

    NOTE: The above code assumes that the field user_id in your users table is a NUMERIC field. If it is not, then we need to make some minor changes.

    **********

    EDIT: Oops! I left out the parentheses around the IN list when I first posted!
    Last edited by Old Pedant; 04-14-2011 at 01:24 AM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Regular Coder
    Join Date
    Jan 2011
    Posts
    120
    Thanks
    6
    Thanked 2 Times in 2 Posts
    When I run that query it echo's this:
    Code:
    DEBUG SQL: SELECT first_name, last_name, points FROM users WHERE user_id='3' ORDER BY points DESC LIMIT 5
    DEBUG SQL: SELECT first_name, last_name, points FROM users WHERE user_id='18' ORDER BY points DESC LIMIT 5
    DEBUG SQL: SELECT first_name, last_name, points FROM users WHERE user_id='14' ORDER BY points DESC LIMIT 5
    DEBUG SQL: SELECT first_name, last_name, points FROM users WHERE user_id='11' ORDER BY points DESC LIMIT 5
    DEBUG SQL: SELECT first_name, last_name, points FROM users WHERE user_id='13' ORDER BY points DESC LIMIT 5
    DEBUG SQL: SELECT first_name, last_name, points FROM users WHERE user_id='2' ORDER BY points DESC LIMIT 5
    DEBUG SQL: SELECT first_name, last_name, points FROM users WHERE user_id='15' ORDER BY points DESC LIMIT 5
    DEBUG SQL: SELECT first_name, last_name, points FROM users WHERE user_id='5' ORDER BY points DESC LIMIT 5
    DEBUG SQL: SELECT first_name, last_name, points FROM users WHERE user_id='9' ORDER BY points DESC LIMIT 5
    DEBUG SQL: SELECT first_name, last_name, points FROM users WHERE user_id='16' ORDER BY points DESC LIMIT 5
    DEBUG SQL: SELECT first_name, last_name, points FROM users WHERE user_id='36' ORDER BY points DESC LIMIT 5

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,603
    Thanks
    80
    Thanked 4,500 Times in 4,464 Posts
    I'm right. You pretty clearly didn't use the same query when you were testing with phpmysqladmin as you have used in your PHP code.
    Last edited by Old Pedant; 04-14-2011 at 01:24 AM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Regular Coder
    Join Date
    Jan 2011
    Posts
    120
    Thanks
    6
    Thanked 2 Times in 2 Posts
    Alright, just one problem. In your code, the query wont run, i'm guessing because $friend_array isn't declared as an exploded array?

    Should I just set
    Code:
    $friend_array = explode(",", $friend_array);
    before the sql statement?

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,603
    Thanks
    80
    Thanked 4,500 Times in 4,464 Posts
    $friend_array should *NOT* be an array.

    It should be a list of numbers with commas between them: 3,18,14,11,13,2,15,5,9,16,36 (example only)

    If there's an extra comma on the end, we can adjust.

    Did you see my edit to my post? I had left out the ( ) around $friend_array in the $sql.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    MattClark (04-14-2011)

  • #9
    Regular Coder
    Join Date
    Jan 2011
    Posts
    120
    Thanks
    6
    Thanked 2 Times in 2 Posts
    ahh, it works!!

    Thank you so much

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,603
    Thanks
    80
    Thanked 4,500 Times in 4,464 Posts
    You're welcome. I had actually started to code it assuming that the user_id was a VARCHAR field, so I had constructed a list of id's like this
    Code:
        ( 'joe','bob','mary' )
    Then, when I realized they were just numbers, I forgot that I had the ( ) as part of my list. DOH.

    If you care, for future reference, we could have done:
    Code:
    if($friend_array != "") 
    {  
        $temp = explode(",", $friend_array);
        $list = "'" . implode( "','", $temp ) . "'";
    and then used $list in place of $friend_array in the SQL query.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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