codeline Posted February 21, 2011 Share Posted February 21, 2011 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.? Link to comment https://forums.phpfreaks.com/topic/228425-printing-us-states-listings-for-each-state/ Share on other sites More sharing options...
requinix Posted February 21, 2011 Share Posted February 21, 2011 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 Link to comment https://forums.phpfreaks.com/topic/228425-printing-us-states-listings-for-each-state/#findComment-1177795 Share on other sites More sharing options...
kickstart Posted February 21, 2011 Share Posted February 21, 2011 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 Link to comment https://forums.phpfreaks.com/topic/228425-printing-us-states-listings-for-each-state/#findComment-1177800 Share on other sites More sharing options...
codeline Posted February 21, 2011 Author Share Posted February 21, 2011 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'] Link to comment https://forums.phpfreaks.com/topic/228425-printing-us-states-listings-for-each-state/#findComment-1177832 Share on other sites More sharing options...
codeline Posted February 21, 2011 Author Share Posted February 21, 2011 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.. Link to comment https://forums.phpfreaks.com/topic/228425-printing-us-states-listings-for-each-state/#findComment-1177839 Share on other sites More sharing options...
codeline Posted February 21, 2011 Author Share Posted February 21, 2011 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.. Link to comment https://forums.phpfreaks.com/topic/228425-printing-us-states-listings-for-each-state/#findComment-1177842 Share on other sites More sharing options...
kickstart Posted February 21, 2011 Share Posted February 21, 2011 Hi When you assign state to previous state you just use a single = sign. You have an extra ! in there All the best Keith Link to comment https://forums.phpfreaks.com/topic/228425-printing-us-states-listings-for-each-state/#findComment-1177894 Share on other sites More sharing options...
codeline Posted February 21, 2011 Author Share Posted February 21, 2011 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. Link to comment https://forums.phpfreaks.com/topic/228425-printing-us-states-listings-for-each-state/#findComment-1177905 Share on other sites More sharing options...
kickstart Posted February 21, 2011 Share Posted February 21, 2011 Hi Just change it from a left outer join to an inner join All the best Keith Link to comment https://forums.phpfreaks.com/topic/228425-printing-us-states-listings-for-each-state/#findComment-1177944 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.