Join problem...

[icdover]

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.

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.

[sylow]

I have a feeling that you are joining tables without needing to join actually.
Can you post table structures here?

[icdover]

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.

[icdover]

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.

[MattR]

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.

[icdover]

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;

[icdover]

Ok finally figured it out after days of pondering...
The code below is what i have ended up with if anyone is interested...

	$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>');
				}