PAGINATION 1 - mysqli_stmtm_store_result() Query

TheStudent2023 · April 30, 2023

Hiya,

Check this PAGINATION out that I built! What you think ?
Built from scratch I did and is working fine to query my Mysql DB and show results.
However, 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

Strider64 · April 30, 2023

To be truthful you need to watch or read a tutorial on pagination as that what I did.

The following is basically the steps to take to do pagination.


// Grab the current page the user is on
if (isset($_GET['page']) && !empty($_GET['page'])) {
    $current_page = urldecode($_GET['page']);
} else {
    $current_page = 1;
}

$per_page = 1; // Total number of records to be displayed:

// Grab Total Pages
$total_pages = $gallery->total_pages($total_count, $per_page);


/* Grab the offset (page) location from using the offset method */
/* $per_page * ($current_page - 1) */
$offset = $gallery->offset($per_page, $current_page);

// Figure out the Links that you want the display to look like
$links = new Links($current_page, $per_page, $total_count, $category);

// Finally grab the records that are actually going to be displayed on the page
$records = $gallery->page($per_page, $offset, 'gallery', $category);

Granted there is much more coding, but the above is the basic steps in pagination. To see it in action just visit my website.

mac_gyver · May 1, 2023

this code can be greatly simplified, because writing out code for every input or combination of values is not effective programming.

switching to the much simpler and more modern PDO database extension will eliminate about half of the database statements. this alone will answer your database questions since the PDO extension was designed to be simple and usable.

here's a specific list of practices, most of which will simplify and clean up the code -

put the php error settings in the php.ini on your system, so that you can set/change them at a single point.
you cannot set display_startup_errors in your code because php has already started when your code is being executed.
the code for any page should be laid out in this general order - 1) initialization, 2) post method form processing, 3) get method business logic - get/produce data needed to display the page, 4) html document.
most of the php code needed for searching/pagination goes in the get method business logic section.
post method forms are used when performing an action on the server, such as inserting, updating, deleting data, sending an email, writing/updating a field.
get method forms/links are used when determining what will be displayed on a page, e.g. searching, pagination.
the second entry in the $tbls array contains a $. did you actually use this array to validate the $tbl input?
don't use the root database user in applications. create a specific database user, with a password, with only the necessary permissions for use in an application.
don't output raw database errors onto a wab page. this only helps hackers. instead, use exceptions for database statement errors (this is the default for both the mysqli and PDO extension in php8+) and in most cases simply let php catch and handle any database statement error, where php will use its error related settings to control what happens with the actual error information (database statement errors will 'automatically' get displayed/logged the same as php errors.) the exception to this rule is when inserting/updating duplicate or out of range user submitted data. in this case, your code would catch the exception, test if the error number is for something your code is designed to handle, and setup a unique and helpful error message for the user. for all other error numbers, just re-throw the exception and let php handle it. this will let you remove all the existing database error handling logic, simplifying the code. btw - mysqli_stmt_execut() can fail with an error but you don't have error handling logic for it, but since you will be removing all the existing error handling logic when using exceptions for database statement errors, you won't have to remove this non-existent code.
if you are going to continue to use the mysqli extension, don't use mysqli_stmt_init() followed by mysqli_stmt_prepare(), just use mysqli_prepare().
if you are going to continue to use the mysqli extension, use the OOP notation. it is both simpler and because the php error response is different between the procedural and OOP notation, things that are fatal problems will produce fatal php errors when using OOP, but only produce warnings when using procedural notation.
in most cases, you don't need to free prepared statements, result sets, or close database connections since php will destroy all resources created on a page when your script ends.
you should keep the input data as a set in a php array variable, then operate on elements in this array variable throughout the rest of the code, i.e. don't write out line after line of code that's basically copying each input to another variable.
you should trim all input data before validating it. after you do the above item, you can accomplish this with one single line of php code.
related to the above item, don't copy fetched data to other variables for nothing. just use the original variables that the data is in.
you should store user/validation errors in an array, using the field/input name as the main array index.
after the end of all the validation logic, if there are no errors (the array holding the errors will be empty), use the submitted data.
if there are errors, the code will continue on to display the html document, test and display any errors, re-display any form, populating the form field values with any existing data so that the user doesn't need to keep reentering/selecting data over and over.
any dynamic value you output in a html context needs to have htmlentities() applied to it to help prevent cross site scripting.
at the point of dynamically building the sql query statement, the table and columns names can technically be anything and should be enclosed by back-ticks so as to not produce sql errors.
are you sure you actually tested this? the definition for $links_table_columns doesn't exist in the code and even if it did, it is apparently permitted column names, so the error message would be for an invalid column, not an invalid table.
pagination involves two similar queries. the first query is to get the total number of matching rows. the second query is to get the logical page of data. the common part of both of these queries should be built once, in a php variable, with a corresponding array of input parameters. the common part of the query is from the FROM term ... through to any HAVING term. the query to get the total number of matching rows should use SELECT COUNT(*) followed by the common part of the query, i.e. it should NOT select all the data just to get a count of the number of rows of data. the data retrieval query would instead SELECT the columns you want, followed by the common part of the sql query, followed by an ORDER BY term, and then the LIMIT term. you should supply the two LIMIT values via prepared query place holders and add (append/merge) the two values to the array of input values.
since you will be using a SELECT COUNT(*) ... query to get the total number of matching rows, you won't need to deal with mysqli's mysqli_stmt_store_result() and mysqli_stmt_num_rows()
don't use a series of name_x variables. use an array. arrays are for sets of data where you will operate on each member in the set in the same/similar way. you can then use php's array functions to operate on the arrays of data.
the pagination link markup should be built in a php variable so that you can output the result wherever and however many times you want.
use relative urls in the pagination links. this will eliminate PHP_SELF.
to build the query string part of the pagination links, get an existing copy of any $_GET parameters (this will automatically include any table, input, col, bool, and limit values so that you don't need to write out code for each one) set/modify the page (pg) element, then use http_build_query() to build the query string. this will take care of urlencodeing the values.

TheStudent2023 · May 1, 2023

@MacGuyver

Thanks for the lengthy feed-back.

I actually did not test the above code. If I copy the original code here that I tested, it might be too long and you people might mind and not bother looking into it. And so, I shortened it in a rush lastnight, changing the arrays and their values by copying them from a new project I am working on, before posting it here when I was about to shut down my laptop for the night as sleep was in my eyes. Hence, you are finding typos. The main body of the script had been tested and working before I made the array changes lastnight as the script was built like 6 mnths ago and it was working. After making lastnight's changes in the array names and values, I did not fully test it. So, you are spot on here!

As for me fixing it with all your fixes tonight, that I am afraid I cannot do overnight as there is a lot to digest of what fix advices you gave above. I just read your fixes once. Will probably have to read about 5 times before everything sinks in. Ok ?

Oh yes, by the way, I actually built many PAGINATION scripts. Each of them coded using different functions from each other.

1.

This particular thread is about the PAGINATION (Template 1) I built using these functions:

//mysqli_stmt_store_result().
//mysqli_stmt_free_result().
//$rows_count = mysqli_stmt_num_rows($stmt).
//mysqli_stmt_get_result().

//urlencode().
//rawurlencode().
//htmlspecialchars().

2.

I have built another 2nd PAGINATION script (Template 2) using the following functions. I will open another thread regarding it.

//mysqli_stmt_store_result();
//mysqli_stmt_free_result();
//$rows_count = mysqli_stmt_affected_rows($stmt);
//mysqli_stmt_get_result();

//urlencode().
//rawurlencode().
//htmlspecialchars().

3.

I have built another 3rd PAGINATION script (Template 3) using the following functions. I will open another thread regarding it.

//SQL COUNT();
//mysqli_stmt_bind_result().
//http_build_query().

The differences between each of the 3 scripts are marked above in bold. Look-out for my upcoming threads regarding them. Bear in mind, I built these 3 templates last year. Probably 6-12mnths ago. I did not think they were faulty. Lastnight, thought best I get them checked here before building any website. And so, doing that. To see what the PROS say.

So, why did I build 3 different PAGINATIONs. Just experimenting with different functions (bold ones above). That is how I learn how to use different functions. I test, experiment, play, fiddle, compare, etc. That is how I gain work experience.

Anyway, did I miss your answers or did you forget to answer them ? Look at my original post for my questions. I need to know their answers. That is why I opened this thread.

If I missed your answers, can you kindly point them out to me ?

Thanks!

Edited May 1, 2023 by TheStudent2023

TheStudent2023 · May 1, 2023

@macguyver

Doing a combing operation on your feed-back ....

>>switching to the much simpler and more modern PDO database extension will eliminate about half of the database statements. this alone will answer your database questions since the PDO extension was designed to be simple and usable.<<

Sorry. I am nearly in my late 40's. You know at this age, nothing sinks into the memory. More like you forget things at this age.

Took me nearly 6yrs to go past beginner level in php. Struggling at intermediate level as learning from home all by myself. Took me that long to learn to build membership site with mysqli & prepared statements in procedural style. It will probably take another 6yrs to learn oop & pdo. And so, not bothering with pdo anymore. After I finish my project to build membership site (reg, login, logout, acc homepage, site search page) and searchengine (index, crawler), I am jumping into the more simpler python language. Php is too complicated. And so, I appreciate all your upcoming reviews, feedbacks, advices, tips, etc. of my codes on mysqli & prepared statements ONLY.

I got no programming background. Self learning php. Php is my first language. I hope you understand. Struggling with php as it is. It's amazing, I got this far daring to build my own membership site, searchengine with beginner level (mysqli & procedural style) programming at this age. Gonna be a pensioner in one & half a dacade, if I live.

>>1. put the php error settings in the php.ini on your system, so that you can set/change them at a single point.<<

I use localhost Wampp. Before Xampp. Do not know how to fiddle with .ini file. I just add the error reporting to another file and call it on the main file. I added the error reporting code on my op so you do not repeat yourself advising me to add it. Show me how you write your error reporting codes, if mine not perfect.

I am not good in following instructions without seeing code examples. Seeing code examples, gets imprinted in my mind and I tend to remember things. Else, I will make the same mistakes again and again on my upcoming codes and threads and you will waste your breath repeating the same thing over & over. So, best you just type your error reporting codes here for me to memorise as I write codes from memory on all my projects after learning the codes. I do not copy-paste codes to my projects. So, for me to memorise the error reporting codes, do write them and show me how I should have written them. Here is my faulty code again:

	//Report Error.
ini_set('display_errors',1);
ini_set('display_startup_errors',1);
error_reporting(E_ALL);

>>2. you cannot set display_startup_errors in your code because php has already started when your code is being executed.<<

Ha ? I do not follow you! How come no other programmer picked-up on this mistake ? Infact, they told me to write those particular codes:

	//Report Error.
ini_set('display_errors',1);
ini_set('display_startup_errors',1);
error_reporting(E_ALL);

Again, do write me your code so it sinks into my understanding what you really mean.

And, when and where shall I write this following code bit then ?

	ini_set('display_startup_errors',1);

EDIT: Oh, I get it. That particular line is supposed to shoe me admin error when I startup the php interpreter on my webserver! Yes ?

>>3. the code for any page should be laid out in this general order - 1) initialization, 2) post method form processing, 3) get method business logic - get/produce data needed to display the page, 4) html document.<<

I did do it in this order. I just forgot the $var initilisation ($vars = NULL) bit and did not bother building the form in this TEMPLATE yet. Apart from that, I see my whole script in your general order. Is it not ? If not, do point out to me where not. I am slow to catch on, at this age.

>>4. most of the php code needed for searching/pagination goes in the get method business logic section.<<

>>5. post method forms are used when performing an action on the server, such as inserting, updating, deleting data, sending an email, writing/updating a field.<<

>>6. get method forms/links are used when determining what will be displayed on a page, e.g. searching, pagination.<<

