Need help with putting links in static info according to query results

[jomama]

Here's one that I haven't been able to do, yet.

I have a page with a list of 50 states and some have links to another page if there are listing in that particular state. Currently, this page is static and I have to make sure there are links to states that have listing and that there aren't links to states that don't have listings.

So here is what I want to do. I want to translate the results of the following query to display a page that looks exactly like the current page( http://www.actorspost.com/directories/talentagencies.php )and that displays all the states that inserts links dynamically into the states that have listings.:

select count(*) as Count, State,
DATE_FORMAT(StartDate, '%c/%e/%Y') as StartDate,
DATE_FORMAT(EndDate, '%c/%e/%Y') as EndDate
from Ad where State = State and Approved = '1' and CompanyType = 'ta' group by State

And it gets even trickier. The states are listed in four columns so that the user doesn't have to scroll.

Can someone help please, I am stumped.

[bastien]

determine if there are links?

ie if count>0 then create the link
else show the statename

is that right?

[jomama]

Exaclty, but how do I skin this cat and how do I loop through all the states and make sure that they correspond with the query results?

Thanks in advance

[bastien]

<?
$sql="select count(*) as Count, State, 
DATE_FORMAT(StartDate, '%c/%e/%Y') as StartDate, 
DATE_FORMAT(EndDate, '%c/%e/%Y') as EndDate 
from Ad where State = State and Approved = '1' and CompanyType = 'ta' group by State ";

$result=mysql_query($sql); [b]//i'll leave the database link up to you[/b]

if (!$result){
   echo "There was a problem with the page. try again";
}else{
   [b]//there are results so[/b]
   echo "<table><tr>";   [b]//start the table[/b]

   $i=1;  [b]//initliaze a counter[/b]
   while ($rows=mysql_fetch_array($result)){

  [b] //assign the data to some variables - not really needed but clearer when coding[/b]
   $state=$rows['State'];
   $count=$rows['Count'];
   $link = $rows['link];   [b]//I don't see a link here. Where is it supposed to go?[/b]

  if($count!=0){
      echo "<a href='$link'>$state</a><br>";
  } else {
      echo "$state<br>";
  }   [b]//close if / then loop[/b]
  if ($i==13){                    [b] //start next column[/b]
     echo "</td><td>;
  }  [b]//close if/then loop[/b]
  $i++;     [b]//increment the counter[/b]

   } [b] //close the while loop[/b]
echo "</tr></table>";
} [b]//close the main if/then loop[/b]

?>

hth

[jomama]

First of all, thanks so far. But the problem with the code you wrote was that if there were no listings found in the databse for Colorado, for example, it would not print "Colorado". So that is what I am trying to solve now. Read on🙂

I added some things to your code to try to make it work better but I know it isn't there yet. The "state" values are in the form of "nv" for Nevada, for example, so I need to run that long "if ("ny" == $state) $VLocation = "New York";" series so that the state's description or $VLocation prints to the page while I need the database's stored short version("nv") to pass the URL variable in the link for each state.

Now we have come to the point. I need all the different locations you see in the code below to be displayed whether or not there was an entry in the database that matched the particular location.

p.s. I bolded the code I added below

Thanks again and again.

<?
//there are results so
echo "<table><tr>"; //start the table

$i=1; //initliaze a counter
while ($rows=mysql_fetch_array($result)){

//assign the data to some variables - not really needed but clearer when coding
$state=$rows['State'];
$count=$rows['Count'];

//Figure out state's full name or description
$state = strtolower($state);

//spell out location
if ("al" == $state) $VLocation = "Alabama";
else if ("ak" == $state) $VLocation = "Alaska";
else if ("az" == $state) $VLocation = "Arizona";
else if ("ar" == $state) $VLocation = "Arkansas";
else if ("ca" == $state) $VLocation = "California";
else if ("co" == $state) $VLocation = "Colorado";
else if ("ct" == $state) $VLocation = "Connecticut";
else if ("de" == $state) $VLocation = "Delaware";
else if ("fl" == $state) $VLocation = "Florida";
else if ("ga" == $state) $VLocation = "Georgia";
else if ("hi" == $state) $VLocation = "Hawaii";
else if ("id" == $state) $VLocation = "Idaho";
else if ("il" == $state) $VLocation = "Illinois";
else if ("in" == $state) $VLocation = "Indiana";
else if ("ia" == $state) $VLocation = "Iowa";
else if ("ks" == $state) $VLocation = "Kansas";
else if ("ky" == $state) $VLocation = "Kentucky";
else if ("la" == $state) $VLocation = "Louisiana";
else if ("me" == $state) $VLocation = "Maine";
else if ("md" == $state) $VLocation = "Maryland";
else if ("ma" == $state) $VLocation = "Massachusetts";
else if ("mi" == $state) $VLocation = "Michigan";
else if ("mn" == $state) $VLocation = "Minnesota";
else if ("ms" == $state) $VLocation = "Mississippi";
else if ("mo" == $state) $VLocation = "Missouri";
else if ("mt" == $state) $VLocation = "Montana";
else if ("ne" == $state) $VLocation = "Nebraska";
else if ("nv" == $state) $VLocation = "Nevada";
else if ("nh" == $state) $VLocation = "New Hampshire";
else if ("nj" == $state) $VLocation = "New Jersey";
else if ("nm" == $state) $VLocation = "New Mexico";
else if ("ny" == $state) $VLocation = "New York";
else if ("nc" == $state) $VLocation = "North Carolina";
else if ("nd" == $state) $VLocation = "North Dakota";
else if ("oh" == $state) $VLocation = "Ohio";
else if ("ok" == $state) $VLocation = "Oklahoma";
else if ("or" == $state) $VLocation = "Oregon";
else if ("pa" == $state) $VLocation = "Pennsylvania";
else if ("ri" == $state) $VLocation = "Rhode Island";
else if ("sc" == $state) $VLocation = "South Carolina";
else if ("sd" == $state) $VLocation = "South Dakota";
else if ("tn" == $state) $VLocation = "Tennessee";
else if ("tx" == $state) $VLocation = "Texas";
else if ("ut" == $state) $VLocation = "Utah";
else if ("vt" == $state) $VLocation = "Vermont";
else if ("va" == $state) $VLocation = "Virginia";
else if ("wa" == $state) $VLocation = "Washington";
else if ("wv" == $state) $VLocation = "West Virginia";
else if ("wi" == $state) $VLocation = "Wisconsin";
else if ("wy" == $state) $VLocation = "Wyoming";
else if ("dc" == $state) $VLocation = "District of Columbia";
else if ("australia" == $state) $VLocation = "Australia";
else if ("england" == $state) $VLocation = "England";
else if ("canada" == $state) $VLocation = "Canada";
else if ("other" == $state) $VLocation = "Other Countries";
else $VLocation = "";

  if($count!=0){
      [B]echo "<a href='listings.php?companytype=ta&state=$state'>$VLocation</a><br>";[/B]
  } else {
      echo "$state<br>";
  }   //close if / then loop
  if ($i==13){                     //start next column
     echo "</td><td>;
  }  //close if/then loop
  $i++;     //increment the counter

} //close the while loop
echo "</tr></table>";
} //close the main if/then loop

?>

[bastien]

data completeness...if Colorado was not existant in the Db table, then how could the code read it? It would be better for you to have all the states/etc listed and leave the links as 0 or NULL to ensure that the app works right...

also your table structure could inlcude the short name (NV) and long name...it would make the linking easier as the echo would then show this:

echo "<a href='$shortname.php'>$longname</a>";

this would also cut down on the code you need to maintain and utlizes the database better....

[jomama]

I had to change some things to make your code worked but yes, adding the description in a different column to the record (i.e. NV and Nevada) worked. You are the bomb....thanks

josu