PHP AJAX Pagination via jQuery issue

Question

Bit of a strange one but probably a straight forward answer. Basically I am using an AJAX script to retrieve results from a PHP file and have these printed in a div with pagination applied. What I am trying to achieve is that the page search.php fires a value (a year) to the getYear.php file, this sets up the pagination process and sends the year to the pagination parser file which inturn returns the results. Essentially I have a script which sends the year, then a script which again sends the year and the pagination details and a script which applies the pagination. I have this working manually when I set the year in the URL, the results print however they do not print in the main page.

Here is the script on the search.php file:

<script>
    function showYear(year) {
        if (year == "") {
            document.getElementById("txtHint").innerHTML = "";
            return;
        } else { 
            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("txtHint").innerHTML = xmlhttp.responseText;
                }
            }
            xmlhttp.open("GET","getYear.php?year="+year,true);
            xmlhttp.send();
        }
    }
    </script> 

this does send the year value and expects the results back in the txthint div, it sends it to getYear.php:

<?php
if(!isset($_GET['year'])){
echo 'No year given';
header("Location= search.php");
}
$year = intval($_GET['year']);

$con = mysqli_connect('localhost','XXXX','XXXX','XXXX');
if (!$con) {
    die('Could not connect: ' . mysqli_error($con));
}

$sql = "SELECT COUNT(taskid) FROM tasks WHERE Year(date) = '$year'";
$query = mysqli_query($con, $sql);
$row = mysqli_fetch_row($query);

// Here we have the total row count
$total_rows = $row[0];
// Specify how many results per page
$rpp = 10;
// This tells us the page number of our last page
$last = ceil($total_rows/$rpp);
// This makes sure $last cannot be less than 1
if($last < 1){
    $last = 1;
}
// Close the database connection
mysqli_close($con);

?>
<script>
var rpp = <?php echo $rpp; ?>; // results per page
var last = <?php echo $last; ?>; // last page number
var year = <?php echo $year; ?>; // last page number
function request_page(pn){
    var results_box = document.getElementById("results_box");
    var pagination_controls = document.getElementById("pagination_controls");
    results_box.innerHTML = "loading results ...";
    var hr = new XMLHttpRequest();
    hr.open("POST", "pagination_parser.php", true);
    hr.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
    hr.onreadystatechange = function() {
        if(hr.readyState == 4 && hr.status == 200) {
            var dataArray = hr.responseText.split("||");
            var html_output = "";
            for(i = 0; i < dataArray.length - 1; i++){
                var itemArray = dataArray[i].split("|");
                html_output += "<tr><td>"+itemArray[0]+"</td> <td>"+itemArray[1]+"</td> <td>"+itemArray[2]+"</td> <td>"+itemArray[3]+"</td> <td>"+itemArray[4]+"</td></tr><hr>";
            }
            results_box.innerHTML = html_output;
        }
    }
    hr.send("rpp="+rpp+"&last="+last+"&year="+year+"&pn="+pn);
    // Change the pagination controls
    var paginationCtrls = "";
    // Only if there is more than 1 page worth of results give the user pagination controls
    if(last != 1){
        if (pn > 1) {
            paginationCtrls += '<button onclick="request_page('+(pn-1)+')">&lt;</button>';
        }
        paginationCtrls += ' &nbsp; &nbsp; <b>Page '+pn+' of '+last+'</b> &nbsp; &nbsp; ';
        if (pn != last) {
            paginationCtrls += '<button onclick="request_page('+(pn+1)+')">&gt;</button>';
        }
    }
    pagination_controls.innerHTML = paginationCtrls;
};
</script>
</head>
<div class="container-fluid"> <!-- Table Headers -->
<table class="table table-hover table-responsive">
    <thead>
        <tr>
            <th></th><th>Title:</th><th>Date:</th><th>Type:</th><th>House Location:</th><th>Housemates:</th>
            <br>
        </tr>
    </thead>      

<tbody>
<div id="pagination_controls"></div>

<div id="results_box"></div>

</tbody>
</div>
</table>

<script> request_page(1); </script>

Here is the pagination parser which does work:

<?php
// Make the script run only if there is a page number posted to this script
if(isset($_POST['pn'])){

    $rpp = preg_replace('#[^0-9]#', '', $_POST['rpp']);
    $last = preg_replace('#[^0-9]#', '', $_POST['last']);
    $year = ($_POST['year']);
    $pn = preg_replace('#[^0-9]#', '', $_POST['pn']);

    // This makes sure the page number isn't below 1, or more than our $last page
    if ($pn < 1) { 
        $pn = 1; 
    } else if ($pn > $last) { 
        $pn = $last; 
    }
    // Connect to our database here
    $con = mysqli_connect('localhost','XXX','XXX','XXXX');
    if (!$con) {
    die('Could not connect: ' . mysqli_error($con));
    }
    // This sets the range of rows to query for the chosen $pn
    $limit = 'LIMIT ' .($pn - 1) * $rpp .',' .$rpp;
    // This is your query again, it is for grabbing just one page worth of rows by applying $limit
    $sql = "SELECT * FROM tasks WHERE Year(date)=$year ORDER BY taskid DESC $limit";
    $query = mysqli_query($con, $sql);
    $dataString = '';
    while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){
        $title = $row["title"];
        $dateProduced = strftime("%b %d, %Y", strtotime($row["date"]));
        $type = $row["type"];
        $houseLocation = $row["houseLocation"];
        $housemates = $row["housemates"];
        $dataString .= $title.'|'.$dateProduced.'|'.$type.'|'.$houseLocation.'|'.$housemates.'||';
    }
    // Close your database connection
    mysqli_close($con);
    // Echo the results back to Ajax
    echo $dataString;
    exit();
}
?>

I have cut out unnecessary code and obviously changed the connection details for security. I also understand I am using mysql, I am currently in the process of learning mysqli in class however we haven't completed this yet.

Any help would really be appreciated. Thanks

Answer 1

Well if the code itself works without any problems then I think it's because your PHP is a bit too slow for javascript. If you use jquery, you may want to wrap all your code in $('document').ready(function(){}); . If you want to stick to pure javascript, you will need to put in a little more work :

document.onreadystatechange = function () {
  var state = document.readyState
  if (state == 'interactive') {
      document.getElementById('interactive').innerHTML = 'Document ready to accept interactions.'
  } else if (state == 'complete') {
      document.getElementById('complete').innerHTML = 'Document completely loaded'
  }
}

http://jsfiddle.net/electricvisions/Jacck/

Code taken from here : https://stackoverflow.com/a/9899701/1560336

EDIT:

Noticed the actual problem :

var rpp = <?php echo $rpp; ?>; // results per page
var last = <?php echo $last; ?>; // last page number
var year = <?php echo $year; ?>; // last page number

This doesn't work. Your PHP executes at server time so JS will never see those values. You will have to send them via an AJAX request. In general, and for several reasons, mixing JS and PHP is bad practice.

ANOTHER　EDIT:

$.ajax({
      type: "POST",
      url: "link to php file here",
      data: "value1="+value1+"&value2="+value2+"&value3="+value3,
      success: function (result) {
           alert('It worked!');
      }
 });

Jquery is a bit easier to do AJAX calls with than plain javascript. For your script, I would personally start by separating all your javascript into a file, and leave the php in php files. It will make things a lot easier to read for both you and everyone else. Then you will be able to save the data in php like this.

$var1 = $_POST['value1'];