Can this be done with 1 query??

[chronister]

Hello All,

 

I am fairly proficient at mysql queries, however I need a little help with this one.

 

Here is my db layout.

 

[stores table]

storeid  | address | city_id | state_id | zip | phone | email | common_name | cafez | fax | latlon

 

[states table]

state_id  | state_name  |  state_name_full

 

[cities table]

city_id  | city_name | state_id

 

Ok, so now that is my db structure where the query is concerned.

 

I want to create a list of stores organized by state, then by city and finally by the actual store. See attached image to see what I want to accomplish. I know I can do this is several queries to pull the states, then the cities then the stores running through a couple loops and print out the results like I want, but I don't want to run a bunch of queries to make it happen.

 

Can this be done in 1 query or will I have to do it in multiple queries.

 

Thanks,

 

Nate

 

[attachment deleted by admin]

[roopurt18]

SELECT
  s.storeid, s.address, s.zip, s.common_name,
  st.state_name, c.city_name
FROM stores s
INNER JOIN states st ON s.state_id=st.state_id
INNER JOIN cities c ON s.state_id=c.state_id AND
  s.city_id=c.city_id
WHERE 1
ORDER BY st.state_name, c.city_name, s.common_name

 

That'll get you a single result set that has the store information in addition to the city and state names.

 

Then you just loop over the entire result set once printing your results.

 

There is a catch.  If you want the state and city name to print only when it changes from one value to another, then you will have to keep track of the current state and city that you are printing from.  Every time the current values differ from the row you are printing, print the new city and state names and then update the current values.

[chronister]

The query itself is no problem for me although mine would not have been as pretty

 

I guess the thing I am having trouble wrapping my brain around is how to loop through the results and separate the data so that it prints out the way I want it to.

 

Can you give me a hint as to how to do this... I am certainly not asking you to write the whole damn thing for me, but a start on this would be greatly appreciated.

 

The way I have done this type of thing before is to run a query to get the states, then as I loop through the states, query to get the cities, and as I loop through the cities, query to get the stores for that city. That ends up making quite a few queries on the page.

 

I am thinking of doing a static page generator like is shown on the tutorial on this site. Then the queries actually don't matter as they would only run once or twice a day.

 

Thanks,

 

Nate

 

 

[cooldude832]

well mysql can't help you on the presentation too much other than using CONTAT to add stylign to the result, however what you will need to do is apply php to your loop

 

Using the query supplemented

<?php
$q = "SELECT
  s.storeid, s.address, s.zip, s.common_name,
  st.state_name, c.city_name
FROM stores s
INNER JOIN states st ON s.state_id=st.state_id
INNER JOIN cities c ON s.state_id=c.state_id AND
  s.city_id=c.city_id
WHERE 1
ORDER BY st.state_name, c.city_name, s.common_name";
$r = mysql_query($q) or die(mysql_error()."<br /><br />".$q);
if(mysql_num_rows($r) >0){
$state = "";
while($row = mysql_fetch_assoc($r)){
if($state != $row['state_name'])){
echo "<Br /><h2>".$row['state_name']."</h2><br />";
}
else{
echo "<hr /><br />";
}
echo $row['common_name']."<br />".$row['city_name'];
$state = $row['state_name'];
}
?>

I didn't style it exactly like yours but you'll get the idea