Hello There,
Look at this PAGINATION I built.
This script I built from scratch and is working fine to query my Mysql DB and show results.
But I need your feed-back to know if I managed to use the functions in the correct order or not.
//FUNCTIONS IN USE TO QUERY DATABASE:
//mysqli_stmt_store_result().
//mysqli_stmt_free_result().
//$rows_count = mysqli_stmt_num_rows($stmt).
//mysqli_stmt_get_result().
//FUNCTIONS IN USE TO BUILD PAGINATION SECTION
//urlencode().
//rawurlencode().
//htmlspecialchars().
I have a few questions.
Q1. Is it true that, I need to use mysqli_stmt_store_result($stmt) prior to using mysqli_stmt_num_rows($stmt) ?
Q2. Is it true that, I need to use mysqli_stmt_free_result($stmt) after every mysqli_stmt_store_result($stmt) ?
<?php
//FUNCTIONS IN USE TO QUERY DATABASE:
//mysqli_stmt_store_result().
//mysqli_stmt_free_result().
//$rows_count = mysqli_stmt_num_rows($stmt).
//mysqli_stmt_get_result().
//FUNCTIONS IN USE TO BUILD PAGINATION SECTION
//urlencode().
//rawurlencode().
//htmlspecialchars().
//Report Error.
ini_set('display_errors',1);
ini_set('display_startup_errors',1);
error_reporting(E_ALL);
//Valid $_GET Items.
$tbls = array('spidered_web_index','$submitted_web_index');
$spidered_web_index = array('id','date_and_time','domain','url','title','header','meta_keyword','meta_description','keyword','keyphrase');
$submitted_web_index = array('id','date_and_time','domain','url','title','description','country','keyword','keyphrase');
//Extract $_GETs.
$tbl = !EMPTY($_POST['tbl'])?strtolower($_POST['tbl']):(!EMPTY($_GET['tbl'])?strtolower($_GET['tbl']):'listed_links');
$input_1 = !EMPTY($_GET['input_1'])?$_GET['input_1']:die('Make your input for us to search!');
$input_2 = !EMPTY($_GET['input_2'])?$_GET['input_2']:null;
$col_1 = !EMPTY($_GET['col_1'])?strtolower($_GET['col_1']):die('Input MySql Column to search!');
$col_2 = !EMPTY($_GET['col_2'])?strtolower($_GET['col_2']):null;
$bool = !EMPTY($_GET['bool'])?strtolower($_GET['bool']):null;
$page = !EMPTY($_GET['pg'])?intval($_GET['pg']):1;
$limit = !EMPTY($_GET['lmt'])?intval($_GET['lmt']):1;
$offset = ($page*$limit)-$limit;
if(ISSET($col_2))
{
if(!in_array($col_2,$links_table_columns))
{
die('Invalid Mysql Table!');
}
}
if(!in_array($col_1,$links_table_columns))
{
die('Invalid Mysql Table!');
}
//Query DB.
mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);
$conn = mysqli_connect("localhost","root","","buzz"); //mysqli_connect("server","user","password","db");
mysqli_set_charset($conn,'utf8mb4');
if(mysqli_connect_errno())
{
printf("Mysqli Connection Error: %s",mysqli_connect_error());
}
$stmt = mysqli_stmt_init($conn);
if($bool=='and')
{
$input_1 = $_GET['input_1'];
$input_2 = $_GET['input_2'];
$sql_count = "SELECT id,domain,word,phrase from $tbl WHERE $col_1 = ? AND $col_2 = ?";
$sql = "SELECT id,domain,word,phrase from $tbl WHERE $col_1 = ? AND $col_2 = ? LIMIT $limit OFFSET $offset";
}
elseif($bool=='or')
{
$input_1 = $_GET['input_1'];
$input_2 = $_GET['input_2'];
$sql_count = "SELECT id,domain,word,phrase from $tbl WHERE $col_1 = ? OR $col_2 = ?";
$sql = "SELECT id,domain,word,phrase from $tbl WHERE $col_1 = ? OR $col_2 = ? LIMIT $limit OFFSET $offset";
}
else
{
$input_1 = $_GET['input_1'];
$sql_count = "SELECT id,domain,word,phrase from $tbl WHERE $col_1 = ?";
$sql = "SELECT id,domain,word,phrase from $tbl WHERE $col_1 = ? LIMIT $limit OFFSET $offset";
}
if(!mysqli_stmt_prepare($stmt,$sql_count)) //Fetch All Matching Rows Number.
{
echo 'Mysqli Error: ' .mysqli_stmt_error($stmt);
echo '<br>';
echo 'Mysqli Error No: ' .mysqli_stmt_errno($stmt);
}
else
{
if($bool=='and' || $bool=='or')
{
mysqli_stmt_bind_param($stmt,"ss",$input_1,$input_2);
}
else
{
mysqli_stmt_bind_param($stmt,"s",$input_1);
}
mysqli_stmt_execute($stmt);
mysqli_stmt_store_result($stmt); //Necessary to use with mysqli_stmt_num_rows() when SQL query is SELECT.
//Fetch Matching Rows Count.
//mysqli_stmt_num_rows() has to come after mysqli_stmt_store_result().
echo 'Total Result: ' .$rows_count = mysqli_stmt_num_rows($stmt); echo '<br><br>';
mysqli_stmt_free_result($stmt); //Is this really necessary ?
}
if(!mysqli_stmt_prepare($stmt,$sql)) //Fetch Rows based on Row Limit per page.
{
echo 'Mysqli Error: ' .mysqli_stmt_error($stmt);
echo '<br>';
echo 'Mysqli Error No: ' .mysqli_stmt_errno($stmt);
}
else
{
if($bool=='and' || $bool=='or')
{
mysqli_stmt_bind_param($stmt,"ss",$input_1,$input_2);
}
else
{
mysqli_stmt_bind_param($stmt,"s",$input_1);
}
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
while($row = mysqli_fetch_array($result,MYSQLI_ASSOC))
{
$id = $row['id'];
$domain = $row['domain'];
$word = $row['word'];
$phrase = $row['phrase'];
echo "$id<br>";
echo "$domain<br>";
echo "$word<br>";
echo "$phrase<br>";
echo "<br>";
}
}
mysqli_stmt_close($stmt);
mysqli_close($conn);
echo 'Total Pages: ' .$total_pages = ceil($rows_count/$limit); echo '<br><br>';
$i = 0;
while($i<$total_pages)
{
$i++;
if($bool=='and' || $bool=='or')
{
$serps_url = $_SERVER['PHP_SELF'].'?'.'tbl='.urlencode($tbl).'&'.'col_1='.urlencode($col_1).'&'.'col_2='.urlencode($col_2).'&'.'bool='.$bool.'&'.'input_1='.urlencode($input_1).'&'.'input_2='.urlencode($input_2).'&'.'lmt='.intval($limit).'&'.'pg='.intval($i);
}
else
{
$serps_url = $_SERVER['PHP_SELF'].'?'.'tbl='.urlencode($tbl).'&'.'col_1='.urlencode($col_1).'&'.'bool='.urlencode($bool).'&'.'input_1='.urlencode($input_1).'&'.'lmt='.intval($limit).'&'.'pg='.intval($i);
}
if($i==$page)
{
echo '<a href="' .htmlspecialchars($serps_url) .'">' ."<b>$i</b>" .'</a>'; //Need to add htmlspecialchars(), to convert '&' to '&', when echoing link here.
}
else
{
echo '<a href="' .htmlspecialchars($serps_url) .'">' ."$i" .'</a>'; //Need to add htmlspecialchars(), to convert '&' to '&', when echoing link here.
}
}
echo '<br>';
?>On the above code, search for the comment:
//Is this really necessary here ?
And answer that question.
Q3. Anything else I need to know apart from I should use pdo ?
Q4. Is my code bad, ok, good or great ? I reckon it is ok.
Thanks
