Question about pagination using PHP and SQL

sgalmeida

Greetings ppl,

I need your help on my problem about paginations and retrieve/update data from database. 🙂

I'm developing an administration site for a web portal and, using this administration site it is possible to manage news, that can be selected, or not, to be part of the newsletter. Well, on my administration site I have a php page that shows me all the news that are stored on the database. On that page I show, on each <td>, the title of the news (that is a link for a details page that opens the news and I can change its data), I show the news'date and I also show a checkbox that I can select if I want that news to be part of the newsletter.

As I was not using pagination, what I did to update the news for the newsletter was something like:

//to obtain the news from the database:
function getNews($from,$maxResults){
	// connects to the database

$sqlStatement="SELECT * FROM news";

// executes query and disconnects from database
}

// on page of the news I have the lines of code:
if($_POST['newscheckbox']){
updateNewsForNewsletter("",0);
foreach ( $_POST['newscheckbox'] as $oid ) { 
   updateNewsForNewsletter($oid,1); 
}

and the function updateNewsForNewsletter is:

function updateNewsForNewsletter($newsOID,$showonnewsletter){
	// connects to the database

if($newsOID){
	$sqlStatement="UPDATE news SET show".$showonnewsletter." WHERE oid=".$newsOID; // updates the show field of a specific news
}
else{
	$sqlStatement="UPDATE news SET show".$showonnewsletter; // resets the show field to all the same 
}
// execute query and disconnects from database
}

this function works if I retrieve ALL the news from the database (title, date and checkbox on each <td>) on page of trhe news(the news that are not checkbox selected are not to show on newsletter, so I do "UPDATE news SET mostrar=".$showonnewsletter😉.

But I need to use pagination (I may have hundreds of news...) so I can show 10 news at a time.
Using pagination, and to retrieve the news from database, I do something like:

function getNews($from,$maxResults){
	// connects to the database

$sqlStatement="SELECT * FROM news LIMIT $from, $maxResults";

// execute query and disconnects from database
}

So, I show the news on <td> but only 10 at a time. My problem is the update of the field "show" (to show or not the news on newsletter), for the 10 news displayed, without the other news on database (those that were not retrieved) not be affected by the query. If I do the query "UPDATE news SET show=".$showonnewsletter without any conditiom, ALL the news are affectred, and I want to be affected ONLY the 10 news that I retrived previously, i.e. , something like LIMIT $from, $maxResults on the UPDATE statement. (I don't know if this is possible.)

Anyone has some idea to this?

Many thanks 🙂

Sérgio.

Pig

I'm not sure if I understand what you are after. Let me know if I didn't get it.

The simplest route would be to have form buttons on each page for the next/previous, instead of just links. This form would have a hidden field for the variables that hold the offset for your place in the series. In addition, you would pass all of the selected items. These items would then be turned into an array and stored in a $_SESSION. On every page you add to the list in the sessions. Then you just have a link on every page (besides the previous and next links) to take you to a process page. It might be a good idea for this process page to display a summary of all the items selected, and allow you to remove those that you don't want.

I hope that made sense.

girishn

Hi,

You will get a lot of pagination scripts/classes on

http://hotscripts.com/
http://phpclasses.com/

and lot of other sites.

Or you can use this one "gnPaginator".

at www.girishn.com/pages/downloads.php