MySQL query to match everything in array

Question

I have a complex query I've been wrestling with for 2 days and can't seem to get it to run. I have two querys - The first query works fine but I can't figure out the second What am I doing doing wrong?

CREATE TABLE `recipes` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(100) NOT NULL default '',
  `cat` int(5) NOT NULL default '0',
  `instructions` text NOT NULL,
  `submitted_by` varchar(100) NOT NULL default '',
  `addDate` date NOT NULL default '0000-00-00',
  `hits` int(7) NOT NULL default '0',
  `metaDesc` text NOT NULL,
  `metaKeys` text NOT NULL,
  `enComments` enum('yes','no') NOT NULL default 'yes',
  `enRating` enum('yes','no') NOT NULL default 'yes',
  `enRecipe` enum('yes','no') NOT NULL default 'yes',
  `isApproved` enum('yes','no') NOT NULL default 'no',
  `comCount` int(7) NOT NULL default '0',
  `ratingCount` int(8) NOT NULL default '0',
  `ipAddresses` text NOT NULL,
  `email` varchar(250) NOT NULL default '',
  `rss_date` varchar(35) NOT NULL default '',
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `name` (`name`,`instructions`,`metaDesc`,`metaKeys`),
  FULLTEXT KEY `submitted_by` (`submitted_by`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=37 ;

CREATE TABLE `ingredients` (
  `id` int(10) NOT NULL auto_increment,
  `recipe` int(8) NOT NULL,
  `qty` varchar(128) NOT NULL,
  `measurement` varchar(128) NOT NULL,
  `ingredient` varchar(128) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `ingredient` (`ingredient`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=135 ;

I've created a query that matches a search term to any ingredient in the ingredients table and results the entire recipe.

$q_recipe = mysql_query("SELECT * FROM recipes
     WHERE enRecipe  = 'yes'
     AND isApproved  = 'no'
     AND MATCH(name,instructions,submitted_by,metaDesc,metaKeys) AGAINST('".$search."') IN BOOLEAN MODE)
     OR id IN (SELECT distinct recipe FROM ingredients WHERE ingredient LIKE '%".$search."%')
          ORDER BY name LIMIT $limit") or die(mysql_error());

The goal is to rewrite the above query to provide a new search feature that matches an array of search terms to only recipes that include all terms in the array. Help!!!

Derrick Smith · Accepted Answer · 2010-10-24 18:41:04Z

1

Found a solution although it may not be the most efficient.

foreach ($search as $item) {
    $sql .= "AND id IN (SELECT recipe FROM ingredients WHERE ingredient like '%".$item."%') ";
}
$q_sql = "SELECT * FROM recipes
          WHERE enRecipe  = 'yes'
          AND isApproved  = 'no'
          ".$sql;

answered Oct 24, 2010 at 18:41

Derrick Smith

112 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

igelkott · Accepted Answer · 2010-10-24 18:21:02Z

0

Extra parenthesis in your 'against'. Change AGAINST('".$search."') IN BOOLEAN MODE) to AGAINST('".$search."' IN BOOLEAN MODE).

I would also be careful about having the two tables use different character sets.

answered Oct 24, 2010 at 18:21

igelkott

1,2878 silver badges9 bronze badges

Collectives™ on Stack Overflow

MySQL query to match everything in array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related