query mySQL and sort

[flash gordon]

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

[per1os]

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.

[flash gordon]

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

[flash gordon]

before everyone goes home for the day

*bump*

[hitman6003]

http://www.phpfreaks.com/tutorials/129/0.php

[flash gordon]

Thanks for the link, dude!

 

I'll read over it and post any questions I have.

[flash gordon]

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.');

[hitman6003]

Look at this page of the tutorial:

 

http://www.phpfreaks.com/tutorials/129/2.php

 

specifically the part about creating the full text index............

[flash gordon]

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.

[Lytheum]

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.

[flash gordon]

Thanks bud!

 

Do you know if there is a phpMyAdmin GUI equivalent for that sql query?

[Lytheum]

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..

[flash gordon]

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>

 

 

[flash gordon]

Hmm....it only works as expected in Boolean mode,

 

but it works. WHOO HOO!

[flash gordon]

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

[Lytheum]

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!

[flash gordon]

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?

[flash gordon]

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?

[flash gordon]

one final desperate plea....