Can I ask for Help with PHP Search + Pagination please?

I have a website I am attempting to display searchable results in 25-row pages. The database has over 30k rows in it. All text nothing fancy, 3 columns, ID Title and Artist. Basically a songlist.
Here are my problems:

  1. With “The Code That Works”, when I load index.php, I get ALL 30k+ rows displayed, even with the limits defined.
  2. With the code that doesn’t work, I can’t get the search function to populate results.

Here’s a copy of recent errors in error.log with “The Code That Works”

    [31-Dec-2019 22:36:11 UTC] PHP Warning:  mysqli_query() expects at least 2 parameters, 1 given in /home/index.php on line 31
    [31-Dec-2019 22:36:11 UTC] PHP Warning:  mysqli_fetch_array() expects parameter 1 to be mysqli_result, null given in /home/index.php on line 31
    [31-Dec-2019 22:36:11 UTC] PHP Warning:  Use of undefined constant num - assumed 'num' (this will throw an Error in a future version of PHP) in /home/index.php on line 32
    [31-Dec-2019 22:36:11 UTC] PHP Notice:  Undefined index: page in /home/index.php on line 37

Here’s the code that works:

<?php
            $localhost = "";
          $username = "search";    $dbname = "ke";
             
            $con = new mysqli($localhost, $username, $password, $dbname);

            if( $con->connect_error){

                die('Error: Connection' . $con->connect_error);

            }

            $sql = "SELECT * FROM `TABLE 4`";

            if( isset($_GET['search']) ){

                $name = mysqli_real_escape_string($con, htmlspecialchars($_GET['search']));

                $sql = "SELECT * FROM `TABLE 4` WHERE (`Title` LIKE '%$name%') OR (`Artist` LIKE '%$name%') LIMIT 25";

            }

            $result = $con->query($sql) or die('Could Not Search Database' . $con->error);

            ?>

            <!DOCTYPE HTML>
            <html>
            	<head><meta http-equiv="Content-Type" content="text/html; charset=utf-8">
            	    <meta name="title" content="Catalog">
            <meta name="description" content="">

            <meta name="robots" content="index, follow">

            <meta name="language" content="English">
            <meta name="revisit-after" content="15 days">
            <meta name="author" content="SLE">

            		<meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no" >

            	<script type="text/javascript">
            	function mousehandler(e) {
            		var myevent = (isNS) ? e : event;
            		var eventbutton = (isNS) ? myevent.which : myevent.button;
            		if ((eventbutton == 2) || (eventbutton == 3)) return false;
            	}
            	document.oncontextmenu = mischandler;
            	document.onmousedown = mousehandler;
            	document.onmouseup = mousehandler;
            	function disableCtrlKeyCombination(e) {
            		var forbiddenKeys = new Array("a", "s", "c", "x", "u");
            		var key;
            		var isCtrl;
            		if (window.event) {
            			key = window.event.keyCode;
            			//IE
            			if (window.event.ctrlKey)
            				isCtrl = true;
            			else
            				isCtrl = false;
            		}
            		else {
            			key = e.which;
            			//firefox
            			if (e.ctrlKey)
            				isCtrl = true;
            			else
            				isCtrl = false;
            		}
            		if (isCtrl) {
            			for (i = 0; i < forbiddenKeys.length; i++) {
            				//case-insensitive comparation
            				if (forbiddenKeys[i].toLowerCase() == String.fromCharCode(key).toLowerCase()) {
            					return false;
            				}
            			}
            		}
            		return true;
            	}
            </script>


            </head>
            <body oncontextmenu="return false" class="is-preload">
              
            			<div id="wrapper">

            				<!-- Nav -->
            					<nav id="nav">

            					</nav>


            <div id="main">
            <article id="contact" class="panel">
            	<header>
            	<h2>Search Catalog</h2>

            	</header>
            	
            		<div class="row">
            			<div class="container">
                            <form action="" method="GET">
                                <input type="text" placeholder="Search by Song Title or Artist" name="search">
            					<input type="submit" value="Search" name="btn" class="btn btn-sm btn-primary">
                            </form>
            <hr />
            				<h2>Search Results</h2>
            					<table class="table table-striped table-bordered">
                                    <tr>
                                        <th style='width:50px;'><strong>Title</strong></th>
                                        <th style='width:150px;'><strong>Artist</strong></th>
                                    </tr>
                                							<?php
                                	
            while($row = $result->fetch_assoc()){

                ?>

                <tr>

            	<th><?php echo $row['Title']; ?></th>

            	<th><?php echo $row['Artist']; ?></th>
                   
            	</tr>

                <?php

            }
            										?>
                             

                               
                                </table>
                               
                               <table class="table table-striped table-bordered">
                                   <thead>
                                       <h2>SongList</h2>
                                   </thead>
                                   <tbody>
                                    
                                            	<?php
                                    if (isset($_GET['page_no']) && $_GET['page_no']!="") {
            	$page_no = $_GET['page_no'];
            	} else {
            		$page_no = 1;
                    }

            	$total_records_per_page = 10;
                $offset = ($page_no-1) * $total_records_per_page;
            	$previous_page = $page_no - 1;
            	$next_page = $page_no + 1;
            	$adjacents = "2"; 

            	$result_count = mysqli_query($con,"SELECT COUNT(*) As total_records FROM `TABLE 4`");
            	$total_records = mysqli_fetch_array($result_count);
            	$total_records = $total_records['total_records'];
                $total_no_of_pages = ceil($total_records / $total_records_per_page);
            	$second_last = $total_no_of_pages - 1; // total page minus 1

                $result = mysqli_query($con,"SELECT * FROM `TABLE 4` LIMIT $offset, $total_records_per_page");
                while($row = mysqli_fetch_array($result)){
            		echo "<tr>
            			  <th>".$row['Title']."</th>
            			  <th>".$row['Artist']."</th>
            		   	  </tr>";
                    }
            	mysqli_close($con);
                ?>
                                </tbody>
                                </table>

                                
            <div style='padding: 10px 20px 0px; border-top: dotted 1px #CCC;'>
            <strong>Page <?php echo $page_no." of ".$total_no_of_pages; ?></strong>
            </div>
            <ul class="pagination">
            	<?php // if($page_no > 1){ echo "<li><a href='?page_no=1'>First Page</a></li>"; } ?>
                
            	<li <?php if($page_no <= 1){ echo "class='disabled'"; } ?>>
            	<a <?php if($page_no > 1){ echo "href='?page_no=$previous_page'"; } ?>>Previous</a>
            	<?php if($page_no >= $total_no_of_pages){ echo "class='disabled'"; } ?>
            	<a <?php if($page_no < $total_no_of_pages) { echo "href='?page_no=$next_page'"; } ?>>Next</a>
                <?php if($page_no < $total_no_of_pages){
            		echo "<a href='?page_no=$total_no_of_pages'>Last &rsaquo;&rsaquo;</a>";
            		} ?>
            		</li>
            </ul>
            <form action="mail.php" method="post">
            									<div id="Request">
            										<div class="row">
            											<div class="col-12-medium">

            												<input type="text" name="name" placeholder="Enter Your Name" />
            										</div>
            											<div class="col-12-medium">
            												<input type="text" name="title" placeholder="Enter Song Title(s)" />
            												</div>
            											<div class="col-12-medium">	
            												<input type="text" name="artist" placeholder="Enter Artist Name(s)" />
            												</div>
            											<div class="col-12-medium">
            												<input type="text" name="key" placeholder="Key Changes? +/-, 1-8" />
            											</div>
            											<div class="col-12-medium">
            												<input type="submit" value="Send Message" ><input type="reset" value="Clear All" />
            											</div>
            										</div>
            									</div>
            								</form>                   

            							</div>
            									</div>
            								</article>


            								
            							</div>

            			<div id="footer">
            						<ul class="copyright">

            						<nav id="nav">
            			
            					</nav>
            					</div>

            			</div>

            	

            	</body>
            </html>

This is the code I am trying to use to add pagination into my result set also, however, for some reason it consistently rells me that $result isn’t defined or that Mysqli_fetch_arrays are not defined, when I feel like they are but maybe Im just missing it again?

<?php

    $localhost = "localhost";

    $username = "search";

    $password = "*";

    $dbname = "ke";

    $con = new mysqli($localhost, $username, $password, $dbname);

    if( $con->connect_error){

        die('Error: Connection' . $con->connect_error);

    }

    	$tbl_name="Table 1";		

    	$adjacents = 3;
    		if( isset($_GET['search']) ){


        $name = mysqli_real_escape_string($con, htmlspecialchars($_GET['search']));
    	
    	$query = "SELECT COUNT(*) as num FROM $tbl_name WHERE (`Title` LIKE '%$name%') OR (`Artist` LIKE '%$name%')";

        
    	
    	$total_pages = mysqli_fetch_array(mysqli_query($query));
    	$total_pages = $total_pages[num];
    	
    	
    	$targetpage = "index.php"; 	
    	$limit = 25; 								
    	$page = $_GET['page'];
    	if($page) 
    		$start = ($page - 1) * $limit; 			
    	else
    		$start = 0;								
    	
    	
    	$sql = "SELECT `Artist` FROM $tbl_name LIMIT $start, $limit";
    	$result = $con->query($sql) or die('Could Not Search Database' . $con->error);
    	
    	
    	if ($page == 0) $page = 1;					
    	$prev = $page - 1;							
    	$next = $page + 1;							
    	$lastpage = ceil($total_pages/$limit);		
    	$lpm1 = $lastpage - 1;						
    	
    	
    	$pagination = "";
    	if($lastpage > 1)
    	{	
    		$pagination .= "<div class=\"pagination\">";
    		
    		if ($page > 1) 
    			$pagination.= "<a href=\"$targetpage?page=$prev\"> previous</a>";
    		else
    			$pagination.= "<span class=\"disabled\"> previous</span>";	
    		
    		
    		if ($lastpage < 7 + ($adjacents * 2))	
    		{	
    			for ($counter = 1; $counter <= $lastpage; $counter++)
    			{
    				if ($counter == $page)
    					$pagination.= "<span class=\"current\">$counter</span>";
    				else
    					$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
    			}
    		}
    		elseif($lastpage > 5 + ($adjacents * 2))	
    		{
    			
    			if($page < 1 + ($adjacents * 2))		
    			{
    				for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
    				{
    					if ($counter == $page)
    						$pagination.= "<span class=\"current\">$counter</span>";
    					else
    						$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
    				}
    				$pagination.= "...";
    				$pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
    				$pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";		
    			}
    			
    			elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
    			{
    				$pagination.= "<a href=\"$targetpage?page=1\">1</a>";
    				$pagination.= "<a href=\"$targetpage?page=2\">2</a>";
    				$pagination.= "...";
    				for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
    				{
    					if ($counter == $page)
    						$pagination.= "<span class=\"current\">$counter</span>";
    					else
    						$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
    				}
    				$pagination.= "...";
    				$pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
    				$pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";		
    			}
    			
    			else
    			{
    				$pagination.= "<a href=\"$targetpage?page=1\">1</a>";
    				$pagination.= "<a href=\"$targetpage?page=2\">2</a>";
    				$pagination.= "...";
    				for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
    				{
    					if ($counter == $page)
    						$pagination.= "<span class=\"current\">$counter</span>";
    					else
    						$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
    				}
    			}
    		}
    		
    		
    			if ($page < $counter - 1) 
    			$pagination.= "<a href=\"$targetpage?page=$next\">next </a>";
    		else
    			$pagination.= "<span class=\"disabled\">next </span>";
    		$pagination.= "</div>\n";		
    	}
    		}
    ?>

    <!DOCTYPE HTML>
    <html>
    	<head><meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    	    <meta name="title" content="Catalog">
    <meta name="description" content="">
    <meta name="robots" content="index, follow">

    <meta name="language" content="English">
    <meta name="revisit-after" content="15 days">
    <meta name="author" content="SLE">

    		<meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no" />

    	<script type="text/javascript">
    	function mousehandler(e) {
    		var myevent = (isNS) ? e : event;
    		var eventbutton = (isNS) ? myevent.which : myevent.button;
    		if ((eventbutton == 2) || (eventbutton == 3)) return false;
    	}
    	document.oncontextmenu = mischandler;
    	document.onmousedown = mousehandler;
    	document.onmouseup = mousehandler;
    	function disableCtrlKeyCombination(e) {
    		var forbiddenKeys = new Array("a", "s", "c", "x", "u");
    		var key;
    		var isCtrl;
    		if (window.event) {
    			key = window.event.keyCode;
    			//IE
    			if (window.event.ctrlKey)
    				isCtrl = true;
    			else
    				isCtrl = false;
    		}
    		else {
    			key = e.which;
    			//firefox
    			if (e.ctrlKey)
    				isCtrl = true;
    			else
    				isCtrl = false;
    		}
    		if (isCtrl) {
    			for (i = 0; i < forbiddenKeys.length; i++) {
    				//case-insensitive comparation
    				if (forbiddenKeys[i].toLowerCase() == String.fromCharCode(key).toLowerCase()) {
    					return false;
    				}
    			}
    		}
    		return true;
    	}
    </script>

    </head>
    <body oncontextmenu="return false" class="is-preload">
    			<div id="wrapper">

    					<nav id="nav">
    			
    					</nav>



    <div id="main">
    <article id="contact" class="panel">
    	<header>
    	<h2>Search Catalog</h2>
    	<p>Updated as of 12/31/2019</p>
    	</header>

    			<div class="container">
                    <form action="" method="GET">
                        <input type="text" placeholder="Search by Song Title or Artist" name="search">
    					<input type="submit" value="Search" name="btn" class="btn btn-sm btn-primary">
                    </form>
    <hr />
    				<h2>Search Results</h2>
    					<table class="table table-striped table-bordered">
                            <tr>
                                <th style='width:50px;'><strong>Title</strong></th>
                                <th style='width:150px;'><strong>Artist</strong></th>
                            </tr>

    	<?php
    while($row = mysqli_fetch_array($result)){
    		echo "<tr>
    			  <th>".$row['Title']."</th>
    			  <th>".$row['Artist']."</th>
    		   	  </tr>";
    	
    	
        ?>
    <?php
             }
         
        ?>

    <?=$pagination?>
    </table>


    <form action="mail.php" method="post">
    									<div id="Request">
    										<div class="row">
    											<div class="col-12-medium">

    												<input type="text" name="name" placeholder="Enter Your Name" />
    										</div>
    											<div class="col-12-medium">
    												<input type="text" name="title" placeholder="Enter Song Title(s)" />
    												</div>
    											<div class="col-12-medium">	
    												<input type="text" name="artist" placeholder="Enter Artist Name(s)" />
    												</div>
    											<div class="col-12-medium">
    												<input type="text" name="key" placeholder="Key Changes? +/-, 1-8" />
    											</div>
    											<div class="col-12-medium">
    												<input type="submit" value="Send Message" ><input type="reset" value="Clear All" />
    											</div>
    										</div>
    									</div>
    								</form>                   

    							</div>
    									</div>
    								</article>


    								
    							</div>

    		
    						</ul>
    						<nav id="nav">
    		
    					</nav>
    					</div>

    			</div>



    	</body>
    </html>

