flash gordon Posted March 27, 2007 Share Posted March 27, 2007 Hello, I need some help with how to query a mySQL db of where a field contains textual values. But I also need to sort the results of which record had the most matches. Can anyone give me a little help or pointers? A step in the right direction should get me going and from there i can read the manual. Cheers Quote Link to comment Share on other sites More sharing options...
per1os Posted March 27, 2007 Share Posted March 27, 2007 Can you provide us with maybe the db structure at least? With that we can determine the data type sand exactly what you want cause right now this can be interpreted many different ways. IE: SELECT SUM(col) FROM tbl_name ORDER BY col will return the sum of a numeric column ordered from lowest to highest. Quote Link to comment Share on other sites More sharing options...
flash gordon Posted March 27, 2007 Author Share Posted March 27, 2007 Howdy and thanks for the reply I actually haven't set the db up yet, but I was thinking that there would be a field called "keywords" that the data type would be "text". So key words would look like this: #1 tom mike jim john matt .... #2 matt jenna lorie marie #3 mike foo bar soda and I would want to search for tom & mike. And therefore the output should be this: #1 tom mike jim john matt #3 mike foo bar soda Does that help any? Cheers Quote Link to comment Share on other sites More sharing options...
flash gordon Posted March 27, 2007 Author Share Posted March 27, 2007 before everyone goes home for the day *bump* Quote Link to comment Share on other sites More sharing options...
hitman6003 Posted March 28, 2007 Share Posted March 28, 2007 http://www.phpfreaks.com/tutorials/129/0.php Quote Link to comment Share on other sites More sharing options...
flash gordon Posted March 28, 2007 Author Share Posted March 28, 2007 Thanks for the link, dude! I'll read over it and post any questions I have. Quote Link to comment Share on other sites More sharing options...
flash gordon Posted March 28, 2007 Author Share Posted March 28, 2007 I am getting errors using that code, plus I don't really see how it will help me. Here is the error message anytime I use "normal" mode: <b>Can't find FULLTEXT index matching the column list</b> Here is the php: // Create the navigation switch $cmd = (isset($_GET['cmd']) ? $_GET['cmd'] : ''); switch($cmd) { default: echo '<h1>Search Database!</h1>'; searchForm(); break; case "search": searchForm(); echo '<h3>Search Results:</h3><br />'; $searchstring = mysql_escape_string($_GET['words']); switch($_GET['mode']) { case "normal": $sql = "SELECT mytable_id, mytable_title, mytable_caption, mytable_dts, MATCH(mytable_title, mytable_caption, mytable_full_body) AGAINST ('$searchstring') AS score FROM mytable WHERE MATCH(mytable_title, mytable_caption, mytable_full_body) AGAINST ('$searchstring') ORDER BY score DESC"; break; case "boolean": $sql = "SELECT mytable_id, mytable_title, mytable_caption, mytable_dts, MATCH(mytable_title, mytable_caption, mytable_full_body) AGAINST ('$searchstring' IN BOOLEAN MODE) AS score FROM mytable WHERE MATCH(mytable_title, mytable_caption, mytable_full_body) AGAINST ('$searchstring' IN BOOLEAN MODE) ORDER BY score DESC"; break; } // echo $sql; $result = mysql_query($sql) or die (mysql_error()); while($row = mysql_fetch_object($result)) { echo '<strong>Title: '.stripslashes(htmlspecialchars($row->mytable_title)).'</strong><br />'; echo 'Score:'. number_format($row->score, 1).' Date: '.date('m/d/y', $row->mytable_dts).'<br />'; echo '<p>'.stripslashes(htmlspecialchars($row->mytable_caption)).'</p>'; echo '<hr size="1" />'; } break; } And here is the database structure: -- phpMyAdmin SQL Dump -- version 2.9.1.1 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Mar 28, 2007 at 01:53 PM -- Server version: 5.0.27 -- PHP Version: 5.2.0 -- -- Database: `phpfreaks_search` -- -- -------------------------------------------------------- -- -- Table structure for table `mytable` -- CREATE TABLE `mytable` ( `mytable_id` int(25) NOT NULL auto_increment, `mytable_title` varchar(100) NOT NULL default '', `mytable_dts` varchar(25) NOT NULL default '', `mytable_caption` tinytext NOT NULL, `mytable_full_body` text NOT NULL, PRIMARY KEY (`mytable_id`), FULLTEXT KEY `mytable_caption` (`mytable_caption`), FULLTEXT KEY `mytable_full_body` (`mytable_full_body`), FULLTEXT KEY `mytable_title` (`mytable_title`), FULLTEXT KEY `mytable_dts` (`mytable_dts`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Testing Full Text Search Functionality.' AUTO_INCREMENT=6 ; -- -- Dumping data for table `mytable` -- INSERT INTO `mytable` VALUES (1, 'Mozilla Firefox is Cool!', '1111813200', 'Mozilla Firefox is the best web browser in the world!', 'Some of the features of Firefox include:\r\nPopup Blocking\r\n Stop annoying popup ads in their tracks with Firefox''s built in popup blocker.\r\nTabbed Browsing\r\n View more than one web page in a single window with this time saving feature. Open links in the background so that they''re ready for viewing when you''re ready to read them. Find out more...\r\nPrivacy and Security\r\n\r\nBuilt with your security in mind, Firefox keeps your computer safe from malicious spyware by not loading harmful ActiveX controls. A comprehensive set of privacy tools keep your online activity your business. \r\nSmarter Search\r\n\r\nGoogle Search is built right into the toolbar, and there is a plethora of other search tools including Smart Keywords (type "dict <word>" in the Location bar), and the new Find bar (which finds text as you type without covering up anything).\r\n\r\nLive Bookmarks\r\n\r\nRSS integration lets you read the latest news headlines and read updates to your favorite sites that are syndicated. Find out more...\r\n\r\nHassle-Free Downloading\r\n\r\nFiles you download are automatically saved to your Desktop so they''re easy to find. Fewer prompts mean files download quicker.\r\nFits Like a Glove\r\n\r\nSimple and intuitive, yet fully featured, Firefox has all the functions you''re used to - Bookmarks, History, Full Screen, Text Zooming to make pages with small text easier to read, etc.\r\nS, M, L or XL?It''s Your Choice\r\n Firefox is the most customizable browser on the planet. Customize your toolbars to add additional buttons, install new Extensions that add new features, add new Themes to browse with style, and use the adaptive search system to allow you to search an infinite number of engines. Firefox is as big or small as you want.\r\nSetup''s a Snap\r\n At only 4.7MB (Windows), Firefox takes just a few minutes to download over a slow connection and seconds over a fast connection. The installer gets you set up quickly, and the new Easy Transition system imports all of your settings - Favorites, passwords and other data from Internet Explorer and other browsers - so you can start surfing right away.\r\nA Developer''s Best Friend\r\nFirefox comes with a standard set of developer tools including a powerful JavaScript and CSS error/warning console, and an optional Document Inspector that gives detailed insight about your pages.'); INSERT INTO `mytable` VALUES (2, 'PHP: PHP Hypertext Processer', '1111813201', 'PHP is a widely-used general-purpose scripting language that is especially suited for Web development and can be embedded into HTML.', 'PHP is the greatest web developing language in the world!'); INSERT INTO `mytable` VALUES (3, 'MySQL Database Server', '1111813200', 'MySQL is the greatest database server int he world!', 'The MySQL database server is the world''s most popular open source database. Over six million installations use MySQL to power high-volume Web sites and other critical business systems ? including industry-leaders like The Associated Press, Yahoo, NASA, Sabre Holdings and Suzuki.'); INSERT INTO `mytable` VALUES (4, 'Google.com', '1111813200', 'The most powerful search engine in the world!', 'Google''s mission is to organize the world''s information and make it universally accessible and useful.\r\n\r\nAs a first step to fulfilling that mission, Google''s founders Larry Page and Sergey Brin developed a new approach to online search that took root in a Stanford University dorm room and quickly spread to information seekers around the globe. Google is now widely recognized as the world''s largest search engine -- an easy-to-use free service that usually returns relevant results in a fraction of a second.\r\n\r\nWhen you visit www.google.com or one of the dozens of other Google domains, you''ll be able to find information in many different languages; check stock quotes, maps, and news headlines; lookup phonebook listings for every city in the United States; search more than one billion images and peruse the world''s largest archive of Usenet messages -- more than 845 million posts dating back to 1981.\r\n\r\nWe also provide ways to access all this information without making a special trip to the Google homepage. The Google Toolbar enables you to conduct a Google search from anywhere on the web, while the Google Deskbar (beta) puts a Google search box in the Windows taskbar so you can search from any application you''re using, without opening a browser. And for those times when you''re away from your PC altogether, Google can be used from a number of wireless platforms including WAP and i-mode phones.'); INSERT INTO `mytable` VALUES (5, 'MSN Search Engine', '1111813200', 'Google''s worse fear.', 'Welcome to the New MSN Search! Searching the web isn''t just about providing a long list of web links but connecting you to the answers and information you''re looking for. To help with this, the new MSN Search includes a new search engine, index and crawler; all built from the ground up on Microsoft technology. With this new service, you''ll be able to find what you''re looking for--faster. From this page you can find out how to use some of our advanced features, how our engine works, or how to submit a site. If you have an opinion please let us know.'); Quote Link to comment Share on other sites More sharing options...
hitman6003 Posted March 29, 2007 Share Posted March 29, 2007 Look at this page of the tutorial: http://www.phpfreaks.com/tutorials/129/2.php specifically the part about creating the full text index............ Quote Link to comment Share on other sites More sharing options...
flash gordon Posted March 29, 2007 Author Share Posted March 29, 2007 thanks for the tip, but can you throw me a bone??? (a hint) I got it to work but I don't know the difference was/is. Quote Link to comment Share on other sites More sharing options...
Lytheum Posted March 29, 2007 Share Posted March 29, 2007 Basicly run this query on the table that you want, modifying the column names to your liking: CREATE FULLTEXT INDEX full_index ON mytable( mytable_title , mytable_caption , mytable_full_body ); You should only use the columns that you want to be searched. Quote Link to comment Share on other sites More sharing options...
flash gordon Posted March 29, 2007 Author Share Posted March 29, 2007 Thanks bud! Do you know if there is a phpMyAdmin GUI equivalent for that sql query? Quote Link to comment Share on other sites More sharing options...
Lytheum Posted March 29, 2007 Share Posted March 29, 2007 Which phpMyAdmin version are you using? I'm using 2.9.1.1 and when my table is selected on the left, it is the third link on the top menu named 'SQL'. Then, enter the query into the area where it says "Run SQL query/queries on database". Hope that helps.. Quote Link to comment Share on other sites More sharing options...
flash gordon Posted March 29, 2007 Author Share Posted March 29, 2007 Yea I see that, I was just hoping to click a button rather than have to memorize an SQL command that I'll maybe use 1 more time in 5 years. But I got a new problem....it does not search correctly. It doesn't even work. When I search for the word "one" i expect ids 1, 2, 3, 4 to all show. Instead I get nothing. Thinking this could have been limited to word length, when I search for "four", I still get no output. There are no error messages. Maybe the are "noise words"....hmm...no i don't think so because the same principle does not work with people's names. -- phpMyAdmin SQL Dump -- version 2.9.1.1 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Mar 28, 2007 at 08:04 PM -- Server version: 5.0.27 -- PHP Version: 5.2.0 -- -- Database: `phpfreaks_search` -- -- -------------------------------------------------------- -- -- Table structure for table `mytable` -- CREATE TABLE `mytable` ( `mytable_id` int(25) NOT NULL auto_increment, `mytable_title` varchar(100) NOT NULL default '', `mytable_caption` tinytext NOT NULL, `mytable_full_body` text NOT NULL, PRIMARY KEY (`mytable_id`), FULLTEXT KEY `full_index` (`mytable_title`,`mytable_caption`,`mytable_full_body`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Testing Full Text Search Functionality.' AUTO_INCREMENT=6 ; -- -- Dumping data for table `mytable` -- INSERT INTO `mytable` VALUES (1, 'Title4', 'Four', 'Four Three Two One'); INSERT INTO `mytable` VALUES (2, 'Title3', 'Three', 'Three Two One'); INSERT INTO `mytable` VALUES (3, 'Title2', 'Two', 'Two One'); INSERT INTO `mytable` VALUES (4, 'Title1', 'One', 'One'); <html> <head><title>Search</title></head> <body> <?php $username = "root"; $password = ""; // Full-Text Search Example // Conect to the database. //$cnx = mysql_connect('localhost', 'phpfreaks', 'phpfreaks') or die ("Could not connect"); $cnx = mysql_connect('localhost', $username, $password) or die ("Could not connect"); mysql_select_db('phpfreaks_search', $cnx) or die (mysql_error()); // Create the search function: function searchForm() { // Re-usable form // variable setup for the form. $searchwords = (isset($_GET['words']) ? htmlspecialchars(stripslashes($_REQUEST['words'])) : ''); $normal = (($_GET['mode'] == 'normal') ? ' selected="selected"' : '' ); $boolean = (($_GET['mode'] == 'boolean') ? ' selected="selected"' : '' ); echo '<form method="get" action="'.$_SERVER['PHP_SELF'].'">'; echo '<input type="hidden" name="cmd" value="search" />'; echo 'Search for: <input type="text" name="words" value="'.$searchwords.'" /> '; echo 'Mode: '; echo '<select name="mode">'; echo '<option value="normal"'.$normal.'>Normal</option>'; echo '<option value="boolean"'.$boolean.'>Boolean</option>'; echo '</select> '; echo '<input type="submit" value="Search" />'; echo '</form>'; } // Create the navigation switch $cmd = (isset($_GET['cmd']) ? $_GET['cmd'] : ''); switch($cmd) { default: echo '<h1>Search Database!</h1>'; searchForm(); break; case "search": searchForm(); echo '<h3>Search Results:</h3><br />'; $searchstring = mysql_escape_string($_GET['words']); switch($_GET['mode']) { case "normal": $sql = "SELECT mytable_id, mytable_title, mytable_caption, MATCH(mytable_title, mytable_caption, mytable_full_body) AGAINST ('$searchstring') AS score FROM mytable WHERE MATCH(mytable_title, mytable_caption, mytable_full_body) AGAINST ('$searchstring') ORDER BY score DESC"; break; case "boolean": $sql = "SELECT mytable_id, mytable_title, mytable_caption, MATCH(mytable_title, mytable_caption, mytable_full_body) AGAINST ('$searchstring' IN BOOLEAN MODE) AS score FROM mytable WHERE MATCH(mytable_title, mytable_caption, mytable_full_body) AGAINST ('$searchstring' IN BOOLEAN MODE) ORDER BY score DESC"; break; } // echo $sql; $result = mysql_query($sql) or die (mysql_error()); while($row = mysql_fetch_object($result)) { echo '<strong>Title: '.stripslashes(htmlspecialchars($row->mytable_title)).'</strong><br />'; echo 'Score:'. number_format($row->score, 1).' Date: '.date('m/d/y', $row->mytable_dts).'<br />'; echo '<p>'.stripslashes(htmlspecialchars($row->mytable_caption)).'</p>'; echo '<hr size="1" />'; } break; } ?> </body> </html> Quote Link to comment Share on other sites More sharing options...
flash gordon Posted March 29, 2007 Author Share Posted March 29, 2007 Hmm....it only works as expected in Boolean mode, but it works. WHOO HOO! Quote Link to comment Share on other sites More sharing options...
flash gordon Posted March 29, 2007 Author Share Posted March 29, 2007 Hey Guys, A huge thanks to everyone who help out. The scripts seems to be working good enough as long as I am in "BOOLEAN" mode, not looking for number-words, and each word is greater than 3 letters. I'm sure these limitations will cause some problems down the road, but for now I'm content. Cheers Quote Link to comment Share on other sites More sharing options...
Lytheum Posted March 29, 2007 Share Posted March 29, 2007 No problem, I tried. Maybe tomorrow I'll give the script a look and see what the problem is; If I do, I'll PM you the details. Good luck! Quote Link to comment Share on other sites More sharing options...
flash gordon Posted March 29, 2007 Author Share Posted March 29, 2007 Actually this isn't going to work. One of the words that I <b>have to</b> search for is going to be the word "sad". With this configuration and since I don't have system level access, do I have to take a different approach or is there a way to force the inclusion of words less than 4 chars? Quote Link to comment Share on other sites More sharing options...
flash gordon Posted March 29, 2007 Author Share Posted March 29, 2007 So anything thoughts? On a shared host that I don't have system level access to, is there any way to force the inclusion of 3 letter words using the FULLTEXT approach? Or do I have to go about this differently? Quote Link to comment Share on other sites More sharing options...
flash gordon Posted March 30, 2007 Author Share Posted March 30, 2007 one final desperate plea.... Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.