0

Possible Duplicate:
MySql Row Number?

I'm using PHP 5 and mysqli. I have a table of chapters with columns 'id' and 'name'. Is there a simple and efficient way to query to check what index a specified row is located at? For example, lets say the records in my 'chapters' table are:

id | name

1 | Hello

4 | World

5 | How are you?

And lets say I want to find out where "How are you?" is. The desired result would be index 3 (since it is the third row in the table sorted by id ascending).

The only solution I've come up with is to query for all of them, and then in PHP loop through and find where $row["id"] == 5 and figuring out what iteration it is in the loop. Is there a more efficient way to do this?

Community
  • 1
  • 1
user1418227
  • 201
  • 1
  • 4
  • 12
  • In this case, id isn't what I am looking for because id is set to auto increment and lets say I've deleted id=2 and id=3. "How are you" will now be at index 3 while its id is still 5. But I think Victor below figured it out. Thanks though! – user1418227 Oct 20 '12 at 21:22

2 Answers2

3
SELECT position FROM
(
   SELECT
   name, @rownum:=@rownum+1 position
   FROM chapters, (SELECT @rownum:=0) r
   ORDER BY id
)
AS position
WHERE name = 'How are you?'
Victor Stanciu
  • 12,037
  • 3
  • 26
  • 34
0

Victor's answer should probs work for you, but here is another way to do it too.

 SELECT count(*) as 'pos' From chapters where id <= (Select id from chapters where name = 1567186831 limit 1) order by id
Matthew Whittemore
  • 465
  • 2
  • 11
  • Yep Victor's worked. Just wondering, is one more efficient over the other? – user1418227 Oct 20 '12 at 21:29
  • Unless your DB is massive I doubt it will make a difference, but I guess it would come down to figuring out if count(*) is better than victors sub-query. If you find any literature about it, please post it in response so we can all read and learn. – Matthew Whittemore Oct 21 '12 at 05:56