I'm creating a site for searching for part numbers. The DB I have has 3 columns with data in: part_number part_name part_description I can do searches by part number, but I'm not sure on how to search generically based on the strings input in a search field. Basically I want people to be able to put in, all of the part number, part of the part number and/or part of the name or description, and it either bring back an exact match, or a list of partial matches. In much the same way a search engine would. For example someone could enter "stereo fascia" and it bring back results with stereo fascia at the top, and lesser queries for "fascia" and "stereo". I've been trying to get full text indexing working. I have an index called fulltext, that i'm trying to query to get relevant lines. PHP: <? include("./config/config.inc.php"); if (isset($_POST['search'])){ $search_part = $_POST['search']; } else { header("Location: http://vissvrav01/wiki/ffoc/"); } $mysqli = new mysqli($db_host, $db_user, $db_password, $db_name); if (mysqli_connect_errno()) { printf("Connect failed: %s\n", mysqli_connect_error()); exit(); } $sql = "SELECT part_number, part_name, part_desc FROM ffoc_partitem WHERE MATCH(part_number, part_name, part_desc) AGAINST('$search_part' IN BOOLEAN MODE) ORDER BY MATCH(part_number, part_name, part_desc) AGAINST('\"$search_part\"' IN BOOLEAN MODE)"; print "<br />"; print $sql . "<br />"; if ($result = $mysqli->query($sql)) { echo $result . "<br />"; echo $result->num_rows; if ($result->num_rows > 0) { print "<table id=\"part_table\">"; while ($row == $result->fetch_object) { print "<tr><td>" . $row->part_number . "</td><td>" . $row->part_name . "</td><td>" . $row->part_category . "</td><td>" . $row->part_desc . "</td></tr>"; } $result->close(); print "</table>"; } else { print "<p> No Results Found </p>"; } } else { printf("There has been an error from MySQL: %s",$mysqli->error); } $mysqli->close(); ?> It seems to get stuck at the while loop, and I dont know why. Anyone any ideas? Joel
PHP: while ($row == $result->fetch_object) { To: PHP: while ($row = $result->fetch_object) { Not really looked at what else is going on, so it might still not work.
I noticed that bit after. I think my issue is with the FULLTEXT index of the database. here is the SQL, it seems that its not actually returning anything, even if I do a query via the command line SELECT * FROM ffoc_partitem WHERE MATCH(part_number, part_name, part_desc) AGAINST ('Test'); 0 rows returned? Code: - phpMyAdmin SQL Dump -- version 2.9.0.1 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Feb 01, 2007 at 02:49 PM -- Server version: 5.0.24 -- PHP Version: 5.1.6 -- -- Database: `ffoc_part` -- -- -------------------------------------------------------- -- -- Table structure for table `ffoc_partitem` -- CREATE TABLE `ffoc_partitem` ( `id` int(10) unsigned NOT NULL auto_increment, `part_number` varchar(45) collate latin1_general_ci NOT NULL, `part_name` varchar(45) collate latin1_general_ci NOT NULL, `part_desc` varchar(45) collate latin1_general_ci NOT NULL, `part_category` varchar(45) collate latin1_general_ci NOT NULL, PRIMARY KEY (`id`), FULLTEXT KEY `FULLTEXT` (`part_number`,`part_name`,`part_desc`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=3 ; -- -- Dumping data for table `ffoc_partitem` -- INSERT INTO `ffoc_partitem` VALUES (1, '100000-1', 'Test Part1', 'Test part, fits on head', 'Random'); INSERT INTO `ffoc_partitem` VALUES (2, '1999202', 'test', 'test', 'test');
There's way too many instances of 'test' in that data for it to actually return any matches - read up on fulltext indexes a bit You're doing absolutely nothing wrong - you just need better test data to run queries against. I just threw this into MySQL: Code: CREATE TABLE `ffoc_partitem` ( `id` int(10) unsigned NOT NULL auto_increment, `part_number` varchar(45) collate latin1_general_ci NOT NULL, `part_name` varchar(45) collate latin1_general_ci NOT NULL, `part_desc` varchar(45) collate latin1_general_ci NOT NULL, `part_category` varchar(45) collate latin1_general_ci NOT NULL, PRIMARY KEY (`id`), FULLTEXT KEY `FULLTEXT` (`part_number`,`part_name`,`part_desc`) ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=5 ; -- -- Dumping data for table `ffoc_partitem` -- INSERT INTO `ffoc_partitem` VALUES (1, '100000-1', 'library terminal trusted', 'ourbugs rails creek headphones', 'keyboard monitor dell awesome'); INSERT INTO `ffoc_partitem` VALUES (2, '1999202', 'test', 'export operations', 'database ebay uknova'); INSERT INTO `ffoc_partitem` VALUES (3, '3123123', 'forums', 'foo', 'installing gentoo penguins ferrari'); INSERT INTO `ffoc_partitem` VALUES (4, '123123123', 'framework documentation berries', 'windows vista password', 'beryllium helium trees green porsche'); and ran the query you posted and it returned a match on the second row http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html You should also note that fulltext does not do sub-word matching by default. for example, searching for 'est' will not return matches with 'test', unless you use fulltext in boolean mode and supply the keyword '*est'. For easy sub-word matches, you need to use LIKE - but that has a whole bunch of downsides that come with it.
Fulltext is a bitch while you're testing. Adding to what RTT said, make sure you have at least 10 rows that are really different to each other in your test table - it seems to not like having too few items to play with.
