15242 row query -> Error 500/ how to fix it

[SystemLord]

Hey guys hope someone can help me out with this:

 

The Plan:

To read my MySQL database and a row, insert the results into an array that is then used to create a drop down menu.

I don't want duplicates in the drop down menu so I check to see if it already in the array. I provided some comments in the following php code so you know what I am trying to do.

 

The Code so far:

[code]

$results = mysql_query("Select * FROM table_name WHERE model LIKE '%$query%''") // Get data from mySQL that match

while($row=mysql_fetch_array($results)) //Loop though all my results that matched the query
{

$model=$row["modle"];  //create variable model that matches current row modle
$modleCount=0;          // Set modleCount to 0 on every new loop

for($run=0;$run<=sizeof($part_numvaluesArray);$run++)  // Run this loop for every value in the array
{
      if($modle==$modlevaluesArray[$run])        // If the current modle is already in the array then add 1 to Count
        {
           $modleCount++;            //Add 1
        }
}
      if($modleCount==0)                                // If 0, so model is not yet in the Array then enter loop
       {
           $modlevaluesArray[]= "$modle";           // And add model to array
       }  
}

.
.
.
.
.
//Then output the created array to a drop down // The code below should be irrelevant to the problem
//I just included it so you see what i am trying to do

Car Model: <br/>';
$modelvalues = $modelvaluesArray;  
   echo '<select class="inputsearch" name="modelMSearch" onChange="this.form.submit();">';
   for($x = 0; $x < count($modelvalues); $x++) 
   { 
   		 // write "selected" if the value matches the one posted 
         if($modelvalues[$x] == $modelMSearch) 
         { 
             $selected = ' selected'; 
          }else{ 
             $selected = ''; 
          } 
         // print the option
		echo '<option value="'.$modelvalues[$x].'"'.$selected.'>'.$modelvalues[$x].'</option>'; 	
  }   echo '</select><br/>';

 

 

The Problem:

Since my database has 15242 rows in it, you see that the looping thought the array will increase very quickly since i have to run the loop at least 15242 times but i am increasing the array also.

This gives me an internal Server Error 500; so i have to figure out a way to reduce the massive looping through the increasing array.

So my question is how can accomplish my goal without all the massive looping.

 

I know that its has to do with the 15242 rows because when I limit it to 7500 or less I don’t get the error [the run time for it is about 8 sec. or so I would need to find a way to reduce that anyhow.

   

Additional required Info:

MySQL server version: MySQL5.0

 

No error message from mysql_error() server just stops. Error 500.

    the raw MySQL statement in question [in a CODE block, and without any PHP variables]

 

Table Structure via Create Table:

 

DROP TABLE IF EXISTS table_name;

CREATE TABLE table_name (part VARCHAR(255), car_model VARCHAR(255));

INSERT INTO table_name VALUES('AC100C', 'Eagle');

.

.

.

.

.

And then 15241 more

 

 

 

That’s about it, hope I got all my bases covered and someone can help me figure out a solution.

Thx

 

[fenway]

I'm confused... if you don't want duplicates, why not have the DB handle this for you?

[SystemLord]

You mean why are there duplicates in the database in the first place?

 

The database is a bit more complex then what i have shown in the post, but this should not be relevant to my problem. Anyhow, the database matches car makes, car models, years, engine liter and configuration to a cylinder head database.

 

Lets take this for example:

INSERT INTO part_car_match VALUES('AC101C', '1984-1987', "", 'Jeep', 'Wagoneer', "", "", "", '2.5L L4 150 CID');
INSERT INTO part_car_match VALUES('AC101C', '1984-2000', "", 'Jeep', 'Cherokee', "", "", "", '2.5L L4 150 CID');
INSERT INTO part_car_match VALUES('AC101C', '1986-1992', "", 'Jeep', 'Comanche', "", "", "", '2.5L L4 150 CID');

 

The Jeep Cherokee 2.5 Liter L4 between 1984 to 2000 is matched to the AC101C cylinder head.

Furtherdown on the list howerver there might be a

Jeep Cherokee 3.0 Liter matched to a different cylinder head.

 

By just taking all the results from the "model" row i would get duplicates of the 'Cherokee' which i don’t want.

Is this your question?

 

A possible solution to my problem might be to get ride of the entire loop all together and just load the array from a create database table just containing the current individual models. However, because this database will be updated somewhat frequently i would like to do this on the fly if possible instead of 'hard coding' the array of models.

 

[PFMaBiSmAd]

Start by finding what is causing the 500 error. Add the following two lines immediately after your first opening <?php tag -

 

ini_set ("display_errors", "1");
error_reporting(E_ALL);

 

You want a DISTINCT list of models? Do this -

 

Select DISTINCT model FROM table_name WHERE model LIKE '%$query%'

 

You might want to add an ORDER BY statement to get the results in some specific order.

[SystemLord]

Since i am pulling out more then just one array, not only model but also make and engine liter i am ordering not the mySQL query but the arrays once they have been populated.

using    sort($ArrayName);

 

Just added your 2 lines and i get a mess of reports :-) F reeking 1&1 guy could of told me that 2 instead of hunting for a non existing 500 error log file....

 

I get this, a few ( 8 ) of the "Undefined variable" errors and then a bucked full (100+) of the 'Undefined offset" error switching off between line 18 and line 34 which correlate to this php code

(LINE 18) for($run=0;$run<=sizeof($partvaluesArray);$run++){if($part==$partvaluesArray[$run]) // My Loop for getting the stuff into the array
(LINE 34) for($run=0;$run<=sizeof($car_makevaluesArray);$run++){if($car_make==$car_makevaluesArray[$run]) // Same just for a different variable

.
.
.
Notice: Undefined variable: car_makevaluesArray in URL/php_500error.php on line 34
Notice: Undefined offset: 1 in URL/php_500error.php on line 18
Notice: Undefined offset: 1 in URL/php_500error.php on line 34
.
.
.

 

Well its defiantly something with the loop; Looks like maybe a mistake in my table? Ill look into this in more detail in a few minutes, once i done with my Anthro HW. :-)

 

 

So this query statement:

Select DISTINCT model FROM table_name WHERE model LIKE '%$query%'

only selects distinct model rows from my table?

I don't think i can use that since i have to save the matching part numbers to further compare to a second database. But thanks for the tip, didn't know that existed; might come in handy later on.

 

 

 

[EDIT by SystemLord]

 

As a sub question related to this, whats the best way to search for a matching date? Year only.

The .xls sheet we use sets dates in this format

1983-1985

This cylinder head is used from 1983 to 1985. The current, and painful way i do this as of now, to list these dates in a separate column called year_search which looks like this:

83 84 85

So that if someone searches for the year 84 the mySQL query will find a match via    LIKE '%$year_s%'

 

Any ideras on how to do this simply on the fly? Or a excel statement that could be used in the original excel file before uploading it to mySQL.

 

[END EDIT]

 

 

[PFMaBiSmAd]

The reason for the two Undefined offset: 1 errors are because your for() loop is using a less-then or equal <= comparison with the sizeof() an array. Because the index numbers are one less than the size, you should only be doing a less-than comparison. Also, putting sizeof($partvaluesArray) in the for() loop is the slowest way of executing a loop. And comparing each value with what is already in the array is also very slow. Just put all the values into an array and use array_unique() to remove duplicates from the array. However, you don't even need to do this because the DISTINCT keyword a the right form of a query will do this for you.

 

For making the drop-down menus, using separate queries for each drop-down with the DISTINCT keyword is going to be at least 100 times more efficient than what you are doing now. The query will be executed using compiled code in the database engine to do the searching and removal of duplicates. What you are doing now with some slow parsed/tokenized/interpreted php code is the slowest way to process a large list of data.