Printing US States & Listings For Each State

[codeline]

I've got 2 database tables (states, stores) and I want to output all the states with a list of specific locations under each state.

 

My 'states' table has a 'state_id' and 'state' while my 'stores' table has all the store information along with the 'state_id' to work with the 'states' table.

 

How would I ago about formulating a loop to display all locations organized by each state, etc.?

[requinix]

Write your query to get all the information, first. Then,

 

1. Before the loop, start with a previous state = nothing

2. Inside the loop, compare the current state with the previous state. If they don't match,

2a. If there was a previous state at all, close a table or do whatever

2b. Start a table or do whatever

2c. previous = current

3. Display the store

4. After the loop, close the table or do whatever

[kickstart]

Hi

 

Basics like this:-

 

<?php

$query = mysql_query ("SELECT * FROM states a LEFT OUTER JOIN stores b ON a.state_id = b.state_id ORDER BY a.state");

// Starts the loop for fetching the records of the previous query.
$prevState = "";
while($row = mysql_fetch_array($query))
{
if ($prevState != $row['state'])
{
	$prevState != $row['state']
	echo "New State $precState<br />";
}
echo "Store Details ".$row['somefield'].$row['someotherfield'];
}

?>

 

Change $row['somefield'] to a field you want to display, etc.

 

All the best

 

Keith

[codeline]

Keith,

 

What exactly is going on here? Not too familiar with the OUTER LEFT join..

 

Shouldn't the '$prevState != $row['state']' within your first if statement actually be equal to?

 

$prevState == $row['state']

[codeline]

Scratch the previous comment.. read that the LEFT OUTER JOIN allows for states to always be outputted even if there isn't a location within the state.

 

Testing as we speak..

[codeline]

This is what I have so far:

 

$q = "SELECT * FROM my_states LEFT OUTER JOIN my_stores ON my_states.state_id = my_stores.state_id ORDER BY my_states.state_id";
$r = mysql_query($q) or die(mysql_error());

$prevState = "";

while ($row = mysql_fetch_array($r)){

	$state = $row['state'];
	$state_id = $row['state_id'];
	$store = $row['store'];

	if($prevState != $state){
		$prevState != $state;
		print $state . '<br />';
	}

	echo $store . '<br /><br />';
}

 

At the moment, I'm getting each location printed under their respected states, but with each state always being printed above each location.

 

I'm stuck on how to print the State name once, above all the locations..

[kickstart]

Hi

 

When you assign state to previous state you just use a single = sign. You have an extra ! in there

 

All the best

 

Keith

[codeline]

Perfect. Thanks, Keith!

 

Also, which type of JOIN would I use if I wanted to display states that only had locations. Currently, my syntax is outputting all states, even ones without any stores within them.

 

 

[kickstart]

Hi

 

Just change it from a left outer join to an inner join

 

All the best

 

Keith