Jump to content

50 States, One Table?


Presto-X

Recommended Posts

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.
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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)
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.