Uusing an array to update multable databases

Hello

So Im stock on something here and would appreciate it if someone would be able to help me out

In a nut shell:
I am building a script to update stock across a number of websites.

The idea is that this script runs on completion of an order.

I have got to the point where I need to insert or update the DB tables but the array that I have pulled needs to be modified or duplicated in order to run a DB save multiple times (correct me if I'm wrong)

See attached screen shot of form in the admin area to get an idea of what I'm trying to achieve.

So the output I get is this, which is exactly the info i need (I don't need the virtuemart_order_item_id).

[order_items] => Array
        (
            [0] => Array
                (
                    [virtuemart_order_item_id] => 13
                    [order_item_sku] => TL-MR3220
                    [product_quantity] => 1
                )

            [1] => Array
                (
                    [virtuemart_order_item_id] => 14
                    [order_item_sku] => TL-WR841N
                    [product_quantity] => 1
                )

            [2] => Array
                (
                    [virtuemart_order_item_id] => 15
                    [order_item_sku] => TL-123455677
                    [product_quantity] => 1
                )

            [virtuemart_order_item_id] => 15
        )

So now from that i need to run a DB multi save to deduct the product_quantity where product_sku = order_item_sku

But as I can only use a model id once I need to recreate the array with a different model id.

How would I do that?

Im not bumping this post but i see that the attachment did not seem to show in the post so i have attached it here

Hi darren,

I'm not sure I could understand what you need to do exactly here but If you do not need the last "virtuemart_order_item_id" item then just use a custom code action to unset it before the db save action:

<?php
unset($array['virtuemart_order_item_id']);

Best regards,
Max

Hi Max,

Thanks for getting back to me.

Just one more thing (hopfully)
Below is my sql script, but for some reason it does not seem to work.
I dont get any of the error or complete messages

<?php
$dbhost = 'xxxxx.xxxxxx.xxxxx.xxxxxx';
$dbuser = 'uxxx4800_xxxx';
$dbpass = 'rs6xxxxxGLmM1hRHj';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$selected = mysql_select_db("db13xxx00_connect",$conn)
or die("Could not find that table");

$sql_start = 'UPDATE c0ntvirtuemart_products SET product_in_stock = product_in_stock - {$p['product_quantity']} WHERE product_sku = {$p['order_item_sku']}';
$sql_array = array();
foreach ( $form->data['order_items'] as $p ) {

$product_sku = {$p['order_item_sku']};
$product_in_stock = {$p['product_quantity']};
}
$sql_array[] = '("'.$product_sku.'", "'.$product_in_stock.'")';
if (count($sql_array) >= $queue_num) {
$query_single=$sql_start . implode(', ', $sql_array);
mysql_query($query_single);
$sql_array = array();
}

if (count($sql_array) > 0) {
$query = $sql_start . implode(', ', $sql_array);
mysql_query($query)or die(mysql_error());
}
echo "completed";
?>

Thanks

Hi Darren,

Why not use Joomla DB class for the db operations you have instead of using plain php functions ?

Do you get any data updated in the database ?

Best regards,
Max

they are all external databases i need to connect to.

I thought it would be easier to use the above script as I can write the sql query rather then using array format.

Also I would need to connect to a number of databases to ensure stock is the same across each site so I was thinking this would be the easiest way.

What would you suggest?

Can you see where I am going wrong with the code?

This is the array from the debugger

  Array
(
    [option] => com_chronoforms5
    [chronoform] => stock_update_form
    [order_number] => QI2B07
    [check] => Array
        (
        )

    [order] => Array
        (
            [virtuemart_order_id] => 5
            [order_number] => QI2B07
            [order_status] => S
        )

    [order_items] => Array
        (
            [0] => Array
                (
                    [order_item_sku] => ipp
                    [product_quantity] => 1
                )

        )

)

This is the php code to update stock qty

<?php

$dbhost = 'xxxxx.xxxxxx.xxxxx.xxxxxx';

$dbuser = 'uxxx4800_xxxx';

$dbpass = 'rs6xxxxxGLmM1hRHj';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$selected = mysql_select_db("db13xxx00_connect",$conn)

or die("Could not find that table");

$sql_start = 'UPDATE c0ntvirtuemart_products SET product_in_stock = product_in_stock - {$p['product_quantity']} WHERE product_sku = {$p['order_item_sku']}';

$sql_array = array();

foreach ( $form->data['order_items'] as $p ) {

$product_sku = {$p['order_item_sku']};

$product_in_stock = {$p['product_quantity']};

}

$sql_array[] = '("'.$product_sku.'", "'.$product_in_stock.'")';

if (count($sql_array) >= $queue_num) {

$query_single=$sql_start . implode(', ', $sql_array);

mysql_query($query_single);

$sql_array = array();

}

if (count($sql_array) > 0) {

$query = $sql_start . implode(', ', $sql_array);

mysql_query($query)or die(mysql_error());

}

echo "completed";

?>

Anything jumping out at you, that would make it not work?

I dont get any error or debug info, so im not sure what would be wrong

Hi Darren,

I can't tell what is wrong with the php code you have without testing it, you should be able to debug it easily in an external php file.

The db save action can save multiple rows if its provided with a correct array structure, for example, in your last debug results, if you setup the "db save" with "data path" = "order_items" then it will save one record with the 2 fields data provided.

Best regards,
Max

Hi darrenhallinan,

As Max said you need to carefully debug your PHP+MySQL.

For example

$sql_start = 'UPDATE c0ntvirtuemart_products SET product_in_stock = product_in_stock - {$p['product_quantity']} WHERE product_sku = {$p['order_item_sku']}';

PHP {$var_name} syntax does not work inside single quotes - only double quotes; the {$p['order_item_sku']} probably needs quoting unless it is always a sequence of digits; and I suspect that = product_in_stock may need backticks ``

Also I think that $product_sku = {$p['order_item_sku']}; may fail as the {} are not required there.

Bob