SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,330
    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 )
    );
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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`
    --

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,330
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    foreign keys aren't gonna work for you anyway, because you're using MyISAM tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,330
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by co.ador View Post
    What would be the way to struture the way you have done it?
    leave everything as is, but remove the FOREIGN KEY stuff

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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']; 

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

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,330
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •