Search the Community

I currently have a fulltext search that works. It matches against a item title column in mysql database. Now I would like to include another column to match against. How can that be done properly? Here's my code. Where it says MATCH, I would like to include the type_1 column as well; like this(type_1.type_1_name). $get_records = $db->prepare("SELECT items.*, type_1.* FROM items LEFT JOIN type_1 ON items.type_1 = type_1.type_1_id WHERE MATCH(items.item_title) AGAINST('$search_query' IN BOOLEAN MODE) ORDER BY items.item_id DESC LIMIT {$limit} OFFSET ".$offset); $get_records->execute(); $result_records = $get_records->fetchAll(PDO::FETCH_ASSOC); if(count($result_records) > 0){ foreach($result_records as $row) { // get results } }

I present videos on my site. The videos (their titles, descriptions, etc.) are stored in a MySQL database. I want visitors to be able to search my database for videos. My MySQL version is 5.5.40-36.1. The storage engine of my tables is INNODB. The two columns I want searched are: title (varchar; unique index) description (text; no index) According to my research, the best way to allow users to search my database would be to use FULLTEXT, but my INNODB tables are incompatible. FULLTEXT is available for INNODB in MySQL 5.6.4 and above. I would rather not use LIKE '%...%', and I would rather not use a third party solution such as Sphinx because my database will never grow particularly large (I doubt it'll ever exceed 6,000–8,000 videos). What do you guys recommend? Is it okay to use LIKE with a database containing 6,000–8,000 items?

The issue I am having is that everytime I click the next button, it takes me to the index page. My question is, how do I set up a proper search results link in "href="?page=1" index.php <form action="search" method="GET"> <input type="search" name="search" placeholder="Find products, services ..."> <input type="submit" name="submit" value=""> </form> search.php if(isset($_GET['submit']) && !empty($_GET['search'])) { $value = escape(trim(Input::get('search'))); try { // Find out how many items are in the table $q = DB::getInstance()->query("SELECT * FROM records WHERE MATCH(title) AGAINST('$value' IN BOOLEAN MODE)"); $total = $q->count(); // How many items to list per page $limit = 10; // How many pages will there be $pages = ceil($total / $limit); // What page are we currently on? $page = min($pages, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, array( 'options' => array( 'default' => 1, 'min_range' => 1, ), ))); // Calculate the offset for the query $offset = ($page - 1) * $limit; // Some information to display to the user $start = $offset + 1; $end = min(($offset + $limit), $total); // Prepare the paged query $stmt = DB::getInstance()->query("SELECT users.*, records.* FROM records LEFT JOIN users ON records.user_id = users.user_id WHERE MATCH(title) AGAINST('$value' IN BOOLEAN MODE) ORDER BY relevance DESC LIMIT {$limit} OFFSET ".$offset); if($stmt->count()) { // Display the results foreach($stmt->results() as $row) { $date = escape($row->posted); $record_date = escape(Input::facebook_date_format($date)); $record_id = escape($row->record_id); $title = trim($row->title); $slug_title = $row->slug_title; $view_count = escape(intval(number_format($row->views + 1))); $username = escape($row->username); require 'snippets/record_widget.php'; } } else { ?><div class="message"><?php echo 'No records found.';?></div><?php } <?php // The "back" link $prevlink = ($page > 1) ? '<a href="?page=1" title="First page"><img src="images/left-page-arrow-end.png"/></a> <a href="?page=' . ($page - 1) . '" title="Previous page"><img src="images/left-page-arrow-start.png"/></a>' : '<span class="disabled"><img src="images/left-page-arrow-end-disabled.png"/></span> <span class="disabled"><img src="images/left-page-arrow-start-disabled.png"/></span>'; // The "forward" link $nextlink = ($page < $pages) ? '<a href="?page=' . ($page + 1) . '" title="Next page"><img src="images/right-page-arrow-start.png"/></a> <a href="?page=' . $pages . '" title="Last page"><img src="images/right-page-arrow-end.png"/></a>' : '<span class="disabled"><img src="images/right-page-arrow-start-disabled.png"/></span> <span class="disabled"><img src="images/right-page-arrow-end-disabled.png"/></a></span>'; // Display the paging information ?> <div class="pages"> <span class="pages-arrows"><?php echo $prevlink; ?></span> <span class="pages-numbers"><?php echo $page; ?> of <?php echo $pages; ?></span> <span class="pages-arrows"><?php echo $nextlink; ?></span> </div> } catch (Exception $e) { echo '<p>', $e->getMessage(), '</p>'; } } else { Redirect::to('index'); }

I have a fulltext search setup. The issue I have is that I have to add it on every page and do an if statement. The search box is shown fixed position at the top of the website. I was wondering if there is a way I can set it up so that no matter what page I am on, it'll do the search without modifying the selected page? For eg. <html> <head> <title>home page</title> </head> <body> <?php if(isset($_GET['search'])) { echo 'search.php'; } else { // echo rest of the page content. } ?> </body> </html>

Is this the correct format to doing a MySQL full text search using PDO? I want to search about 4 tables for the same keyword and not sure of the syntax $count = "SELECT COUNT(*) FROM (SELECT t.boardid, t.topicid, 'messages' as mytable, topicname as title, message as content, MATCH(topicname, message) AGAINST(':keywords*' IN BOOLEAN MODE) as score FROM topics t INNER JOIN messages m ON t.topicid=m.topicid WHERE MATCH(topicname, message) AGAINST(':keywords*' IN BOOLEAN MODE) UNION ALL SELECT id,'', 'news' as mytable,title, content, MATCH(title, content) AGAINST(':keywords*' IN BOOLEAN MODE) as score FROM news WHERE MATCH(title, content) AGAINST(':keywords*' IN BOOLEAN MODE) UNION ALL SELECT id,'', 'events' as mytable,title, content, MATCH(title, content) AGAINST(':keywords*' IN BOOLEAN MODE) as score FROM events WHERE MATCH(title, content) AGAINST(':keywords*' IN BOOLEAN MODE) UNION ALL SELECT id,'', 'blogs' as mytable,title, content, MATCH(title, content) AGAINST(':keywords*' IN BOOLEAN MODE) as score FROM blogs WHERE MATCH(title, content) AGAINST(':keywords*' IN BOOLEAN MODE)) a GROUP BY title"; $keywords = strtolower($_SESSION['keywords']); $sth = $dbh->prepare($count); $sth->bindValue(':keywords', $keywords, PDO::PARAM_STR); $sth->execute();