Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 15 of 15
  1. #1
    New Coder
    Join Date
    Apr 2011
    Posts
    92
    Thanks
    26
    Thanked 0 Times in 0 Posts

    Prev/Next Button to scroll through mysql database

    I have a mysql db with these columns:

    story_id (unique), user_id (foreign key), story, post_date

    I currently show on my web page, a default story, which is the story with the last (most recent) post date, so I used the following query:

    Code:
    $sql = mysql_query("SELECT story, post_date FROM memberstories WHERE user_id='$id' ORDER BY post_date DESC LIMIT 1");
    Then I just echo the story and its post_date onto the page.

    What I want to do now, is add two links "Prev." and "Next" to my web page. So that when someone clicks on previous, they will be shown the previous posted story by that user, and so on. And when they click next, they will be shown the next posted story, and so on. Kind of like click scrolling through each story.

    I am just having a little trouble figuring out what query would work in conjunction with the default story grabber above.... Any ideas?

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Since you're using MySQL, you can take advantage of the power of the LIMIT clause, which gives you the ability to say "give me 1 result, starting on the third row". You do that with "LIMIT 2, 1", because the first row starts at "0".

    So, to use this, your SELECT query will need to accept a variable to indicate which row you are viewing. You can use your webpage's query string to determine the value of this variable, and since you're viewing one row at a time (a single story), you don't have to do any calculating-- the most recent story (by post_date) is page=0 in your query string, and the "next" link will have a query string like http://yourwebpage.com?page=1. The way to build this query string in PHP:

    PHP Code:
    //get the current value of "page" in your query string and scrub it to prevent SQL injection
    if (isset($_GET['page']))
    {
        
    $currentPage mysql_real_escape_string($_GET['page']);
    }
    else
    {
        
    $currentPage 0;
    }
    //validate it and calculate values for the Next and Prev links
    if (ctype_digit($currentPage) && $currentPage >= 0)
    {
        
    $nextPage $currentPage 1;
        
    $prevPage $currentPage 1;
        
    //You probably want to get fancier with this to disable the Prev link if you're at the top
        
    if ($prevPage 0)
        {
            
    $prevPage 0;
        }
        
    //You probably want to run a query here to see if $nextPage is past the end of your resultset
    }
    $nextLink "<a href='http://yourwebpage.com?page=$nextPage'>Next</a>";
    $prevLink "<a href='http://yourwebpage.com?page=$prevPage'>Prev</a>";

    //later on in the script when you're creating your HTML output, drop in the link variables 
    Your query would change to look like:
    PHP Code:
    $sql "SELECT story, post_date FROM memberstories WHERE user_id='$id' ORDER BY post_date DESC LIMIT $page, 1";
    $result mysql_query();
    //as always, check your query for errors!
    if (!$result)
    {
        die(
    "Query Error! Query: $sql<br />Error: ".mysql_error());


  • Users who have thanked Fumigator for this post:

    Juniper747 (06-30-2011)

  • #3
    New Coder
    Join Date
    Apr 2011
    Posts
    92
    Thanks
    26
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fumigator View Post
    Since you're using MySQL, you can take advantage of the power of the LIMIT clause, which gives you the ability to say "give me 1 result, starting on the third row". You do that with "LIMIT 2, 1", because the first row starts at "0".

    So, to use this, your SELECT query will need to accept a variable to indicate which row you are viewing. You can use your webpage's query string to determine the value of this variable, and since you're viewing one row at a time (a single story), you don't have to do any calculating-- the most recent story (by post_date) is page=0 in your query string, and the "next" link will have a query string like http://yourwebpage.com?page=1. The way to build this query string in PHP:

    PHP Code:
    //get the current value of "page" in your query string and scrub it to prevent SQL injection
    if (isset($_GET['page']))
    {
        
    $currentPage mysql_real_escape_string($_GET['page']);
    }
    else
    {
        
    $currentPage 0;
    }
    //validate it and calculate values for the Next and Prev links
    if (ctype_digit($currentPage) && $currentPage >= 0)
    {
        
    $nextPage $currentPage 1;
        
    $prevPage $currentPage 1;
        
    //You probably want to get fancier with this to disable the Prev link if you're at the top
        
    if ($prevPage 0)
        {
            
    $prevPage 0;
        }
        
    //You probably want to run a query here to see if $nextPage is past the end of your resultset
    }
    $nextLink "<a href='http://yourwebpage.com?page=$nextPage'>Next</a>";
    $prevLink "<a href='http://yourwebpage.com?page=$prevPage'>Prev</a>";

    //later on in the script when you're creating your HTML output, drop in the link variables 
    Your query would change to look like:
    PHP Code:
    $sql "SELECT story, post_date FROM memberstories WHERE user_id='$id' ORDER BY post_date DESC LIMIT $page, 1";
    $result mysql_query();
    //as always, check your query for errors!
    if (!$result)
    {
        die(
    "Query Error! Query: $sql<br />Error: ".mysql_error());


    Thank you very much, this worked out great. The only problem I had was that ctype_digit was giving me problems, I think it was making my values null, so I had to remove ctype_digit($currentPage), after that it all worked fine...

  • #4
    New to the CF scene
    Join Date
    Aug 2011
    Posts
    5
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Fumigator View Post
    Since you're using MySQL, you can take advantage of the power of the LIMIT clause, which gives you the ability to say "give me 1 result, starting on the third row". You do that with "LIMIT 2, 1", because the first row starts at "0".

    So, to use this, your SELECT query will need to accept a variable to indicate which row you are viewing. You can use your webpage's query string to determine the value of this variable, and since you're viewing one row at a time (a single story), you don't have to do any calculating-- the most recent story (by post_date) is page=0 in your query string, and the "next" link will have a query string like http://yourwebpage.com?page=1. The way to build this query string in PHP:

    PHP Code:
    //get the current value of "page" in your query string and scrub it to prevent SQL injection
    if (isset($_GET['page']))
    {
        
    $currentPage mysql_real_escape_string($_GET['page']);
    }
    else
    {
        
    $currentPage 0;
    }
    //validate it and calculate values for the Next and Prev links
    if (ctype_digit($currentPage) && $currentPage >= 0)
    {
        
    $nextPage $currentPage 1;
        
    $prevPage $currentPage 1;
        
    //You probably want to get fancier with this to disable the Prev link if you're at the top
        
    if ($prevPage 0)
        {
            
    $prevPage 0;
        }
        
    //You probably want to run a query here to see if $nextPage is past the end of your resultset
    }
    $nextLink "<a href='http://yourwebpage.com?page=$nextPage'>Next</a>";
    $prevLink "<a href='http://yourwebpage.com?page=$prevPage'>Prev</a>";

    //later on in the script when you're creating your HTML output, drop in the link variables 
    Your query would change to look like:
    PHP Code:
    $sql "SELECT story, post_date FROM memberstories WHERE user_id='$id' ORDER BY post_date DESC LIMIT $page, 1";
    $result mysql_query();
    //as always, check your query for errors!
    if (!$result)
    {
        die(
    "Query Error! Query: $sql<br />Error: ".mysql_error());

    I was facing a similar problem as well, thank you for your detail answer!

  • #5
    New Coder
    Join Date
    Aug 2011
    Location
    Virginia
    Posts
    10
    Thanks
    2
    Thanked 1 Time in 1 Post
    Thats the issue with Mysql, it doesn't always accept SQL statements properly. I have migrated over to SQL myself. I am also trying flat files though I don't think they work near as nicely as MySQL.

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    MySQL handles SQL statements just fine, unless you are talking about something that is proprietary to another database engine and does not conform to current SQL standards.

    As an example, SQL Server uses TOP for instance and MySQL uses LIMIT to do similar things in returning a certain number of rows matching your query. I don't believe either is compliant with SQL standards though.

  • #7
    New Coder
    Join Date
    Aug 2011
    Location
    Fuquay Varina NC
    Posts
    21
    Thanks
    1
    Thanked 2 Times in 2 Posts

    Alternative

    That's a great solution and if you make it work, fantastic. I would just throw in my 2 cents, which would be to generate the query and pull back the initial record, say for example record #8. Then when someone clicks the >> button, you've already got the data for #8 in a variable, so just add 1 to that and rerun the query with the updated variable and redisplay the page.

    Does this make sense?

  • Users who have thanked FuquayDentist for this post:

    travin69 (08-24-2011)

  • #8
    New Coder
    Join Date
    Aug 2011
    Location
    Virginia
    Posts
    10
    Thanks
    2
    Thanked 1 Time in 1 Post
    Great idea.

  • #9
    New Coder
    Join Date
    Aug 2011
    Posts
    26
    Thanks
    2
    Thanked 1 Time in 1 Post
    Fumigator, you rock!

  • #10
    New to the CF scene
    Join Date
    Aug 2011
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The Limit clause and the code above will be a big help on a simple video gallery I have been thinking about adding to one of my websites. Glad Fumigator mentioned the fist row starts with 0 as I always get that wrong.




    Thanks!

  • #11
    New Coder
    Join Date
    Aug 2011
    Location
    Virginia
    Posts
    10
    Thanks
    2
    Thanked 1 Time in 1 Post
    Just used this for a wordpress plugin I built. Works decent.
    My Work: US Navy
    My Hobby: Best Credit Score

  • #12
    New Coder
    Join Date
    Jul 2011
    Location
    Texas
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Fumigator, How to add "prev 1 2 3 next" links ? ( not only prev next )

  • #13
    New to the CF scene
    Join Date
    Dec 2011
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by lenachassue View Post
    MySQL handles SQL records just fine, unless you are talking about an item is proprietary even to another database engine and won't conform to present SQL standards. As one example, SQL Server uses TOP an example and MySQL uses LIMIT you need to do similar things in returning a certain number of rows matching your query. I don't imagine either is compliant along with SQL standards nevertheless.
    The MYSQL max_user_connections and Max_connections can only be set in a ratio of 1:3 for best results otherwise you will ruin your server.
    Last edited by WA; 12-21-2011 at 07:17 AM.

  • #14
    New to the CF scene
    Join Date
    Dec 2011
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello.

    For pagination purposes you can user PHP Pagination from phpagination.com.
    It works very well.

    Regards.

  • #15
    New to the CF scene
    Join Date
    Feb 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks so much Fumigator!

    http://marriagerevisited.com/
    Last edited by jack78; 02-20-2012 at 08:36 AM.


  •  

    Posting Permissions

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