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,
Finding position of a row in a query
Moderator: General Moderators
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Re: Finding position of a row in a query
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.
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.
-
impulse()
- Forum Regular
- Posts: 748
- Joined: Wed Aug 09, 2006 8:36 am
- Location: Staffordshire, UK
- Contact:
Re: Finding position of a row in a query
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.
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Re: Finding position of a row in a query
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.
so they do or they don't?!??!from a query and they don't have a primary key or an ID column
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?
-
impulse()
- Forum Regular
- Posts: 748
- Joined: Wed Aug 09, 2006 8:36 am
- Location: Staffordshire, UK
- Contact:
Re: Finding position of a row in a query
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:
And then while inside that loop I'm running:
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.
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.gidCode: 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;
}
}