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

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

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

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.

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.