I know. I built my registration.php & login.php with POST METHOD. Built pagination.php/search.php with GET Method originally few yrs back. Then last yr, attempted with POST method and got stuck and quit half-way. I think IO built another half with both POST & GET methods. I will show you them to you maybe in future when I look back into those projects. Ok ? I like to fiddle around. Experiment unorthodox ways. And so, doing it.

>>7. the second entry in the $tbls array contains a $. did you actually use this array to validate the $tbl input?<<

Already answered this in my previous reply to you.

>>8. don't use the root database user in applications. create a specific database user, with a password, with only the necessary permissions for use in an application.<<

Scratching my head! How did you know I was using the root here ? What is the big techno secret ?

EDIT: Silly me! You spotted this:

	$conn = mysqli_connect("localhost","root","","buzz"); //mysqli_connect("server","user","password","db");

How will hacker know I used root and how will he benefit if I did and how will I benefit if I did not use the root user ?

>>9. don't output raw database errors onto a wab page. this only helps hackers. instead, use exceptions for database statement errors (this is the default for both the mysqli and PDO extension in php8+) and in most cases simply let php catch and handle any database statement error, where php will use its error related settings to control what happens with the actual error information (database statement errors will 'automatically' get displayed/logged the same as php errors.) the exception to this rule is when inserting/updating duplicate or out of range user submitted data. in this case, your code would catch the exception, test if the error number is for something your code is designed to handle, and setup a unique and helpful error message for the user. for all other error numbers, just re-throw the exception and let php handle it. this will let you remove all the existing database error handling logic, simplifying the code. btw - mysqli_stmt_execut() can fail with an error but you don't have error handling logic for it, but since you will be removing all the existing error handling logic when using exceptions for database statement errors, you won't have to remove this non-existent code.<<

Kindly, show me how to write this code on this particular TEMPLATE. Do write comments as I want to understand each line. Not interested in just copy-pasting & memorising codes I do not understand.

>>10. if you are going to continue to use the mysqli extension, don't use mysqli_stmt_init() followed by mysqli_stmt_prepare(), just use mysqli_prepare().<<

I thought I was using the new one. That other one is some wrapper of some sort or alias. Php interpreting the alias might slow down my page if millions of people are on my website. Right ?

>>11. if you are going to continue to use the mysqli extension, use the OOP notation. it is both simpler and because the php error response is different between the procedural and OOP notation, things that are fatal problems will produce fatal php errors when using OOP, but only produce warnings when using procedural notation.<<

You mean, procedural uses the circular vrackets and oop uses the ->. I got used to learning the former as initial tutorials taught me that format and I memorised it. Then, I came across the -> format and I tried learning it but the other format I learnt kept muddling me up and so I quit the new format. Even though new format is shorter. I thought that stuff -> was oop. But now I hear it's not oopand so that is probably what you mean by oop notation. You mean "oop like martker" ? Yes ? I tried learning pdo and oop. I just do not understand what an object is. read tutorials. No luck To me, an object is a physical item. And sop, do not understand all this object stuff in programming. Do not understand the difference between the procedural & oop. All I see is marker's different. On one you use () and on the other you use ->. That is all I understand. What has object or item or thing got to do with any of this programming, I do not understand. Beats me. Hence, I stick to mysqli and procedural style. Understand ?

>>12. in most cases, you don't need to free prepared statements, result sets, or close database connections since php will destroy all resources created on a page when your script ends.<<

So, why does the php manual and tutorials show to write all these lines of codes then ? or, they were necessary in previous versions ?

In that case, they should have updated the manual. Because, originally I did not use them but few yrs later came across them and thought have to use them.

I tell you what. You seem to know your stuffs. And not some any Tom, Dick & Harry, Larry and Mahony. So, I think it is best you weed-out the chaff from the wheat on my op code. That way, I can learn your format, the proper professional way and unlearn the chaffs I learnt from tutorials and the manual. That would be a very good idea!

>>13. you should keep the input data as a set in a php array variable, then operate on elements in this array variable throughout the rest of the code, i.e. don't write out line after line of code that's basically copying each input to another variable.<<

Sorry. I do not quite follow. Getting confused now. Can you grab snippets of my code and show me how to write what you saying to write. Then, I should be able to understand what you mean. Examples speak a million words.

Guessing you mean not to do like this the long way:

	$col = $_GET[col];
	echo $col;

But do it short & direct:

	echo $_GET[col];

I used to originally write like that. But then saw others or some tutorials do it that other long way and so stuck to it.

When I write TEMPLATES, on one I write the long way and on another I write the short way. I like doing things different ways and making many versions of the same script. It is my habit. Good or bad. If it is bad, then explain why.

In the retail industry, they build model 1, then 2. But release model 2 not. Else, model 1 will not sell. So they release model 1 first and then model 2. Thus making same customer buy twice. I doing same. Release long version first. Then, the short version. Currently, not selling any versions of scripts. Planning on giving them away for free or build my website with long version first and sell it. And then build a 2nd website with short version. Customer cannot argue my new website got exactly same code. No copyright issue will be here.

>>14. you should trim all input data before validating it. after you do the above item, you can accomplish this with one single line of php code.<<

You mean do some thing like this:

	$input_2 = !EMPTY(trim($_GET['input_2']))?trim($_GET['input_2']):null;

I used to do that. On this case, I forgot. I trim the $_POSTs.

>>15. related to the above item, don't copy fetched data to other variables for nothing. just use the original variables that the data is in.<<

I think you talking about this:

	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>";
    }

To do it like this:

	while($row = mysqli_fetch_array($result,MYSQLI_ASSOC))
    {
        echo $id = $row['id'];
        echo  $domain = $row['domain'];
        echo  $word = $row['word'];
        echo $phrase = $row['phrase'];
        
   }

Well, tutorials showed me to do it like former. And I do make 2 versions on these, like I mentioned earlier. The former version and the latter.

On my latest 4th PAGINATION version, lastnight I wrote like this:

	while($row = mysqli_fetch_array($result,MYSQLI_ASSOC))
        {
            for($i=0;$i<$columns_count;$i++)
            {
                echo '<b>'.$headings[$i].': </b>'.$row[$columns[$i]]; echo '<br>';
            }
        }

But that is another story & project. That new project is about shortening the PAGINATION code as much as possible. PAGINATION version 4. I will open a thread about that on this forum. So, do lookout for it.

>>16. you should store user/validation errors in an array, using the field/input name as the main array index.<<

Yes.very little tutorials and a programmer or 2 showed me to do it like that. But I find it complicated and just go straight and direct rather than deal with arrays here as the php intepreter going inside arrays and grabbing the error data will slow down my page if millions of people are browsing the SERP at the same time. More cpu used. Remember, I am building a searchengine.

>>17. after the end of all the validation logic, if there are no errors (the array holding the errors will be empty), use the submitted data.<<

If you beg to differ what I claimed above then do remind me how to write these error arrays as I have forgotten it as I am not in the habit of writing errors in arrays.

>>18. if there are errors, the code will continue on to display the html document, test and display any errors, re-display any form, populating the form field values with any existing data so that the user doesn't need to keep reentering/selecting data over and over.<<

Thanks. I have done this before on my other projects. This is where when user makes input mistakes on signup form then input gets repopulated on the form after they get error from the reg page. But this code on this thread is a BAREBONE TEMPLATE. hence, I did not add all that luxury stuff. When I work on a project, I will just copy-paste these BAREBONE TEMPLATES onto my project file and then add the extra bits, like search filters and all the other luxury stuffs. Right now, I just showing you BAREBONE. On my other threads, I will show you the NON-TEMPLATES/NON-BARE BONES but my actual project codes for you to review. On them all these extra luxury bits will be present. Ok ? On my threads and on my codes, when it says TEMPLATE, then it is BARE MINIMUM BONE. Else, it is PROJECT FILE that will have all the luxury bits you expecting. Remember, what I said as my codes & threads will come in 2 flavours: TEMPLATE, PROJECT.

I think you get the picture.

>>19. any dynamic value you output in a html context needs to have htmlentities() applied to it to help prevent cross site scripting.

Yeah, I know. Notice my PAGINATION section at the bottom. It took me mnths to learn all the htmlspecialchars(), htmlentities(), urlencode(), intval(), etc, stuffs (to build PAGINATION section at bottom of page) and they still confuse me now and then. And lo and behold! I came across http_nuild_query() and got pissed off why php INC does not weed-out old stuffs! You understand why it taking me 6yrs at beginner level ? I first learnt mysql little bit. The come across mysqli_. Then have to unlearn some stuffs about querying DBs as came across prepared statements. Then spent a yr or 2 learning that. Then came across pdo and got even more pissed off why php INC did not delete mysqli_, prepared statements from manual and why they made me learn old stuffs. Now I very angry and unwilling to jump to pdo as by the time I learn it another nonsense will come out and pdo will be museum piece. Hence, once I finish my current projects I quitting php as php is full of old and new stuffs all mixed up. Do not forget I am self learning from home and had to learn things the hard way what is old and what is new. Best, after current projects I ditch php for python as that is new lang and simpler and most likely not riddled with old timers alongside new timers. Won't have to learn and then unlearn to learn a new stuff. Life is short.

>>20 at the point of dynamically building the sql query statement, the table and columns names can technically be anything and should be enclosed by back-ticks so as to not produce sql errors.<<

Back ticks ? I do not follow you. Care to elaborate with an example code snippet ?

>>22. pagination involves two similar queries. the first query is to get the total number of matching rows. the second query is to get the logical page of data. the common part of both of these queries should be built once, in a php variable, with a corresponding array of input parameters. the common part of the query is from the FROM term ... through to any HAVING term. the query to get the total number of matching rows should use SELECT COUNT(*) followed by the common part of the query, i.e. it should NOT select all the data just to get a count of the number of rows of data. the data retrieval query would instead SELECT the columns you want, followed by the common part of the sql query, followed by an ORDER BY term, and then the LIMIT term. you should supply the two LIMIT values via prepared query place holders and add (append/merge) the two values to the array of input values.<<

I do not want to use the COUNT() sql here as I have the 3rd TEMPLATE using it. This is TEMPLATE 1 to use mysqli_stmt_num_rows(). Note, I did ask in my op for programmers tpo check if I used that function correctly or not. On my 3rd TEMPLATE thread, I will ask programmers,whether I used the COUNT() sql function correctly or not. On that one, I use mysqli_stmtm_bind() and not mysqli_stmt_get_result(). You do understand what I am doing here, right ? I am using different functions on different TEMPLATES to achieve the same purpose. Build pagination. Then, I can test which TEMPLATE is faster when millions of people are on the same SERP. It's all about experimenting which functions are faster.

Hence, kindly grab my code on my op and show me how to use the mysqli_stmt_num_rows().

	SELECT id,domain,word,phrase from $tbl WHERE $col_1 = ? OR $col_2 = ? LIMIT $limit OFFSET $offset"

And not:

	SELECT COUNT(id) from $tbl WHERE $col_1 = ? AND $col_2 = ?

As latter is on TEMPLATE 3.

This thread is about TEMPLATE 1 only.

>>23. since you will be using a SELECT COUNT(*) ... query to get the total number of matching rows, you won't need to deal with mysqli's mysqli_stmt_store_result() and mysqli_stmt_num_rows() <<

No. I have used SELECT COUNT(*) on TEMPLATE 3. Will update it to meet your min standards you have advised on this thread. But on this thread, we deal with

mysqli's mysqli_stmt_store_result() and mysqli_stmt_num_rows() as I still want to learn them. Even if they are old. So, do not mind fixing my code and showing me how it should be done so I can see and learn from YOU how things should be done with these 2 particular functions. Once, I embark on a function, I do not quit half way. You do not want me getting in the habit of quitting things half-way so assist me to learn these 2 functions without quitting now for anything else.

>>24. don't use a series of name_x variables. use an array. arrays are for sets of data where you will operate on each member in the set in the same/similar way. you can then use php's array functions to operate on the arrays of data.<<

I am confused. Why did I use name_x variables that I should have used arrays ? Point me the lines you do not like. Then write the lines how you fancy them. Then, I get rolling to memorise your format.

