Which row did query come from

**Banana Man** · Jan 10, 2006 13:44

hey,

Im doing a mysql query from a php script. I have been trying to get this to work for a while now with the help of some people on the php forum and this is what we've come up with so far.

I need a script to get a specific variable from a database but i also need to be able to get which row in the query the result came from. I want to order the query by highest figure first (ORDER BY total_points DESC) and then tell me what row the results came from i.e. if the result that i want was the 6th highest number from the query i want to be able to print out that this was the 6th highest number.

The code so far is

Code:

		$query = "SELECT points_total_".$game." as total_points
  		FROM apple_users 
 		WHERE user_id = ".$_SESSION['user_id']."
		";
		$results = mysql_query($query)
			or die(mysql_error());

		$rows = mysql_fetch_array($results);
		extract ($rows);

		$query2 = "SELECT points_total_".$game." as row_count
		(count(*) + 1 
  		FROM apple_users
 		WHERE total_points > ".$total_points.") as rank
  		FROM apple_users
 		where user_id = ".$_SESSION['user_id']."
		";
		$results2 = mysql_query($query2)
			or die(mysql_error());

Anyone know how to do this?

Thanks

**r937** · Jan 10, 2006 18:28

yep, i do

this sounds a lot like one i did for somebody just the other day

you gotta use count(*)+1

**Banana Man** · Jan 11, 2006 06:38

I think that was me you helped out the other day. The code you gave me worked but then i had to change the query slightly and because i didnt really understand what you had posted to me when i tried to amend it to suit the new query it wouldnt work.

The code at present is as above but this isint doing it. Do u know where ive gone wrong?

Cheers!!

**r937** · Jan 11, 2006 06:50

so you no longer want to look up some specific user's rank? now you want to rank everybody? i'm sorry, but it's not obvious from those two queries why you're using two queries, nor what your trying to do

if you want to rank all the rows, just do that in your php loop

set the ORDER BY to sort by number descending, return the rows from the simple query (the first of your two, i guess), and rank them as you print them

**Banana Man** · Jan 12, 2006 09:11

Ok, im getting into a big mess here now. Too many cook spoil the broth

. I should have been putting in the ORDER BY DESC and only take out the result where the user_id is = session user_id. Does this make sense to you?

Sorry for all this messing around!!

**r937** · Jan 12, 2006 09:18

Originally Posted by Banana Man

Does this make sense to you?

not any more

in your first post in this thread, you said "a specific variable from a database"

could you show me the query for this please

**Banana Man** · Jan 12, 2006 10:26

This is what i have now:

Code:

		$query = "SELECT (points_total_".$game.") as total_points
     		, ( select count(*)+1
           	FROM apple_users
          	WHERE total_points > t.total_points
 		) as ranking
  		FROM (points_total_".$game.") as total_points
 		WHERE survey_user_id = ".$_SESSION['user_id']."
		ORDER BY total_points DESC
		";
		$results = mysql_query($query)
			or die(mysql_error());

		while ($rows=mysql_fetch_assoc($results)) {
		extract ($rows);
		echo "<table width='320' class='text_main border='0' cellspacing='0' cellpadding='0'>
  			<tr>
    		<td width='60' height='20'><div align='center'>".$ranking."</div></td>
    		<td width='20'>&nbsp;</td>
   			<td>".$_SESSION['first_name']." ".$_SESSION['last_name']."</td>
    		<td width='80'><div align='center'>".$total_points."</div></td>
  			</tr>
			</table>";
		}

but it is giving me the following error message:

Code:

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 'as total_points WHERE survey_user_id = 81 ORDER BY total_

**longneck** · Jan 12, 2006 10:40

change FROM (points_total_".$game.") as total_points to FROM (points_total_".$game.") as t

**r937** · Jan 12, 2006 10:47

gotta change something in the SELECT list, too

can't select the table name like that

**Banana Man** · Jan 12, 2006 10:47

Right, i made that change so the code is now:

Code:

		$query = "SELECT (points_total_".$game.") as total_points, user_id
     		, ( select count(*)+1
           	FROM apple_users
          	WHERE total_points > t.total_points
 		) as ranking
  		FROM (points_total_".$game.") as t
 		WHERE user_id = ".$_SESSION['user_id']."
		ORDER BY total_points DESC
		";
		$results = mysql_query($query)
			or die(mysql_error());

		while ($rows=mysql_fetch_assoc($results)) {
		extract ($rows);
		echo "<table width='320' class='text_main border='0' cellspacing='0' cellpadding='0'>
  			<tr>
    		<td width='60' height='20'><div align='center'>".$ranking."</div></td>
    		<td width='20'>&nbsp;</td>
   			<td>".$_SESSION['first_name']." ".$_SESSION['last_name']."</td>
    		<td width='80'><div align='center'>".$total_points."</div></td>
  			</tr>
			</table>";
		}

but i am still getting an mysql error:

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 'as t WHERE user_id = 81 ORDER BY total_points DESC' at li

**Banana Man** · Jan 12, 2006 10:49

Originally Posted by r937

gotta change something in the SELECT list, too

can't select the table name like that

I think the php is ok, is it something in the mysql SELECT statement i have to change?

**r937** · Jan 12, 2006 10:51

the very first column after the word SELECT is the table name, that can't be right

**Banana Man** · Jan 12, 2006 11:00

Originally Posted by r937

the very first column after the word SELECT is the table name, that can't be right

Thats's the name of the field im getting the info from:

points_total_".$game."

**r937** · Jan 12, 2006 11:39

Originally Posted by Banana Man

Thats's the name of the field im getting the info from

i see

and what is the name of the table that has this field?

