Database Query Help

[SPetty]

Needing some assistance with Joining some databases together for result display. I have been reading some of the JOIN topics in the forum and I am still somewhat lose when it comes to my example.

There will be four different search types (center_name, city, state, and zipcode), obviously each search will be conducted based on that type.

my main table looks as follows:
center_id, alley_name, alley_address, city_id, state_id, zip_id, alley_phone, alley_website, alley_email

When I go to list based on the search term, I need to be able to pull the city, state and zip IDs from each of 3 seperate tables and create a listing that may have 1 to 50 or more results.

Any assistance would be excellent.

Shawn

[ahundiak]

Not positive what you are asking for but assuming you have city, state and zip tables then

SELECT
main.alley_name,
main.alley_address,
city.name as city_name,
state.name as state_name,
zip.code as zip_code
FROM
city
LEFT JOIN main ON main.city_id = city.city_id
LEFT JOIN state ON main.state_id = state.state_id
LEFT JOIN zip ON main.zip_id = zip.zip_id
WHERE
city.name = 'Huntsville';

Should be about what you need. Suggest building up the query a little bit at a time and checking the results.

[SPetty]

main table = bowling_alley_list
city table = bowling_city (city_id, city_name)
state_table = bowling_state (state_id, state_name, state_abbrev)
zip_table = bowling_zipcodes (zip_id, zipcode)

Here is the code I came up with based upon your suggestion. It sounds like what I'm looking for. Did I lose something in the translation, as I get a

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in externals/alley_list.php on line 67

error when it runs.

$alley_search_text=$_POST['alley_search_text'];
$alley_search_type=$_POST['alley_search_type'];	

if ($alley_search_type == "alley_city")
	{
		$query = "SELECT bowling_alley_list.alley_name, bowling_alley_list.alley_address, bowling_city.city_name, bowling_state.state_abbrev, bowling_zipcodes.zipcode
				  FROM
				  bowling_city
				  LEFT JOIN bowling_alley_list ON bowling_alley_list.city_id = bowling_city.city_id
				  LEFT JOIN bowling_alley_list ON bowling_alley_list.state_id = bowling_state.state_id
				  LEFT JOIN bowling_alley_list ON bowling_alley_list.zip_id = bowling_zipcodes.zip_id
				  WHERE
				  bowling_city.city_name = '$alley_search_text'";
		$result = mysql_query($query);
		$num_rows = mysql_num_rows($result);

	for ($i=0; $i < $num_rows; $i++)
	{
		$row = mysql_fetch_array($result);

		echo $row;
		echo '<br>';
	}
}

[SPetty]

Ok scratch my last post. I noticed the syntax errors in the actual query. It looks like it is posting the needed information.

I will return in a bit if I run into more issues.

Thanks much!

[dmacman1962]

When you resolve yor issue...

1) Post the answer (in this case...)

Line 67, I assume (If you tell us this is line 67, it helps debug the issue a LOT faster!).

for ($i=0; $i < $num_rows; $i++)

Should be...

for ($i=0; $i < $num_rows; $i++;)

And

2) Mark your message RESOLVED

Think of the others searching this forum and help them as well as yourself.

Don

[SPetty]

Actually the Line 67 error was due to the $query being incorrect. It couldn't run the query.

Here is the code I ended up with to produce the results I am looking for.

if ($alley_search_type == "alley_city")
	{
		$query = "SELECT bowling_alley_list.alley_name, bowling_alley_list.alley_address, bowling_city.city_name, bowling_state.state_abbrev, bowling_zipcodes.zipcode, bowling_alley_list.alley_phone, bowling_alley_list.alley_website, bowling_alley_list.alley_email
				  FROM
				  bowling_city
				  LEFT JOIN bowling_alley_list ON bowling_alley_list.city_id = bowling_city.city_id
				  LEFT JOIN bowling_state ON bowling_alley_list.state_id = bowling_state.state_id
				  LEFT JOIN bowling_zipcodes ON bowling_alley_list.zip_id = bowling_zipcodes.zip_id
				  WHERE
				  bowling_city.city_name = '$alley_search_text'
				  ORDER BY bowling_state.state_abbrev, bowling_city.city_name, bowling_zipcodes.zipcode, bowling_alley_list.alley_name";
		$result = mysql_query($query);
		$num_rows = mysql_num_rows($result);

	include '../data/alley_list_code.php';		
}

Changing this line

bowling_city.city_name = '$alley_search_text'

With what I wanted to search for.