...

View Full Version : Prev/Next Button to scroll through mysql database



Juniper747
06-29-2011, 04:11 AM
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:



$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?

Fumigator
06-29-2011, 05:19 PM
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:



//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:


$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());
}

Juniper747
06-30-2011, 05:40 AM
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:



//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:


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

justin482000
08-16-2011, 07:57 AM
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:



//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:


$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!

travin69
08-16-2011, 11:51 AM
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.

guelphdad
08-18-2011, 03:19 PM
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.

FuquayDentist
08-22-2011, 04:08 PM
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? :thumbsup:

travin69
08-24-2011, 03:59 PM
Great idea.

dhape
08-28-2011, 10:01 PM
Fumigator, you rock!

Coolest
08-29-2011, 02:10 AM
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!

travin69
08-29-2011, 03:40 PM
Just used this for a wordpress plugin I built. Works decent.

devonssmart
09-02-2011, 06:37 PM
Hi Fumigator, How to add "prev 1 2 3 next" links ? ( not only prev next )

blapage
12-21-2011, 05:44 AM
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.

cyadron
12-27-2011, 03:25 PM
Hello.

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

Regards.

jack78
02-20-2012, 08:12 AM
Thanks so much Fumigator!

http://marriagerevisited.com/



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum