Thank you Psycho for your reply. What I am trying to do is join two pieces of code together to make a search form. Both Code A and Code B work but what I would like to do is to add Code B to Code A. I have tried to remove parts of Code A and insert the code from Code B which is where I am at in the original post above. No matter what I search I get "No matching records".
CODE A
<?php
define("PERPAGE", 15); // number of records on each page
/**************************************************************************************
* function to output page selection buttons
*
* @param int $total total records
* @param int $page current page number
* @return string selection buttons html
*/
function page_selector($total, $page)
{
if ($total==0) {
return '';
}
$kPages = ceil($total/PERPAGE);
$filler = ' · · · ';
$lim1 = max(1, $page-2);
$lim2 = min($kPages, $page+3);
$p = $page==1 ? 1 : $page - 1;
$n = $page== $kPages ? $kPages : $page + 1;;
$out = "$kPages page" . ($kPages==1 ? '' : 's') . "  ";
if ($kPages==1) {
return $out;
}
$out .= ($page > 1) ? "<div class='pagipage' data-pn='$p'>Prev</div> " : '';
if ($page > 4) {
$out .= "<div class='pagipage' data-pn='1'>1</div> $filler";
}
elseif ($page==4) {
$out .= "<div class='pagipage' data-pn='1'>1</div>";
}
for ($i=$lim1; $i<=$lim2; $i++) {
if ($page==$i)
$out .= "<div class='pagicurrent'>$i</div>";
else
$out .= "<div class='pagipage' data-pn='$i'>$i</div>";
}
if ($page < $kPages-3) {
$out .= "$filler <div class='pagipage' data-pn='$kPages'>$kPages</div>";
}
$out .= $page < $kPages ? " <div class='pagipage' data-pn='$n'>Next</div>" : '';
return $out;
}
/***********************************************
** SEARCH FOR MATCHING PRODUCTS
************************************************/
$showResults = 0;
$search = '';
if (isset($_GET['tag'])) {
$showResults = 1;
$search = $_GET['tag'];
$srch = array_unique(explode(' ', trim($_GET['tag'])));
foreach ($srch as $t) {
$repl[] = "<span class='hilite'>$t</span>";
$placeholders[] = '?';
$params[] = $t;
}
$params[] = count($srch);
//
// FINDTOTAL RECORDS IN SEARCH RESULTS
//
$res = $db->prepare("SELECT COUNT(*) as tot
FROM (
SELECT id
FROM (
SELECT v.id
, v.title
, GROUP_CONCAT(tag SEPARATOR ' ') as alltags
FROM product v
JOIN
product_tag t ON v.id = t.product_id
GROUP BY v.id
) v
JOIN
product_tag t ON v.id = t.product_id
WHERE tag IN (" . join(",", $placeholders) . ")
GROUP BY id
HAVING COUNT(t.tag) = ?
) found;
");
$res->execute($params);
$total = $res->fetchColumn();
$page = $_GET['page'] ?? 1;
$params[] = ($page-1)*PERPAGE; // append parameters offset
$params[] = PERPAGE; // and number of records for limit clause
//
// GET A PAGEFUL OF RECORDS
//
$sql = "SELECT
id
, title
, alltags
FROM (
SELECT v.id
, v.title
, GROUP_CONCAT(tag SEPARATOR ' ') as alltags
FROM product v
JOIN
product_tag t ON v.id = t.product_id
GROUP BY v.id
) v
JOIN
product_tag t ON v.id = t.product_id
WHERE tag IN (" . join(",", $placeholders) . ")
GROUP BY id
HAVING COUNT(t.tag) = ?
LIMIT ?,? ";
$stmt = $db->prepare($sql);
$stmt->execute($params);
if ($stmt->rowCount()==0) {
$results = "<h3>No matching records</h3>";
} else {
$results = "<tr><th>Product Id</th><th>Title</th><th>Tags</th><th>Edit</th></tr>\n";
foreach ($stmt as $rec) {
$alltags = str_replace($srch, $repl, $rec['alltags']);
$results .= "<tr><td>{$rec['id']}</td><td>{$rec['title']}</td><td>$alltags</td>
<td><a href='?action=edit&id={$rec['id']}'><img src='edit-icon.png' alt='edit'></a></td>
</tr>\n";
}
}
}
?>
<div id='title'>Product Search</div>
<form id='form1'>
<fieldset>
<legend>Search for tags</legend>
<input type='text' name='tag' size='50' value='<?=$search?>' placeholder='Search for...' >
<input type="hidden" name="page" id="page" value="1">
<input type="hidden" name="action" id="page" value="search">
<input type="submit" name="btnSub" value="Search">
</fieldset>
<div id='formfoot'></div>
</form>
<?php if ($showResults) { ?>
<div class="paginate_panel">
<?=page_selector($total, $page)?>
</div>
<table border='1'>
<?=$results?>
</table>
<?php } ?>
CODE B
$txt = $_GET['search'] ?? '';
$whereclause = '';
$search_values = [];
if (isset($_GET['search'])) {
$where = ['active <> 0'];
$search_values = [];
if ($_GET['search'] != '') {
$words = array_filter(explode(' ', $_GET['search']));
$badft = array_filter($words, function($v) { return strlen($v) <= 3;});
$goodft = array_filter($words, function($v) { return strlen($v) > 3;});
array_walk($goodft, function(&$v, $k) { // add the +'s to the search tags
$v = '+'.$v;
});
if ($goodft) {
$srch = join(' ', $goodft);
$where[] = "MATCH (title, description, keywords) AGAINST (? IN BOOLEAN MODE)";
$search_values[] = $srch;
}
foreach ($badft as $w) {
$w = trim($w, ', ');
$where[] = "((title LIKE ?) OR (description LIKE ?) OR (keywords LIKE ?))";
array_push($search_values, "%$w%", "%$w%", "%$w%");
}
}
if ($where) {
$whereclause = "WHERE " . join(' AND ', $where);
}
}
$count = $db->prepare("SELECT COUNT(*)
FROM television
$whereclause
");
$count->execute( $search_values );
$total_recs = $count->fetchColumn();
$page = isset($_GET['page']) ? $_GET['page'] : 1;
if (intval($page) == 0) $page = 1;
$offset = ($page - 1) * PERPAGE;
$limit = PERPAGE;
array_push($search_values, $offset, $limit); // add offset and limit to the search parameters
$stmt = $db->prepare("SELECT image
, title
, description
, url
, id
FROM television
$whereclause
ORDER BY upload DESC
LIMIT ?,?
");
$stmt->execute( $search_values );
$search_results = '';
if ($txt) {
foreach ($stmt as $row) {
$search_results .= '<li>
<a href="' . 'product.php?id=' . $row['id'] . '"><img src="' . $row['image'] . '" class="thumbnail" alt="' . $row['title'] . '"></a>
<div>
<h1><a href="' . 'product.php?id=' . $row['id'] . '">' . $row['title'] . '</a></h1>
<p>' . $row['description'] . '</p>
</div>
</li>' . PHP_EOL;
}
$search_msg = count($rows) > 0 ? "$total_recs products found" : "Please check your spelling or try a more generic search term";
}
else {
$total_recs = 0;
$search_msg = "Please enter your search text.";
}