SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Addict
    Join Date
    Jan 2008
    Location
    Palm Harbor, FL
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation 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.

  2. #2
    SitePoint Enthusiast zombat's Avatar
    Join Date
    Jun 2008
    Location
    Victoria, BC
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Morthian View Post
    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:

    Code:
    	$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.
    PHP/MySQL programmer for hire!
    http://www.zombat.net

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Morthian View Post
    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?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict
    Join Date
    Jan 2008
    Location
    Palm Harbor, FL
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation

    Quote Originally Posted by r937 View Post
    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?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Morthian View Post
    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?"
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    Jan 2008
    Location
    Palm Harbor, FL
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation

    Quote Originally Posted by r937 View Post
    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"

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 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?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict
    Join Date
    Jan 2008
    Location
    Palm Harbor, FL
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.

  9. #9
    SitePoint Wizard bronze trophy
    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.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 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?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Addict
    Join Date
    Jan 2008
    Location
    Palm Harbor, FL
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation

    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.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 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 number
    Code:
    SELECT COUNT(*)/50 + 1 AS page_number
      FROM blog_entries
     WHERE user_id = 4
       AND `timestamp` > 
           ( SELECT timestamp
               FROM blog_entries
              WHERE id = 440 )
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Addict
    Join Date
    Jan 2008
    Location
    Palm Harbor, FL
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation

    Quote Originally Posted by r937 View Post
    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?

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Morthian View Post
    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

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Addict
    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' )
    Thanks for all your help. =)

  16. #16
    SitePoint Enthusiast
    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?

  17. #17
    SitePoint Wizard bronze trophy
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •