TheStudent2023 Posted May 1, 2023 Share Posted May 1, 2023 Good Night, This is my pagination script which I built from memory. No copy-pasting from anywhere. And so, it is unorthodox. Trying to shorten the code as much as possible. See if you can shorten it or not using mysqli, prepared statements and procedural style. No OOP or pdo as I do not know them. Oh, and do not repeat yourselves telling me what Mac Guyver has told me over here: Because, once I understand all the things he has advised over there, I will try heeding his advice over on this thread & code too. In the meanwhile, see if this code can be further shortened or not. That is all. Thanks! <?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,'','&'); //Did convert '&' to '&' 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 '&', when echoing link here. } else { echo '<a href="' .$serps_url .'">' ."$i" .'</a>'; //No need to add htmlspecialchars(), to convert '&' to '&', when echoing link here. } } echo '<br>'; ?> Link to comment Share on other sites More sharing options...
Recommended Posts