Jump to content

Help With PHP, Search, MySQLi, + Pagination

wmaster216

By wmaster216
in PHP Coding Help

 Share

Recommended Posts

wmaster216 Newbie

wmaster216

Posted
Posted (edited)

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:

With “The Code That Works”, when I load index.php, I get ALL 30k+ rows displayed, even with the limits defined.

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/musicso1/karaoke.musicsoundlife.com/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/musicso1/karaoke.musicsoundlife.com/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/musicso1/karaoke.musicsoundlife.com/index.php on line 32
[31-Dec-2019 22:36:11 UTC] PHP Notice:  Undefined index: page in /home/musicso1/karaoke.musicsoundlife.com/index.php on line 37

Here’s the code that works: 

<?php
$localhost = "";
$username = "rsearch";
$password = "";
$dbname = "ke";

$con = new mysqli($localhost, $username, $password, $dbname);
if( $con->connect_error){
    die('Error: Connection' . $con->connect_error);
}

$sql = "SELECT * FROM `TABLE 1`";
if( isset($_GET['search']) ){
    $name = mysqli_real_escape_string($con, htmlspecialchars($_GET['search']));
    $sql = "SELECT * FROM `TABLE 1` 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="keywords" content="">
	<meta name="robots" content="index, follow">
	<meta name="language" content="English">
	<meta name="revisit-after" content="15 days">
	<meta name="author" content="">
	<meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no" />
	<link rel="stylesheet" href="main.css" />
	<link rel="stylesheet" href="bootstrap.min.css">
	<noscript><link rel="stylesheet" href="noscript.css" /></noscript>
	<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="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> Catalog</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 = 15;
    $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 1`");
	$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 1` 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 ››</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">
							<li>© SLE Inc.</li><li>Design: <a href="">SLE</a></li><li>License<a href="License.txt">.txt</a></li>
						</ul>
						< 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 = "";
$username = "rsearch";
$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="keywords" content="">
<meta name="robots" content="index, follow">

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

		
		<meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no" />
		<link rel="stylesheet" href="main.css" />
		<link rel="stylesheet" href="bootstrap.min.css">
		<noscript><link rel="stylesheet" href="noscript.css" /></noscript>
		<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++) {
				
				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="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 = 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>

			
					<div id="footer">
						<ul class="copyright">
							<li>© SLE Inc.</li><li>Design: <a href="">SLE</a></li><li>License<a href="License.txt">.txt</a></li>
						</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?

Edited by Psycho
Link to comment
https://forums.phpfreaks.com/topic/309772-help-with-php-search-mysqli-pagination/
Share on other sites
Barand Prolific Member

Barand

Posted
Posted
4 hours ago, wmaster216 said:

$query = "SELECT COUNT(*) as num FROM $tbl_name WHERE (`Title` LIKE '%$name%') OR (`Artist` LIKE '%$name%')";

              ###   this query needs executing !!!!!

$total_pages = mysqli_fetch_array(mysqli_query($query));

Merely defining a query string does not execute the query.

 

4 hours ago, wmaster216 said:

WHERE (`Title` LIKE '%$name%')

If $name is empty you have "LIKE '%%' " which will match every record in the table

wmaster216 Newbie

wmaster216

Posted
  • Author
Posted
10 hours ago, Barand said:

If $name is empty you have "LIKE '%%' " which will match every record in the table

$name is given by the input into the search box, I understand that if there is NO INPUT in the search box then name matches every record in the table, how do I fix that?  

I don't want it to show every record in the table when I load the page, that's the first fix, then I can add in pagination to search results etc.

Barand Prolific Member

Barand

Posted
Posted
1 minute ago, wmaster216 said:

how do I fix that?  

You check that it isn't empty before running the query. At present, if $_GET is not set, you run the query to fetch all data.

Get out of the habit of using "SELECT * ", specify the columns you need to retrieve.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.