To add or NOT to add links in a loop...that's the question.

[markkanning]

Hey Folks,
This is a tricky 2-part dilemma about outputting looped data from a DB and, possibly, joining tables in the process.

Here's the deal: I want to dynamically create a list of state names(Montana, New Jersey, etc.) and then add a link to that text depending on whether or not there is data present in each state's corresponding data table. In other words, there will be 2 tables: something like "state_names" and "state_info".

Problem #1: How do I add an "if" statement in the middle of the proper loop that will add or not add a link?
Problem #2: How do I get that "if" statement to confirm the presence or absence of data in some other table?
Problem #3: How do I get red wine stains out of white carpet? (optional)

Illumination on any part of the above is most welcome!
Mark

[artacus]

[code]
SELECT name.name, COUNT(info.id) AS cnt
FROM state_names AS name
LEFT JOIN state_info AS info ON name.id = info.state_id
GROUP BY name.id ORDER BY name.name
[/code]

in php
[code]
while($row = mysql_fetch_assoc($result)) {
  $link = ($row['cnt'] > 0) ? "<a href='?state=$row[abrev]'>$row[name]</a>" : $row[name];
}[/code]

[markkanning]

Does the reference to "id" in the query suggest that there must be an auto-incremented id row present in the "state_names" table?

Mark

[artacus]

No, it suggest that I don't know what your table structure is like. ;)

[markkanning]

I've yet to define a table structure for "state_names" yet. I was simply going to have an id, name, and (believe it or not) image map coordinates as there'll be a map of the U.S. on the page as well.

A question about the link code:
"<a href='?state=$row[abrev]'>$row[name]</a>"

If I didn't want to abbreviate the states in the href, could I just as well have:
"<a href='?state=$row[name]'>$row[name]</a>"

Mark

[markkanning]

Duuuuuuh! I just realized that your reference to abbreviation was an assumption that I may have had that as a row in the table.

Sorry for the brain fart.

Thanks, Artacus!  You've been a big help, brother.

[artacus]

LOL. No problem.

[markkanning]

Oh, brother...something just dawned on me.

I may have misrepresented the relationship between the tables. In all, there will be 51 tables in this database. One will be "state_names". All the others would be called something like "montana_info", "newjersey_info", etc. The script would have to check a specific row in "state_names", say Montana, against its' corresponding table, "montana_info". It seems to me the query and the "while" loop you quoted earlier:

[code]
SELECT name.name, COUNT(info.id) AS cnt
FROM state_names AS name
LEFT JOIN state_info AS info ON name.id = info.state_id
GROUP BY name.id ORDER BY name.name

while($row = mysql_fetch_assoc($result)) {
  $link = ($row['cnt'] > 0) ? "<a href='?state=$row[abrev]'>$row[name]</a>" : $row[name];
}
[/code]

...only takes into account 2 tables. Am I right, or am I DUUUUUUUUUUUUUH?

Mark

[fenway]

Why would have you each state's info in a different table?  Their underlying structure is probably identical, is it not?