How can I relate this two tables?

[co.ador]

 

If you notice in the picture there is no a submit bottom but let's suppose there is one.

 

table one...

CREATE TABLE IF NOT EXISTS `restaurants` (

  `restaurants_id` mediumint unsigned NOT NULL AUTO_INCREMENT,

  `restaurantname` varchar(255) NOT NULL,

  `image` varchar(100) DEFAULT NULL,

  PRIMARY KEY (`restaurants_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=173 ;

 

 

Contains a list of restaurants and input tag name above in the picture is where through an user will get through the restaurant table to look for an X restaurant.

 

What about if an user get to choose a food type of the select tags in the pictures, Then how can a query be formed to relate table one with table two which is called restaurant_food_types and is as below..

 

CREATE TABLE IF NOT EXISTS `restaurant_food_types` (

  `restaurant_food_types_id` mediumint unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

  PRIMARY KEY (`restaurant_food_types_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

Thank you very much.

 

 

[co.ador]

 

If you notice in the picture there is no a submit bottom but let's suppose there is one.

 

table one...

CREATE TABLE IF NOT EXISTS `restaurants` (

  `restaurants_id` mediumint unsigned NOT NULL AUTO_INCREMENT,

  `restaurantname` varchar(255) NOT NULL,

  `image` varchar(100) DEFAULT NULL,

  PRIMARY KEY (`restaurants_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=173 ;

 

 

Contains a list of restaurants and input tag name above in the picture is where through an user will get through the restaurant table to look for an X restaurant.

 

What about if an user get to choose a food type of the select tags in the pictures, Then how can a query be formed to relate table one with table two which is called restaurant_food_types and is as below..

 

CREATE TABLE IF NOT EXISTS `restaurant_food_types` (

  `restaurant_food_types_id` mediumint unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

  PRIMARY KEY (`restaurant_food_types_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

Thank you very much.

 

 

sorry for the duplication I made a mistake

[corbin]

Well, you basically need to have a many (multiple places) to many (multiple types of food) relationship.

 

 

That typically involves a intermediary table:

 

 

CREATE TABLE restaurant_foods (

    restaurant_id mediumint unsigned NOT NULL,

    restaurant_food_types_id mediumint unsigned NOT NULL

);

 

(And you would want index(es) on which ever field(s) you plan on searching the table by.)

 

Then, you would use a JOIN to get it all together for each restaurant.

[co.ador]

i have another query structure now and i need some help. the query seems to be well contructed but it won't display the div container4 and wrap in the screen. Right now as it is it doesn't display any warnings, errors etc.

 

It won't display the results

 

<div id="container4">
  <div class="wrap">  
  <?php"SELECT
     r.*
  FROM
     RESTAURANTS r 
INNER
  JOIN
     RESTAURANTS_TO_RESTAURANT_FOOD_TYPES r2rft
    ON
     r.restaurants_id = r2rft.restaurants_id 
WHERE
     r2rft.restaurant_food_types_id IN (6,4)   
GROUP
    BY
     r.restaurants_id
HAVING
     COUNT(DISTINCT r2rft.restaurant_food_types_id) = 2 ";


$result3 = mysql_query($query3, $connection);
$i = 1;
while ($content3 = mysql_fetch_array($result3)) {
  echo "<div class=\"shoeinfo1\">
   <img src=\"images/spacer.gif\" alt=\"spacer\" class=\"spacer2\" />
      <h2 class=\"infohead\">". $content3['restaurantname'] . "</h2>
      <div class=\"pic\"><img class=\"line\" src= ". $content3['image'] ." alt=\"picture\" width=\"100%\" height=\"100%\" /></div>

      
    </div>";
$i++; 
}
if ($i > 1 && $i % 3 == 0 ) 
{
  echo "<div class=\"clearer\"></div>";

}?></div>

 

i have to say I am coming from page1.php to restaurant.php page and I am passing values throught the url to restaurant.php  and I recieve the following values in restaurants such as..

 

<?php 
$restaurantname = $_GET['name']; 
$zipcode = $_GET['zipcode'];
$state = $_GET['state']; 
$foodtype = $_GET['foodtype'];
$checkboxes = $_GET['example']; 

?>

 

The file together would look like

 

<?php 
$restaurantname = $_GET['name']; 
$zipcode = $_GET['zipcode'];
$state = $_GET['state']; 
$foodtype = $_GET['foodtype'];
$checkboxes = $_GET['example']; 

?>
<div id="container4">
  <div class="wrap">  
  <?php "SELECT
     r.*
  FROM
     RESTAURANTS r 
INNER
  JOIN
     RESTAURANTS_TO_RESTAURANT_FOOD_TYPES r2rft
    ON
     r.restaurants_id = r2rft.restaurants_id 
WHERE
     r2rft.restaurant_food_types_id IN (6,4)   
GROUP
    BY
     r.restaurants_id
HAVING
     COUNT(DISTINCT r2rft.restaurant_food_types_id) = 2 ";


$result3 = mysql_query($query3, $connection);
$i = 1;
while ($content3 = mysql_fetch_array($result3)) {
  echo "<div class=\"shoeinfo1\">
   <img src=\"images/spacer.gif\" alt=\"spacer\" class=\"spacer2\" />
      <h2 class=\"infohead\">". $content3['restaurantname'] . "</h2>
      <div class=\"pic\"><img class=\"line\" src= ". $content3['image'] ." alt=\"picture\" width=\"100%\" height=\"100%\" /></div>

      
    </div>";
$i++; 
}
if ($i > 1 && $i % 3 == 0 ) 
{
  echo "<div class=\"clearer\"></div>";

}?></div>

 

If you notice i have added the image field in the restaurant table while thinking about building a table along for the images later.

 

Now with the ideas of passing variables through the url instead of structuring.

 

[HIGHLIGHT=SQL]HAVING

    COUNT(DISTINCT r2rft.restaurant_food_types_id) = 2[/HIGHLIGHT]

 

should it include the variable as a comparison instead of the number like:

 

[HIGHLIGHT=SQL]HAVING

    COUNT(DISTINCT r2rft.restaurant_food_types_id) = $foodtype[/HIGHLIGHT]

 

The main issue is displaying the content assigned above. It doesn't display any erros messages or warning or anything like that just, the background of the body but not the content which would be the name of the restaurant and it's respective image.