Jump to content

wmaster216

New Members
 View Profile  See their activity

  • Posts

    5

  • Joined

  • Last visited

 Content Type 

Posts posted by wmaster216

    Help With PHP, Search, MySQLi, + Pagination

    in PHP Coding Help

    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.

    Help With PHP, Search, MySQLi, + Pagination

    in PHP Coding Help

    Posted · Edited by Psycho

    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?

×
×
  • 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.