50 States, One Table?

[Presto-X]

Ok so I’m new to php so I fill that this question is going to be a newbie question for most of you php ninjas out there.

I’m working on a links page that will pull the list of links from my database, then arrange the links on my page by state. The only way I know how to set this up, I would need to use 50 tables in my database one for each of the states.

Is there a way to setup one table with a field for state then in php tell where each entry should go? I have asked this on 3 other sites including mysql.com and know one seems to have an answer.

I’m not sure if I’m describing what I’m needing to do correctly or not. Any help would be grate, thanks guys.

[micah1701]

I think your just having trouble descibing what you want to do. it sounds like its a simple fix.
you certainly dont want 50 tables. what a headache that would be.

just add a column to the one table of links that defines that links state.

what does your table look like now? here's what I'm picturing:

linkID | linkDescription | linkTitle | linkURL | state
---------------------------------------------------------
1 | a link to yahoo | yahoo! | [a href=\"http://www.yahoo.com\" target=\"_blank\"]http://www.yahoo.com[/a] | Connecticut
2 | a link to google | google | [a href=\"http://www.google.com\" target=\"_blank\"]http://www.google.com[/a] | Nevada
etc...

one way to sort this in your script is:

//first create an array of all the states
$states[0] = "Connecticut";
$states[1] = "Nevada";
$states[2] = "New Jersey";
//etc...

//now loop through your states and do a query for each one.
for($i=0; $i<50; $i++)
{
$state = $states[$i];

echo "<b>Links for the state of ".$state."</b><br>;

$query = mysql_query("SELECT * FROM linksTable WHERE state='$state' ORDER BY state");
$total = mysql_num_results($query);

//loop through results (its a loop within a loop :-)
for($k=0; $k<$total; $k++)
{
$row = mysql_fetch_array($query);
echo '<a href="'.$row[linkURL].'" title="'.$row[linkTitle].'">'.$linkTitle.'</a><br>'.$row[linkDescription];
}//end loop through query results for this state

}//end loop through all the states

[Presto-X]

Wahooo you get what I’m tying to do :O)

This is the fields I have setup in my table:
title, url, email, address, state, zip, phone, fax, map

Then on my page I want to have a div tag setup for each state with a nice border ext ext the css is the easy part I was not sure how to get the php to work with it all split up on the page by div tags and html.

Well here is the old page done in html [a href=\"http://www.k-pump.com/dealers.htm\" target=\"_blank\"]http://www.k-pump.com/dealers.htm[/a] so you can see what I want the new page to look something like. The reason I’m switching to php and using the database is I want the site’s owner to be able to add in links on is own and it does not know any thing coding :O)

[Presto-X]

Ok I played around with the code but I'm a newbie and I think I just ended up making a big mess of the code...
[code]<?PHP
ini_set('error_reporting',7); import_request_variables("GP");

$username="*****"; $password="*****"; $database="*****";
mysql_connect(localhost,$username,$password);

$states[0] = "Alabama";
$states[1] = "Alaska";
$states[2] = "Arizona";
$states[3] = "Arkansas";
$states[4] = "California";
$states[5] = "Colorado";
$states[6] = "Connecticut";
$states[7] = "Delaware";
$states[8] = "Florida";
$states[9] = "Georgia";
$states[10] = "Hawaii";
$states[11] = "Idaho State";
$states[12] = "Illinois";
$states[13] = "Indiana";
$states[14] = "Iowa";
$states[15] = "Kansas";
$states[16] = "Kentucky";
$states[17] = "Louisiana";
$states[18] = "Maine";
$states[19] = "Maryland";
$states[20] = "Massachusetts";
$states[21] = "Michigan";
$states[22] = "Minnesota";
$states[23] = "Mississippi";
$states[24] = "Missouri";
$states[25] = "Montana";
$states[26] = "Nebraska";
$states[27] = "Nevada";
$states[28] = "New Hampshire";
$states[29] = "New Jersey";
$states[30] = "New Mexico";
$states[31] = "New York";
$states[32] = "North Carolina";
$states[33] = "North Dakota";
$states[34] = "Ohio";
$states[35] = "Oklahoma";
$states[36] = "Oregon";
$states[37] = "Pennsylvania";
$states[38] = "Rhode Island";
$states[39] = "South Carolina";
$states[40] = "SouthDakota";
$states[41] = "Tennessee";
$states[42] = "Texas";
$states[43] = "Utah";
$states[44] = "Vermont";
$states[45] = "Virginia";
$states[46] = "Washington";
$states[47] = "West Virginia";
$states[48] = "Wisconsin";
$states[49] = "Wyoming";

for($i=0; $i<50; $i++)
{

$title = $title[$i];
$url = $url[$i];
$email = $email[$i];
$address = $address[$i];
$city = $city[$i];
$state = $states[$i];
$zip = $zip[$i];
$phone = $phone[$i];
$fax = $fax[$i];
$map = $map[$i];

echo "<b />Links for the state of ".$state."</b /><br />";

$query = mysql_query("SELECT * FROM dealers WHERE state='$state' ORDER BY state");

$total = mysql_num_results($query);

for($k=0; $k<$total; $k++)
{
$row = mysql_fetch_array($query);
echo '<div style=\"border:1px solid #E0E0E0; padding:5px;\" />';
echo '<a href=\"'.$row[url].'\" title=\"'.$row[title].'\" />'.$title.'</a /><br />
<a href=\"'.$row[email].'\" title=\"'.$row[email].'\" />'.$email.'</a /><br />'
.$row[address].'<br />'
.$row[city].', '.$row[state].' '.$row[zip].'<br />
<a href=\"'.$row[map].'\" title=\"View Map\" />View Map <img src=\"../k_pump/zoom.gif\" /></a /><br />'
.$row[phone].'<br />'
.$row[fax].'<br />';
echo '</div />';

}

}
?>[/code]