Page 1 of 1
[SOLVED] State dropdown too slow.
Posted: Thu Nov 25, 2004 3:15 am
by jim_73_mk1
I use the following code to populate a dropdown list of state abbreviations in a form. It works fine but is too slow and I want to speed it up. The code also marks the users perviously selected option as the selected option if the forms which use this fail validation. This works but takes too long - about 20 seconds on my server:
http://jim.dynalias.net/websales/newaccount.php - and I want to speed it up.
Thanks!
Jim
Code: Select all
<?php
function state_dropdown()
{
$query = 'SELECT abbrev FROM states';
db_connect();
$result = mysql_query($query);
while ($row = mysql_fetch_row($result)) {
// make user selection the selected option
if ($_SESSION['state'] == $row[0]) {
echo "<option selected>$row[0]</option>";
$selected = 'y';
} else {
echo "<option>$row[0]</option>";
}
}
if (!$selected)
echo '<option selected></option>';
db_close();
}
?>
Posted: Thu Nov 25, 2004 6:18 am
by thomas777neo
How many records are in your states table?
There are 65 records in the states table.
Posted: Thu Nov 25, 2004 6:21 am
by jim_73_mk1
There are 65 records in the states table.
Try This
Posted: Thu Nov 25, 2004 7:55 am
by thomas777neo
I modified the code, try it and tell me if it runs faster
Code: Select all
<?php
function state_dropdown()
{
db_connect();
$sql = "SELECT abbrev FROM states";
$query = mysql_query($sql);
$rows = mysql_num_rows($query);
echo "<select name="state">";
$i = 0;
while ($rows > $i)
{
$arrayResult[$i]["abbrev"] = mysql_result($query,$i,"abbrev");
$abbrev = $arrayResult[$i]["description"];
if ($state == $abbrev)
{
$selected = "selected";
} //if ($submitted_value == $abbrev)
else
{
$selected = "";
} //if ($submitted_value == $abbrev)
echo "<option value="".$abbrev."" ".$selected.">".$abbrev."</option>";
$i++;
} //while ($rows > $i)
echo "</select>";
db_close();
} //function state_dropdown()
$x = state_dropdown();
?>
Re: State dropdown too slow.
Posted: Thu Nov 25, 2004 8:07 am
by timvw
jim_73_mk1 wrote:I use the following code to populate a dropdown list of state abbreviations in a form. It works fine but is too slow and I want to speed it up. The code also marks the users perviously selected option as the selected option if the forms which use this fail validation. This works but takes too long - about 20 seconds on my server:
http://jim.dynalias.net/websales/newaccount.php - and I want to speed it up.
odd.. first you can time how long it takes to run the query. then time how long it takes to retrieve the results...
if the first part takes extremely long, get a better mysql server :p
if the second part takes this long, change your query so that it also calculates wether something is selected or not. this way, you offload some cycles from php to mysql.
Posted: Fri Nov 26, 2004 12:54 am
by jim_73_mk1
Thomas thanks for the help!
When trying your script I couldn't get it to function so I rewrote what I had with the ideas in your script. The page now loads in about seven seconds. I think what was happening before was that all the trips between PHP and Apache was slowing it way down. The new version (below) eliminates all that until the html string is completed.
Tim - I actually think the slowness was caused by my poor coding not from a slow server. True though that a faster server would speed things up considerably.
Any more ideas on how to speed this thing up?
Code: Select all
<?php
function state_dropdown()
{
$c = 0; // clear counter
$options = NULL; // clear options string
$state = $_SESSION['state']; // save selected variable
$query = 'SELECT abbrev FROM states';
db_connect();
$result = mysql_query($query);
$row = mysql_num_rows($result);
while ($c < $row) {
$abbrev = mysql_result($result, $c);
if ($state == $abbrev) {
$options .= '<option selected>' . $abbrev . '</option>';
$selected = 'y';
} else {
$options .= '<option>' . $abbrev . '</option>';
}
$c++;
}
if (!$selected)
$options .= '<option selected></option>';
echo $options;
db_close();
}
?>
Posted: Fri Nov 26, 2004 1:56 am
by timvw
if the mysql has some spare cpu cycles:
Code: Select all
function state_dropdown()
{
$options = "";
$state = $_SESSION['state'];
$query = "SELECT abbrev, IF(abbrev = '$state', ' SELECTED', '') AS selected FROM states";
db_connect();
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result))
{
$options .= "<option{$row['selected']}>{$row['abbrev']}</option>";
}
db_close();
echo $options;
}
MySql based function....
Posted: Sun Nov 28, 2004 11:03 am
by jim_73_mk1
Tim,
Thanks for your reply and script. I tried it out and it works well, about the same speed as my PHP script. Being that your code has fewer lines (I'm always for fewer lines!) and the added benifit of showing a state on initial load I am using your script for now!
Thanks again!
Jim