Multiple table select

Anon

I'm trying to pull information in from three or four seperate tables into one query. I have a table called store which holds id, store_name_id, store_address_id and groups. the store_location table holds the id, address, city, store_state_id, zip and phone. the store_name table hold th id and store_name. the store_state table holds id and state. groups is something else it points to a table that references product groups offered at the various retail locations.

anyway my problem is doing a select statement that will get me the information and do it like that

Store Name

address - city - state - zip - phone - group name

x the number of entries for that store name.

here is my fourth attempt at this query

$sql = "SELECT store_name.name as name, store_location.address as address, store_location.city as city, store_state.state as state, store_location.zip as zip, store_location.phone as phone, store.id FROM store, store_name, store_location, store_state WHERE (store.store_name_id=store_name.id) AND (store.store_address_id==store_location.id) AND (store_location.store_state=store_state.id)";

$result = mysql_query($sql);

$counter = 0;

while ($retail_store = mysql_fetch_array($result) ) {

if ($counter % 2 == 0) {

$retail_store_output .= "<TR><TD BGCOLOR=\"#F7F7F7\" WIDTH=\"60%\">".$retail_store["name"]." - ".$retail_store["address"]." - ".$retail_store["city"]." - ".$retail_store["state"]." - ".$retail_store["zip"]." - ".$retail_store["phone"]."</TD>";
$retail_store_output .= "<TD BGCOLOR=\"#F7F7F7\" WIDTH=\"40%\">Would you like to <a href=\"index.php?area=retail&type=rsnedit&id=".$retail_store_name["id"]."\">Edit</a> or <a href=\"index.php?area=retail&type=rsndelete&id=".$retail_store_name["id"]."\">Delete</a></TD></tr>";

} else {

$retail_store_output .= "<TR><TD BGCOLOR=\"#F7F7F7\" WIDTH=\"60%\">".$retail_store["name"]." - ".$retail_store["address"]." - ".$retail_store["city"]." - ".$retail_store["state"]." - ".$retail_store["zip"]." - ".$retail_store["phone"]."</TD>";
$retail_store_output .= "<TD BGCOLOR=\"#DDDDDD\" WIDTH=\"40%\">Would you like to <a href=\"index.php?area=retail&type=rsnedit&id=".$retail_store_name["id"]."\">Edit</a> or <a href=\"index.php?area=retail&type=rsndelete&id=".$retail_store_name["id"]."\">Delete</a></TD></tr>";

}

$counter++;

}

$retail_store_output .= "<TR><TD BGCOLOR=\"#F7F7F7\" WIDTH=\"20%\"></TD><TD BGCOLOR=\"#F7F7F7\" WIDTH=\"80%\"><a href=".$PHP_SELF."?area=retail&type=rsnadd>Add</a> New Retail Store?</TD></tr>";

mysql_close();

I know I am missing something and I have looked at it several times my mind is starting to gloss over, can someone help ..thanks

dan

tomhath

Looks like it should work. Here are some comments:

You don't need to name every field with an "as" if the column name is what you already want. The less typing the less chance of a typo.

Second, the double equal doesn't look right (store.store_address_id==store_location.id)

Third, it looks like you are missing _id after store_location.store_state here: store_location.store_state=store_state.id)

Fourth, you really should be checking for an error and error message from mysql_query. It might've told you the problems right away.

Anon

heres the non descript error

Supplied argument is not a valid MySQL result resource in /home/sites/projects.heathermccullough.com/web/copeland/administration/index.php on line 1351

Anon

Made some of the changes ... and changed the query I removed the as part and put it back in. Now instead of an error I get nothing .. $sql = "SELECT store_name.store_name as name, store_location.address as address, store_location.city as city, store_state.state as state, store_location.zip as zip, store_location.phone as phone FROM store, store_name, store_location, store_state WHERE (store.id = '$id') AND (store.store_name_id = store_name.id) AND (store.store_address_id = store_location.id) AND (store_location.store_state = store_state.id)";

which if memory serves me means either I have grabbed the wrong information or something else about the field names, I believe they are correct as the only error that I have gotten form mysql_error() was state_name.name doesnt exist it was .state ... needing to take another step back

anyone have any ideas

tomhath

At this point I'd start taking tables out of the query. Fetch from just one table, then two, until the whole join works.

Anon

This is a warning to all coders, when doing a multi table select, do not, I repeat, do not do it while under heavy sinus medication. Why you ask, well the SELECT statement might work, but if you forget to print out your results its not going to work.

Tom the forth post worked, worked great, if you look at the $var, you'll notice that its $retail_store_output, in the first one, or something like that for the HTML and the order that I want the inforomation to appear.

I forgot to do this, very basic.

print $retail_store_output;

D'oh!