Help with optimizing a php query

[Failing_Solutions]

Hi guys and gals,

 

I am working on refreshing our inventory in a mysql database and have finished writing a page that works, but it probably isn't optimized.

 

Would you mind looking at this code and seeing if there is a better way to handle it?

 

I only have 3 fields in my table they are;  part_number, qty, warehouse and since I know my file will always be in this format I wrote this which works but takes a very long time to execute. Any input is very much appreciated.

 

<?php
//Connect to Database with include
include 'con2db.php';
//Set our File Name
$file ="inventory.txt";

//Very file name
//echo $file , '<br>';

//Set file as string
$str =file_get_contents($file,'a') or die("can't open file");

//Replace any Quotes in our string
$find='/"/';
$find2='/\s+/';
$replace='';
$str=preg_replace($find,$replace,$str);
$str=trim(preg_replace($find2,$replace,$str));

//Turn our string into an array
if (is_string($str) && $str !="")
{
$print=explode(",",$str);
}
//Verify we have an array
// print_r($print);

//Clear out old data
$sql = mysql_query("TRUNCATE TABLE inventory");

//Get Max Value of Array
$count=count($print);

//Verify Max Value
echo "<b>".$count;

//Set Counters 
$i=0;
$ii=1;
$iii=2;
///Write the Data to our Database
while ($iii <= $count) {
mysql_query("INSERT INTO inventory (part_number,qyt_on_hand,warehouse) VALUES ('$print[$i]','$print[$ii]','$print[$iii]')");
$i+=3;
$ii+=3;
$iii+=3;
}

?>

 

[Psycho]

You should never run queries in loops. If you have multiple records to insert, then create ONE insert query. Also, you need to validate/escape the values as appropriate for their type (I guessed int eh following code)

 //Set Counters 
$idx=0;
///Create array of insert values
$includeValues = array();
while ($idx < $count)
{
     $partNo = mysql_real_escape_string(trim($print[$idx]));
     $qty = intval($print[$idx]);
     $warehouse = mysql_real_escape_string(trim($print[$idx]));
     $includeValues[] = "('{$partNo}', '{$qty}', '{$warehouse}')";
     $idx += 3;
}
///Write the Data to our Database
$query = "INSERT INTO inventory (part_number, qyt_on_hand, warehouse) VALUES " . implode(', ', $includeValues);
mysql_query($query) or die(mysql_error());

 

However, why are you truncating the table instead of updating the existing records (and inserting any new ones)? You could use an INSERT with an "ON DUPLICATE KEY UPDATE" clause. That way existing records are updated and new ones are inserted. Are any of the values unique? For example, do you have multiple entries for the same part number if different warehouses? There are better approaches to this than truncating the table, but without knowing more about the requirements and the data it's hard to provide the appropriate solution.

[Failing_Solutions]

Hi Psycho thanks for the advice.

 

You also asked:

However, why are you truncating the table instead of updating the existing records (and inserting any new ones)?,

I have no reason other then I had to initially insert all these values into the table.

 

You could use an INSERT with an "ON DUPLICATE KEY UPDATE" clause. That way existing records are updated and new ones are inserted.

Good advice I'll look at that procedure I have never used it before.

 

 

Are any of the values unique? For example, do you have multiple entries for the same part number if different warehouses?

No, none of these particular values are unique there will be duplicate part numbers as you guessed because there are parts at several warehouses. I have another table I'll be updating with the total in stock.

 

 

There are better approaches to this than truncating the table, but without knowing more about the requirements and the data it's hard to provide the appropriate solution.

Thanks again, I hope I answered the questions well enough to allow further help.

 

 

[Failing_Solutions]

Thank you once again,

 

I did have to modify the code slightly but the logic and advice was spot on.

 

Just for reference, I had to add 2 other counters since we are starting at 0,1,2 then adding 3 to get to the next row and position of each of the next entries.

 

Here is what I ended up with.

<?php
//Set Counters 
$idx=0;
$idi=1;
$idn=2;
///Create array of insert values
$includeValues = array();
while ($idn < $count)
{
     $partNo = mysql_real_escape_string(trim($print[$idx]));
     $qty = intval($print[$idi]);
     $warehouse = intval($print[$idn]);
     $includeValues[] = "('{$partNo}', '{$qty}', '{$warehouse}')";
         $idx += 3;
 $idi += 3;
 $idn += 3;
}
?>

 

 

