Help!! INNER JOINing tables within this script.

[co.ador]

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':''
    );?>

 

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( 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; 

 

#2-restaurants_restaurant_food_types

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

 

 

 

#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 ; 

 

#4- restaurants_to_restaurant_offerings.

CREATE TABLE IF NOT EXISTS `restaurants_to_restaurant_offerings` (
  `restaurants_id` mediumint( 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; 

 

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.

[fenway]

Conditions can't be set up as anything -- it's your table that needs to support many-to-many