Retrieving the row value of a record with a specfic ID in the results from a query

[Morthian]

I want to make a PHP script that will get the results from a MySQL query, and then tell me how far down the list of results a particular record is?

For example:
I want to know how far down the list of results I will find the record with the ID of 440. If I manually look at the results, I find that it is the 112th record down on the list.

[zombat]

I want to make a PHP script that will get the results from a MySQL query, and then tell me how far down the list of results a particular record is?

For example:
I want to know how far down the list of results I will find the record with the ID of 440. If I manually look at the results, I find that it is the 112th record down on the list.

Whoops, I'm going to delete my original post. I misunderstood your post I think. I'll re-think it and post again.

[edit]
Ok, how about this:

	$ids = array();
	while($row = mysql_fetch_array($res,MYSQL_ASSOC)) {
		$ids[] = $row['id'];
	}
	$key_index = array_search('440',$ids);

Remember that $ids is going to be zero-based, so you might want to add 1 to $key_index, depending on the usage.

[r937]

I want to make a PHP script that will get the results from a MySQL query, and then tell me how far down the list of results a particular record is?

is it necessary to actually return the results of the mysql query to php?

wouldn't just getting the single number back from mysql be easier?

[Morthian]

is it necessary to actually return the results of the mysql query to php?

wouldn't just getting the single number back from mysql be easier?

Yes, it would be. Could you explain how to do that?

[r937]

Yes, it would be. Could you explain how to do that?

i'd be happy to

could you please first explain what the sequence is that determines "how far down the list of results a particular record is?"

[Morthian]

i'd be happy to

could you please first explain what the sequence is that determines "how far down the list of results a particular record is?"

You mean the query?

It would be something similar to this:

"SELECT id, entry, timestamp FROM blog_entries WHERE user_id = '$user_id' ORDER BY timestamp DESC"

[r937]

okay, and which particular row did you want to find?

i mean, it looks like that query will return all rows for a specific user, so which of that user's rows is the one you want the position of?

[Morthian]

okay, and which particular row did you want to find?

i mean, it looks like that query will return all rows for a specific user, so which of that user's rows is the one you want the position of?

Well the ID would be dynamic, based on $_GET['id']

This is the example I used in my first post:

For example:
I want to know how far down the list of results I will find the record with the ID of 440. If I manually look at the results, I find that it is the 112th record down on the list.

[crmalibu]

Just curious, why would you want this? I'm thinking maybe theres a better solution to your problem.

[r937]

okay, i think i get it... you sort all of the blog entries by latest timestamp, and you want to know where todd's entry is?

so basically, you get a user's id, and you want to know how many entries there are that came after his

is this correct?

[Morthian]

Alright, let me explain what this is for:

A web page (weblog.php) contains a listing of a blog entries (from newest to oldest) from a specific user. The URL variable, 'id', defines which user's blogs we are viewing.

Only up to 50 entries will be displayed at once. The URL variable, 'page_number', defines which 50 are shown. The most recent 50 would be on page_number 1, the next most recent 50 would be on page_number 2, etc.

I allow users the ability to edit their blog entries. When a user submits the edited entry, I want them to be redirected back to weblog.php so they can view what they just edited. The problem is that I don't know how to program the script to define the correct 'page_number'.

For example: User '4' decides to edit their 112th most recent blog entry. The script should redirect them back to: "weblog.php?user_id=4&page_number=3."

Its a rather simple concept, but hard to explain. Basically I just want a script to figure out which page a record with a particular ID is on if each page contains 50 records.

[r937]

you'll know which user it is, right? and you'll know which entry he just edited, right?

so just count the number of later entries, and use the count to calculate the page number

SELECT COUNT(*)/50 + 1 AS page_number
  FROM blog_entries
 WHERE user_id = 4
   AND `timestamp` > 
       ( SELECT timestamp
           FROM blog_entries
          WHERE id = 440 )

[Morthian]

you'll know which user it is, right? and you'll know which entry he just edited, right?

so just count the number of later entries, and use the count to calculate the page number

Wow, thanks. That does make a lot of sense.

Does MySQL have a function that would round up the page_number, like PHP's ceil() function? ...or do I just have to use PHP to do that?

[r937]

Does MySQL have a function that would round up the page_number, like PHP's ceil() function?

you know, that's a really good question

yes, mysql has the CEIL() function, but i didn't use it, i added 1 to the integer dividend instead

of course, you have to be comfortable with integer arithmetic... for example, if the user's page is 37th in sequence, then there are 36 rows with a later timestamp, so 36/50 using integer arithmetic gives 0, and then i just add 1, giving page 1

CEIL(x) returns "the smallest integer value not less than x" and i always have to stop and think (which annoys me) before deciding how to use it

[Morthian]

"the smallest integer value not less than x"?
That's a confusing way of putting it. I would just say "rounds fractions up."

Anyway, this is what I ended up with and it seems to be working just fine:

SELECT CEIL(COUNT(*)/50) AS page_number
FROM weblog_entries
WHERE user_id = '$user_id' 
    AND deletion_date IS NULL
    AND timestamp >= 
        ( SELECT timestamp
        FROM exhibition_comments
        WHERE id = '$entry_id' )

Thanks for all your help. =)

[blobaugh]

Why not just remember the page number in the get or the session and pull that back out when the user saves? Why go through all the trouble of adding extra stuff to your mysql query?

[crmalibu]

Putting such volatile state info in a session will get you into trouble when the user opens a second browser window and starts editing with both. Not that it would be disasterous here, but may confuse the user. Same with putting this in the query string. By the time they submit window#2, they may have submitted #1 too and now the state info in #2 is invalid and will show incorrect results.

Querying the db, which holds the most current state info, will always return as expected, in this circumstance at least.