I did have one question, in the array $includeValues you listed each value ($partNo, $qty, $warehouse) wrapped in { } can you explain what is going on there? It works, I just never seen an array separated with the curly brackets like that, and would like to understand it for any future usage.

 

Thanks again.

 

[Psycho]

OK, a few things, so let's tackle one at a time:

 

Are any of the values unique? For example, do you have multiple entries for the same part number if different warehouses?

No, none of these particular values are unique there will be duplicate part numbers as you guessed because there are parts at several warehouses. I have another table I'll be updating with the total in stock.

 

But, you would not have two entries for the same part number at the same warehouse, right? Then you can make the "combination" of the part number and the warehouse as a unique constraint! If you set that up in the database, then you can use the "ON DUPLICATE KEY UPDATE" so new records will be added and existing records would be updated. It would take too much time to try and explain all that here, so you should do some research, but it is definitely the right way to do this.

 

However, you should NOT have another table to store the total stock of the products. You already have that data in this table by combining the relevant records. You should never store the same data in different tables as you will ultimately run into problems when concurrency/race conditions occur.

 

Just for reference, I had to add 2 other counters since we are starting at 0,1,2 then adding 3 to get to the next row and position of each of the next entries.

I may have had a bug in my code, but you do not need separate counters. My code used just one counter and then applied the offset within the loop. Maintaining three separate counters is too much overhead and adds complexity where it does not need to be. And, on second thought there is an even better solution: array_chunk()!

 

<?php
///Create array of insert values
$includeValues = array();
//Split array into multidimensional array by record
$records = array_chunk($print, 3);
foreach($records as $record)
{
     //Verify the record has three values
     if(count($record)==3)
     {
         //Process the record into an insert value
         $partNo = mysql_real_escape_string(trim($record[0]));
         $qty = intval($record[1]);
         $warehouse = intval($record[2]);
         $includeValues[] = "('{$partNo}', '{$qty}', '{$warehouse}')";
     }
}
?>

 

I did have one question, in the array $includeValues you listed each value ($partNo, $qty, $warehouse) wrapped in { } can you explain what is going on there? It works, I just never seen an array separated with the curly brackets like that, and would like to understand it for any future usage.

 

When you have a string defined in a double quoted string you can include variables in that string and they will be parsed into their values. But, in some cases the parsing engine can get confused as to where the variable starts and ends - especially when using arrays in the string. So, you can enclose the variable within curly braces to help the parsing engine. I do this much consistently for ALL my variables within string to be consistent and it also makes it easier to "see" the variables within my code.

 

See the manual for string parsing here: http://www.php.net/manual/en/language.types.string.php#language.types.string.parsing

 

Scroll down after example #8 to see the explanation about complex syntax.

[Failing_Solutions]

Thank you very much for the careful and complete explanations. It is very helpful.

 

Only thing I can really comment on is storing the same value more then once. As I'm actually trying to deal with this issue now. So a small explanation will be needed.

 

What I wanted to do was first parse the flat file, we accomplished that (thanks for the optimization help!).

 

The next thing I have to tackle is there maybe more then 1 part number for a specific part. This is because our current system is old and 16 years ago when they started making part numbers they used to use part of the manufactures number so they knew where to buy it from. In the end we have some parts that have 6 different part numbers in our system, but they are technically the same part.

 

I spent the better part of the last 2 days mapping every single part number to a specific part number that we use on a live/hosted database. So I now have a table which holds a PRIMARY Key which is the live part number, and up to 6 additional columns of part numbers that could have stock from the old system.

 

What I am ultimately trying to achieve is updating a local copy of our hosted database (products table) then push those updated qty values to the live database on a daily basis.

 

Our end of the day reports generate those flat files so I'll have those, the last steps is to deal with many-to-many-to-one relationship so our hosted database will always (within a 24 hour window) show accurate stock totals.

 

So my thinking was to take this first many to many relationship (multiple part numbers in different warehouses) and put them into one field.

Next, take the multiple part_numbers and multiple stock totals and condense them into 1 update for a local copy of our hosted database..

Finally, using mysql bridge push those updates to the hosted database.

 

I hope that makes some sense.

 

I am working through the second half of this now, and I am sure I will have some more questions. I'll open a new topic once I arrive at something that works.

 

Thank you very much once again.