SitePoint Sponsor |
|
User Tag List
Results 1 to 17 of 17
-
Jul 4, 2008, 10:46 #1
- Join Date
- Jan 2008
- Location
- Palm Harbor, FL
- Posts
- 348
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Retrieving the row value of a record with a specfic ID in the results from a query
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.
-
Jul 4, 2008, 12:09 #2
- Join Date
- Jun 2008
- Location
- Victoria, BC
- Posts
- 63
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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:
Code:$ids = array(); while($row = mysql_fetch_array($res,MYSQL_ASSOC)) { $ids[] = $row['id']; } $key_index = array_search('440',$ids);
PHP/MySQL programmer for hire!
http://www.zombat.net
-
Jul 4, 2008, 14:46 #3
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Jul 4, 2008, 17:24 #4
- Join Date
- Jan 2008
- Location
- Palm Harbor, FL
- Posts
- 348
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
-
Jul 4, 2008, 17:33 #5
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Jul 4, 2008, 17:37 #6
- Join Date
- Jan 2008
- Location
- Palm Harbor, FL
- Posts
- 348
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
-
Jul 4, 2008, 17:58 #7
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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?
-
Jul 4, 2008, 22:10 #8
- Join Date
- Jan 2008
- Location
- Palm Harbor, FL
- Posts
- 348
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Jul 4, 2008, 22:43 #9
- Join Date
- Jul 2008
- Posts
- 5,757
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Just curious, why would you want this? I'm thinking maybe theres a better solution to your problem.
-
Jul 5, 2008, 01:00 #10
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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?
-
Jul 5, 2008, 07:09 #11
- Join Date
- Jan 2008
- Location
- Palm Harbor, FL
- Posts
- 348
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Jul 5, 2008, 07:29 #12
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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 numberCode:SELECT COUNT(*)/50 + 1 AS page_number FROM blog_entries WHERE user_id = 4 AND `timestamp` > ( SELECT timestamp FROM blog_entries WHERE id = 440 )
-
Jul 5, 2008, 13:39 #13
- Join Date
- Jan 2008
- Location
- Palm Harbor, FL
- Posts
- 348
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
-
Jul 5, 2008, 15:37 #14
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
Jul 5, 2008, 16:53 #15
- Join Date
- Jan 2008
- Location
- Palm Harbor, FL
- Posts
- 348
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
"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:
Code: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' )
-
Jul 5, 2008, 18:36 #16
- Join Date
- Oct 2007
- Posts
- 44
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?
-
Jul 5, 2008, 20:55 #17
- Join Date
- Jul 2008
- Posts
- 5,757
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
Bookmarks