SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: Join problem...

  1. #1
    SitePoint Enthusiast icdover's Avatar
    Join Date
    Jul 2003
    Location
    MA
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Join problem...

    Ok so i have this query where im trying to interact with some data selected/inputed from a search page... My intent is to display all restaurants that meet one or all of the 3 inputed criteria which are state, town, and/or name.

    Code:
    SELECT Distinct Restaurants.ID, Restaurants.name, States.StateName, Towns.TownName FROM Restaurants, States, Towns, Lookup WHERE Restaurants.name LIKE '%$searchtext%' AND $tid=Lookup.TID AND $sid=Towns.SID
    $sid = State Table ID
    $tid = Town Table ID

    The output i get from running this query, is a list of The correct restaurant name, with each state, and i believe it an attempt to do it with each town also. I dont actually know for sure if it is, but i know that if there is only 1 town in the database then it will return the correct restaurant followed by a state and the one town, repeated for all states in the database. If there is more than one town it seems to go to an endless loop.

    Any help with this would be great. Im confused where i went wrong.

  2. #2
    SitePoint Guru
    Join Date
    Jan 2001
    Location
    Alkmaar, Netherlands
    Posts
    710
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have a feeling that you are joining tables without needing to join actually.
    Can you post table structures here?

  3. #3
    SitePoint Enthusiast icdover's Avatar
    Join Date
    Jul 2003
    Location
    MA
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Its setup liek this... I have 6 tables.
    One table holds the general restaurant information, another table is the lookup, a table with all the states (that the website serves), all the towns, the different cuisine types, and the restaurant styles.

    If you havent figured it out, the site is to be a "menu" database for restaurants in new-england. These tables are jsut for storing all the restaurant information. I had thought that i had this database setup correctly, but if im wrong lemme know.

    Thanks for the help.

  4. #4
    SitePoint Enthusiast icdover's Avatar
    Join Date
    Jul 2003
    Location
    MA
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you all think i should even bother having seperate tables in this case? At the time i thought it was justified, but now i have second thoughts.

  5. #5
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You need to tie the states table to the towns table, then tie the towns table to the restaraunts table. The lookup table is not clear -- post the SQL you used to create the tables.

  6. #6
    SitePoint Enthusiast icdover's Avatar
    Join Date
    Jul 2003
    Location
    MA
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The SQL for the tables is...

    CREATE TABLE Restaurants (
    ID int(11) NOT NULL auto_increment,
    ModCounter tinyint(4) NOT NULL default '0',
    name tinytext NOT NULL,
    contact tinytext NOT NULL,
    email tinytext,
    phone tinytext NOT NULL,
    fax tinytext,
    street tinytext NOT NULL,
    website tinytext,
    description text NOT NULL,
    logoname tinytext NOT NULL,
    hours text,
    functions tinyint(4) default NULL,
    pricerating tinyint(4) NOT NULL default '0',
    smoking tinyint(4) default NULL,
    takeout tinyint(4) default NULL,
    seating int(11) default NULL,
    entertainment tinytext,
    coupon tinyint(4) default NULL,
    photo1name tinytext,
    photo2name tinytext,
    photo3name tinytext,
    PRIMARY KEY (ID)
    ) TYPE=MyISAM;

    CREATE TABLE Lookup (
    RID int(11) NOT NULL auto_increment,
    TID int(11) NOT NULL default '0',
    SID tinyint(4) NOT NULL default '0',
    CID tinyint(4) NOT NULL default '0',
    STID tinyint(4) NOT NULL default '0',
    PRIMARY KEY (RID)
    ) TYPE=MyISAM;

    CREATE TABLE Cuisines (
    ID tinyint(4) NOT NULL auto_increment,
    Name tinytext NOT NULL,
    PRIMARY KEY (ID)
    ) TYPE=MyISAM;

    CREATE TABLE States (
    ID tinyint(4) NOT NULL auto_increment,
    StateName tinytext NOT NULL,
    PRIMARY KEY (ID)
    ) TYPE=MyISAM;

    CREATE TABLE Styles (
    ID tinyint(4) NOT NULL auto_increment,
    Name tinytext NOT NULL,
    PRIMARY KEY (ID)
    ) TYPE=MyISAM;

    CREATE TABLE Towns (
    ID tinyint(4) NOT NULL auto_increment,
    SID tinyint(4) NOT NULL default '0',
    TownName tinytext NOT NULL,
    PRIMARY KEY (ID)
    ) TYPE=MyISAM;

  7. #7
    SitePoint Enthusiast icdover's Avatar
    Join Date
    Jul 2003
    Location
    MA
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok finally figured it out after days of pondering...
    The code below is what i have ended up with if anyone is interested...

    Code:
    	$select = 'SELECT DISTINCT Restaurants.ID, Restaurants.name, States.StateName, Towns.TownName';
    			$from = ' FROM Restaurants, States, Towns, Lookup';
    			$where = ' WHERE 1=1 AND States.ID=Towns.SID AND Lookup.TID=Towns.ID';
    			
    			$sid = $_POST['sid'];
    			if ($sid != '') {
    				$where .= " AND $sid=States.ID";
    				}
    				
    			$tid = $_POST['tid'];
    			if ($tid != '') {
    				$where .= " AND $tid=Lookup.TID";
    				}
    				
    			$searchtext = $_POST['searchtext'];
    			if ($searchtext != '') {
    				$where .= " AND Restaurants.Name LIKE '%$searchtext%'";
    				}
    				
    			$restaurants = @mysql_query($select . $from . $where);
    			if (!$restaurants) {
    				die ('<p>Error retrieving restaurants from database!<br/>' .
    					'Error: ' . mysql_error() . '</p>');
    				}


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
  •