I'm new at scripting, I tried to made a pagination for database results paged with javascript after a tutorial, but doesn't work, it give some error, i followed all the steps, if i did a mistake in the script but i didn't find anything so.. I will put the error
Warning: mysqli_query(): Couldn't fetch mysqli in C:..\localweb\test-pagini.php on line 6
Warning: mysqli_fetch_row() expects parameter 1 to be mysqli_result, null given in C:..\localweb\test-pagini.php on line 7
Warning: mysqli_close(): Couldn't fetch mysqli in C:...\localweb\test-pagini.php on line 19
and this is the code
//connection named mysqli_connection.php
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "db";
//create connection to mysql
$db_conx = mysqli_connect($servername, $username, $password, $dbname);
if(!$db_conx){
die("Connection failed: ". mysqli_connect_error());
}
// create table
$users = "CREATE TABLE IF NOT EXISTS users(
id INT(11) NOT NULL AUTO_INCREMENT,
titlu VARCHAR(50) NOT NULL,
descriere VARCHAR(50) NOT NULL,
data DATE NOT NULL,
approved ENUM('0','1') DEFAULT '1',
PRIMARY KEY (id)
)";
//confirm success or fail
$query = mysqli_query($db_conx, $users);
if(mysqli_query($db_conx, $users)){
echo "<h2>Success user table</h2>";
}else{
echo "<h2>Fail user table</h2>";
echo mysqli_error($db_conx);
}
// Close the database connection
mysqli_close($db_conx);
?>
// now the test-pagini.php
<?php
// Connect to our database here
include_once("/mysqli_connection.php");
// This first query is just to get the total count of rows
$sql = "SELECT COUNT(id) FROM users WHERE approved='1'";
$query = mysqli_query($db_conx, $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($db_conx);
?>
<!DOCTYPE html>
<html>
<head>
<script>
var rpp = <?php echo $rpp; ?>; // results per page
var last = <?php echo $last; ?>; // 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", "/pagini_plugin.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 += "ID: "+itemArray[0]+" - Testimonial from <b>"+itemArray[1]+"</b><hr>";
}
results_box.innerHTML = html_output;
}
}
hr.send("rpp="+rpp+"&last="+last+"&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)+')"><</button>';
}
paginationCtrls += ' <b>Page '+pn+' of '+last+'</b> ';
if (pn != last) {
paginationCtrls += '<button onclick="request_page('+(pn+1)+')">></button>';
}
}
pagination_controls.innerHTML = paginationCtrls;
}
</script>
</head>
<body>
<div id="pagination_controls"></div>
<div id="results_box"></div>
<script> request_page(1); </script>
</body>
</html>
//and the pagini_plugin.php
<?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']);
$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
include_once("mysqli_connection.php");
// 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 id, titlu, descriere, data FROM testimonials WHERE approved='1' ORDER BY id DESC $limit";
$query = mysqli_query($db_conx, $sql);
$dataString = '';
while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){
$id = $row["id"];
$titlu = $row["titlu"];
$descriere = $row["descriere"];
$itemdate = strftime("%b %d, %Y", strtotime($row["data"]));
$dataString .= $id.'|'.$firstname.'|'.$lastname.'|'.$itemdate.'||';
}
// Close your database connection
mysqli_close($db_conx);
// Echo the results back to Ajax
echo $dataString;
exit();
}
?>
I used easyPhp v14.1 , and Windows7 .
You've close your db connection to quickly:
mysqli_close($db_conx);
At end of mysqli_connection.php
included into test-pagini.php
Connection close, then no fetch possible..
Note:
include_once("/mysqli_connection.php");
remove the "/", is correct path syntax
Note2:
in first line of pagini_plugin.php
if(isset($_POST['pn'])){
Use empty instead of isset, to avoid an empty query that native function, avoid as also the same behavior as isset (two in one) Look the difference : empty() VS isset()
if(!empty($_POST['pn'])){
EDIT:
Actually your iterate too much errors to continue in comments box.. I think the asked question have been answered and more.
Looking for a modern system: You will save lot of time, lot of errors due to outdated and erroneous coding method.
PHP, MYSQL, HTML table with tablesorter
http://tablesorter.com/docs/#Examples
(sry but i doesn't have more time to help you, 8 hours on stackoverflow, i've to sleep :))
I think that is because you are closing your connection in mysqli_connection.php at the end of the file.
// Close the database connection mysqli_close($db_conx);