Help comparing 2 arrays

[TheBrandon]

Hello everyone,

 

I'm currently working on a large inventory management screen; the website has multiple "distributors" with their own inventory of products. Each distributor has the potential to carry the entire catalog of SKU's so right now, I have the stock/sku/dealer ID/product ID in one table with the product data/ID in another and am only inserting new rows if they are carrying the product.

 

However they want to manage all of the inventory at once so I need to determine which IDs are update queries and which are insert queries.

 

I'm trying to achieve this by comparing an array of the submitted data against an array of their existing inventory. If they don't have an entry for the submitted data key, we need to insert a new row for it to track their inventory.

 

I just can't figure out how to get the keys to line up. Here is what I have; I tried to put it all into obviously defined variables for this forum.

 

// assign our submitted values to an array
$submitted_data = $_POST;

// remove the SKUs with 0 or no inventory submitted
$submitted_data = array_filter($submitted_data);

// pull the distributor id out of the array before the insertion loop
$distributors_inventory_distributor_ID = array_pop($submitted_data);

// pull all existing inventory for this distributor to see if we are adding new inventory
// or updating old inventory
$existing_Inventory_Result = mysql_query("SELECT distributors_inventory_product_ID FROM distributors_inventory WHERE distributors_inventory_distributor_ID = $distributors_inventory_distributor_ID ORDER BY distributors_inventory_product_ID ASC", $db);

// verify there is a result
$existing_Inventory_num_results = mysql_num_rows($existing_Inventory_Result);
if ($existing_Inventory_num_results > 0){

	while($existing_Inventory_row = mysql_fetch_assoc($existing_Inventory_Result)){

		// put existing inventory into an array
		$existing_Inventory[] = $existing_Inventory_row['distributors_inventory_product_ID'];

	}

}

$update_Inventory_Array = array_diff($submitted_data, $existing_Inventory);

// print the array [DEBUG ONLY]
echo '<h1>$update_Inventory_Array:</h1>';
print_r($update_Inventory_Array);
echo '<hr/>';
echo '<h1>$submitted_data:</h1>';
print_r($submitted_data);
echo '<hr/>';
echo '<h1>$existing_Inventory:</h1>';
print_r($existing_Inventory);

 

That is outputting this:

$update_Inventory_Array:

Array ( [963] => 5 [979] => 2 [982] => 2 [974] => 1 [32] => 5 )

 

$submitted_data:

Array ( [963] => 5 [979] => 2 [982] => 2 [974] => 1 [32] => 5 )

 

$existing_Inventory:

Array ( [0] => 32 [1] => 963 [2] => 974 [3] => 979 )

 

I'd like it to be:

$update_Inventory_Array:

Array ( [963] => 5 [979] => 2  [974] => 1 [32] => 5 )

 

$submitted_data:

Array ( [982] => 2 )

 

$existing_Inventory:

Array ( [32] => 5 [963] => 1 [974] => 1 [979] => 2 )

 

Can anyone suggest how to do this? I think if I could make the existing_Inventory array line up with my submitted_data array I could sort the rest out but I'm not sure how to get the keys/data to line up properly.

 

[Psycho]

I didn't read your entire post but I think you are making this more difficult than it needs to be. Look into using the INSERT ON DUPLICATE KEY UPDATE clause. You might need to set the uniqueness on a two column combination though.

[TheBrandon]

I didn't read your entire post but I think you are making this more difficult than it needs to be. Look into using the INSERT ON DUPLICATE KEY UPDATE clause. You might need to set the uniqueness on a two column combination though.

 

Can you elaborate on how to do this or link me to a more detailed tutorial on how to achieve this?

 

I've never used the ON DUPLICATE KEY UPDATE clause. So it can take an array of say, 3 "new" inserts to the database and 2 "update" queries and do them both in one query? This would certainly be a tremendous time saver for me to learn.

 

I looked into the mysql website site here: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html and read a few examples I found but I really am having trouble taking the example and applying it concept wise to what I want to do.

 

Would you mind helping explain it a little further in relation to what I'm trying to do so I can learn it?

[Psycho]

Here is a page that give a brief, less technical explanation. http://thomashunter.name/blog/mysql-replace-vs-insert-on-duplicate-key-update/

 

I don't think a forum is the appropriate venue to teach. Do some Googling to research then try it out on some sample tables. Once you've seen it work it will make more sense.