Search - Strange results using LIKE or MATCH () AGAINST ()

[slyme]

Hi,
If I run the following mysql_query: SELECT * FROM products WHERE product_name LIKE '%SOM%' I get the following results:
SOM 1 - Coloured option
SOM 2 - Magnetic Roof Sign
SOM 4 - Magnetic Roof Sign - Centre L
SOM 6 - Magnetic Roof Sign - Curved Top
SOM 7 - Magnetic Roof Sign - Angled L
SOM 10 - Magnetic Roof Sign - Pyramid
SOM 12 (Top Hat) - Magnetic Roof Sign
SOM 1 - Magnetic Roof Sign
If I run this query: SELECT * FROM products WHERE MATCH (product_name) AGAINST ('SOM') I get no results. Surely I should be getting the same results from each in this case? If I use 'Magnetic Roof Sign' as a search term then I do get the results that I expect.

Can anyone give me a clue where to look please? Do I need to post any more code/detail?

Thanks,
Simon.

[smantscheff]

Post a full test case with table creation and data insertion code together with your presumably erroneous query.

[facarroll]

You could try going into phpmyadmin and posting this. "SELECT * FROM products WHERE MATCH(product_name) AGAINST ('SOM')".
The output there might make things clearer for you.

[slyme]

You could try going into phpmyadmin and posting this. "SELECT * FROM products WHERE MATCH(product_name) AGAINST ('SOM')".
The output there might make things clearer for you.

Brilliant idea ... why didn't I think of that? So off I went and did the above, fully expecting it to work therefore indicating that the problem would be somewhere in my code. Nope. Same results.

So ... here's the table:

CREATE TABLE IF NOT EXISTS `test` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(300) NOT NULL,
  `category` varchar(200) NOT NULL,
  `product_desc` varchar(300) DEFAULT NULL,
  `description_left` text,
  `product_note` varchar(200) DEFAULT NULL,
  `discount_note` varchar(200) DEFAULT NULL,
  `left_panel` text,
  `options_panel` text,
  `discount` tinyint(1) NOT NULL DEFAULT '0',
  `product_image` varchar(200) NOT NULL,
  `image_alt` varchar(200) DEFAULT NULL,
  `image_hyperlink` varchar(200) DEFAULT NULL,
  `hyperlink_text` varchar(200) DEFAULT NULL,
  `rank` int(11) NOT NULL DEFAULT '0',
  `lower_text` varchar(300) DEFAULT NULL,
  `extra_options` text,
  PRIMARY KEY (`product_id`),
  FULLTEXT KEY `category` (`category`),
  FULLTEXT KEY `product_name` (`product_name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=296 ;

Here's some data:

INSERT INTO `test` (`product_id`, `product_name`, `category`, `product_desc`, `description_left`, `product_note`, `discount_note`, `left_panel`, `options_panel`, `discount`, `product_image`, `image_alt`, `image_hyperlink`, `hyperlink_text`, `rank`, `lower_text`, `extra_options`) VALUES
(19, 'SOM 1 - Coloured option', 'School of Motoring', 'Available in Black, Red or Silver', 'Only available in som 1\r\nnon illuminated with graphics', 'For a small additional fee the SOM 1 roof sign can be spray painted\r\nprior to the application of your graphics', '', NULL, NULL, 0, 'images/assets/som-black.jpg', 'magnetic top box roof signs', 'images/assets/som/som01-roof-sign.pdf', 'click image for more details', 12, '', 'Black\r\nRed\r\nSilver'),
(224, 'SOM 2 - Magnetic Roof Sign', 'School of Motoring', 'School of Motoring displaying a legal size L', 'Standard - suits most cars\r\nSmall - Ford KA, Corsa 2009+, Micra\r\n\r\nSchool of Motoring\r\nDriving Instructors\r\nDisplays a legal size L\r\n', 'Illuminated version available.\r\n2 sizes Standard or Small', 'See Discounts above', NULL, NULL, 0, 'images/assets/roof-sign-som02.jpg', 'magnetic top box roof signs', 'images/assets/som/som02-roof-sign.pdf', 'click image for more details', 20, 'If lettering / graphics are required please email details or contact us', NULL),
(225, 'SOM 4 - Magnetic Roof Sign - Centre L', 'School of Motoring', 'School of Motoring displaying a legal size L', 'Standard size - suits most cars\r\n', 'Illuminated version available.\r\n', 'See Discounts above', NULL, NULL, 0, 'images/assets/roof-sign-som04.jpg', 'magnetic top box roof signs', 'images/assets/som/som04-roof-sign.pdf', 'click image for more details', 40, 'If lettering / graphics are required please email details or contact us', NULL),
(227, 'SOM 6 - Magnetic Roof Sign - Curved Top', 'School of Motoring', 'A great way to advertise any business', 'Standard size - suits most cars\r\n', 'Illuminated version available.\r\n', 'See Discounts above', NULL, NULL, 0, 'images/assets/roof-sign-som06.jpg', 'magnetic top box roof signs', 'images/assets/som/som06-roof-sign.pdf', 'click image for more details', 60, 'If lettering / graphics are required please email details or contact us', NULL),
(228, 'SOM 7 - Magnetic Roof Sign - Angled L', 'School of Motoring', 'School of Motoring with legal size L at an angle', 'Standard size - suits most cars\r\n', 'Illuminated version available.\r\n', 'See Discounts above', NULL, NULL, 0, 'images/assets/roof-sign-som07.jpg', 'magnetic top box roof signs', 'images/assets/som/som07-roof-sign.pdf', 'click image for more details', 70, 'If lettering / graphics are required please email details or contact us', NULL),
(229, 'SOM 10 - Magnetic Roof Sign - Pyramid', 'School of Motoring', '4 sided pyramid shape roof sign', '', '-', 'See Discounts above', NULL, NULL, 0, 'images/assets/roof-sign-som10.jpg', 'magnetic top box roof signs', 'images/assets/som/som10-pyramid-sign.pdf', 'click image for more details', 100, 'If lettering / graphics are required please email details or contact us', NULL),
(230, 'SOM 12 (Top Hat) - Magnetic Roof Sign', 'School of Motoring', 'L plate roof sign', '', '', '', NULL, NULL, 0, 'images/assets/roof-sign-som12.jpg', 'magnetic top box roof signs', 'images/assets/som/tophat-roof-sign.pdf', 'click image for more details', 120, 'For other graphics please contact us', NULL),
(241, 'SOM 1 - Magnetic Roof Sign', 'School of Motoring', 'A great way to advertise any business', 'Standard - suits most cars\r\nSmall - Ford KA, Corsa 2009+, Micra\r\n\r\nSchool of Motoring\r\nPizza / takeaway Delivery\r\nBusiness Promotion, etc', 'Illuminated version available.\r\n2 sizes Standard or Small', 'See Discounts above', NULL, NULL, 0, 'images/assets/roof-sign-som01.jpg', 'magnetic top box roof signs', 'images/assets/som/som01-roof-sign.pdf', 'click image for more details', 10, 'If lettering / graphics are required please email details or contact us', NULL);

Here's a page to use to test it all ...

<?php
include 'site/includes/conn_str.php';
mysql_connect($host, $usr, $pword);
mysql_select_db($dbase);

$outcome_1 = '';
$outcome_2 = '';

if (isset($_POST['input'])) {
	$input = $_POST['input'];
} else {
	$input = '';
}

$select_str_1 = "SELECT * FROM test WHERE product_name LIKE '%$input%'";
$result = mysql_query($select_str_1);
$num_rows = mysql_num_rows($result);
if ($num_rows > 0) {
	while($row = mysql_fetch_array($result)) {
		$outcome_1 .= $row['product_name'] . '<br />' . "\n";
	}
} else {
	$outcome_1 = '<p>None found.</p>';
}

$select_str_2 = "SELECT * FROM test WHERE MATCH (product_name) AGAINST ('$input')";
$result = mysql_query($select_str_2);
$num_rows = mysql_num_rows($result);
if ($num_rows > 0) {
	while($row = mysql_fetch_array($result)) {
		$outcome_2 .= $row['product_name'] . '<br />' . "\n";
	}
} else {
	$outcome_2 = '<p>None found.</p>';
}
mysql_close();
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Search Test</title>
<style type="text/css">
<!--
.wrap {
	font-family: Arial, Helvetica, sans-serif;
	font-size: 10px;
	margin: 40px;
}
.outcome {
	color: #333333;
}
-->
</style>
</head>

<body>
<div class="wrap">
  <form method="post">
		<input name="input" type="text" value="<?php echo $input ?>" /><br />
		<input name="search" type="submit" value="search" />
  </form>	
</div>
<div class="wrap">
	<p><?php echo $select_str_1 ?></p>
	<p class="outcome"><?php echo $outcome_1 ?></p>
</div>
<div class="wrap">
	<p><?php echo $select_str_2 ?></p>
	<p class="outcome"><?php echo $outcome_2 ?></p>
</div>
</body>
</html>

If anyone can help then I would be most grateful.

thanks,

Simon.

[drjohn]

Short words are ignored, the default minimum length is 4 characters. You can change the min and max word length with the variables ft_min_word_len and ft_max_word_len

according to http://www.petefreitag.com/item/477.cfm

so try 'SOM ' - note the space after som

[slyme]

Thanks drjohn ... 'SOM ' (with the space) doesn't work but I feel that you have put me on the right track ... perhaps the answer is to use LIKE for one word searches and MATCH() AGAINST() for multi word searches ... although that still doesn't work if i search for 'SOM 1', and now I do understand why. Perhaps a more complex test before deciding which SELECT statement to use.

I shall leave this thread open for a bit in case anyone else knows a more elegant solution then I shall mark it solved.

thanks everyone,

Simon.

here's a thought ... what if I:

1. search using LIKE and create an array
2. then search using MATCH() AGAINST() and create another array
3. merge the two arrays ditching any duplicates in the process

is that a bit heavy handed? Comments? A better solution?

[erickci]

Hi,
Try using BOOLEAN MODE while using MATCH() AGAINST()