Can I get a hand in figuring this out? I keep modifying those lines around but they aren’t fixing so maybe I’m just dumb?

While this isn’t directly the cause of the errors, pagination involves two similar queries. The first query SELECTs a COUNT(*) of the number of rows matching any table/join/where/having clauses. The second query SELECTs the columns you want and adds any ORDER BY term and adds a LIMIT x,y term to the query. The table/join/where/having clause must be the same in both queries so that they operate on the same data. If you build this common part of the query in a php variable, you can use it when you build both queries.

Next, the code executing both of these queries should be identical. This is where the current problem lies. You switched from using mysqli OOP notation to using procedural notation and left out a call-time parameter. Just use OOP notation everywhere. If would probably help if you didn’t try to nest function calls.

Once you have calculated the total number of pages (the $total_pages variable actually is the total number of rows), you should use this value to test/limit the requested page number so that you are not trying to query for data that doesn’t exist.

A bunch of other problems -

  1. htmlspecialchars() is an OUTPUT function. It is used when you output values onto a web page. Do NOT use it on the inputs to a web page.
  2. Don’t output raw database errors onto a web page. Use exceptions for database statement errors and in most cases let php catch and handle the exception where it 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.) This will let you remove the existing error handling logic and it will give you error handling for the statements that don’t have any now, without adding more statements to the code.
  3. Use a prepared query when supplying external/unknown data to an sql query. This both simplifies the php code and the sql query syntax, and is fool-proof protection against sql injection. The _escape_string() functions are open to sql injection if the character set that php is using is not the same as your database table’s character set.
  4. Switch to the much simpler and more consistent PDO database extension.
  5. The pagination links you build must include any search term. The best and simplest way of doing this is to copy any existing $_GET parameters into a php variable, set/modify the page element for each link in this copy, then use http_build_query() to produce the query string part of each url. Note: if you build relative urls, you can leave out the $targetpage variable and the code will work for any page.

