Page 1 of 1
Finding position of a row in a query
Posted: Thu Apr 10, 2008 4:11 pm
by impulse()
If I have 100 rows output from a query and they don't have a primary key or an ID column then how would I find a column position in that list? I assume there's a way to output a incremented number as a column in a query but I can't think how I would do that.
Any help appreciated.
Thanks,
Re: Finding position of a row in a query
Posted: Thu Apr 10, 2008 4:57 pm
by John Cartwright
Short answer: no
Another short answer: add a primary key?
I have never come across a situation that allowed for a table to not use a PK. Even if you don't plan on using it, its purposes serves to identify the row.
Re: Finding position of a row in a query
Posted: Thu Apr 10, 2008 5:11 pm
by impulse()
It's not my table to change. They do have primary keys but after a where clause and group by the primary key number isn't the row position number anymore.
Re: Finding position of a row in a query
Posted: Thu Apr 10, 2008 5:38 pm
by John Cartwright
impulse() wrote:It's not my table to change. They do have primary keys but after a where clause and group by the primary key number isn't the row position number anymore.
from a query and they don't have a primary key or an ID column
so they do or they don't?!??!
You want to know its relative positive in the result set or its relative positive to the entire database?
The former,
Code: Select all
$i = 0;
while ($row = mysql_fetch_assoc($result)) {
++$i;
echo 'Your row: '. $i .'<br />';
}
Care to post what you've tried so far?
Re: Finding position of a row in a query
Posted: Fri Apr 11, 2008 2:11 am
by impulse()
I was hoping to do it all in a single query. I'm looking for the relative position in the result set. Because I'd already be inside a loop with a userID to run the query that you'd posted then I could be running your query upto 50 times, which is what I'm trying to reduce.
I have this at the moment:
Code: Select all
SELECT MAX(gs.score) as highScore, gl.gname, gl.gid, COUNT(*) as played, gs.datescored
FROM games_scores AS gs
JOIN games_list AS gl ON gs.gid = gl.gid
WHERE mid = {$_GET['uid']}
GROUP BY score, gl.gname, gl.gid
And then while inside that loop I'm running:
Code: Select all
$query = mysql_query("SELECT name, mid
FROM games_scores
WHERE gid = {$r['gid']}
ORDER BY score DESC");
while($r2 = mysql_fetch_array($query)) {
if($r2['mid'] != $_GET['uid'])
++$i;
else {
$pos = $i;
continue;
}
}
But as you may see, I'm running the 2nd query as many times are there are rows from the first query. I was hoping to do everything in just a single query.