A question of efficiency: 50 States in an Array or in mysql??

[cunoodle2]

Which is going to use less CPU resources in terms of allowing the user to select their state from like a drop down menu?

 

Looping through an array and selecting an individual state out of it?

 

OR

 

Looking it up in mysql, doing a loop and allowing an individual state to be selected?

 

The site that I'm working on is going to be looking up state names, FIPS and most likely abbreviations very often.  I could either store them all in a multi-dimensional array or in a mysql table.  Just trying to use the least amount of resources.

[Maq]

Either way you're putting them in an array. 

 

The only difference with MySQL is that you'll have them permanently stored in a DB, so if you're going to reference them multiple times it's better to extract the the data from the DB.  It will be easier to keep track, maintain, mine data, and read.  This is kind of the point of databases.

 

We're only talking 50 states here, which shouldn't take up relatively any resources.  If you plan on building upon this implementation, then I would suggest storing them in a database.

[tivrfoa]

You will need to have these datas in the database anyway.

I don't know exactly what you want, but if just to display the states in the select, then you could generate this select from the database just once, copy the generated html and use it direct in the code.

You won't have problems, because states don't change very often. xD

[cunoodle2]

I was going to use the html code all pre-written but then there is the matter of having the selection already "SELECTED" on like user/member update pages.  The only way around that would be a series of php brackets and if statements like this..

 

<select name="state">
<option value="1" <?php echo ($state ==1)?"SELECTED"; ?> >Alabama</option>
<option value="2" <?php echo ($state ==2)?"SELECTED"; ?> >Alaska</option>
</select>

 

I don't think that would be much better either.  I guess I was just thinking that since it never ever changed it would be nice to just have it hard coded in a file but it is not looking like it would be beneficial at all.

 

 

[Maq]

You can store the selection in the database for that use.  When creating the drop down you can dynamically compare the user's selection with the state id (or however you stored the user's selection) and if it matches then echo "SELECTED".

 

There's no need for hard-coding.

[cunoodle2]

K.  Here is my code.  I'm now getting an error and I cannot figure it out..

 

<?php
//print a drop down menu of all 50 states.  If "selected" is set then it will set that in the print out itself
function PrintStates($selected="")
{
//commented out for now but still doesn't work if un-commented
//require_once "/db-connect-file.php";

//develop a query
$sql = "SELECT * FROM `StateList` ORDER BY Name ASC;";

//prepare SQL query using pdo statement
$stmt = $conn->prepare($sql);

//if the statement was successfully executed then loop through and print out all states in a drop down menu
if($stmt->execute(array()))
{
	echo "<select name=\"state\">\n";

	//loop through all the results one at a time
	while($result = $stmt->fetch(PDO::FETCH_ASSOC))
	{
		echo "<option value=\"".$result["id"]."\" ";
		echo ($result["id"] == $selected)?"SELECTED >":">"; 
		echo $result["Name"]." (".$result["Code"].")</option>\n";
	}

	echo "</select>\n";
}
}

?>

 

The error I'm getting is.. "Fatal error: Call to a member function prepare() on a non-object"

I know my db connection file is valid as I used it all the time on other pages.  I've tried including/not including it and I'm still getting this error.  Any idea why?  I'm sure it has something to do with the function itself because if I remove the function brackets and just execute the script it works fine.

[Daniel0]

You will have to pass the $conn variable by argument to your function. Lookup the word "scope" in the manual.

[cunoodle2]

You will have to pass the $conn variable by argument to your function. Lookup the word "scope" in the manual.

Why do I need to pass it in if I'm connecting to it within the function as well?  I'm aware of scope but not understanding why I cannot include a connection from within the function itself.

[Daniel0]

You shouldn't open a new connection in that function just to get that states array.

[cunoodle2]

You shouldn't open a new connection in that function just to get that states array.

I agree with you 100%.  I didn't want to open a new connection but wanted to determine why it was not working.  I'm aware of scope and using variables from within a function only and how they will not work on the outside unless they are initially declared differently.  This seems like a bug to me.

 

It's not a mater of making another connection in each function (we both agree this is just plain dumb and a waste of resources).  I'm just more so curious more so as to WHY it would not work with the connection statement being within the function.  Anyone have any ideas?

[haku]

You are calling $conn inside your function, but it doesn't exist there.

 

If it exists outside the function, then inside you can declare it as a global:

 

global $conn;

[Daniel0]

If it exists outside the function, then inside you can declare it as a global:

 

A thousand kittens will die each time you use the global keyword.