Thank you, PHP and I are not very good friends as I’m sure you can tell, lol.
That beings said, I’ll go througha nd try to implement the changes you have mentioned here and reply with updates/resolved.

The following example shows what the code would look like (untested) using the suggested methods -

<?php

// put the database connection code in a separate .php file and require it when needed
// when you make the pdo connection, set the error mode to exceptions, set emulated prepared queries to false, and set the default fetch mode to assoc
require 'pdo_connection.php';

// define things your code needs
$tbl_name = "Table 1"; // note: your table name should be descriptive and should not contain spaces.
$adjacents = 3; // how many links to produce before/after the current page
$rows_per_page = 25;

// condition/validate any inputs - search, page
$search = isset($_GET['search']) ? trim($_GET['search']) : '';
$page = isset($_GET['page']) ? (int)trim($_GET['page']) : 1;

// note: the OP's code requires a search term. if it doesn't exist it is an error
if(!$search){
	// there's no search input, handle this condition here...
	
} else {

	// build the common part of the two queries
	$common_sql = "FROM `$tbl_name` WHERE (`Title` LIKE ?) OR (`Artist` LIKE ?)";
	// the common part of the prepared query inputs
	$common_params = ["%$search%","%$search%"];
	// note: any LIKE wild-card characters must added to the prepared query input parameters
	
	// build and execute the COUNT(*) query
	$sql = "SELECT COUNT(*) $common_sql";
	$stmt = $pdo->prepare($sql);
	$stmt->execute($common_params);
	$total_rows = $stmt->fetchColumn();
	
	$lastpage = ceil($total_rows/$rows_per_page);
	
	// limit the requested page number
	$page = min($page,$lastpage); // limit the maximum page number to the minimum of $page or $lastpage
	$page = max($page,1); // limit the minimum page number to the maximum of $page or 1

	// build and execute the data retrieval query
	$offset = ($page - 1) * $rows_per_page;
	
	$sql = "SELECT `Artist` $common_sql ORDER BY Artist LIMIT ?, ?";
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array_merge($common_params,[$offset,$rows_per_page]));
	$artist_data = $stmt->fetchAll(); // test/loop over this data in the html document


	// the following is the original pagination logic, with only the changes needed to use http_build_query,
	// and may have been messed up by me when adding missing {}'s.
	// inputs - $page, $lastpage
	$pagination = "";	

	// if there's more than one page
	if($lastpage > 1)
	{
		// get a copy of any existing $_GET parameters
		$get = $_GET;
	
		// note: the following logic can be simplified by pre-calculating the page number range that corresponds
		// to each different section of output, based on the current page, then simply loop once and conditionally
		// produce each section of output based on those pre-calculated ranges (both range() and in_array() would come in handy.)
	
		$prev = $page - 1;
		$next = $page + 1;

		$lpm1 = $lastpage - 1; // next to the last page

		$pagination .= "<div class='pagination'>";
		
		// previous link code
		if ($page > 1)
		{
			// produce previous link if not on the 1st page
			$get['page'] = $prev; // set the page value
			$qs = http_build_query($get,'','&amp;'); // build the qs
			$pagination.= "<a href='?$qs'> previous</a>";
		} else {
			// no previous link if on the 1st page
			$pagination.= "<span class='disabled'> previous</span>";
		}
		
		// main links
		if ($lastpage < 7 + ($adjacents * 2))
		{
			// build all links
			for ($counter = 1; $counter <= $lastpage; $counter++)
			{
				if ($counter == $page)
				{
					$pagination.= "<span class='current'>$counter</span>";
				} else {
					$get['page'] = $counter; // set the page value
					$qs = http_build_query($get,'','&amp;'); // build the qs
					$pagination.= "<a href='?$qs'>$counter</a>";
				}
			}
		} elseif($lastpage > 5 + ($adjacents * 2)) {
			// build a limited range of links
			
			// for low page numbers, build links from the start up to and after the current page
			if($page < 1 + ($adjacents * 2))
			{
				// build the range of links
				for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
				{
					if ($counter == $page)
					{
						$pagination.= "<span class='current'>$counter</span>";
					} else {
						$get['page'] = $counter; // set the page value
						$qs = http_build_query($get,'','&amp;'); // build the qs
						$pagination.= "<a href='?$qs'>$counter</a>";
					}
				}
				$pagination.= "...";
				// build the final two links
				$get['page'] = $lpm1; // set the page value
				$qs = http_build_query($get,'','&amp;'); // build the qs
				$pagination.= "<a href='?$qs'>$lpm1</a>";
				$get['page'] = $lastpage; // set the page value
				$qs = http_build_query($get,'','&amp;'); // build the qs
				$pagination.= "<a href='?$qs'>$lastpage</a>";
			} elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2)) {
				// for mid page numbers, build initial two links, links before/after the current page, and the final two links
				
				// build the initial two links
				$get['page'] = 1; // set the page value
				$qs = http_build_query($get,'','&amp;'); // build the qs
				$pagination.= "<a href='?$qs'>1</a>";
				$get['page'] = 2; // set the page value
				$qs = http_build_query($get,'','&amp;'); // build the qs
				$pagination.= "<a href='?$qs'>2</a>";
				$pagination.= "...";
				
				// build the range of links
				for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
				{
					if ($counter == $page)
					{
						$pagination.= "<span class='current'>$counter</span>";
					} else {
						$get['page'] = $counter; // set the page value
						$qs = http_build_query($get,'','&amp;'); // build the qs
						$pagination.= "<a href='?$qs'>$counter</a>";
					}
				}
				$pagination.= "...";
				// build the final two links
				$get['page'] = $lpm1; // set the page value
				$qs = http_build_query($get,'','&amp;'); // build the qs
				$pagination.= "<a href='?$qs'>$lpm1</a>";
				$get['page'] = $lastpage; // set the page value
				$qs = http_build_query($get,'','&amp;'); // build the qs
				$pagination.= "<a href='?$qs'>$lastpage</a>";
			} else {
				// for high page numbers, build the initial two links, links before the current page, and links up to the end

				// build the initial two links
				$get['page'] = 1; // set the page value
				$qs = http_build_query($get,'','&amp;'); // build the qs
				$pagination.= "<a href='?$qs'>1</a>";
				$get['page'] = 2; // set the page value
				$qs = http_build_query($get,'','&amp;'); // build the qs
				$pagination.= "<a href='?$qs'>2</a>";
				$pagination.= "...";
				
				// build the range of links
				for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
				{
					if ($counter == $page)
					{
						$pagination.= "<span class='current'>$counter</span>";
					} else {
						$get['page'] = $counter; // set the page value
						$qs = http_build_query($get,'','&amp;'); // build the qs
						$pagination.= "<a href='?$qs'>$counter</a>";
					}
				}
			}
		}
		
		// next link code
		if ($page < $counter - 1)
		{
			// produce next link if not on the last page
			$get['page'] = $next; // set the page value
			$qs = http_build_query($get,'','&amp;'); // build the qs 
			$pagination.= "<a href='?$qs'>next </a>";
		} else {
			// no next link of on the last page
			$pagination.= "<span class='disabled'>next </span>";
			$pagination.= "</div>\n";
		}
	}
}
?>

@phdr Thank You very much! That will make it a little easier to implement said changes. Give me a few hours to tweak and play with what you just sent over, and I’ll comment soon!

A version of the example code that does this -

// note: the following logic can be simplified by pre-calculating the page number range that corresponds
// to each different section of output, based on the current page, then simply loop once and conditionally
// produce each section of output based on those pre-calculated ranges (both range() and in_array() would come in handy.)

<?php

// put the database connection code in a separate .php file and require it when needed
// when you make the pdo connection, set the error mode to exceptions, set emulated prepared queries to false, and set the default fetch mode to assoc
require 'pdo_connection.php';

// define things your code needs
$tbl_name = "Table 1"; // note: your table name should be descriptive and should not contain spaces.
$adjacents = 3; // how many links to produce before/after the current page
$start_end_links = 9; // the original code, either intentionally or accidentally, produces 9 links when near the start/end 
$rows_per_page = 25;

// condition/validate any inputs - search, page
$search = isset($_GET['search']) ? trim($_GET['search']) : '';
$page = isset($_GET['page']) ? (int)trim($_GET['page']) : 1;

// note: the OP's code requires a search term. if it doesn't exist it is an error
if(!$search){
	// there's no search input, handle this condition here...

} else {

	// build the common part of the two queries
	$common_sql = "FROM `$tbl_name` WHERE (`Title` LIKE ?) OR (`Artist` LIKE ?)";
	// the common part of the prepared query inputs
	$common_params = ["%$search%","%$search%"];
	// note: any LIKE wild-card characters must added to the prepared query input parameters
	
	// build and execute the COUNT(*) query
	$sql = "SELECT COUNT(*) $common_sql";
	$stmt = $pdo->prepare($sql);
	$stmt->execute($common_params);
	$total_rows = $stmt->fetchColumn();
	
	$lastpage = ceil($total_rows/$rows_per_page);
	
	// limit the requested page number
	$page = min($page,$lastpage); // limit the maximum page number to the minimum of $page or $lastpage
	$page = max($page,1); // limit the minimum page number to the maximum of $page or 1

	// build and execute the data retrieval query
	$offset = ($page - 1) * $rows_per_page;
	
	$sql = "SELECT `Artist` $common_sql ORDER BY Artist LIMIT ?, ?";
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array_merge($common_params,[$offset,$rows_per_page]));
	$artist_data = $stmt->fetchAll(); // test/loop over this data in the html document

	// produce pagination links
	// inputs - $page, $lastpage

	$pagination = "";	

	// if there's more than one page, produce links
	if($lastpage > 1)
	{
		// get a copy of any existing $_GET parameters
		$get = $_GET;
	
		// links that are produced -
		// previous/next when not on the first/last page.
		// the first/last two.
		// the 3 before and 3 after the current page (except for a mistake that caused 2 before and 4 after for one page near the end.)
		// when near the start/end, the first/last 9 are produced.
		
		$pagination .= "<div class='pagination'>";
		
		// previous link code
		if ($page > 1)
		{
			// produce previous link if not on the 1st page
			$get['page'] = $page - 1; // set the page value
			$qs = http_build_query($get,'','&amp;'); // build the qs
			$pagination.= "<a href='?$qs'>previous </a>";
		} else {
			// no previous link if on the 1st page
			$pagination.= "<span class='disabled'>previous </span>";
		}
		
		// produce links near the start/end
		$start_end = [];
		if($page < $start_end_links - $adjacents)
		{
			$start_end = range(1,$start_end_links);
		}
		if($lastpage - $page < $start_end_links - $adjacents)
		{
			$start_end = range($lastpage - $start_end_links + 1,$lastpage);
		}
		
		// produce the initial/final links
		$initial_final = [1,2,$lastpage-1,$lastpage];

		// produce range of links around the current page
		$adj = range($page-$adjacents,$page+$adjacents);
		
		// combine the links
		$arr = array_merge($start_end,$initial_final,$adj);
		
		// remember the last output - used to output the ... only once.
		$last_out = '';
		
		// loop over the range of pages
		foreach(range(1,$lastpage) as $x)
		{
			// should there be a link
			if(in_array($x,$arr))
			{
				// yes, remember the output and add the link to the output
				$last_out = $x;
				// is the current page the same as the link being produced
				if($page == $x)
				{
					// yes, just output the page number
					$pagination .= "<span class='current'>$x </span>";
				} else {
					// no, produce a link
					$get['page'] = "$x"; // set the page value
					$qs = http_build_query($get,'','&amp;'); // build the qs 
					$pagination.= "<a href='?$qs'>$x </a>";
				}
			} else {
				// the link is not in the array, output ... once
				if($last_out != '...')
				{
					$pagination .= '...';
					$last_out = '...';
				}
			}
		}
		
		// next link code
		if ($page < $lastpage) // this originally compared against the $counter - 1 and probably worked for all cases, but it should actually test against the $lastpage
		{
			// produce next link if not on the last page
			$get['page'] = $page + 1; // set the page value
			$qs = http_build_query($get,'','&amp;'); // build the qs 
			$pagination.= "<a href='?$qs'>next </a>";
		} else {
			// no next link if on the last page
			$pagination.= "<span class='disabled'>next </span>";
		}
		$pagination.= "</div>\n"; // this was originally in the wrong place above and only closed the div when on the last page
	}
}
?>
Sponsor our Newsletter | Privacy Policy | Terms of Service