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
// 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>';
}
?>

Humor me?

Change this:

$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

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

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.

I don't use PHP, but I'm pretty sure that what you really want is this:

<?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!

When I run that query it echo's this:

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

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.

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 $friend_array = explode(",", $friend_array);

before the sql statement?

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

ahh, it works!!

Thank you so much :)

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
( '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:

if($friend_array != "")
{
$temp = explode(",", $friend_array);
$list = "'" . implode( "','", $temp ) . "'";

and then used $list in place of $friend_array in the SQL query.