Hi
I am trying to add my new query to my working code to make a new search form. Initially I had two tables in my database so the query involved joining two tables together.
Now I have a fulltext table in my database and I do not know which parts of the code I should remove and add my new query.
My new query is
SELECT
id
, title
, description
, keywords
FROM video
WHERE MATCH(title,description,keywords) AGAINST(? IN BOOLEAN MODE)
ORDER BY TITLE
I would be much appreciated if someone could help me regarding my problem, thank you.
Tony
<?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 } ?>