Help me code for speed.

[njdubois]

I have the following code, and it takes FOREVER.  How could I speed it up?

	$Car_Info_All_SQL="SELECT DISTINCT make FROM car_make ORDER BY make ASC";

	$Car_Info_All_results=mysql_query($Car_Info_All_SQL);

	$make_html = '<select id="make" name="make" onchange="update_auto_dropdowns(this.id);" onfocus="" onblur="" style="font-size:10px;"><option value="">select make ...</option>';
	
	while ($row = mysql_fetch_array($Car_Info_All_results)) {
		if($row['make']!='') {
			if ( strtoupper($row['make']) == $_GET['make'] ) {
				$make_html.='<option value="'.strtoupper($row['make']).'" SELECTED>'.strtoupper($row['make']).'</option>';
			}
			else {
				$make_html.='<option value="'.strtoupper($row['make']).'">'.strtoupper($row['make']).'</option>';
			}
		}
	}

	$make_html.='</select>';


	$Car_Info_All_SQL="SELECT DISTINCT model FROM car_make ORDER BY model ASC";

	$Car_Info_All_results=mysql_query($Car_Info_All_SQL);

	$model_html = '<select id="model" name="model" onchange="update_auto_dropdowns(this.id);" onfocus="" onblur="" style="font-size:10px;"><option value="">select model ...</option>';
	
	while ($row = mysql_fetch_array($Car_Info_All_results)) {
		if($row['model']!='') {
			if ( strtoupper($row['model']) == $_GET['model'] ) {
				$model_html.='<option value="'.strtoupper($row['model']).'" SELECTED>'.strtoupper($row['model']).'</option>';
			}
			else {
				$model_html.='<option value="'.strtoupper($row['model']).'">'.strtoupper($row['model']).'</option>';
			}
		}
	}

	$model_html.='</select>';



	$Car_Info_All_SQL="SELECT DISTINCT trim FROM car_make ORDER BY trim ASC";

	$Car_Info_All_results=mysql_query($Car_Info_All_SQL);

	$trim_html = '<select id="trim" name="trim" onchange="update_auto_dropdowns(this.id);" onfocus="" onblur="" style="font-size:10px;"><option value="">select trim ...</option>';
	
	while ($row = mysql_fetch_array($Car_Info_All_results)) {
		if($row['trim']!='') {
			if ( strtoupper($row['trim']) == $_GET['time'] ) {
				$trim_html.='<option value="'.strtoupper($row['trimel']).'" SELECTED>'.strtoupper($row['trim']).'</option>';
			}
			else {
				$trim_html.='<option value="'.strtoupper($row['trimel']).'">'.strtoupper($row['trim']).'</option>';
			}
		}
	}

	$trim_html.='</select>';

Thanks

 

Nick

[mac_gyver]

are you really selecting ALL the model information withing knowing and limiting it by a selected make and also selecting ALL the trim information without knowing the model and limiting it by the selected model?

[njdubois]

I don't know what they will pick first.  Make or Model, though I'm sure I could ignore trim.

 

The actual use of it has to be as simple and pain free as possible.  I can't say, do you have either a make or model?

 

I can't assume they are going to always get the make first.

 

How else should I do it?

 

I can't just have a textbox, cause I have to assume my target user can't spell.

 

I understand that populating the dropdown with so much is what's causing it to take so long.  I need a faster way to present the user with the full list of Make, and the full list of Model.

 

thanks!

 

Nick

 

[edit]The customer may say I have a "Contour SVT"  and the user may not know that a Contour is a Ford.[/edit]

[davidannis]

Why not force them to select the make first, just hide the dropdown for model until they select make, then use javascript to show the model selection and Ajax to populate it?

[njdubois]

Anywhere else I use the auto database, that is exactly how I do it.  Send the selected Make over ajax... fill the model select....send the model over...fill the trim.  The user has to, absolutely must be able to pick either a make or a model.  It is the only way this project is going to work.  It has to be simple, simple simple.

[boompa]

I think your database is fundamentally flawed if you are storing make, model, and trim in one table. Each of those should be in a separate table related by foreign keys. This is how relational database systems such as MySQL are used correctly and efficiently.

 

A make has many models

A model has many trims

[njdubois]

I know for a fact that my database is way...WAY far from perfect.  I learn as I go, a major overhaul based on all the things I have learned over the past few years is planned.  That is exactly one of those things I want to do.  Just.... ha, when.. who knows.  This project is the kind of project that is never done.  Always changing.

 

Changing over the auto database would be easy.  I don't understand what you mean by foreign key though?  Also, would splitting the auto table, into 3 tables increase the speed by a noticed factor??

 

Thanks

 

Nick