Uusing an array to update multable databases

darrenhallinan , January 12 at 10:31
d
darrenhallinan

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?

d
darrenhallinan

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
https://s27.postimg.org/74w6qruzj/db_update_script_screen_shot.jpg

admin

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

d
darrenhallinan

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

admin

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

d
darrenhallinan

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

admin

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

GreyHead

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

Powered by ChronoForums - ChronoEngine.com

ChronoForms Book

The ChronoForms Book, written for ChronoForms v3 contains 350 pages of invaluable ChronoForms How-tos hints and tips.

Note: many of the ideas can be used in ChronoForms v4 but the admin interface is very different and code examples may need to be modified.

SSL

Members Login

 

2CheckOut.com Inc. (Ohio, USA) is an authorized retailer for
goods and services provided by ChronoEngine.com