Select ... Until?

[Afrow UK]

Is it possible to SELECT until a certain condition is met?
For example I might want to stop counting the records when the key field value matches some input (so a less than or greater than will not work here).

I tried writing a stored function for this but had no success in PHP. I don't know how to 'store' it in the first place!

Stu

[felgall]

That would be a where not condition to select everything that isn't the value you don't want. As the retrieval has no defined order using until would make no sense whatsoever. Any order in the results is applied by the order by after the retrieval of all of the results.

[Afrow UK]

Basically what I have is a table of players for our game.

Currently what I do is SELECT all the players in order of their score and play date (ORDER BY score DESC, connect_date DESC) and then loop through them (PHP) until the record player id matches the player id I want to get the rank for. This means that each player has to have another query followed by a loop so I'm having 21 queries to show 20 players. When I get 1000 players this is bound to be slower.

A < for the score or connect_date will not work because some players have not played yet (connect_date = 0) and some players have not been given a score (score = 0).

[GJay]

if this is for a player listing, then you can use the iteration-number for the rank, surely?

$query = 'SELECT * FROM table ORDER BY score DESC';
...
$rank=0;
while($row=mysql_fetch_array($rs)) {
echo "player ranked $rank";
$rank++;
}

?
if you're looking at a single player, then 2 queries will do:
SELECT * FROM table WHERE id=2;
(then you'll have the score)
SELECT count(*) FROM table WHERE score > $player_score
(will give you the number of players with more score, which is effectively the rank...

[Afrow UK]

I've had an idea.

Rather than have a SELECT for each player (so 1000 players = 1001 SELECT's and loops = a few seconds to load) I can have one SELECT at the start with ORDER BY score DESC, connect_date DESC, login_date DESC. If I loop through that once I can load an associative array tying ranks to player id's.

That'd still mean I am fetching data for 1000 players, however, but it should be faster in theory. It is still a shame I have to do it like this because say I want to display 10 players per page I'd be wasting 990 results.

Stu

[Afrow UK]

Question: Where do I put MySQL stored procedures?

Stu

[guelphdad]

Did you look at GJay's post?

even better if you are looking for a single player and their rank by score then you don't need a loop at all.

That's why it is always good to explain what you are after:


select playername
, ( select count(*)+1
from yourtable
where scorecolumn < X.scorecolumn ) as rank
from yourtable as X
where id = theidyouaresearchingon


Note that this: theidyouaresearchingon can be numerical or the playername or whatever.

now notice you have a single query and if your scores column is indexed you are going to be significantly, significantly faster getting the result that you want.

As a good friend of mine Rudy so elequantly put it. You have a bunch of people in Chicago. You are in California and want to know how many people you have in your group in Chicago (and for your purposes find the 57th person hired).

In your looping scenario it is like flying each of those people to California and counting them and then figuring out the 57th. Much easier, with my method above, to fly to Chicago, count the people and then by date/score or whatever find that 57th person! ;-)

[Afrow UK]

Like I already said unfortunately that will not work because most players have a score of 0 and therefore all those players will have the same rank.

Stu

[guelphdad]

and so those players should have the same rank.

sorry you aren't really clear on what the problem is other than you've identified that doing it in a loop is slow.

my post above will tell you how to get the rank for anyone with a score. if you want to exclude those with a score of 0 then do so in the query. but they aren't going to show above a ranked player and thus won't affect them.

if you are looking for John's score and there are 10 scores above his you will find out he is ranked 11th.

if instead he has a score of 0 (because you are still searching for him and his rank but are not sure whether or not he has played), he will still show as 11th if there are only 10 scores above his even if there are 500 other players with a score of 0.

[Afrow UK]

Only about 50-100 of 1000 players will have a score at any one time (scores are decreased by 1 on a daily basis) and so we can't just rank players by their score. All players need to be ranked out of the 1000.

Players should be ranked by their score, followed by their last played date followed by their last login date. An ORDER BY works perfectly for this: ORDER BY score DESC, connect_date DESC, login_date DESC. However, I can't figure a way of doing it purely by using conditional statements in the WHERE clause.

Stu

[GJay]

changing the sub-query in guehelpdad's post to be:
(select count(*)+1
from yourtable
where scorecolumn < X.scorecolumn OR (scorecolumn=X.scorecolumn and connect_date < X.connect_date))

should do it, shouldn't it?

[guelphdad]

Players should be ranked by their score, followed by their last played date followed by their last login date. An ORDER BY works perfectly for this: ORDER BY score DESC, connect_date DESC, login_date DESC. However, I can't figure a way of doing it purely by using conditional statements in the WHERE clause.

Okay that makes sense, but don't forget you knew that, we didn't. :)
You have to include such info in your post and then people can help you around ALL of the conditions and not just some of them.