...

View Full Version : My query isn't working right - help please



MattClark
04-14-2011, 12:34 AM
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>';
}
?>

Old Pedant
04-14-2011, 01:07 AM
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.

MattClark
04-14-2011, 01:17 AM
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.

Old Pedant
04-14-2011, 01:20 AM
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!

Old Pedant
04-14-2011, 01:21 AM
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.

MattClark
04-14-2011, 01:21 AM
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

MattClark
04-14-2011, 01:29 AM
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?

Old Pedant
04-14-2011, 01:31 AM
$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.

MattClark
04-14-2011, 01:33 AM
ahh, it works!!

Thank you so much :)

Old Pedant
04-14-2011, 01:47 AM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum