使用AJAX从数据库中检索数据，并在没有框架的情况下对它们进行分页

Question

I'm working on a project for a course on Web technologies. I want to retrieve some data from a database with AJAX request and then paginate them. The data have been filtered with this script :

function applyFilter( str, id)
{
if (window.XMLHttpRequest)
{// code for IE7+, Firefox, Chrome, Opera, Safari
    xmlhttp=new XMLHttpRequest();
}
else
{// code for IE6, IE5
    xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}

xmlhttp.onreadystatechange=function()
{
    if (xmlhttp.readyState==4 && xmlhttp.status==200)
        {
            document.getElementById("ajax_content").innerHTML=xmlhttp.responseText;
        }
}
xmlhttp.open("GET","filter_script.php?value='"+str+"'&filter="+id,true);
xmlhttp.send();
}

And this is the filter_script.php :

<?php
    $value = $_GET['value'];
    $filter = $_GET['filter'];

    //Ζητούμε από τον server το global_var αρχείο
    require_once("global_var.php");

    //Συνδεόμαστε στη Βάση δεδομένων
    $dbc = mysqli_connect(HOST, USER, PASS, DB_NAME)
        or die("Error in connecting with Databse");

    //Δημιουργούμε το κατάλληλο query. Εδώ χρειαζόμαστε
    //να πάρουμε τη λίστα με τις παραγγελίες από τη Βάση
    $query = "select date, service, price, product from orders WHERE $filter = $value  ORDER BY date DESC";

    //Αποθηκεύουμε τα αποτελέσματα από τη Βάση
    $result = mysqli_query($dbc, $query)
        or die("Error in querying the Databse");

    //Δημιουργούμε το header του πίνακα με τις παραγγελίες
    echo '<table id="orders">';
    echo    '<tr class="header">';
    echo        '<td>Date</td>';
    echo        '<td>Service</td>';
    echo        '<td>Price</td>';
    echo        '<td>Product</td>';
    echo    '</tr>';

    //Εμφανίζουμε τις παραγγελίες σε μορφή πίνακα.
    while ($row = mysqli_fetch_array($result)) {
        echo    "<tr>";
        echo        '<td class="date">'.$row['date'].'</td>';
        echo        '<td class="service">'.$row['service'].'</td>';
        echo        '<td class="price">'.$row['price'].'</td>';
        echo        '<td class="product">'.$row['product'].'</td>';
        echo    "</tr>";
    }

    echo '</table>';

    //ΚΛείνουμε τη σύνδεση με τη βάση δεδομένων
    mysqli_close($dbc);
    ?>

The comments are in greek so ignore them.

So I want a way to paginate this data every time AJAX retrive new data from the database. Please help, cause I can't find a way to do it.

Thanks in advance.

Answer 1

Have a look at: Server side paging in SQL Server

Include another parameter (say start) in your AJAX request and use that in the BETWEEN cnodition.

xmlhttp.open("GET","filter_script.php?value='"+str+"'&filter="+id+"&start="+start,true);

In real world, you'll be using some frameworks such as jQuery or Prototype. That makes use of AJAX simpler. Also consider using JSON response so that you don't have to keep changing your PHP for any UI level change in future. Any server sided script should, in most of the cases, contain only your main application logic. Don't mix up HTML there. I'm not too much into PHP but here is something you might find interesting: http://www.php.net/manual/en/function.json-encode.php

Good luck.