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/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 ››</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?