MySQL query help

Not clear on what you're trying to accomplish.  Could you show a sample data set of what you would like returned with the names of columns?

Ah I think you meant you wanted this:

SELECT food_name FROM foods_i_like WHERE user_id=1

Select allOpen in new window

SELECT menu.* FROM menu M left join foods_i_like F on
M.ingrediants LIKE '%'+F.food_name+'%' where F.user_id=1

Select allOpen in new window

is this what you want?

Sorry made a typo in the second code snippet, the word matches shouldnt be in there.

Just to clarify I have two tables.

menu, which a field called called ingredients.  Sample record = "milk, bananas, nuts".

foods_i_like, which has a field called food_name. .  Sample record = "bananas"

So if I want to find all the menu items with bananas in the ingredients list I would use

SELECT * FROM menu WHERE ingredients LIKE '%bananas%'

Select allOpen in new window

I want to get all the menu items for all the foods the user likes.

The following works if only one record is returned from food_name.

SELECT * FROM menu WHERE ingredients LIKE (SELECT food_name FROM foods_i_like WHERE user_id=1)

Select allOpen in new window

How can I get it to return all the matches?

I don't know your field names but I think you're going to need to join the tables.

select *
from menu m
inner join ingredients i on m.ingredientsid = i.id
inner join foods_i_like fil on i.ingredient = fil.ingredient
where user_id=1

Select allOpen in new window

Oh, this will be more difficult than I thought.

Here's the actual MySQL statements

Gets a list of ID's

SELECT movies.imdbID AS MyLink FROM user_likes 
INNER JOIN movies ON movies.id = user_likes.id
WHERE user_id=1 AND rating=5 

Select allOpen in new window

The above ID's need to be JOINED with a wildcard on each side as below

SELECT * FROM imdb_likes WHERE matches LIKE '%MyLink%'

Select allOpen in new window

Maybe try something like this...

SELECT movies.imdbID AS MyLink FROM user_likes
INNER JOIN movies ON movies.id = user_likes.id
WHERE user_id=1 AND rating=5 AND matches LIKE '%MyLink%'

The SSCCE for this question would contain the CREATE TABLE statements for these tables and a brief script to load the test data into the tables.   Once we had that we could experiment with the query to get it just right.

%MyLink% in the second query needs to be replaced with the results of the first query

Can you please show us the SSCCE?  Thanks.

ignore my real MySQL code above as the demo data below is for the original question.

Database with sample data

/*
SQLyog Ultimate v9.63 
MySQL - 5.1.73 : Database - EE
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`EE` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `EE`;

/*Table structure for table `food_i_like` */

DROP TABLE IF EXISTS `food_i_like`;

CREATE TABLE `food_i_like` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `user_id` int(3) DEFAULT NULL,
  `menu_items_id` varchar(35) DEFAULT NULL,
  `rating` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

/*Data for the table `food_i_like` */

insert  into `food_i_like`(`id`,`user_id`,`menu_items_id`,`rating`) values (1,1,'5',5),(2,1,'6',4),(3,2,'3',4),(4,2,'1',5),(5,1,'1',5);

/*Table structure for table `menu` */

DROP TABLE IF EXISTS `menu`;

CREATE TABLE `menu` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `title` varchar(45) DEFAULT NULL,
  `ingredients` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

/*Data for the table `menu` */

insert  into `menu`(`id`,`title`,`ingredients`) values (1,'Banana Pie','banana, pie, custard'),(2,'Apple Pie','apples, pie, custard'),(3,'Jam Tart','strawberry, jam'),(4,'Banoffee pie','banana, pie');

/*Table structure for table `menu_items` */

DROP TABLE IF EXISTS `menu_items`;

CREATE TABLE `menu_items` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `food_names` varchar(35) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

/*Data for the table `menu_items` */

insert  into `menu_items`(`id`,`food_names`) values (1,'custard'),(2,'apples'),(3,'strawberry'),(4,'jam'),(5,'banana'),(6,'pie');

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Select allOpen in new window

What I want to do is display all the menu item for user_id=1 where they have given a rating=5

I can get the items the user rates as a 5 using the following MySQL

SELECT menu_items.food_names FROM food_i_like
INNER JOIN menu_items ON menu_items.id = food_i_like.menu_items_id
WHERE user_id=1 AND rating=5

Select allOpen in new window

I can see user_id 1 likes banana and custard.

In the MySQL below I can find all the menu items

banana

SELECT * FROM menu WHERE ingredients LIKE '%banana%'

Select allOpen in new window

custard

SELECT * FROM menu WHERE ingredients LIKE '%custard%'

Select allOpen in new window

How can I join the MySQL together so I can get the all the items the user_id 1 rates as 5?


Many thanks

I think you will seriously regret storing the menu ingredients as a comma separated string of names

Thanks Paul that worked perfectly.

Each of the menu items has exactly 12 ingredients, so if I stored the ingredients as ingredient1, ingredient2 ... ingredients12.  How would the code look?

oh no, don't do it that way..

"Each of the menu items has exactly 12 ingredients" this may be true today, but at some point you will need 14. Please don't try 12 columns - shudder - that's worse than the comma separated string (although it might perform better in some cases)

While you may not believe it, the best way to store 12 ingredients is in 12 rows
foreign keys to menu and to ingredient, this table could also store things like quantity_per_serve

(nb: and you can derive a comma separated string of ingredients using group_concat if that is really wanted)

it would look like this:

| MENU_ID | INGREDIENT_ID |
|---------|---------------|
|       1 |             1 |
|       1 |             5 |
|       1 |             6 |
|       2 |             1 |
|       2 |             2 |
|       2 |             6 |
|       3 |             3 |
|       3 |             4 |
|       4 |             5 |
|       4 |             6 |


which I produced by this:

select
        m.id as menu_id
      , mi.id as ingredient_id
from menu m
inner join menu_items mi on m.ingredients like concat('%', mi.food_names, '%')
order by 1,2

Select allOpen in new window

This question is already closed, so I'll sign off, but you might want to make a Google search for the exact phrase, "Should I Normalize My Database" and read the very interesting arguments on both sides of the question.

Some good learning resources for PHP and MySQL are available in this article and this book.
http://www.amazon.com/PHP-MySQL-Web-Development-4th/dp/0672329166/