>>25. the pagination link markup should be built in a php variable so that you can output the result wherever and however many times you want.

use relative urls in the pagination links. this will eliminate PHP_SELF.<<

"link matrkup" ? What is that ? "markup" ?

This TEMPLATE 1 builds PAGINATION SECTION like this:

	$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.
    }
}

Template 2 & 3 uses this instead:

	$i = 0;
while($i<$total_pages)
{
    $i++;
    if($_GET['bool']=='null')
    {
        //Page Format: $_GET List.
        $array = array("tbl"=>"$tbl","col_1"=>"$col_1","bool"=>"$bool","input_1"=>"$input_1","lmt"=>"$limit","pg"=>intval($i));
    }
    else
    {
        //Page Format: $_GET List.
        $array = array("tbl"=>"$tbl","col_1"=>"$col_1","col_2"=>"$col_2","bool"=>"$bool","input_1"=>"$input_1","input_2"=>"$input_2","lmt"=>"$limit","pg"=>intval($i));
    }
    
    $serps_url = $_SERVER['PHP_SELF'].'?'.http_build_query($array);
    
    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.
    }
}

Now, what do you mean by "markup" ? Point!

>>27. to build the query string part of the pagination links, get an existing copy of any $_GET parameters (this will automatically include any table, input, col, bool, and limit values so that you don't need to write out code for each one) set/modify the page (pg) element, then use http_build_query() to build the query string. this will take care of urlencodeing the values.<<

One day, I am likely to pester you about urldecode().

CONCLUSION

You know, few nights ago, I came to the conclusion that, having these $vars was a big mistake:

$input_1

$col_1

$col_2

And replacing them with just:

$input

That will have the tbl, cols, AND/OR, etc. (the whole keyword search). No need to have separate $vars where more than one one for each col and more than one for each BOOL. I should fix all this on my TEMPLATE 1, 2,3 and the LATEST (short pagination).

Here, is the thread for the LATEST PAGINATION TEMPLATE (Template 4) or SHORT TEMPLATE. I tried shortening and reusing lines as much as possible.

Remember, I worked on it last night before reading your fix advices here and so do not get upset where that thread is not heeding your advice giving here. Ok ?

Thank you Mac Guyver!

It's nearly sunrise here, I was supposed to fix all my 4 PAGINATION TEMPLATES based on your fix advices here but it took me about 4hrs replying to your reply. Nearly 4:30am here. So, I should work on them the upcoming night. In the meanwhile, do write the example codes I asked for here so that I can add your fixes to my 4 TEMPLATES. And do not worry. I am not the guy just copies codes. I test them, memorise them, then create derivations out of them for further experimenting purposes. I need to start learning the fixes from somewhere. From some example codes. You are my first point of that.

Take care and good night, or should I say good morning!

Edited May 1, 2023 by TheStudent2023

ignace · May 2, 2023

Hi,

It won't take you 6 years to understand OOP. OOP is about ownership and responsibility. Procedural is about functionality.

From a procedural point of view a user is an array with certain fields like id, username, password, ... Any function can freely use, modify, or even remove these fields as they see fit.

Nobody owns the data, and so anything can do to it what it wants.

In OOP you assign a class with the ownership and responsibility of this data:

class User {
  private ?int $id = null;
  private string $username;
  private string $password;
}

In the above example the User class owns the data, and doesn't allow anyone to touch it. Not very useful.

class User {
  private ?int $id = null;
  private string $username;
  private string $password;

  public function changeUsername(string $username) {
    assertLength($username, 8);
    assertNotProfane($username);
    assertNotSameAsFirstOrLastName($username);
    assertNotSameAsEmail($username);
    $this->username = $username;
  }
}

Now the User class allows changing the username, if ALL requirements are met. Otherwise it will throw an Exception.

Exceptions are a big part of OOP, it ensures your code follows a controlled path, like an assembly line, any bad items are cast to a different line.

The advantage of encapsulating data is that there is only one place that controls access to the data, and not hundreds of places.

So that if your requirements change, you can make the change in one place and not spend weeks tracking down all uses.

I am not saying you should switch to OOP, just that it won't take you 6 years as the concept is quite simple.

ignace · May 2, 2023

As for your question on how the pros do it, this is how procedural programmers structure their project:

load any required files
get the inputs and validate them and take appropriate action if they are invalid
do something with the input
report back to the user

A popular pattern found online is Controller-Model-View. Where your controller is your php file (index.php, search.php, register.php), and the view is your HTML. Your model are the necessary functions you execute to do the thing.

Take for example a search:

Your controller:

// search.php
// 
// Some description
// 
// Arguments:
//   q: the search query 
//   page: the page number, defaults to 1
// Supports:
//   GET, HEAD
// Usage:
//   search.php?q=some+query&page=1

require __DIR__ . '/bootstrap.php';
require APP_PROJECT_DIR . '/lib/search_functions.php';
require APP_PROJECT_DIR . '/lib/page_functions.php';

$term = get_search_term();

if (empty($term)) {
  redirect('/to/the/search/form');
}

// more validation here

$page = get_page();

$search_results = do_page_search($term, $page, 30 /* results per page */);
// $search_results contains:
// [items]
// [total_items]
// [total_pages]
// [items_per_page]
// [current_page]

// everything is rendered in a view
require APP_PROJECT_DIR . '/views/search_results.php';

Then the result is passed off to the view:

<?php
// views/search_results.php
// 
// Description
//
 
require APP_PROJECT_DIR . '/views/header.php';
?>
<div class="search-results">
  <?php foreach ($search_results as $search_result): ?>
    ...
  <?php endforeach ?>
</div>
<div class="pagination">
  <?php
  // uses total_items, total_pages, current_page, items_per_page because these are abstract names and can be reused
  require APP_PROJECT_DIR . '/views/pagination.php';
  ?>
</div>
<?php require APP_PROJECT_DIR . '/views/footer.php'; ?>

TheStudent2023 · May 2, 2023

4 hours ago, ignace said:
Hi,

It won't take you 6 years to understand OOP. OOP is about ownership and responsibility. Procedural is about functionality.

From a procedural point of view a user is an array with certain fields like id, username, password, ... Any function can freely use, modify, or even remove these fields as they see fit.

Nobody owns the data, and so anything can do to it what it wants.

In OOP you assign a class with the ownership and responsibility of this data:
class User {
  private ?int $id = null;
  private string $username;
  private string $password;
}
In the above example the User class owns the data, and doesn't allow anyone to touch it. Not very useful.
class User {
  private ?int $id = null;
  private string $username;
  private string $password;

  public function changeUsername(string $username) {
    assertLength($username, 8);
    assertNotProfane($username);
    assertNotSameAsFirstOrLastName($username);
    assertNotSameAsEmail($username);
    $this->username = $username;
  }
}
Now the User class allows changing the username, if ALL requirements are met. Otherwise it will throw an Exception.

Exceptions are a big part of OOP, it ensures your code follows a controlled path, like an assembly line, any bad items are cast to a different line.

The advantage of encapsulating data is that there is only one place that controls access to the data, and not hundreds of places.

So that if your requirements change, you can make the change in one place and not spend weeks tracking down all uses.

I am not saying you should switch to OOP, just that it won't take you 6 years as the concept is quite simple.

From the looks of this example, it seems you added characteristics to a variable and then added restrictions or standard to be met if the variable name needs to be changed. So, what has objects got to do with any of this ?

And characteristics is called "class" ?

TheStudent2023 · May 2, 2023

4 hours ago, ignace said:

As for your question, this is how procedural programmers structure their project:

// search.php
// 
// Some description
// 
// Arguments:
//   q: the search query 
//   page: the page number, defaults to 1
// Supports:
//   GET, HEAD
// Usage:
//   search.php?q=some+query&page=1

require APP_PROJECT_DIR . '/lib/search_functions.php';
require APP_PROJECT_DIR . '/lib/page_functions.php';

$term = get_search_term();

if (empty($term)) {
  redirect('/to/the/search/form');
}

$page = get_page();

$search_results = do_page_search($term, $page, 30 /* results per page */);
// $search_results contains:
// [items]
// [total_items]
// [total_pages]
// [items_per_page]
// [current_page]

// everything is rendered in a view
require APP_PROJECT_DIR . '/views/search_results.php';

Then the result is passed off to the view:

<?php
// views/search_results.php
// 
// Description
//
 
require APP_PROJECT_DIR . '/views/header.php';
?>
<div class="search-results">
  <?php foreach ($search_results as $search_result): ?>
    ...
  <?php endforeach ?>
</div>
<div class="pagination">
  <?php
  // uses total_items, total_pages, current_page, items_per_page because these are abstract names and can be reused
  require APP_PROJECT_DIR . '/views/pagination.php';
  ?>
</div>
<?php require APP_PROJECT_DIR . '/views/footer.php'; ?>

Sorry. Did not really understand the structure or the code that much. This is adv stuff. Right ? Not intermediate, let alone beginner level. Yes ?

ignace · May 2, 2023

3 minutes ago, TheStudent2023 said:

Sorry. Did not really understand the structure or the code that much. This is adv stuff. Right ? Not intermediate, let alone beginner level. Yes ?

Actually this is beginner level. You start with a directory structure something like:

/functions
/html
index.php
search.php
register.php
bootstrap.php

Where index.php, search.php, register.php are the files users will visit like http://website.com/search.php and http://website.com/register.php

Inside search.php and register.php the code will look something like:

// defines constants like DB_USER, DB_PASS, DB_HOST, .. so you only need to change this in one place
require __DIR__ . '/bootstrap.php'; 

// load functions you need
require APP_PROJECT_DIR . '/functions/page_functions.php';
require APP_PROJECT_DIR . '/functions/search_functions.php';

// do the work

// load the HTML
require APP_PROJECT_DIR . '/html/search_view.php';

Using this way of working you avoid having a big wall of text and give each file their own responsibility.

TheStudent2023 · May 2, 2023

@mac_gyver

Sorry, I got your username wrong when tagging you in my reply above.

Edited May 2, 2023 by TheStudent2023

ignace · May 3, 2023

Also don't do this:

SELECT id,domain,word,phrase from $tbl WHERE $col_1 = ?

Because now it's easy for a hacker to do something like:

?col_1=1;DELETE FROM users WHERE id&input_1=1

Instead do something like:

/**
 * @param array{col_1: string} $fields
 * @param string $operator Possible values AND, OR
 * @param int $limit Possible value between 0 and 100, if invalid defaults to 100
 * @param int $offset 
 *
 * @return array{items: array, total_items: int}
 */
function page_search(array $fields, string $operator, int $limit, int $offset = 0): array {
  $where = $values = [];
  $operator = in_array($operator, ['OR', 'AND']) ? $operator : 'AND';
  $limit = 0 < $limit && $limit < 100 ? $limit : 100;
  $offset = 0 <= $offset ? $offset : 0;

  foreach ($fields as $field => $term) {
    switch ($field) {
      case 'col_1':
        $where[] = 'col_1 = ?';
        $values[] = $term;
        break;
      // other fields you want to allow to search on
  }

  $result = [
    'items' => [],
    'total_items' => 0,
  ];

  if ([] === $where) {
    return $result;
  }
    
  $result['items'] = db_fetch_all('SELECT * FROM some_table WHERE ' . implode($operator, $where) . " LIMIT $offset, $limit", $values);
    
  if (count($result['items'])) {
    // only execute a count() query if we have a result
    $result['total_items'] = db_fetch_column('SELECT count(*) FROM some_table WHERE ' . implode($operator, $where), $values);
  }

  return $result;
}

By dividing your program into little pieces you reduce the cognitive load necessary to work on pieces of your application or to find and fix bugs.

The same goes for the database. I see you use mysqli_connect in the script with the values hardcoded. Instead you should create a file that holds your configuration values:

// bootstrap.php
define('DB_HOST', 'localhost');
define('DB_USER', 'the_username');
define('DB_PASS', 'the_password');
define('DB_NAME', 'the_database');
define('DB_PORT', 3306);
define('DB_CHARSET', 'utf8mb4');

define('EMERGENCY_EMAIL', 'some@email.com'); // in case of fatal errors

// other configuration values

Then in your database functions file:

function db_connect() {
  static $connection;

  if (null === $connection) {
    $connection = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME, DB_PORT);
    if ($connection) {
      mysqli_set_charset($connection, DB_CHARSET);
    }
  }

  return $connection;
}

All of this creates building blocks you can use to build upon further. The next step would be to create a db_fetch_all function that uses db_connect to get the active DB connection etc...

TheStudent2023 · May 3, 2023

15 hours ago, ignace said:

Also don't do this:

SELECT id,domain,word,phrase from $tbl WHERE $col_1 = ?

Because now it's easy for a hacker to do something like:

?col_1=1;DELETE FROM users WHERE id&input_1=1

Instead do something like:

/**
 * @param array{col_1: string} $fields
 * @param string $operator Possible values AND, OR
 * @param int $limit Possible value between 0 and 100, if invalid defaults to 100
 * @param int $offset 
 *
 * @return array{items: array, total_items: int}
 */
function page_search(array $fields, string $operator, int $limit, int $offset = 0): array {
  $where = $values = [];
  $operator = in_array($operator, ['OR', 'AND']) ? $operator : 'AND';
  $limit = 0 < $limit && $limit < 100 ? $limit : 100;
  $offset = 0 <= $offset ? $offset : 0;

  foreach ($fields as $field => $term) {
    switch ($field) {
      case 'col_1':
        $where[] = 'col_1 = ?';
        $values[] = $term;
        break;
      // other fields you want to allow to search on
  }

  $result = [
    'items' => [],
    'total_items' => 0,
  ];

  if ([] === $where) {
    return $result;
  }
    
  $result['items'] = db_fetch_all('SELECT * FROM some_table WHERE ' . implode($operator, $where) . " LIMIT $offset, $limit", $values);
    
  if (count($result['items'])) {
    // only execute a count() query if we have a result
    $result['total_items'] = db_fetch_column('SELECT count(*) FROM some_table WHERE ' . implode($operator, $where), $values);
  }

  return $result;
}

By dividing your program into little pieces you reduce the cognitive load necessary to work on pieces of your application or to find and fix bugs.

The same goes for the database. I see you use mysqli_connect in the script with the values hardcoded. Instead you should create a file that holds your configuration values:

// bootstrap.php
define('DB_HOST', 'localhost');
define('DB_USER', 'the_username');
define('DB_PASS', 'the_password');
define('DB_NAME', 'the_database');
define('DB_PORT', 3306);
define('DB_CHARSET', 'utf8mb4');

define('EMERGENCY_EMAIL', 'some@email.com'); // in case of fatal errors

// other configuration values

Then in your database functions file:

function db_connect() {
  static $connection;

  if (null === $connection) {
    $connection = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME, DB_PORT);
    if ($connection) {
      mysqli_set_charset($connection, DB_CHARSET);
    }
  }

  return $connection;
}

All of this creates building blocks you can use to build upon further. The next step would be to create a db_fetch_all function that uses db_connect to get the active DB connection etc...

@ignace

Thank you very much! I am sure yourhard work code will become handy to me once I get a little bit more adv in php. Right now, I did not understand most of it. I won't bug you now to explain each of your code lines as it's gonna be sun rise here soon and I got sleep in my eyes.

>>Also don't do this:

SELECT id,domain,word,phrase from $tbl WHERE $col_1 = ?

Because now it's easy for a hacker to do something like:

?col_1=1;DELETE FROM users WHERE id&input_1=1 <<

I got one question now though, I thought with prepared statements there is no way a hacker can inject anything in the SQL. So, how can he do it here ? Is it because to foil sql injection the SQL has to be hard coded or CONSTANT while mine is not ($tbl) ? How come no one ever told me this ?

How come big dogs aided me cook-up the following code but they never pointed my flaw out ? Look what I cooked-up lately last week:

LATEST UPDATE

	<?php
//TEMPLATE.
	//FULLY WORKING!
//COMPLETE!
	//mysqli_stmt_store_result().
//mysqli_stmt_free_result().
//$rows_count =  mysqli_stmt_num_rows($stmt).
//mysqli_stmt_get_result().
	//http_build_query().
	//Report Error.
ini_set('display_errors',1);
ini_set('display_startup_errors',1);
error_reporting(E_ALL);
	//Valid $_GET Items.
//$tables = array('spidered_web_index','$submitted_web_index','$items_listings','visiting_history','following_history');
$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');
$items_listings = array('id','date_and_time','item','brand','manufacturer','model','heading','year','description','price','country','keyword','keyphrase');
$visiting_history = array('id','date_and_time','searcher','domain','url','title','header','description','country','meta_keyword','meta_description','keyword','keyphrase');
$following_history = array('id','date_and_time','searcher','follower','domain','url','title','header','description','country','meta_keyword','meta_description','keyword','keyphrase');
	$tables = ['spidered_web_index'=>
                        ['id'=>'ID',
                        'date_and_time'=>'Date & Time',
                        'domain'=>'Domain',
                        'domain_point'=>'Domain Point',
                        'url'=>'Url',
                        'url'=>'Url Point',
                        'title'=>'Title',
                        'title'=>'Title Point',
                        'heading_1'=>'Heading 1',
                        'heading_1_point'=>'Heading 1 Point',
                        'heading_2'=>'Heading 2',
                        'heading_2_point'=>'Heading 2 Point',
                        'heading_3'=>'Heading 3',
                        'heading_3_point'=>'Heading 3 Point',
                        'heading_4'=>'Heading 4',
                        'heading_4_point'=>'Heading 4 Point',
                        'heading_5'=>'Heading 5',
                        'heading_5_point'=>'Heading 5 Point',
                        'heading_6'=>'Heading 6',
                        'heading_6_point'=>'Heading 6 Point',
                        'keyword_superscript'=>'Keyword Superscript',
                        'keyword_superscript'=>'Keyword superscript',
                        'keyword_strong'=>'Keyword Strong',
                        'keyword_strong'=>'Keyword Strong',
                        'keyword_emphasised'=>'Keyword Emphasised',
                        'keyword_emphasised'=>'Keyword Emphasised',
                        'keyword_bold'=>'Keyword Bold',
                        'keyword_bold'=>'Keyword Bold',
                        'keyword_italic'=>'Keyword Italic',
                        'keyword_italic'=>'Keyword Italic',
                        'keyword_marked'=>'Keyword Marked',
                        'keyword_marked'=>'Keyword Marked',
                        'keyword_inserted'=>'Keyword Inserted',
                        'keyword_inserted'=>'Keyword Inserted',
                        'keyword_deleted'=>'Keyword Deleted',
                        'keyword_deleted'=>'Keyword Deleted',
                        'keyword_small'=>'Keyword Small',
                        'keyword_small'=>'Keyword Small',
                        'keyword_subscript'=>'Keyword Subscript',
                        'keyword_subscript'=>'Keyword Subscript'
                        ],
        'submitted_web_index'=>
                        ['id'=>'ID',
                        'date_and_time'=>'Date & Time',
                        'domain'=>'Domain',
                        'domain_point'=>'Domain Point',
                        'url'=>'Url',
                        'url'=>'Url Point',
                        'title'=>'Title',
                        'title'=>'Title Point',
                        'heading_1'=>'Heading 1',
                        'heading_1_point'=>'Heading 1 Point',
                        'heading_2'=>'Heading 2',
                        'heading_2_point'=>'Heading 2 Point',
                        'heading_3'=>'Heading 3',
                        'heading_3_point'=>'Heading 3 Point',
                        'heading_4'=>'Heading 4',
                        'heading_4_point'=>'Heading 4 Point',
                        'heading_5'=>'Heading 5',
                        'heading_5_point'=>'Heading 5 Point',
                        'heading_6'=>'Heading 6',
                        'heading_6_point'=>'Heading 6 Point',
                        'keyword_superscript'=>'Keyword Superscript',
                        'keyword_superscript'=>'Keyword superscript',
                        'keyword_strong'=>'Keyword Strong',
                        'keyword_strong'=>'Keyword Strong',
                        'keyword_emphasised'=>'Keyword Emphasised',
                        'keyword_emphasised'=>'Keyword Emphasised',
                        'keyword_bold'=>'Keyword Bold',
                        'keyword_bold'=>'Keyword Bold',
                        'keyword_italic'=>'Keyword Italic',
                        'keyword_italic'=>'Keyword Italic',
                        'keyword_marked'=>'Keyword Marked',
                        'keyword_marked'=>'Keyword Marked',
                        'keyword_inserted'=>'Keyword Inserted',
                        'keyword_inserted'=>'Keyword Inserted',
                        'keyword_deleted'=>'Keyword Deleted',
                        'keyword_deleted'=>'Keyword Deleted',
                        'keyword_small'=>'Keyword Small',
                        'keyword_small'=>'Keyword Small',
                        'keyword_subscript'=>'Keyword Subscript',
                        'keyword_subscript'=>'Keyword Subscript'
                        ]
                        ,
        'links_crawls_drummin' =>
                            ['id' => "ID"
                            ,'date_and_time' => "Date and Time"
                            ,'domain' => "Domain"
                            ,'domain_point' => "Domain Point"
                            ,'url' => "URL"
                            ,'url_point' => "Url Point"
                            ,'title' => "Title"
                            ,'title_point' => "Title Point"
                            ,'header' => "Header"
                            ,'header_point' => "Header Point"
                            ,'kw_1' => "Keyword 1"
                            ,'kw_1_point' => "Keyword 1 Point"
                            ,'kw_2' => "Keyword 2"
                            ,'kw_2_point' => "Keyword 2 Point"
                            ,'kw_3' => "Keyword 3"
                            ,'kw_3_point' => "Keyword 3 Point"
                            ,'kw_4' => "Keyword 4"
                            ,'kw_4_point' => "Keyword 4 Point"
                            ,'description' => "Description"    
                            ,'description_point' => "Description Point"
                            ],
            'links_submits_drummin' =>
                            ['id' => "ID"
                            ,'date_and_time' => "Date and Time"
                            ,'domain' => "Domain"
                            ,'domain_point' => "Domain Point"
                            ,'url' => "URL"
                            ,'url_point' => "Url Point"
                            ,'title' => "Title"
                            ,'title_point' => "Title Point"
                            ,'header' => "Header"
                            ,'header_point' => "Header Point"
                            ,'kw_1' => "Keyword 1"
                            ,'kw_1_point' => "Keyword 1 Point"
                            ,'kw_2' => "Keyword 2"
                            ,'kw_2_point' => "Keyword 2 Point"
                            ,'kw_3' => "Keyword 3"
                            ,'kw_3_point' => "Keyword 3 Point"
                            ,'kw_4' => "Keyword 4"
                            ,'kw_4_point' => "Keyword 4 Point"
                            ,'description' => "Description"    
                            ,'description_point' => "Description Point"
                            ],
        ];
	
//Extract $_GETs.
$table = !EMPTY($_POST['tbl'])?$_POST['tbl']:(!EMPTY($_GET['tbl'])?$_GET['tbl']:'spidered_web_index');
$column = !EMPTY($_POST['col'])?$_POST['col']:(!EMPTY($_GET['col'])?$_GET['col']:'id');
$limit = !EMPTY($_POST['lmt'])?$_POST['lmt']:(!EMPTY($_GET['lmt'])?$_GET['lmt']:1);
$match = !EMPTY($_POST['mtch'])?$_POST['mtch']:(!EMPTY($_GET['mtch'])?$_GET['mtch']:'fuzzy');
$search = !EMPTY($_POST['srch'])?$_POST['srch']:(!EMPTY($_GET['srch'])?$_GET['srch']:'mobile');
	$page = !EMPTY($_GET['pg'])?intval($_GET['pg']):1;
$offset = ($page*$limit)-$limit;
	$headings = array_values($tables[$table]);
$columns = array_keys($tables[$table]);
echo $columns_count = count($columns);
	if(!in_array($column,$columns))
{
    die('Invalid Column!');
}
	if(!in_array($tables[$table],$tables))
{
    die('Invalid Table!');
}
	$search = (!EMPTY($search) && $match==='fuzzy')?str_replace('*','%',$search):$search;
	$char_types = str_repeat('s',$columns_count);
$search_values = array_fill(0,$columns_count,$search);
$comparator = ($match==='exact')?'=':'LIKE';
$sql = "SELECT * FROM $table WHERE " .implode(" $comparator ? OR ",$columns) ." $comparator ? ";
$sql .= "ORDER BY id DESC LIMIT $limit OFFSET $offset";
	//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(!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
{
    mysqli_stmt_bind_param($stmt,$char_types,...$search_values);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_store_result($stmt); //Necessary to use with mysqli_stmt_num_rows() when SQL query is SELECT.
    echo '<br><br>';echo '<br><br>';echo '<br><br>';echo '<br><br>';
    
    //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
    {
        mysqli_stmt_bind_param($stmt,$char_types,...$search_values);
        mysqli_stmt_execute($stmt);
        $result = mysqli_stmt_get_result($stmt);
        mysqli_stmt_free_result($stmt); //Is this really necessary ?
        
        while($row = mysqli_fetch_array($result,MYSQLI_ASSOC))
        {
            for($i=0;$i<$columns_count;$i++)
            {
                echo '<b>'.$headings[$i].': </b>'.$row[$columns[$i]]; 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++;
    $pagination_section_array = array("tbl"=>"$table","mtch"=>"$match","lmt"=>"$limit","srch"=>"$search","pg"=>intval($i));
    $serps_url = $_SERVER['PHP_SELF'].'?'.http_build_query($pagination_section_array,'','&amp;'); //Did convert '&' to '&amp' and so NO need to add htmlspecialchars() when echoing link.
    
    if($i==$page)
    {
        echo '<a href="' .$serps_url .'">' ."<b>$i</b>" .'</a>'; //No need to add htmlspecialchars(), to convert '&' to '&amp;', when echoing link here.
    }
    else
    {
        echo '<a href="' .$serps_url .'">' ."$i" .'</a>'; //No need to add htmlspecialchars(), to convert '&' to '&amp;', when echoing link here.
    }
}
	echo '<br>';
	?>

The above is a dynamic pagination script. Unorthodox Pagination script. Flexible Coding Pagination script.

On it, can you guess what I am trying to achieve there since I did not write the prepared statements the orthodox way such as the following ? The bold text are the hints:

	$stmt = mysqli_stmt_init($conn);
	mysqli_stmt_prepare($stmt,$sql_count);
	mysqli_stmt_bind_param($stmt,"s",$input);
	mysqli_stmt_execute($stmt);

That LATEST UPDATE code you see above. I wrote it less than a week ago. It was derived from the SIMPLE pagination script you see in my original post you see in this thread.

Anyway, I am starting to like you. You are getting in my good books. You are starting to engage in my threads. Good man! I think if I hang onto you just a little longer without frustrating you with tonnes of basic questions and you bear patiently my unorthodox childish programming questions, then I might just learn a few wisdom (gold nuggets) from you! Your latest code has got me interested. Grabbed my attention. Great piece of work! You know what you talking about!

After addressing this particular post (reply to you), do you mind checking up above what I asked Mac_gyver and answer on his behalf as he is not responding for a few days now. I need answers to the questions I asked him as a reply to his reply to my op ?

Finally, do checkout all my other threads. Trying to build a searchengine. That is the project.

Do not mind me tagging you now and then. Respond when you are free. Not gonna expect you to respond immediately.

Thanks!

ignace · May 5, 2023

You avoid SQL injection when you use prepared statements assuming you write the full SQL and don't inject foreign SQL using variables.

So while this is safe:

mysqli_stmt_prepare($stmt, 'SELECT * FROM users WHERE username = ?');

This is not:

mysqli_stmt_prepare($stmt, "SELECT * FROM $tbl WHERE $col_1 = ?");

Because now a hacker can control what the prepared statement is. So this could end up being:

mysqli_stmt_prepare($stmt, "SELECT * FROM users; DELETE FROM users WHERE 1 = ?");

Which is not what you intended.

--

I assume nobody told you, because I don't think anyone read over your code like I did. Walls of text do that to people. Which is why, as a programmer, we divide the program into little pieces like lego blocks. Building or re-using blocks to build a car or a house.

We even have "rules" defining how small things should be: https://refactoring.guru/refactoring/smells/bloaters

--

I assumed it was either a search engine or an SEO kinda tool. You might want to read up on cursor based pagination: https://medium.com/swlh/how-to-implement-cursor-pagination-like-a-pro-513140b65f32 as LIMIT OFFSET is not ideal for pagination (performance wise).

ignace · May 5, 2023

Quote

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) ?

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.

Q1. yes, it needs a stored result to count the rows.

Q2. yes, it clears the stored result from the internal buffer freeing memory. It's good practice to always clean up after yourself.

Q3. either use mysqli_* or PDO, most people use PDO for its simple API. PDO has no pdo_* functions, only it's Object Oriented interface.

Q4. bad, but not in the way of ugly or "newbie" but because

it can be exploited by hackers;
it will be hard to maintain (lots of duplicate code, weird indents, no separation between HTML and PHP, ..);
using die() in a script is not good UX, the user can't get back to the homepage or try another search;

ignace · May 5, 2023

 <?php
 //TEMPLATE.
 //FULLY WORKING!
 //COMPLETE!
 //mysqli_stmt_store_result().
 //mysqli_stmt_free_result().
 //$rows_count =  mysqli_stmt_num_rows($stmt).
 //mysqli_stmt_get_result().
 //http_build_query().
 //Report Error.
 ini_set('display_errors', 1);
 ini_set('display_startup_errors', 1);
 error_reporting(E_ALL);
 //Valid $_GET Items.
 //$tables = array('spidered_web_index','$submitted_web_index','$items_listings','visiting_history','following_history');
 $spidered_web_index = ['id', 'date_and_time', 'domain', 'url', 'title', 'header', 'meta_keyword', 'meta_description', 'keyword', 'keyphrase'];
 $submitted_web_index = ['id', 'date_and_time', 'domain', 'url', 'title', 'description', 'country', 'keyword', 'keyphrase'];
 $items_listings = ['id', 'date_and_time', 'item', 'brand', 'manufacturer', 'model', 'heading', 'year', 'description', 'price', 'country', 'keyword', 'keyphrase'];
 $visiting_history = ['id', 'date_and_time', 'searcher', 'domain', 'url', 'title', 'header', 'description', 'country', 'meta_keyword', 'meta_description', 'keyword', 'keyphrase'];
 $following_history = ['id', 'date_and_time', 'searcher', 'follower', 'domain', 'url', 'title', 'header', 'description', 'country', 'meta_keyword', 'meta_description', 'keyword', 'keyphrase'];
 $tables = [
     'spidered_web_index' => [
         'id' => 'ID',
         'date_and_time' => 'Date & Time',
         'domain' => 'Domain',
         'domain_point' => 'Domain Point',
         'url' => 'Url',
         'url' => 'Url Point',
         'title' => 'Title',
         'title' => 'Title Point',
         'heading_1' => 'Heading 1',
         'heading_1_point' => 'Heading 1 Point',
         'heading_2' => 'Heading 2',
         'heading_2_point' => 'Heading 2 Point',
         'heading_3' => 'Heading 3',
         'heading_3_point' => 'Heading 3 Point',
         'heading_4' => 'Heading 4',
         'heading_4_point' => 'Heading 4 Point',
         'heading_5' => 'Heading 5',
         'heading_5_point' => 'Heading 5 Point',
         'heading_6' => 'Heading 6',
         'heading_6_point' => 'Heading 6 Point',
         'keyword_superscript' => 'Keyword Superscript',
         'keyword_superscript' => 'Keyword superscript',
         'keyword_strong' => 'Keyword Strong',
         'keyword_strong' => 'Keyword Strong',
         'keyword_emphasised' => 'Keyword Emphasised',
         'keyword_emphasised' => 'Keyword Emphasised',
         'keyword_bold' => 'Keyword Bold',
         'keyword_bold' => 'Keyword Bold',
         'keyword_italic' => 'Keyword Italic',
         'keyword_italic' => 'Keyword Italic',
         'keyword_marked' => 'Keyword Marked',
         'keyword_marked' => 'Keyword Marked',
         'keyword_inserted' => 'Keyword Inserted',
         'keyword_inserted' => 'Keyword Inserted',
         'keyword_deleted' => 'Keyword Deleted',
         'keyword_deleted' => 'Keyword Deleted',
         'keyword_small' => 'Keyword Small',
         'keyword_small' => 'Keyword Small',
         'keyword_subscript' => 'Keyword Subscript',
         'keyword_subscript' => 'Keyword Subscript',
     ],
     'submitted_web_index' => [
         'id' => 'ID',
         'date_and_time' => 'Date & Time',
         'domain' => 'Domain',
         'domain_point' => 'Domain Point',
         'url' => 'Url',
         'url' => 'Url Point',
         'title' => 'Title',
         'title' => 'Title Point',
         'heading_1' => 'Heading 1',
         'heading_1_point' => 'Heading 1 Point',
         'heading_2' => 'Heading 2',
         'heading_2_point' => 'Heading 2 Point',
         'heading_3' => 'Heading 3',
         'heading_3_point' => 'Heading 3 Point',
         'heading_4' => 'Heading 4',
         'heading_4_point' => 'Heading 4 Point',
         'heading_5' => 'Heading 5',
         'heading_5_point' => 'Heading 5 Point',
         'heading_6' => 'Heading 6',
         'heading_6_point' => 'Heading 6 Point',
         'keyword_superscript' => 'Keyword Superscript',
         'keyword_superscript' => 'Keyword superscript',
         'keyword_strong' => 'Keyword Strong',
         'keyword_strong' => 'Keyword Strong',
         'keyword_emphasised' => 'Keyword Emphasised',
         'keyword_emphasised' => 'Keyword Emphasised',
         'keyword_bold' => 'Keyword Bold',
         'keyword_bold' => 'Keyword Bold',
         'keyword_italic' => 'Keyword Italic',
         'keyword_italic' => 'Keyword Italic',
         'keyword_marked' => 'Keyword Marked',
         'keyword_marked' => 'Keyword Marked',
         'keyword_inserted' => 'Keyword Inserted',
         'keyword_inserted' => 'Keyword Inserted',
         'keyword_deleted' => 'Keyword Deleted',
         'keyword_deleted' => 'Keyword Deleted',
         'keyword_small' => 'Keyword Small',
         'keyword_small' => 'Keyword Small',
         'keyword_subscript' => 'Keyword Subscript',
         'keyword_subscript' => 'Keyword Subscript',
     ],
     'links_crawls_drummin' => [
         'id' => "ID",
         'date_and_time' => "Date and Time",
         'domain' => "Domain",
         'domain_point' => "Domain Point",
         'url' => "URL",
         'url_point' => "Url Point",
         'title' => "Title",
         'title_point' => "Title Point",
         'header' => "Header",
         'header_point' => "Header Point",
         'kw_1' => "Keyword 1",
         'kw_1_point' => "Keyword 1 Point",
         'kw_2' => "Keyword 2",
         'kw_2_point' => "Keyword 2 Point",
         'kw_3' => "Keyword 3",
         'kw_3_point' => "Keyword 3 Point",
         'kw_4' => "Keyword 4",
         'kw_4_point' => "Keyword 4 Point",
         'description' => "Description",
         'description_point' => "Description Point",
     ],
     'links_submits_drummin' => [
         'id' => "ID",
         'date_and_time' => "Date and Time",
         'domain' => "Domain",
         'domain_point' => "Domain Point",
         'url' => "URL",
         'url_point' => "Url Point",
         'title' => "Title",
         'title_point' => "Title Point",
         'header' => "Header",
         'header_point' => "Header Point",
         'kw_1' => "Keyword 1",
         'kw_1_point' => "Keyword 1 Point",
         'kw_2' => "Keyword 2",
         'kw_2_point' => "Keyword 2 Point",
         'kw_3' => "Keyword 3",
         'kw_3_point' => "Keyword 3 Point",
         'kw_4' => "Keyword 4",
         'kw_4_point' => "Keyword 4 Point",
         'description' => "Description",
         'description_point' => "Description Point",
     ],
 ];

 //Extract $_GETs.
 $table = !empty($_POST['tbl']) ? $_POST['tbl'] : (!empty($_GET['tbl']) ? $_GET['tbl'] : 'spidered_web_index');
 $column = !empty($_POST['col']) ? $_POST['col'] : (!empty($_GET['col']) ? $_GET['col'] : 'id');
 $limit = !empty($_POST['lmt']) ? $_POST['lmt'] : (!empty($_GET['lmt']) ? $_GET['lmt'] : 1);
 $match = !empty($_POST['mtch']) ? $_POST['mtch'] : (!empty($_GET['mtch']) ? $_GET['mtch'] : 'fuzzy');
 $search = !empty($_POST['srch']) ? $_POST['srch'] : (!empty($_GET['srch']) ? $_GET['srch'] : 'mobile');
 $page = !empty($_GET['pg']) ? intval($_GET['pg']) : 1;
 $offset = $page * $limit - $limit;
 $headings = array_values($tables[$table]);
 $columns = array_keys($tables[$table]);
 echo $columns_count = count($columns);
 if (!in_array($column, $columns)) {
     die('Invalid Column!');
 }
 if (!in_array($tables[$table], $tables)) {
     die('Invalid Table!');
 }
 $search = !empty($search) && $match === 'fuzzy' ? str_replace('*', '%', $search) : $search;
 $char_types = str_repeat('s', $columns_count);
 $search_values = array_fill(0, $columns_count, $search);
 $comparator = $match === 'exact' ? '=' : 'LIKE';
 $sql = "SELECT * FROM $table WHERE " . implode(" $comparator ? OR ", $columns) . " $comparator ? ";
 $sql .= "ORDER BY id DESC LIMIT $limit OFFSET $offset";
 //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 (!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 {
     mysqli_stmt_bind_param($stmt, $char_types, ...$search_values);
     mysqli_stmt_execute($stmt);
     mysqli_stmt_store_result($stmt); //Necessary to use with mysqli_stmt_num_rows() when SQL query is SELECT.
     echo '<br><br>';
     echo '<br><br>';
     echo '<br><br>';
     echo '<br><br>';

     //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 {
         mysqli_stmt_bind_param($stmt, $char_types, ...$search_values);
         mysqli_stmt_execute($stmt);
         $result = mysqli_stmt_get_result($stmt);
         mysqli_stmt_free_result($stmt); //Is this really necessary ?

         while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
             for ($i = 0; $i < $columns_count; $i++) {
                 echo '<b>' . $headings[$i] . ': </b>' . $row[$columns[$i]];
                 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++;
     $pagination_section_array = ["tbl" => "$table", "mtch" => "$match", "lmt" => "$limit", "srch" => "$search", "pg" => intval($i)];
     $serps_url = $_SERVER['PHP_SELF'] . '?' . http_build_query($pagination_section_array, '', '&amp;'); //Did convert '&' to '&amp' and so NO need to add htmlspecialchars() when echoing link.

     if ($i == $page) {
         echo '<a href="' . $serps_url . '">' . "<b>$i</b>" . '</a>'; //No need to add htmlspecialchars(), to convert '&' to '&amp;', when echoing link here.
     } else {
         echo '<a href="' . $serps_url . '">' . "$i" . '</a>'; //No need to add htmlspecialchars(), to convert '&' to '&amp;', when echoing link here.
     }
 }
 echo '<br>';

?>

To improve this script, the first thing I would do is cut out the large arrays. So I would create a new directory called config and create a new file tables.php to hold the table configuration, like this:

search-engine-project/
├─ config/
│  ├─ tables.php

Then in tables.php I would paste the large array:

<?php

return [
     'spidered_web_index' => [
         'id' => 'ID',
         'date_and_time' => 'Date & Time',
         'domain' => 'Domain',
         'domain_point' => 'Domain Point',
         'url' => 'Url',
         'url' => 'Url Point',
         'title' => 'Title',
         'title' => 'Title Point',
         'heading_1' => 'Heading 1',
         'heading_1_point' => 'Heading 1 Point',
         'heading_2' => 'Heading 2',
         'heading_2_point' => 'Heading 2 Point',
         'heading_3' => 'Heading 3',
         'heading_3_point' => 'Heading 3 Point',
         'heading_4' => 'Heading 4',
         'heading_4_point' => 'Heading 4 Point',
         'heading_5' => 'Heading 5',
         'heading_5_point' => 'Heading 5 Point',
         'heading_6' => 'Heading 6',
         'heading_6_point' => 'Heading 6 Point',
         'keyword_superscript' => 'Keyword Superscript',
         'keyword_superscript' => 'Keyword superscript',
         'keyword_strong' => 'Keyword Strong',
         'keyword_strong' => 'Keyword Strong',
         'keyword_emphasised' => 'Keyword Emphasised',
         'keyword_emphasised' => 'Keyword Emphasised',
         'keyword_bold' => 'Keyword Bold',
         'keyword_bold' => 'Keyword Bold',
         'keyword_italic' => 'Keyword Italic',
         'keyword_italic' => 'Keyword Italic',
         'keyword_marked' => 'Keyword Marked',
         'keyword_marked' => 'Keyword Marked',
         'keyword_inserted' => 'Keyword Inserted',
         'keyword_inserted' => 'Keyword Inserted',
         'keyword_deleted' => 'Keyword Deleted',
         'keyword_deleted' => 'Keyword Deleted',
         'keyword_small' => 'Keyword Small',
         'keyword_small' => 'Keyword Small',
         'keyword_subscript' => 'Keyword Subscript',
         'keyword_subscript' => 'Keyword Subscript',
     ],
     'submitted_web_index' => [
         'id' => 'ID',
         'date_and_time' => 'Date & Time',
         'domain' => 'Domain',
         'domain_point' => 'Domain Point',
         'url' => 'Url',
         'url' => 'Url Point',
         'title' => 'Title',
         'title' => 'Title Point',
         'heading_1' => 'Heading 1',
         'heading_1_point' => 'Heading 1 Point',
         'heading_2' => 'Heading 2',
         'heading_2_point' => 'Heading 2 Point',
         'heading_3' => 'Heading 3',
         'heading_3_point' => 'Heading 3 Point',
         'heading_4' => 'Heading 4',
         'heading_4_point' => 'Heading 4 Point',
         'heading_5' => 'Heading 5',
         'heading_5_point' => 'Heading 5 Point',
         'heading_6' => 'Heading 6',
         'heading_6_point' => 'Heading 6 Point',
         'keyword_superscript' => 'Keyword Superscript',
         'keyword_superscript' => 'Keyword superscript',
         'keyword_strong' => 'Keyword Strong',
         'keyword_strong' => 'Keyword Strong',
         'keyword_emphasised' => 'Keyword Emphasised',
         'keyword_emphasised' => 'Keyword Emphasised',
         'keyword_bold' => 'Keyword Bold',
         'keyword_bold' => 'Keyword Bold',
         'keyword_italic' => 'Keyword Italic',
         'keyword_italic' => 'Keyword Italic',
         'keyword_marked' => 'Keyword Marked',
         'keyword_marked' => 'Keyword Marked',
         'keyword_inserted' => 'Keyword Inserted',
         'keyword_inserted' => 'Keyword Inserted',
         'keyword_deleted' => 'Keyword Deleted',
         'keyword_deleted' => 'Keyword Deleted',
         'keyword_small' => 'Keyword Small',
         'keyword_small' => 'Keyword Small',
         'keyword_subscript' => 'Keyword Subscript',
         'keyword_subscript' => 'Keyword Subscript',
     ],
     'links_crawls_drummin' => [
         'id' => "ID",
         'date_and_time' => "Date and Time",
         'domain' => "Domain",
         'domain_point' => "Domain Point",
         'url' => "URL",
         'url_point' => "Url Point",
         'title' => "Title",
         'title_point' => "Title Point",
         'header' => "Header",
         'header_point' => "Header Point",
         'kw_1' => "Keyword 1",
         'kw_1_point' => "Keyword 1 Point",
         'kw_2' => "Keyword 2",
         'kw_2_point' => "Keyword 2 Point",
         'kw_3' => "Keyword 3",
         'kw_3_point' => "Keyword 3 Point",
         'kw_4' => "Keyword 4",
         'kw_4_point' => "Keyword 4 Point",
         'description' => "Description",
         'description_point' => "Description Point",
     ],
     'links_submits_drummin' => [
         'id' => "ID",
         'date_and_time' => "Date and Time",
         'domain' => "Domain",
         'domain_point' => "Domain Point",
         'url' => "URL",
         'url_point' => "Url Point",
         'title' => "Title",
         'title_point' => "Title Point",
         'header' => "Header",
         'header_point' => "Header Point",
         'kw_1' => "Keyword 1",
         'kw_1_point' => "Keyword 1 Point",
         'kw_2' => "Keyword 2",
         'kw_2_point' => "Keyword 2 Point",
         'kw_3' => "Keyword 3",
         'kw_3_point' => "Keyword 3 Point",
         'kw_4' => "Keyword 4",
         'kw_4_point' => "Keyword 4 Point",
         'description' => "Description",
         'description_point' => "Description Point",
     ],
 ];

Then back to our main script I would then include the configuration like this:

 <?php
 ini_set('display_errors', 1);
 ini_set('display_startup_errors', 1);
 error_reporting(E_ALL);
 
 $items_listings = ['id', 'date_and_time', 'item', 'brand', 'manufacturer', 'model', 'heading', 'year', 'description', 'price', 'country', 'keyword', 'keyphrase'];
 $visiting_history = ['id', 'date_and_time', 'searcher', 'domain', 'url', 'title', 'header', 'description', 'country', 'meta_keyword', 'meta_description', 'keyword', 'keyphrase'];
 $following_history = ['id', 'date_and_time', 'searcher', 'follower', 'domain', 'url', 'title', 'header', 'description', 'country', 'meta_keyword', 'meta_description', 'keyword', 'keyphrase'];
 
 // refactored part
 $tables = require 'config/tables.php';
 $spidered_web_index = array_keys($tables['spidered_web_index']);
 $submitted_web_index = array_keys($tables['submitted_web_index']);
 // end refactored part

 //Extract $_GETs.
 $table = !empty($_POST['tbl']) ? $_POST['tbl'] : (!empty($_GET['tbl']) ? $_GET['tbl'] : 'spidered_web_index');
 $column = !empty($_POST['col']) ? $_POST['col'] : (!empty($_GET['col']) ? $_GET['col'] : 'id');
 $limit = !empty($_POST['lmt']) ? $_POST['lmt'] : (!empty($_GET['lmt']) ? $_GET['lmt'] : 1);
 $match = !empty($_POST['mtch']) ? $_POST['mtch'] : (!empty($_GET['mtch']) ? $_GET['mtch'] : 'fuzzy');
 $search = !empty($_POST['srch']) ? $_POST['srch'] : (!empty($_GET['srch']) ? $_GET['srch'] : 'mobile');
 $page = !empty($_GET['pg']) ? intval($_GET['pg']) : 1;
 $offset = $page * $limit - $limit;
 $headings = array_values($tables[$table]);
 $columns = array_keys($tables[$table]);
 echo $columns_count = count($columns);
 if (!in_array($column, $columns)) {
     die('Invalid Column!');
 }
 if (!in_array($tables[$table], $tables)) {
     die('Invalid Table!');
 }
 $search = !empty($search) && $match === 'fuzzy' ? str_replace('*', '%', $search) : $search;
 $char_types = str_repeat('s', $columns_count);
 $search_values = array_fill(0, $columns_count, $search);
 $comparator = $match === 'exact' ? '=' : 'LIKE';
 $sql = "SELECT * FROM $table WHERE " . implode(" $comparator ? OR ", $columns) . " $comparator ? ";
 $sql .= "ORDER BY id DESC LIMIT $limit OFFSET $offset";
 //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 (!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 {
     mysqli_stmt_bind_param($stmt, $char_types, ...$search_values);
     mysqli_stmt_execute($stmt);
     mysqli_stmt_store_result($stmt); //Necessary to use with mysqli_stmt_num_rows() when SQL query is SELECT.
     echo '<br><br>';
     echo '<br><br>';
     echo '<br><br>';
     echo '<br><br>';

     //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 {
         mysqli_stmt_bind_param($stmt, $char_types, ...$search_values);
         mysqli_stmt_execute($stmt);
         $result = mysqli_stmt_get_result($stmt);
         mysqli_stmt_free_result($stmt); //Is this really necessary ?

         while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
             for ($i = 0; $i < $columns_count; $i++) {
                 echo '<b>' . $headings[$i] . ': </b>' . $row[$columns[$i]];
                 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++;
     $pagination_section_array = ["tbl" => "$table", "mtch" => "$match", "lmt" => "$limit", "srch" => "$search", "pg" => intval($i)];
     $serps_url = $_SERVER['PHP_SELF'] . '?' . http_build_query($pagination_section_array, '', '&amp;'); //Did convert '&' to '&amp' and so NO need to add htmlspecialchars() when echoing link.

     if ($i == $page) {
         echo '<a href="' . $serps_url . '">' . "<b>$i</b>" . '</a>'; //No need to add htmlspecialchars(), to convert '&' to '&amp;', when echoing link here.
     } else {
         echo '<a href="' . $serps_url . '">' . "$i" . '</a>'; //No need to add htmlspecialchars(), to convert '&' to '&amp;', when echoing link here.
     }
 }
 echo '<br>';

?>

Then as the second step I would cut out the pagination HTML into a separate directory (html) and file (pagination.php), like this:

search-engine-project/
├─ config/
│  ├─ tables.php
├─ html/
│  ├─ pagination.php

In our pagination.php file:

 <?php for ($i = 0; $i < $total_pages; $i++): ?>
     <a href="?<?= http_build_query(array_merge($pagination_section_array, ['pg' => $i]), '', '&amp;') ?>">
       <?php if ($i == $page): ?>
         <b>
       <?php endif ?>
       <?= $i ?>
       <?php if ($i == $page): ?>
         </b>
       <?php endif ?>
     </a>
 <?php endfor ?>
 <br>

Then back to our original script (scroll to the end):

 <?php
 ini_set('display_errors', 1);
 ini_set('display_startup_errors', 1);
 error_reporting(E_ALL);
 
 $items_listings = ['id', 'date_and_time', 'item', 'brand', 'manufacturer', 'model', 'heading', 'year', 'description', 'price', 'country', 'keyword', 'keyphrase'];
 $visiting_history = ['id', 'date_and_time', 'searcher', 'domain', 'url', 'title', 'header', 'description', 'country', 'meta_keyword', 'meta_description', 'keyword', 'keyphrase'];
 $following_history = ['id', 'date_and_time', 'searcher', 'follower', 'domain', 'url', 'title', 'header', 'description', 'country', 'meta_keyword', 'meta_description', 'keyword', 'keyphrase'];
 
 $tables = require 'config/tables.php';
 $spidered_web_index = array_keys($tables['spidered_web_index']);
 $submitted_web_index = array_keys($tables['submitted_web_index']);

 //Extract $_GETs.
 $table = !empty($_POST['tbl']) ? $_POST['tbl'] : (!empty($_GET['tbl']) ? $_GET['tbl'] : 'spidered_web_index');
 $column = !empty($_POST['col']) ? $_POST['col'] : (!empty($_GET['col']) ? $_GET['col'] : 'id');
 $limit = !empty($_POST['lmt']) ? $_POST['lmt'] : (!empty($_GET['lmt']) ? $_GET['lmt'] : 1);
 $match = !empty($_POST['mtch']) ? $_POST['mtch'] : (!empty($_GET['mtch']) ? $_GET['mtch'] : 'fuzzy');
 $search = !empty($_POST['srch']) ? $_POST['srch'] : (!empty($_GET['srch']) ? $_GET['srch'] : 'mobile');
 $page = !empty($_GET['pg']) ? intval($_GET['pg']) : 1;
 $offset = $page * $limit - $limit;
 $headings = array_values($tables[$table]);
 $columns = array_keys($tables[$table]);
 echo $columns_count = count($columns);
 if (!in_array($column, $columns)) {
     die('Invalid Column!');
 }
 if (!in_array($tables[$table], $tables)) {
     die('Invalid Table!');
 }
 $search = !empty($search) && $match === 'fuzzy' ? str_replace('*', '%', $search) : $search;
 $char_types = str_repeat('s', $columns_count);
 $search_values = array_fill(0, $columns_count, $search);
 $comparator = $match === 'exact' ? '=' : 'LIKE';
 $sql = "SELECT * FROM $table WHERE " . implode(" $comparator ? OR ", $columns) . " $comparator ? ";
 $sql .= "ORDER BY id DESC LIMIT $limit OFFSET $offset";
 //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 (!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 {
     mysqli_stmt_bind_param($stmt, $char_types, ...$search_values);
     mysqli_stmt_execute($stmt);
     mysqli_stmt_store_result($stmt); //Necessary to use with mysqli_stmt_num_rows() when SQL query is SELECT.
     echo '<br><br>';
     echo '<br><br>';
     echo '<br><br>';
     echo '<br><br>';

     //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 {
         mysqli_stmt_bind_param($stmt, $char_types, ...$search_values);
         mysqli_stmt_execute($stmt);
         $result = mysqli_stmt_get_result($stmt);
         mysqli_stmt_free_result($stmt); //Is this really necessary ?

         while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
             for ($i = 0; $i < $columns_count; $i++) {
                 echo '<b>' . $headings[$i] . ': </b>' . $row[$columns[$i]];
                 echo '<br>';
             }
         }
     }
 }
 mysqli_stmt_close($stmt);
 mysqli_close($conn);
 echo 'Total Pages: ' . ($total_pages = ceil($rows_count / $limit));
 echo '<br><br>';
 
 $pagination_section_array = ["tbl" => "$table", "mtch" => "$match", "lmt" => "$limit", "srch" => "$search"];

 // refactored part
 require 'html/pagination.php';
 // end refactored part