**Banana Man** · Jan 12, 2006 11:44

Originally Posted by r937

i see

and what is the name of the table that has this field?

The table name is "apple_users"

**r937** · Jan 12, 2006 11:56

Originally Posted by Banana Man

The table name is "apple_users"

aha!

so, you know how to fix the query now, right?

**Banana Man** · Jan 12, 2006 12:05

I'll have a guess at switching them around. Well here goes nothing!!!!

**Banana Man** · Jan 12, 2006 12:12

lol, the code is now:

Code:

		$query = "SELECT (points_total_".$game.") as total_points, user_id
     		, ( select count(*)+1
           	FROM apple_users
          	WHERE total_points > t.total_points
 		) as ranking
  		FROM apple_users
 		WHERE user_id = ".$_SESSION['user_id']."
		ORDER BY total_points DESC
		";
		$results = mysql_query($query)
			or die(mysql_error());

		while ($rows=mysql_fetch_assoc($results)) {
		extract ($rows);

but this gives me a mysql error:

Unknown column 't.total_points' in 'where clause'

If i take out the t. from t.total_points it gets rid of the mysql error but the ranking variable just comes up as 1, no matter where it was in the query!!

**r937** · Jan 12, 2006 12:20

Code:

SELECT (points_total_".$game.") as total_points
     , user_id
     , ( select count(*)+1
           FROM apple_users
          WHERE total_points > t.total_points
       ) as ranking
  FROM apple_users as t
 WHERE user_id = ".$_SESSION['user_id']."
ORDER 
    BY total_points DESC

compare to this -- http://www.sitepoint.com/forums/show...37&postcount=2

**Banana Man** · Jan 12, 2006 12:28

i tried it with the "as t" in it also but that gave me an error too so i removed it coz i thought it was a mistake. Anyways, i'll compare that now. Thanks for all the time youve spent on this by the way!!!!!!!

**Banana Man** · Jan 12, 2006 12:38

Ok, compared them and they look to be set up exactly as i have it set up. Man, when i did this query before after you gave me the code it worked 1st time and that was a slightly more complicated query!!!! Such is life

**Banana Man** · Jan 12, 2006 15:29

Right, just made the plunge and bought a MySQL book on amazon!!

**Banana Man** · Jan 13, 2006 10:19

Got it sorted, Finally!!!! I ended up using a "WHERE total_points_all > total_points" query and then put that into the php function "mysql_num_rows($results);" which counts the number of rows in the query and then added 1 to this figure. This still gives me a problem if two people have the same points but it will have to do for now

Code:

		//get the points total for current user from the apple_users database
		$query = "SELECT (points_total_".$game.") as total_points, first_name
       	FROM apple_users
 		WHERE user_id = ".$_SESSION['user_id']."
		";
		$results = mysql_query($query)
			or die(mysql_error());

		$rows=mysql_fetch_assoc($results);
		extract ($rows);


		//find out how many rows are above this row
		$query = "SELECT (points_total_".$game.") as total_points_all, (first_name) as current_first_name
       	FROM apple_users
		WHERE points_total_".$game." > ".$total_points."
		ORDER BY total_points_all DESC,
		first_name ASC
		";
		$results = mysql_query($query)
			or die(mysql_error());

		$num_rows = mysql_num_rows($results);

		$row_where_result_comes_from = $num_rows + 1;

		echo "<table width='320' class='text_main border='0' cellspacing='0' cellpadding='0'>
  			<tr>
    		<td width='60' height='20'><div align='center'>".$row_where_result_comes_from."</div></td>
    		<td width='20'>&nbsp;</td>
   			<td>".$_SESSION['first_name']." ".$_SESSION['last_name']."</td>
    		<td width='80'><div align='center'>".$total_points."</div></td>
  			</tr>
			</table>";

**r937** · Jan 13, 2006 10:31

congrats for getting it working

are you sure they are returning the correct answers?

and even if those queries may work, look at all the unnecessary extra work they're doing, e.g. two queries instead of one, returning the entire table and then counting them in php, etc.

please, please try the query in post #19

**Banana Man** · Jan 14, 2006 06:15

Originally Posted by r937

congrats for getting it working

are you sure they are returning the correct answers?

and even if those queries may work, look at all the unnecessary extra work they're doing, e.g. two queries instead of one, returning the entire table and then counting them in php, etc.

please, please try the query in post #19

Believe me, id much prefer the other piece of code myself but i need to get other things done on the site also and this is just taking too long to sort out. I just tried the code again from post #19 just to make sure but it is still giving me the same problems. The code i just tried is as follows:

Code:

$sql = "SELECT (points_total_".$game.") as total_points, user_id
     	, ( select count(*)+1
      	FROM apple_users
      	WHERE total_points > t.total_points
       	) as ranking
  		FROM apple_users as t
 		WHERE user_id = ".$_SESSION['user_id']."
		ORDER BY total_points DESC	
		";
		$results = mysql_query($sql)
			or die(mysql_error());

		$rows = mysql_fetch_array($results);
		extract ($rows);

		echo "<table width='320' class='text_main border='0' cellspacing='0' cellpadding='0'>
  			<tr>
    		<td width='60' height='20'><div align='center'>".$ranking."</div></td>
    		<td width='20'>&nbsp;</td>
   			<td>".$_SESSION['first_name']." ".$_SESSION['last_name']."</td>
    		<td width='80'><div align='center'>".$total_points."</div></td>
  			</tr>
			</table>";

but this gives me the following error message:

Unknown column 't.total_points' in 'where clause'

User Tag List

Thread: Which row did query come from

Thread Tools

Display

Which row did query come from

Bookmarks

Bookmarks

Posting Permissions