[SOLVED] Selecting data from 2 tables with data-checking loop

[markkanning]

Hey ya'll,

I've got a MySQL select-query-with-loop problem. I'm putting together a database-driven, "store locator" page for a wholesale foods outlet. The page will have a list of the 50 states that's being created from one table, AND that will be linked depending on whether corresponding store data for that state actually exists in another table. I'm figuring the query might look something like...

select states.stateid, states.statename, stores.stateid from states, stores where states.stateid = stores.stateid

...but I'm not sure as I'm stumped as to how the following loop will work.

 

I'm guessing the loop may be something like...

for ($i=0; $i <$num_results; $i++) {
$row = mysql_fetch_array($result);
   if (no corresponding table data for this state) {
      echo $statename."<br>\n";
   }
   else {
      echo "<a href=\"?stateid=".$stateid."\">$statename."</a><br>\n";
   }
}

 

Am I on the right track?

 

 

[effigy]

Your where clause is only going to pull states that have stores. If you want to list states that do not have stores, you need to use the LEFT JOIN syntax, which will include every state and give you NULL store data when a state is without store.

[markkanning]

Good tip, man!

 

So, could I do something like...

SELECT states.stateid, states.statename FROM states LEFT JOIN stores ON states.stateid = stores.stateid

 

[markkanning]

Hold up! I just registered what you are saying the "where" clause is doing. In other words, I don't need a where clause? How, then, do I associate the stateid in the "states" table with any row(s) of stateid in the "stores" table?

[effigy]

If you say where states.stateid = stores.stateid, you're only going to get states that have stores.

If you say FROM states LEFT JOIN stores ON states.stateid = stores.stateid, you're going to get all the states, even if they don't have stores.

 

Both of these statements join the tables; only the data they return differs. See this example.

[markkanning]

I am laughing out loud, brother. It just so happens I got the syntax for my last query adjustment from that very Tizag page about 10 minutes ago!

 

Thanks for the input, effigy!

 

Mark