?>

This way bit by bit, the code gets smaller, and easier to maintain.

TheStudent2023 · May 7, 2023

@mc_gyver

I am still waiting for your answers.

TheStudent2023 · May 7, 2023

@mc_gyver

Oops! Sorry! Ignore my last post. As after refreshing this page, I see Ignace has answered on your behalf!

TheStudent2023 · May 7, 2023

On 5/3/2023 at 12:03 PM, ignace said:

Also don't do this:

SELECT id,domain,word,phrase from $tbl WHERE $col_1 = ?

Because now it's easy for a hacker to do something like:

?col_1=1;DELETE FROM users WHERE id&input_1=1

Instead do something like:

/**
 * @param array{col_1: string} $fields
 * @param string $operator Possible values AND, OR
 * @param int $limit Possible value between 0 and 100, if invalid defaults to 100
 * @param int $offset 
 *
 * @return array{items: array, total_items: int}
 */
function page_search(array $fields, string $operator, int $limit, int $offset = 0): array {
  $where = $values = [];
  $operator = in_array($operator, ['OR', 'AND']) ? $operator : 'AND';
  $limit = 0 < $limit && $limit < 100 ? $limit : 100;
  $offset = 0 <= $offset ? $offset : 0;

  foreach ($fields as $field => $term) {
    switch ($field) {
      case 'col_1':
        $where[] = 'col_1 = ?';
        $values[] = $term;
        break;
      // other fields you want to allow to search on
  }

  $result = [
    'items' => [],
    'total_items' => 0,
  ];

  if ([] === $where) {
    return $result;
  }
    
  $result['items'] = db_fetch_all('SELECT * FROM some_table WHERE ' . implode($operator, $where) . " LIMIT $offset, $limit", $values);
    
  if (count($result['items'])) {
    // only execute a count() query if we have a result
    $result['total_items'] = db_fetch_column('SELECT count(*) FROM some_table WHERE ' . implode($operator, $where), $values);
  }

  return $result;
}

By dividing your program into little pieces you reduce the cognitive load necessary to work on pieces of your application or to find and fix bugs.

The same goes for the database. I see you use mysqli_connect in the script with the values hardcoded. Instead you should create a file that holds your configuration values:

// bootstrap.php
define('DB_HOST', 'localhost');
define('DB_USER', 'the_username');
define('DB_PASS', 'the_password');
define('DB_NAME', 'the_database');
define('DB_PORT', 3306);
define('DB_CHARSET', 'utf8mb4');

define('EMERGENCY_EMAIL', 'some@email.com'); // in case of fatal errors

// other configuration values

Then in your database functions file:

function db_connect() {
  static $connection;

  if (null === $connection) {
    $connection = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME, DB_PORT);
    if ($connection) {
      mysqli_set_charset($connection, DB_CHARSET);
    }
  }

  return $connection;
}

