I try to create search result page with pagination, the pagination works fine, but i have problem with multiple word search and mysqli_stmt::bind_param():
So, here's my url: http://localhost/pg/page.php?k=hello+buddy
And this is page.php code:
<?php
// Script and tutorial written by Adam Khoury @ developphp.com
// Line by line explanation : youtube.com/watch?v=T2QFNu_mivw
include 'koneksi.php';
// This first query is just to get the total count of rows
$param = '%'.$_GET['k'].'%';
$khusus = ''.$_GET['k'].'';
$z = explode(" ", $khusus);
$c = 'judul LIKE ?';
$d = ' OR judul LIKE ?';
$g = 's';
$v = 's';
$f = '%$z[0]%';
$o = '$z[';
$j = ']';
for ($i = 1; $i < count($z); ++$i) {
$x = $o . $i . $j;
$c = $c . ' ' . $d;
$f = $f . ', ' . $x;
$g = $g . '' . $v;
}
$stmt=$con->prepare('SELECT COUNT(id) FROM import WHERE '.$c.'');
$stmt->bind_param(''.$g.'', $f);
// Don't use bind_result()...
// execute your statement
$stmt->execute();
// Get result set into a MySQLi result resource
$result = $stmt->bind_result($id);
// array to hold all rows
$rows = array();
// All results bound to output vars
while ($stmt->fetch()) {
// Append an array containing your result vars onto the rowset array
$rows[] = array(
'id' => $id
);
}
$rows=$id;
// This is the number of results we want displayed per page
$page_rows = 10;
// This tells us the page number of our last page
$last = ceil($rows/$page_rows);
// This makes sure $last cannot be less than 1
if($last < 1){
$last = 1;
}
// Establish the $pagenum variable
$pagenum = 1;
// Get pagenum from URL vars if it is present, else it is = 1
if(isset($_GET['pn'])){
$pagenum = preg_replace('#[^0-9]#', '', $_GET['pn']);
}
// This makes sure the page number isn't below 1, or more than our $last page
if ($pagenum < 1) {
$pagenum = 1;
} else if ($pagenum > $last) {
$pagenum = $last;
}
$dynamicList = "";
$stmt = $con->prepare('SELECT id,judul,harga FROM import WHERE '.$c.' ORDER BY judul LIMIT ?,? ');
$begin= ($pagenum - 1) * $page_rows;
$end= $page_rows;
$stmt->bind_param(''.$g.'ii', $f, $begin, $end);
$stmt->execute();
/* store result */
$stmt->store_result();
/* get the row count */
$count = $stmt->num_rows;
if ($count >= 1) {
$stmt->bind_result($id, $judul, $harga);
// This shows the user what page they are on, and the total number of pages
$textline1 = "Products (<b>$rows</b>)";
$textline2 = "Page <b>$pagenum</b> of <b>$last</b>";
// Establish the $paginationCtrls variable
$paginationCtrls = '';
// If there is more than 1 page worth of results
if($last != 1){
/* First we check if we are on page one. If we are then we don't need a link to
the previous page or the first page so we do nothing. If we aren't then we
generate links to the first page, and to the previous page. */
if ($pagenum > 1) {
$previous = $pagenum - 1;
$paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?k='.$khusus.'&q='.$khususs.'&pn='.$previous.'">Previous</a> ';
// Render clickable number links that should appear on the left of the target page number
for($i = $pagenum-4; $i < $pagenum; $i++){
if($i > 0){
$paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?k='.$khusus.'&q='.$khususs.'&pn='.$i.'">'.$i.'</a> ';
}
}
}
// Render the target page number, but without it being a link
$paginationCtrls .= ''.$pagenum.' ';
// Render clickable number links that should appear on the right of the target page number
for($i = $pagenum+1; $i <= $last; $i++){
$paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?k='.$khusus.'&q='.$khususs.'&pn='.$i.'">'.$i.'</a> ';
if($i >= $pagenum+4){
break;
}
}
// This does the same as above, only checking if we are on the last page, and then generating the "Next"
if ($pagenum != $last) {
$next = $pagenum + 1;
$paginationCtrls .= ' <a href="'.$_SERVER['PHP_SELF'].'?k='.$khusus.'&q='.$khususs.'&pn='.$next.'">Next</a> ';
}
}
while ($stmt->fetch()) {
"$id, $judul, $harga,";
$dynamicList .= "
<li><div class='product'>
<a href='product.php?id=$id' class='info'>
<span class='holder'>
<img src='inventory_images/$id.jpg' alt='$judul' />
<span class='book-name'>$judul</span>
</a>
<a href='product.php?id=$id' class='buy-btn'>RM<span class='price'>$harga</span></a>
</div>
</li>
";
}
}
// Close your database connection
mysqli_close($con);
?>
<!DOCTYPE html>
<html>
<head>
<style type="text/css">
body{ font-family:"Trebuchet MS", Arial, Helvetica, sans-serif;}
div#pagination_controls{font-size:21px;}
div#pagination_controls > a{ color:#06F; }
div#pagination_controls > a:visited{ color:#06F; }
</style>
</head>
<body>
<div>
<h2><?php echo $textline1; ?> Paged</h2>
<p><?php echo $textline2; ?></p>
<p><?php echo $dynamicList; ?></p>
<div id="pagination_controls"><?php echo $paginationCtrls; ?></div>
</div>
</body>
</html>
The result in browser is like this:
Warning: mysqli_stmt::bind_param(): Number of elements in type definition string doesn't match number of bind variables in C:\xampp\htdocs\pg\page.php on line 23
Warning: mysqli_stmt::bind_param(): Number of elements in type definition string doesn't match number of bind variables in C:\xampp\htdocs\pg\page.php on line 68
Notice: Undefined variable: textline1 in C:\xampp\htdocs\pg\page.php on line 144 Paged
Notice: Undefined variable: textline2 in C:\xampp\htdocs\pg\page.php on line 145
Notice: Undefined variable: paginationCtrls in C:\xampp\htdocs\pg\page.php on line 147
If I change the url like this: http://localhost/pg/page.php?k=hello&q=buddy
And the code into this one:
<?php
// Script and tutorial written by Adam Khoury @ developphp.com
// Line by line explanation : youtube.com/watch?v=T2QFNu_mivw
include 'koneksi.php';
// This first query is just to get the total count of rows
$param = '%'.$_GET['k'].'%';
$khusus = ''.$_GET['k'].'';
$params = '%'.$_GET['q'].'%';
$khususs = ''.$_GET['q'].'';
$searchCondition = 'judul LIKE ? AND judul LIKE ? OR deskripsi LIKE ?';
$stmt=$con->prepare('SELECT COUNT(id) FROM import WHERE '.$searchCondition.'');
$stmt->bind_param('sss', $param, $params, $param);
it works good, so i think the problem is in my looping, and how i write %
