Queries Within Queries

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
calvinc
Forum Newbie
Posts: 3
Joined: Wed Oct 27, 2004 1:54 pm

Queries Within Queries

Post by calvinc »

I have quite a few tables in my database. An extract of the tables is as follows:


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"];
  .
  .  

}
?>
I'm sure that there are much better (and simpler) ways of doing this. Can anyone help me?

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
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

do a join on the tables

select a.*, b.state from suppliers a inner join states b on a.state_id = b.state_id [where clause]

Are there cases where the business and mailiing addresses can be in different states?
calvinc
Forum Newbie
Posts: 3
Joined: Wed Oct 27, 2004 1:54 pm

Post by calvinc »

Thanks.

I have assumed that the street & mailing addresses can be in different states. However, I have assumed they are in the same country. How do I get both the mailing_state & street_state to be joined? Also, what will the name of my street_state & mailing_state columns be when I do a $row=mysql_fetch_assoc($results)? Will it be $row["a.street_state"] ??
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

one trick might be to put all the states in an array using the state_id as the key, then use the queried value to pull the name from the array

Code: Select all

<?
$states_array = array('AL'=>'Alabama','AK'=>'Alaska'...'WY'=>'Wyoming');

$mailing_state = $rows['mailing_state'];
$street_state = $rows['street_state'];

echo "<tr><td>Mailing State</td><td>".$states_array[$mailing_state]."</td></tr>";
?>
calvinc
Forum Newbie
Posts: 3
Joined: Wed Oct 27, 2004 1:54 pm

Post by calvinc »

Thanks, I took the hints and I have come up with the following which works (posted for those following this):

Code: Select all

<?php
// *** Get Data From States Table

$state_abbrevs = array();
$state_names = array();
$state_country_ids = array();

$state_query = "select * from states";
$state_results = mysql_query($state_query, $open_directory_db);

$state_index = 0;

while ($state_row = mysql_fetch_array($state_results))
{
  $state_index = (integer) ($state_row["state_id"]);
  $state_abrrevs[$state_index] = $state_row["state_abbrev"];
  $state_names[$state_index] = $state_row["state_name"];
  $state_country_ids[$state_index] = $state_row["country_id"];
}

$supplier_id = array();
$supplier_name = array();
.
.
$country = array();
$street_address = array();
$street_city = array();
$street_state = array();
$street_postalcode = array();
$mailing_address = array();
$mailing_city = array();
$mailing_state = array();
$mailing_postalcode = array();
.
.

while ($row = mysql_fetch_array($supplier_results))
{
  $supplier_id[] = $row["supplier_id"];
  $supplier_name[] = $row["supplier_name"];
  $country[] = $country_names[$row["country_id"]];
  $street_address[] = $row["street_address"];
  $street_city[] = $row["street_city"];
  $street_state[] = $state_abbrevs[$row["street_state"]];
  $street_postalcode[] = $row["street_postalcode"];
  $mailing_address[] = $row["mailing_address"];
  $mailing_city[] = $row["mailing_city"];
  $mailing_state[] = $state_abbrevs[$row["mailing_state"]];
  $mailing_postalcode[] = $row["mailing_postalcode"];
  .
  .
  .
}
?>
I needed to be able to add states to the database as required, and it still work. That is why I did it as above.

Thanks very much for your help. :)
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

since the states never change, save yourself the overhead and call processing and simply populate the arrays manually inside the script
Post Reply