All of this creates building blocks you can use to build upon further. The next step would be to create a db_fetch_all function that uses db_connect to get the active DB connection etc...

@ignace

I not in the habit of noticing things. Just noticed you are a MOD. So, do excuse me addressing you as "good man" as if I was your senior. Nearly addressed "Good Boy!" back then but changed my mind thinking you might think I am being sarcastic.

Anyways, you have got a lot of explaining to do. I'm afraid!

I can't keep your method waiting to be implemented any longer. (I remember now, few yrs back Mc_Gyver used to complain why I never fixed my code based on his feed-back. Frankly, I tried my best. But it seems there were few things I did not understand, (I thought I did), and so missed out a few things to update and he was all over my case now and then getting ~~cross~~, thinking I deliberately ignoring his advice. Don't want you thinking the same). Now, do I ?

So, here goes. Let's get rolling. Ready to update my code based on your samples. But, I ain't no copy-cat who asks people for code samples just to copy-paste without learning anything atall. And so, I am going to ask you a lot of questions about your code which will sound weird and childish and frustrating. But, can't be helped. Remember, I do not know the basics of programming. Well, maybe I do a little.But not in the REAL sense. Only memorized the basic php lang syntax and some functions and copied codes from tutorials and forums and memorized them and then cooked-up the codes you see in this thread. (And no, I do not memorize things I do not understand). Cooked them up with a little help here and there, ofcourse. And after a few test, experiments, fiddling and procrastinating about. Can't be pretending, I understood all your code lines and drift into procrastinating again, thinking I understood every line of code, even though I probably managed to understand the spirit of your code. Have to understand every line. Else, a waste of your time trying to teach me something that is going through one ear and will go out the other DOUBLE QUICK (as USUAL).

