TonyR

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 = '&nbsp;&middot;&nbsp;&middot;&nbsp;&middot;&nbsp;'; $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') . " &emsp;"; if ($kPages==1) { return $out; } $out .= ($page > 1) ? "<div class='pagipage' data-pn='$p'>Prev</div>&ensp;" : ''; 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 ? "&ensp;<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."; }

Apologies to start this up again but I had some personal problems at home which meant I had to stop my coursework. My college had made arrangements for me to restart this after the Easter holidays but I felt I wanted to get back into this. I really have racked my brains trying to figure out the issue with the $params but to no avail. Any more help please?

Thank you Barand and mac_gyver for your replies. I guess I’ll go back to looking at the $params and not any other issues with the code.

Unfortunately my teacher would not fix the code for me so I am back. After doing some reading online I read that I cannot use placeholders for table names, column names or limit values. So LIMIT ?,? is not correct as well as the $param issue that you have mentioned. Is that correct?

My php knowledge is very limited (it is improving) but unfortunately this bug is too difficult for me. I wish I had seen your post this morning before I went to college. Then I could've asked my teacher for some help. I will tell him tomorrow morning that the issue is with the $params and hopefully he will help me. If not, I'm sure I will be back here. Thank you requinix.

Thank you requinix for the reply. Excuse my ignorance but do you mean this: <?php $host = 'localhost'; $username = 'root'; $password = ''; $database = 'tonyruttle'; $dsn = "mysql:dbname=$database; host=$host; charset=utf8"; $db = new pdo($dsn, $username, $password, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC ]); If you do then I'm still getting the same results.

Hi I am having a problem with my form output. No matter what I search I get "No matching records". Even if I am searching words that I know should return a result. Any help would be much appreciated <?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 = '&nbsp;&middot;&nbsp;&middot;&nbsp;&middot;&nbsp;'; $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') . " &emsp;"; if ($kPages==1) { return $out; } $out .= ($page > 1) ? "<div class='pagipage' data-pn='$p'>Prev</div>&ensp;" : ''; 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 ? "&ensp;<div class='pagipage' data-pn='$n'>Next</div>" : ''; return $out; } /*********************************************** ** SEARCH FOR MATCHING TVS ************************************************/ $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 television WHERE MATCH(title,description,keywords) AGAINST(? IN BOOLEAN MODE) "); $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 , description , keywords FROM television WHERE MATCH(title,description,keywords) AGAINST(? IN BOOLEAN MODE) ORDER BY TITLE 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>Description</th><th>Tags</th><th>Edit</th></tr>\n"; foreach ($stmt as $rec) { $alltags = str_replace($srch, $repl, $rec['keywords']); $results .= "<tr><td>{$rec['id']}</td><td>{$rec['title']}</td><td>{$rec['description']}</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'>Television 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 } ?>

I managed to get rid of that error. For anyone else who gets this error basically the MATCH clause should be in the same order as the fulltext index and include all the fulltext indexes.

I will definitely do what you recommended and remove some variables and run the query directly. Also I am interested in you saying that this is a SQL error. Does that mean the PHP code has no errors? I have been struggling with this code for around a week now so I'll be very pleased to know the code is clean.

When I do SHOW INDEX FROM television it comes back with PRIMARY id FT title FT description FT keywords

Thank you NotionCommotion for the reply. I have managed to connect to the database. I am now having a fatal error. I understand the error but just don't know how to fix it. The fulltext columns in my database are title, description and keywords. Uncaught PDOException: SQLSTATE[HY000]: General error: 1191 Can't find FULLTEXT index matching the column list on line 65 <?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 = '&nbsp;&middot;&nbsp;&middot;&nbsp;&middot;&nbsp;'; $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') . " &emsp;"; if ($kPages==1) { return $out; } $out .= ($page > 1) ? "<div class='pagipage' data-pn='$p'>Prev</div>&ensp;" : ''; 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 ? "&ensp;<div class='pagipage' data-pn='$n'>Next</div>" : ''; return $out; } /*********************************************** ** SEARCH FOR MATCHING TVS ************************************************/ $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 television p WHERE MATCH(title,keywords) AGAINST(? IN BOOLEAN MODE) "); $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 , keywords FROM television p WHERE MATCH(title,keywords) AGAINST(? IN BOOLEAN MODE) ORDER BY TITLE 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['keywords']); $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'>Television 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 } ?>

Hi I am struggling to connect to my database and was wondering if someone could please help me. I am not sure what I need to put on line 10 in this code <?php define('DB_HOST', 'localhost'); define('DB_USER', 'root'); define('DB_PASS', ''); define('DB_NAME', 'tonyruttle'); define("PERPAGE", 10); $mydb = (DB_HOST, DB_USER, DB_PASS, DB_NAME); /*********************************************** ** SEARCH FOR MATCHING PRODUCTS ************************************************/ $showResults = 0; $search = ''; if (isset($_GET['srch'])) { $search = $_GET['srch']; $showResults = 1; $srch = array_filter(array_unique(explode(' ', trim($_GET['srch'])))); array_walk($srch, function(&$v, $k) { // add the +'s to the search tags $v = '+'.$v; }); $tagparam = join(' ', $srch); // // FINDTOTAL RECORDS IN SEARCH RESULTS // $params[] = $tagparam; $res = $db->prepare("SELECT COUNT(*) as tot FROM television p WHERE MATCH(title,keywords) AGAINST(? IN BOOLEAN MODE) "); $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 , active FROM television p WHERE MATCH(title,keywords) AGAINST(? IN BOOLEAN MODE) ORDER BY TITLE 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>Active</th><th colspan='2'>Action</th></tr>\n"; foreach ($stmt as $rec) { $cls = $rec['active']==0 ? "class='inactive'" : ''; $results .= "<tr $cls><td>{$rec['id']}</td><td>{$rec['title']}</td><td class='ca'>{$rec['active']}</td> <td class='ca'><a href='?action=edit&id={$rec['id']}'><img src='images/edit-icon.png' alt='edit'></a></td> </tr>\n"; } } } ?> <div id='title'>Product List</div> <form id='form1' "> <fieldset> <legend>Search titles and tags</legend> <input type='text' name='srch' id='srch' size='50' value='<?=$search?>' placeholder='Search for...' > <input type="hidden" name="page" id="page" value="1"> <input type="hidden" name="action" 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, PERPAGE)?> </div> <table border='1'> <?=$results?> </table> <?php } ?>