Merging two different queries together

[brooksh]

I am trying to combine two different queries but I can only seem to get the first query to display. It only comes up with the first state. Here is my code.

 

$sql = "SELECT DISTINCT state from cities where country = 'US' ORDER BY state";
        $result = mysql_query ($sql);

            while($row = mysql_fetch_array ($result, MYSQL_ASSOC))
        {
$state = $row[state];
$arr = array($state);
reset($arr);
while (list(, $value) = each($arr)) {

}
}
foreach ($arr as $key => $value) {
$sql = "SELECT cities from cities where country='US' AND state='$value'";
        $result = mysql_query ($sql);

            while($row = mysql_fetch_array ($result, MYSQL_ASSOC))
        {
$cities = $row[cities];
}
}

[revraz]

One thing you should do is use mysql_error() after your queries so you can see if there is a error in the query.

[rhodesa]

I think what you are looking for is something like this:

 

$sql = "SELECT DISTINCT state from cities where country = 'US' ORDER BY state";
$result = mysql_query($sql);

$states = array();
while($row = mysql_fetch_array ($result, MYSQL_ASSOC)){
  $states[] = $row[state];
}

$cities = array();
foreach ($states as $value) {
  $sql = "SELECT cities from cities where country='US' AND state='$value'";
  $result = mysql_query ($sql);
  while($row = mysql_fetch_array ($result, MYSQL_ASSOC)){
    $cities[] = $row[cities];
  }
}

 

It would help if you specified exactly what you are trying to accomplish.

[brooksh]

I would like to display each city in the database from every state in the US.

 

table 1 "state - CA, cities - Los Angeles, San Jose, San Francisco"

table 2 "state - WA, cities - Olympia, Seattle, Tacoma"

 

 

Displayed as:

 

Los Angeles

Olympia

San Jose

San Francisco

Seattle

Tacoma

 

[rhodesa]

It seems like a nested loop would be better:

 

$s_sql = "SELECT `state` FROM `cities` WHERE `country` = 'US' ORDER BY `state`";
$s_result = mysql_query($s_sql);
while($state = mysql_fetch_assoc($result)){
  echo "Current state: {$state['state']}\n";
  $c_sql = "SELECT cities from cities where country='US' AND state='$value'";
  $c_result = mysql_query ($c_sql);
  while($city = mysql_fetch_assoc($c_result)){
    echo " -{$city['cities']\n";
  }
}

[brooksh]

Doesn't seem to work for me. I'm running PHP 4.3

 

I got these two errors

 

Warning: Unexpected character in input: '\' (ASCII=92) state=1

 

Parse error: syntax error, unexpected T_STRING, expecting '}'