Let's do a combing operation on your lines of code. Bear in mind, I do not know the basics of oop. Do not understand the difference between the procedural style & oop programming. Only understand, whether right or wrong, that the former goes in a straight flow, like a water-fall. While with latter, you can jump here and there like a frig. Up, down, up. All over the script flow. And oop re-uses the same lines of codes, through a lot of referencing. That's what I understood the benefits of oop. But still do not understand what oop really is. All that object and class stuff confuses me. Using the class, I do not understand why it cannot be used in procedural style and how using the class turns a code from procedural style to oop. I just do not get it. I guess, I am not made for adv programming. Just basic layman degree. You might be able to change that. For good.This time, where others have failed.

NEW QUESTIONS ON YOUR SECURED CODE

1.

>>Also don't do this:

SELECT id,domain,word,phrase from $tbl WHERE $col_1 = ?

Because now it's easy for a hacker to do something like:

?col_1=1;DELETE FROM users WHERE id&input_1=1 <<

I have read many tutorials on how sql is injected. They always say, hacker could input this and that. Just how will he input it ? Via the html form (POST METHOD) or the url (GET METHOD) ?

So, limiting what values could be inputted will not foil the injection attempt ? Like the value has to be from a white-list (array) ? ALthough, my current scripts you see above do not do the white-listing using arrays, I have done in past.

(Also, let's limit from where SQL commands come come to my db. Only from my domain. That way, hacker cannot build his own fake form on his localhost or webserver and gateway it to my db. How to write code to do that ? Good idea ? I know it won't solve all problems but atleast some *rs*hole won't get the satisfaction to build his own html form on his Xampp and connect it to my gateway and LAZILY start sending SQL injections. Got to keep the **ck*rs on their feet. On their tip toes where if they try too much they will tip over the fringe down mount everest in an ice pool. Ready to be frozen).

On some other form, we will try cooking up TRAPS for hackers. Give them a hard-time. Screw up their PCs, if possible. Teach them a LESSON not to mess with us ever AGAIN.

2.

>>Also don't do this:

SELECT id,domain,word,phrase from $tbl WHERE $col_1 = ?

Because now it's easy for a hacker to do something like:

?col_1=1;DELETE FROM users WHERE id&input_1=1

Instead do something like:

	/**
	* @param array{col_1: string} $fields * @param string $operator Possible values AND, OR
	* @param int $limit Possible value between 0 and 100, if invalid defaults to 100
	* @param int $offset *
	*@return array{items: array, total_items: int}
	*/
	function page_search(array $fields, string $operator, int $limit, int $offset = 0): array {
	$where = $values = []; $operator = in_array($operator, ['OR', 'AND']) ? $operator : 'AND';
	$limit = 0 < $limit && $limit < 100 ? $limit : 100;
	$offset = 0 <= $offset ? $offset : 0;

>>By dividing your program into little pieces you reduce the cognitive load necessary to work on pieces of your application or to find and fix bugs. <<

So, I should not use $vars but hard code CONSTANTS instead ? That is always safe ? OK.

Explain this comment line. What is going on here ?

>>* @param array{col_1: string} $fields<<

3.

>> * @param string $operator Possible values AND, OR <<

Your comment is telling me, the OPERATOR data type is STRING only. You limited to that ?

Yes ? If so, let me see & learn, just how you did the restricting! This is GOLD NUGGET. I was trying to thinkup my own function or method to do these restrictions. Best not re-invent the wheel if by default php has a method. Or, you created a custom function.

>>$operator = in_array($operator, ['OR', 'AND']) ? $operator : 'AND';<<

I see you placed no restrictions here ? Can it not be done here ?

I see you placed restriction there on the following line, instead. But cannot the restriction be placed in the above line ?

>> function page_search(array $fields, string $operator, int $limit, int $offset = 0): array { $where = $values = [];<<

I mean, something like these ...

$operator = in_array(string $operator, ['OR', 'AND']) ? string $operator : 'AND';

$operator = in_array((string) $operator, ['OR', 'AND']) ? (string) $operator : 'AND'; ..Similar to the Type Casting.

You will tell me why don't I test it and see if it works ? My answer: It might fail if I slipped in one place but you may no how not to slip.

4.

>>The same goes for the database. I see you use mysqli_connect in the script with the values hardcoded. Instead you should create a file that holds your configuration values:

	// bootstrap.php define('DB_HOST', 'localhost');
	define('DB_USER', 'the_username'); define('DB_PASS', 'the_password');
	define('DB_NAME', 'the_database'); define('DB_PORT', 3306);
	define('DB_CHARSET', 'utf8mb4');
	define('EMERGENCY_EMAIL', 'some@email.com'); // in case of fatal errors // other configuration values

If no hard coding the SQL leaves room for sql injection then how come hard coding the DB connection is bad ? Did you not mean earlier to hard code CONSTANTS than use $vars as that leaves room for data injection ? Now, I am scratching my head. When should I hard code CONSTANT as values and when $vars as values for security purpose ? How do you figure when to do what out of the two ?

What exactly are you trying to generate on this block here ? And, why specifically this way ?

	foreach ($fields as $field => $term)
	{ switch ($field) { case 'col_1': $where[] = 'col_1 = ?';
	    $values[] = $term; break; // other fields you want to allow to search on
	}

What are you doing in this block ?

	$result = [ 'items' => [], 'total_items' => 0, ];

Explain separately, what you are doing in this block:

	if ([] === $where)
	{
	    return
	   $result;
	}

Explain this block separately:

	$result['items'] = db_fetch_all('SELECT * FROM some_table WHERE ' . implode($operator, $where) . " LIMIT $offset, $limit", $values);
	if (count($result['items']))
	{
	    // only execute a count() query if we have a result
	   $result['total_items'] = db_fetch_column('SELECT count(*) FROM some_table WHERE ' . implode($operator, $where), $values); } return $result;
	}

Result ? Execute only if there is a result ? What result ? What result are you expecting ? What will happen if there is no result, though ?

Now,we are really gonna get somewhere. Making a good pace. Lol!

Edited May 7, 2023 by TheStudent2023

ignace · May 7, 2023

It's good practice to assume that if data was entered by a user it is dangerous and should be validated and not be input directly into any strings ("INSERT INTO users (username) VALUE('$username')") or in any HTML (<span><?= $username ?></span>) without validating it thoroughly.
What do you mean with constants? Do you mean define('SOME_CONSTANT', 'some value'); or are you referring to the 'AND', 'OR' in my example?
- @param, @return are for documentation, you can learn more about them here: https://docs.phpdoc.org/3.0/guide/references/phpdoc/basic-syntax.html#basic-syntax
It's a new feature from PHP 8.0+ where you can define the type of a variable.
- In previous versions you would write function foo($bar) when you declared a function. Which meant a lot of boilerplate code inside your functions, now you can just write function foo(string $bar) and you know it's a string.
The general rule of thumb is that if the value will not be changed after it's been set initially in your application, use a constant, otherwise use a variable.
- So your DB hostname, port, username and password are gonna be set at the start of your application, and then nothing should be able to change these values.

The first block loops over all fields and then tests each field against a list of known field names, if a match is found, it executes the case statement.

The second block I define the default return value.

The third block I check if the $where variable is empty, it's the same as if (empty($where))

The fourth block I check if there is a result, if there was no result, there is no point in doing a SELECT count(*) query as the count would be 0, which is the same as the default set previously.

I highly recommend you view this video on youtube. It does a great job of explaining everything in depth.

TheStudent2023 · May 7, 2023

@ignace

Mmm.I just went through your post: Posted Friday at 01:07 PM.

Now, I got more questions. Like 10. But, I think I won't have any questions or hardly any, if your code was in procedural style. Oop lines I do not understand and we are gonna go in a loop where I ask you questions after questions and bore you to death. And I won;t be able to memorise any of the oop lines since I do not completely understand.

You want to get some basics of security across to me and so you showed some examples. Now, because I don't understand oop, it's resulting in my having questions after questions. To cut thinks short, best you proceduralize your code and add your blocks to my original code and give it to me. I think with one or two looks I will understand your security lines without needing to ask anymore tonnes of questions.

Edited May 7, 2023 by TheStudent2023

kicken · May 7, 2023

2 hours ago, TheStudent2023 said:

Just how will he input it ? Via the html form (POST METHOD) or the url (GET METHOD) ?

They will input however they can. That may be via POST data, GET parameters, the URL path, HTTP headers, Cookies, etc. Anywhere that your program accepts external input has potential for bad data. If that external input is then used in some way in your SQL query, you have the potential for SQL Injection.

2 hours ago, TheStudent2023 said:

That way, hacker cannot build his own fake form on his localhost or webserver and gateway it to my db. How to write code to do that ? Good idea ?

They don't need to make their own form on their own host, they can just manipulate your form using a browser's Development tools or submit requests with a tool like cURL. What you're describing regarding using another server to host a fake form an entirely different security issue known as Cross-site request forgery and the solution to that is unique tokens as part of your form data.

2 hours ago, TheStudent2023 said:

So, I should not use $vars but hard code CONSTANTS instead ? That is always safe ? OK.

2 hours ago, TheStudent2023 said:

If no hard coding the SQL leaves room for sql injection then how come hard coding the DB connection is bad ? Did you not mean earlier to hard code CONSTANTS than use $vars as that leaves room for data injection ? Now, I am scratching my head.

You're conflating things here. Hard-coding a value means you're just inputting a string or number or whatever directly where it needs to be. This can make maintainence or configuration changes harder as you need to change every place that value is needed. Defining values as a variable or constant means you can put the values in one place, then just re-use that variable/constant where needed. Using those variables/constants doesn't suddenly mean you are vulnerable to SQL injection.

SQL injection is caused by using end-user having control over the value of variables used in your queries. Using variables that have a hard-coded value is fine. Using variables whose value comes from outside your control is a problem. You seem to have some idea about this being the case.

2 hours ago, TheStudent2023 said:

So, limiting what values could be inputted will not foil the injection attempt ? Like the value has to be from a white-list (array) ?

Yes, if you have a specific set of safe values, and require your variable to be one of those values before it's used in a query, then that is ok. Your code doest at least make an attempt to validate that $table is an expected value via the check:

if(!in_array($tables[$table],$tables))
{
    die('Invalid Table!');
}

That check may work, but is not ideal. An invalid table value would give you E_NOTICE errors about an undefined index. Your $headings and $columns values would also be invalid since the invalid table doesn't exist.

You should checking with isset or array_key_exists instead. You should also be checking this condition before you try and use the value for other things, not after. For example:

$table = !empty($_POST['tbl']) ? $_POST['tbl'] : (!empty($_GET['tbl']) ? $_GET['tbl'] : 'spidered_web_index');
if (!isset($tables[$table])){
   die('Invalid table');
}

$headings = array_values($tables[$table]);
$columns = array_keys($tables[$table]);

2 hours ago, TheStudent2023 said:

Do not understand the difference between the procedural style & oop programming. Only understand, whether right or wrong, that the former goes in a straight flow, like a water-fall. While with latter, you can jump here and there like a frig. Up, down, up.

Fundamentally, the difference between what is commonly called "procedural" vs "OOP" styles has to do with how data is handled, not code flow. Either way, code runs top to bottom within a block, and will jump randomly between blocks as you run functions, conditionals, etc.

What's different is that with OOP, your classes hold both the context data they need and the functions to operate on that context. With procedural programming, the context gets passed around from function to function. In OOP, since the object owns it's context, it can control whether other parts of code are allowed to manipulate that context or not and the programmer cannot accidentally pass in the wrong context or just omit it entirely. With procedural, the program has to be sure to always pass along the context and make sure they pass along the correct context.

For the mysqli API, you might have noticed that when you compare the function prototypes in the manual between the two styles, the procedural version usually has an extra parameter. eg:

//OOP
public mysqli_stmt::prepare(string $query): bool

//Procedural
mysqli_stmt_prepare(mysqli_stmt $statement, string $query): bool

That extra $statement parameter is the context that you end up having to pass around from function to function when you code procedural style. If you pass in the wrong value, things break. In the OOP version of the API, that context is part of the object itself so there's no need to pass it between the functions. The functions can just grab it from the object they are being called on (the $this value in a class method).

As a novice that's just using pre-made classes, there's little difference from your point of view, other than you end up typing $someObject->someMethod() instead of someMethod($someObject). When you start getting into writing libraries for others (or just yourself) to use in various projects, then you start seeing more of the benefits of OOP. Studying small libraries written in an OOP style may help to understand things more.

Sign In

PAGINATION 1 - mysqli_stmtm_store_result() Query

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Important Information