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...

[code]CREATE TABLE IF NOT EXISTS `restaurants` (
  `restaurants_id` MEDIUMINT(8) 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 ;
[/code]

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..

[code]
CREATE TABLE IF NOT EXISTS `restaurant_food_types` (
  `restaurant_food_types_id` MEDIUMINT(8) 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 ;[/code]

Thank you.

[r937]

here is my suggestion:

CREATE TABLE restaurants
( id  INTEGER NOT NULL PRIMARY KEY
, name VARCHAR(255) NOT NULL
, image VARCHAR(100)
);
CREATE TABLE foodtypes
( id  INTEGER NOT NULL PRIMARY KEY
, foodtype VARCHAR(37) NOT NULL
);
CREATE TABLE restaurant_foodtypes
( restaurants_id INTEGER NOT NULL
, foodtypes_id INTEGER NOT NULL 
, FOREIGN KEY ( restaurants_id ) REFERENCES restaurants ( id )
, FOREIGN KEY ( foodtypes_id ) REFERENCES foodtypes ( id )
, PRIMARY KEY ( restaurants_id, foodtypes_id )
, INDEX reversi ( foodtypes_id, restaurants_id )
);

i have changed several names in the interest of clarity

the restaurant_foodtypes table is called a many-to-many or association or relationship table

[co.ador]

I am trying to import to mysql the format of the restaurant_foodtypes table into phpmyadmin in mysql 5.1 and it throw this error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOREIGN KEY ( foodtypes_id) REFERENCES foodtypes (id)
PRIMARY KEY (`restauran' at line 12

I don't know how to structure the syntax for FOREIGN KEY and REFERENCES in this mysql version. I tried to do it like you did but it didn't work.

The mysql dump of the table restaurant_foodtypes in the mysql version in my computer dumps it as below without the FOREING KEY and REFERENCES you have add to it.


- phpMyAdmin SQL Dump
-- version 3.1.3.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Nov 22, 2009 at 06:07 AM
-- Server version: 5.1.33
-- PHP Version: 5.2.9-2

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `menu`
--

-- --------------------------------------------------------

--
-- Table structure for table `restaurants_foodtypes`
--

CREATE TABLE IF NOT EXISTS `restaurants_foodtypes` (
`restaurants_id` int(1) NOT NULL,
`foodtypes_id` int(1) NOT NULL,
PRIMARY KEY (`restaurants_id`,`foodtypes_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `restaurants_foodtypes`
--

[r937]

foreign keys aren't gonna work for you anyway, because you're using MyISAM tables

[co.ador]

so it's ok if I leave like that?

CREATE TABLE IF NOT EXISTS `restaurants_foodtypes` (
`restaurants_id` int(1) NOT NULL,
`foodtypes_id` int(1) NOT NULL,
PRIMARY KEY (`restaurants_id`,`foodtypes_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I using phpmyadmin and mysql.

What would be the way to struture the way you have done it?

[r937]

Originally Posted by co.ador

What would be the way to struture the way you have done it?

leave everything as is, but remove the FOREIGN KEY stuff

[co.ador]

r937

The variables below are the one passed through the url to restaurantslist.php

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

?>

then after putting the above variable in the top of the document

I want to form a query where a list of restaurants display according to the foodtype the user has selected.

Query...

$query3= "SELECT r.restaurantname, r.image
FROM restaurants r 
INNER JOIN foodtypes f
ON id = id
WHERE r.restaurnatname = what?";

Another question how is Restaurants_foodtypes table is used? I have learn that's the link in between the restaurant table and the footypes table.

[r937]

i'm sorry, i don't do php

don't give up though, there are several really good php guys in this forum

[co.ador]

Mr. r937 forget about the php.

$query3= "SELECT r.restaurantname, r.image
FROM restaurants r
INNER JOIN foodtypes f
ON id = id
WHERE r.restaurnatname = what?";

is to combine the three tables we have formed so far...

How is it possible to have a query where the r.restaurantname and r.image of the restaurants that contain x foodtype based on users choices?