Leaderboard

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.