SitePoint Sponsor |
|
User Tag List
Results 1 to 9 of 9
-
Nov 21, 2009, 11:43 #1
How can I relate this two tables?
If you notice in the picture there is no a submit bottom but let's suppose there is one.
table one...
Code MySQL:
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 MySQL:[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.
-
Nov 21, 2009, 13:32 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
here is my suggestion:
Code: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 ) );
the restaurant_foodtypes table is called a many-to-many or association or relationship table
-
Nov 22, 2009, 05:29 #3
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
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`
--
-
Nov 22, 2009, 05:38 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
foreign keys aren't gonna work for you anyway, because you're using MyISAM tables
-
Nov 22, 2009, 07:14 #5
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;
What would be the way to struture the way you have done it?
-
Nov 22, 2009, 07:53 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Nov 22, 2009, 08:38 #7
r937
The variables below are the one passed through the url to restaurantslist.php
PHP Code:<?php
$restaurantname = $_GET['name'];
$zipcode = $_GET['zipcode'];
$state = $_GET['state'];
$foodtype = $_GET['foodtype'];
$checkboxes = $_GET['example'];
?>
I want to form a query where a list of restaurants display according to the foodtype the user has selected.
Query...
Code:$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.
-
Nov 22, 2009, 08:50 #8
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
i'm sorry, i don't do php
don't give up though, there are several really good php guys in this forum
-
Nov 22, 2009, 09:01 #9
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?";
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?
Bookmarks