[SOLVED] Help with listing

[timmah1]

I have a ranking system which I have on my front page. You can't see the rankings unless your logged in.

I want it to show only the top 10, which I have this

 

$rank="SELECT * FROM ranking ORDER BY rank ASC LIMIT 10";

 

It works perfect. But i want to take it a step further, and I'm not sure how.

 

If the user viewing the rankings is ranked further than 10, I want them to see their ranking, below the top 20.

 

Example:

User is ranked 32

i want the top 20 to show, then after some spacing, I want the user's info to show

 

1

2

3

4

5

6

7

8

9

10

 

 

32 user

 

I tried using

if($rank > 10) {
echo $user;
}

but that don't work because I limited the query to 10.

How do I get around this?

 

thanks in advance

 

 

 

[MasterACE14]

<?php
$rank="SELECT * FROM ranking ORDER BY rank DESC LIMIT 10"; /* note your grabbing the last 10 ranks, not the first 10 change ASC to DESC */
$you="SELECT * FROM ranking WHERE id = $id"; // grab the rank info for the user logged in

 

then echo $rank how you are now, and then after echo $you in the same way

 

Regards ACE

[br0ken]

This code should do the trick. First you select the top 10 rankings and then union this with the users ranking. You set an alias for this as if it were a table and then select all distinct columns from this 'fake' table. This way, if the users ranking is in the top 10, it wont be displayed at the bottom as the distinct clause will remove it, however if it's not in the top 10, it will be displayed at the bottom.

 

Hope this works for you.

 

$sql = "SELECT DISTINCT(*) FROM ((SELECT * FROM ranking ORDER BY rank DESC LIMIT 10) UNION (SELECT * FROM ranking WHERE id = $id)) as tblRankings"

[timmah1]

br0ken,

 

I've tried your method

require("config.php"); 
$username = $_SESSION['SESS_USERNAME'];
$sql = "SELECT DISTINCT(*) FROM ((SELECT * FROM ranking ORDER BY rank DESC LIMIT 10) UNION (SELECT * FROM ranking WHERE username = '$username')) as tblRankings";
$result = mysql_query($sql);
$numrows = mysql_num_rows($result)
or die("Sorry, there was a problem selecting the rankings<br> ".mysql_error());	
echo "$username";

 

But I get errors

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/tourwarz/public_html/tour/test.php on line 6
Sorry, there was a problem selecting the rankings
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 '*) FROM ((SELECT * FROM ranking ORDER BY rank DESC LIMIT 10) UNION (SELECT * FRO' at line 1

 

Any ideas??

Thanks

[timmah1]

MasterACE14,

 

I used your method and it works perfect!

Thank you very much

[br0ken]

$sql = "SELECT DISTINCT* FROM ((SELECT * FROM ranking ORDER BY rank DESC LIMIT 10) UNION (SELECT * FROM ranking WHERE username = '$username')) as tblRankings";

 

Sorry, this should work. The DISTINCT (*) part didn't need the braces around it. Give it a try, it should accomplish your goal with one query.

[timmah1]

yes br0ken, works perfect now.

 

Thank you both very much, both solutions work great