SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 32
  1. #1
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Which row did query come from

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yep, i do

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

    you gotta use count(*)+1
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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!!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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!!

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote 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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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_

  8. #8
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    change FROM (points_total_".$game.") as total_points to FROM (points_total_".$game.") as t

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    gotta change something in the SELECT list, too

    can't select the table name like that
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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

  11. #11
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote 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?

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    the very first column after the word SELECT is the table name, that can't be right
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote 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."

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote 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?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    i see

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

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Banana Man
    The table name is "apple_users"
    aha!

    so, you know how to fix the query now, right?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I'll have a guess at switching them around. Well here goes nothing!!!!

  18. #18
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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!!

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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!!!!!!!

  21. #21
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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

  22. #22
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Right, just made the plunge and bought a MySQL book on amazon!!

  23. #23
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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>";

  24. #24
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  25. #25
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote 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'


Bookmarks

Posting Permissions

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