Web Analytics Made Easy -
StatCounter Question about pagination using PHP and SQL - CodingForum


No announcement yet.

Question about pagination using PHP and SQL

  • Filter
  • Time
  • Show
Clear All
new posts

  • Question about pagination using PHP and SQL

    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:

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

    PHP Code:
    // on page of the news I have the lines of code:
    foreach ( 
    $_POST['newscheckbox'] as $oid ) { 

    and the function updateNewsForNewsletter is:

    PHP Code:
    function updateNewsForNewsletter($newsOID,$showonnewsletter){
    // connects to the database
    $sqlStatement="UPDATE news SET show".$showonnewsletter." WHERE oid=".$newsOID// updates the show field of a specific news
    $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:

    PHP Code:
    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


  • #2
    this link might help you out a bit....

    why not have two vars (when reading):

    $start & $end

    and store the ID's...then insert/update where id =< start or => end ?