[SOLVED] State dropdown too slow.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jim_73_mk1
Forum Newbie
Posts: 15
Joined: Mon Nov 24, 2003 9:42 pm
Contact:

[SOLVED] State dropdown too slow.

Post 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();
}
?>
Last edited by jim_73_mk1 on Sun Nov 28, 2004 11:06 am, edited 1 time in total.
User avatar
thomas777neo
Forum Contributor
Posts: 214
Joined: Mon Mar 10, 2003 6:12 am
Location: Johannesburg,South Africa

Post by thomas777neo »

How many records are in your states table?
jim_73_mk1
Forum Newbie
Posts: 15
Joined: Mon Nov 24, 2003 9:42 pm
Contact:

There are 65 records in the states table.

Post by jim_73_mk1 »

There are 65 records in the states table.
User avatar
thomas777neo
Forum Contributor
Posts: 214
Joined: Mon Mar 10, 2003 6:12 am
Location: Johannesburg,South Africa

Try This

Post 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();
?>
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: State dropdown too slow.

Post 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.
jim_73_mk1
Forum Newbie
Posts: 15
Joined: Mon Nov 24, 2003 9:42 pm
Contact:

Post 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();
}

?>
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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;   
}
jim_73_mk1
Forum Newbie
Posts: 15
Joined: Mon Nov 24, 2003 9:42 pm
Contact:

MySql based function....

Post 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
Post Reply