Table1: suppliers
supplier_id autonum
supplier_name varchar
.
.
.
street_address varchar
street_city varchar
street_state mediumint
street_postalcode mediumint
.
.
mailing_address varchar
mailing_city varchar
mailing_state mediumint
mailing_postalcode mediumint
.
.
etc
Table2: states
state_id autonum
state_abbrev varchar
state_name varchar
.
.
Obviously, the "street_state" and the "mailing_state" are to be linked to the
states.state_id
I want to know the best way to get all the supplier info into variables
so that I can output the info into tables. In particular, I want to output the state_abbrev, not the street_state (from the suppliers table), which is only a number.
My first attempt was to use:
Code: Select all
<?php
$supplier_results = mysql_query("select * from suppliers, $opendb);
while ($row = mysql_fetch_array($supplier_results))
{
$supplier_id[] = $row["supplier_id"];
$supplier_name[] = $row["supplier_name"];
.
.
$state_id_temp = $row["street_state"];
$state_result = mysql_query("select state_abbrev from states where
states.stateid=$state_id_temp", $opendb);
$row2 = mysql_fetch_array($state_result);
$street_state[] = $row2["state_abbrev"];
.
.
}
?>Also, remember that there are 2 fields in the suppliers table that use the state_id, so I
couldn't think of an obvious way to do a "where (suppliers.street_state=states.state_id)
and (suppliers.mailing_state=states.state_id)".
I know that I could use separate address tables that are linked to each supplier (ie each
supplier had 2 address entries - street and mailing), but this would have complicated some
of my output quite a bit.
Thanks,
Calvin