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 '&amp;', when echoing link here.
    }
    else
    {
        echo '<a href="' .htmlspecialchars($serps_url) .'">' ."$i" .'</a>'; //Need to add htmlspecialchars(), to convert '&' to '&amp;', 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

Q1: True, but only visible in the examples: https://www.php.net/mysqli_stmt_store_result

Q2: True, implicitly mentioned here: https://www.php.net/manual/en/mysqli-stmt.free-result.php
"Frees the result memory associated with the statement, which was allocated by mysqli_stmt_store_result()."
It is a rule of thumb in most languages that you should free/release what you allocate.

Q3: Why do you say "I should use PDO" ?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.