Help!! INNER JOINing tables within this script.

The following query is the one I have set up to display in the screen the fields restaurantname, image, description, address, zip and state. Right now it works as a one_to_one relationship but the goal is to convert it to a many_to_many relationship.

Query as it now.

<?php  $strSQL = sprintf(
    'SELECT
          r.restaurants_id
          ,r.restaurantname
          ,r.image
		  ,r.description
		  ,r.address
		  ,z.zip
		  ,z.state
       FROM
          restaurants r
         %s
         %s
         %s'
     ,$boolIncludeZipCodes === true?'INNER JOIN zip_codes z ON r.restaurants_id = z.restaurants_id ':''
     ,empty($arrSQLFilters)?'':' WHERE '.implode(' AND ',$arrSQLFilters)  
     ,$boolIncludeZipCodes === true?'GROUP BY r.restaurants_id':''
    );?>

PHP:

Beside INNER joining the tables zip_codes I want to INNER JOIN several others tables ON others conditions.

The number of tables beside zip_codes are four

#1-restaurant_food_types.

CREATE TABLE IF NOT EXISTS `restaurant_food_types` (
  `restaurant_food_types_id` mediumint(8) UNSIGNED NOT NULL,
  `name` varchar(37) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`restaurant_food_types_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8; 

Code (markup):

#2-restaurants_restaurant_food_types

CREATE TABLE IF NOT EXISTS `restaurants_restaurant_food_types` (
  `restaurants_id` mediumint(8) UNSIGNED NOT NULL,
  `restaurants_food_types_id` mediumint(8) UNSIGNED NOT NULL,
  PRIMARY KEY (`restaurants_id`,`restaurants_food_types_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

Code (markup):

#3-restaurant_offerings

CREATE TABLE IF NOT EXISTS `restaurant_offerings` (
  `restaurant_offerings_id` tinyint(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(48) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`restaurant_offerings_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ; 

Code (markup):

#4- restaurants_to_restaurant_offerings.

CREATE TABLE IF NOT EXISTS `restaurants_to_restaurant_offerings` (
  `restaurants_id` mediumint(8) UNSIGNED NOT NULL,
  `restaurant_offerings_id` tinyint(3) UNSIGNED NOT NULL,
  `offers_service` tinyint(3) UNSIGNED NOT NULL,
  PRIMARY KEY (`restaurants_id`,`restaurant_offerings_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

Code (markup):

As you can see above there are many_to_many relationship tables.I want to have an idea of how to INNER JOIN table number 1 and 2 in the query above based ON restaurants_food_types_id=restaurants_food_types_id and table number 2 and 3 ON restaurant_offerings_id=restaurant_offerings_id. I also want to select the z.zip and z.state fields of the zip_codes tables to display if condition restaurants_food_types_id=restaurants_food_types_id or restaurant_offerings_id=restaurant_offerings_id is met. Right now as the query is it able the html frame to display only one restaurant ON r.restaurants_id = z.restaurants_id. This condition " r.restaurants_id = z.restaurants_id " is set up as a one_to many relationship and I am looking for a many_to_many relationship.

Any help is appreciated.

 

This is how you will link your 4 tables together. I haven't test the following code but I'm sure it will work. What you can do is modify the following query to met your requirement.

I hope it will help.

 SELECT  rft.name, 
               rtro.offers_service, 
               ro.name 
         FROM  restaurant_food_types AS rft 
         JOIN  restaurants_restaurant_food_types AS rrft 
           ON  rft.restaurant_food_types_id = rrft.restaurants_id 
    LEFT JOIN  restaurants_to_restaurant_offerings AS rtro
           ON  rrft.restaurants_id = rtro.restaurants_id
    LEFT JOIN  restaurant_offerings AS ro
           ON  rtro.restaurant_offerings_id = ro.restaurant_offerings_id
        WHERE  rft.restaurant_food_types_id = 1

PHP: