[SOLVED] SELECT not pulling all relevant records

[arkweb]

I have a db of recipes and my SELECT statement is giving me fits.

SELECT * FROM modx_recipes WHERE published = '1' AND (category LIKE '%$search%' OR category = '$search' OR title LIKE '%$search%' OR  title = '$search' OR intro LIKE '%$search%' OR ingredients LIKE '%$search%' OR instructions LIKE '%$search%' ) ORDER BY title ASC

 

I have a dropdown menu to select categories. That works fine. And then I have an input box for typing search terms. A specific example would be, say, hummus recipes. If I type in 'hummus', it will return 'Edamame Hummus'. If I type in 'hum', it will return all of the hummus recipes. But, and here's where it gets weird to me, if I type in 'Easy Hummus', which is the exact title of one of the recipes, it returns absolutely nothing. Even when I simplify my statement to

 

SELECT * FROM modx_recipes WHERE title = 'Easy Hummus'

or even

SELECT * FROM modx_recipes WHERE id = '197'

 

My mysql knowledge is not extensive, so I'm sure I'm missing something incredibly obvious. Any help would be greatly appreciated.

 

PHP 4.3.9

MySQL 4.1.20

 

CREATE TABLE `modx_recipes` (\n  `id` int(4) NOT NULL auto_increment,\n  `category` varchar(255) NOT NULL default '',\n  `title` varchar(255) NOT NULL default '',\n  `author` varchar(255) NOT NULL default '',\n  `intro` text NOT NULL,\n  `date` varchar(20) NOT NULL default '',\n  `published` char(1) NOT NULL default '',\n  `vegetarian` char(1) NOT NULL default '',\n  `servings` varchar(20) NOT NULL default '',\n  `ingredients` text NOT NULL,\n  `instructions` text NOT NULL,\n  `image` varchar(100) NOT NULL default '',\n  `notes` text NOT NULL,\n  `keywords` text NOT NULL,\n  `featured` char(1) NOT NULL default '',\n  `homepage_featured` char(1) default NULL,\n  PRIMARY KEY  (`id`),\n  KEY `index_title` (`title`)\n) ENGINE=MyISAM DEFAULT CHARSET=utf8

 

 

 

 

[cytech]

My first thought is make sure there is no whitespace at the end of your Easy Humus data in the db.

[Xurion]

Can you post your table data?

[fenway]

My first thought is make sure there is no whitespace at the end of your Easy Humus data in the db.

To this end, use LIKE '%Easy Hummus%' and see if the results change.

[arkweb]

I figured it out. It was an error in my php. I had

if($row = mysql_fetch_array($result))
{
print '<table class="article_list">';
print '<tr><th>Title</th><th>Rating</tr>';
}
while($row = mysql_fetch_array($result))
{
$rating_id =  'r' . $row['id'];
$row_color = ($row_count % 2) ? $color1 : $color2; 

print '<tr bgcolor="'.$row_color.'"><td class="title"><a href="/index.php?id=1648&recipe='.$row['id'].'&name='.$row['title'].'">'.$row['title'].'</a></td><td></td></tr>';
$row_count ++;
}
print '</table>';

 

Apparently, the

if($row = mysql_fetch_array($result))
{
print '<table class="article_list">';
print '<tr><th>Title</th><th>Rating</tr>';
}

 

Was screwing it up.

 

Thanks for all of your replies.