[RESOLVED] Query Problem

thekidscareya

MySQL Database Structure:

Table: Shrimp_Stores
id (primary key)
name
city
state

Table: Shrimp_Restaurants
id (primary key)
name
city
state

How do I query for Shrimp_Restaurants.city AND Shrimp_Stores.city WHERE their corresponding state is = to $state and put them in one array?

Eg:

Data in DB:
Shrimp_Stores
(1, Shrimp USA, Miami, FL)
(2, Shrimp Florida, Orlando, FL)

Shrimp_Restaurants
(1, Excellent Seafood, Miami, FL)
(1, Gourmet Seafood, Orlando, FL)

I want the following array when I query the database:
Array(Miami, Orlando)

I would rather not have to make relationships and have the city and state columns in their own tables.

THanks in advance for your help.

konsu

"inner join" tables shrimp_stores and shrimp_restaurants on their "state" fields and select just these records that have the given value in the "state" field in either of the tables.

thekidscareya

ive never used inner join before...can you show me how it works?

thanks

konsu

something like select * from shrimp_stores inner join shrimp_restaurants on shrimp_stores.state=shrimp_restaurants.state.

check out an SQL tutorial. for example http://www.w3schools.com/sql/sql_join.asp.

thekidscareya

What if there is a city on one of the tables and not on the other table...how do I get that into there?

thekidscareya

Would it be easier if i did something like this
$sql = select city from shrimp_restaurant;
$query = mysql_query($sql);
$row = mysql_fetch_array($query);

would $row be an array of the city column? if so i can do the same query on the other table and combine the $row variables into one array...and go from there

konsu

you can do it this way, yes, but joins are designed to do the same but with a single database request. to answer your question about cities, the result of the join is a table where rows from the table on the left of the join operator are concatenated with the rows from the table on the right, so you will get all possible combinations of cities which are in the same state.

thekidscareya

Yeah..but what if I have a city and state combo that is just in one table?

Here...I started a topic at mysqlfreaks.com...this goes a bit more in depth
http://www.mysqlfreaks.com/forums/index.php?showtopic=8302

konsu

i see now what you are trying to do. i misunderstood the problem. you can use UNION:

select city, state from table0 union select city,state from table1

thekidscareya

One more question...

$sql = "SELECT city, state ";
$sql .= "FROM 8_shrimp_restaurants ";
$sql .= "UNION SELECT city, state ";
$sql .= "FROM 8_shrimp_stores";

$query = mysql_query($sql);

How could I get all the data from the query without using mysql_fetch_array() in a while loop?

I wanted to do something like this:

$row = mysql_fetch_array($query);

but I would only get the first row returned by the query. Is there a way to make it a multi dimensional array or something like that. I don't want it in a while loop because I want to remove all cities from a given state and have an array of just the cities left.

konsu

to extract just cities for a given state you can change the query. select only the "city" field. UNION by default selects only distinct records. i do not know how to extract a resultset without looping.

thekidscareya

Ok...but that would bring back all the cities from both tables...but I want to get the cities from a specific state...so I would have to use a WHERE statement ...but what do i put for the where statement?

WHERE shrimp_stores.state = '$state' AND shrimp_restaurants.state = '$state'

WHERE shrimp_stores.state = '$state'

WHERE shrimp_restaurants.state = '$state'

konsu

please take a look at http://dev.mysql.com/doc/mysql/en/union.html. you can insert the WHERE clause in to each select and put each SELECT in parentheses.

thekidscareya

Thanks...I got it:

SELECT city
FROM 8_shrimp_restaurants
WHERE 8_shrimp_restaurants.state = '$state'
UNION SELECT city
FROM 8_shrimp_stores
WHERE 8_shrimp_stores.state = '$state'

Thanks konsu for all your hard work...I really appreciate it.

I still have one problem:

I want to get Array(Newark, Camden) as an array...when I do a while loop when the state is NJ I get:
Array ( [0] => Newark [city] => Newark [1] => NJ [state] => NJ ) Array ( [0] => Camden [city] => Camden [1] => NJ [state] => NJ )

When I just do $row = mysql_fetch_array i get
Array ( [0] => Newark [city] => Newark [1] => NJ [state] => NJ )
that only returns the first row...but not the second row...

How do I do this?

konsu

could you post your code?

thekidscareya

Nvm...I figured it out.. Thanks for your help konsu...you rock!

I really appreciate it.