co.ador Posted December 5, 2009 Share Posted December 5, 2009 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. Link to comment https://forums.phpfreaks.com/topic/184080-help-inner-joining-tables-within-this-script/ Share on other sites More sharing options...
fenway Posted December 5, 2009 Share Posted December 5, 2009 Conditions can't be set up as anything -- it's your table that needs to support many-to-many Link to comment https://forums.phpfreaks.com/topic/184080-help-inner-joining-tables-within-this-script/#findComment-971906 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.