I am building a simple PHP file to query Magento to get all orders and order items to insert into an external Database.
I am able to query Magento and get the rercrods I need.
When I try to connect to 3rd party DB and insert, it simply fails on first insert but gives now error or feedback at all!
When I run print_r($external_db_write); I can see that my external 3rd part DB username and password and DB name are all returned in the result of that so I believe the DB connection is setup correctly which is set in some XML config file.
Below are the PHP and SQL that I am trying to run on each order record to insert into my own DB.
I just cannot figure out why it is not creating the record and is not showing any messages or anything either!
Any help extremely appreciated I am on verge of getting fired over this, thanks
<?php
error_reporting(E_ERROR | E_WARNING | E_PARSE | E_NOTICE);
//load the magento app with the admin store
require_once('../../app/Mage.php');
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);
// Insert Order data into NAM App DB
$resource = Mage::getSingleton('core/resource');
$external_db_write = $resource->getConnection('externaldb_write');
$orderQuery = "insert into `neonandmore_timeclock`.`nam_orders` (
`order_id` ,
`order_number` ,
`order_type` ,
`customer_id` ,
`sugarcrm_qms_id` ,
`date_created` ,
`date_modified` ,
`order_status` ,
`total_item_count` ,
`order_shipping_price` ,
`order_tax_price` ,
`order_items_price` ,
`order_grand_total_price` ,
`order_shipping_tracking` ,
`order_shipping_ship_date` ,
`order_notes`,
`billing_address_firstname`,
`billing_address_lastname`,
`billing_address_telephone`,
`billing_address_email`,
`billing_address_street`,
`billing_address_city`,
`billing_address_region`,
`billing_address_postcode`,
`billing_address_country_id`,
`shipping_address_firstname`,
`shipping_address_lastname`,
`shipping_address_telephone`,
`shipping_address_email`,
`shipping_address_street`,
`shipping_address_city`,
`shipping_address_region`,
`shipping_address_postcode`,
`shipping_address_country_id`,
)
VALUES (:order_id, :order_number, :order_type, :customer_id, :sugarcrm_qms_id, :date_created, :date_modified, :order_status, :total_item_count, :order_shipping_price, :order_tax_price, :order_items_price, :order_grand_total_price, :order_shipping_tracking, :order_shipping_ship_date, :order_notes, :billing_address_firstname, :billing_address_lastname, :billing_address_telephone, :billing_address_email, :billing_address_street, :billing_address_city, :billing_address_region, :billing_address_postcode, :billing_address_country_id, :shipping_address_firstname, :shipping_address_lastname, :shipping_address_telephone, :shipping_address_email, :shipping_address_street, :shipping_address_city, :shipping_address_region, :shipping_address_postcode, :shipping_address_country_id)";
// insert into 3rd part DB
$external_db_write->query($orderQuery, $orderBinds);
The data being passed to the query looks like this...
Array
(
[order_id] => 5761
[order_number] => 100005363
[order_type] => RUSH
[customer_id] => 4355
[sugarcrm_qms_id] => 1
[date_created] => 2015-08-03 20:03:40
[date_modified] => 2015-08-18 20:32:29
[order_status] => 19
[total_item_count] => 1
[order_shipping_price] => 0.0000
[order_tax_price] => 0.0000
[order_items_price] => null
[order_grand_total_price] => 464.0000
[order_shipping_tracking] => null
[order_shipping_ship_date] => 2015-08-11
[order_notes] => null
[billing_address_firstname] => John
[billing_address_lastname] => Doe
[billing_address_telephone] => 645-666-6666
[billing_address_email] => [email protected]
[billing_address_street] => 123 Street
[billing_address_city] => Austin
[billing_address_region] => Texas
[billing_address_postcode] => 78701
[billing_address_country_id] => US
[shipping_address_firstname] => John
[shipping_address_lastname] => Doe
[shipping_address_telephone] => 512-223-3257
[shipping_address_email] => [email protected]
[shipping_address_street] => 1212 Rio Grande
[shipping_address_city] => Austin
[shipping_address_region] => Texas
[shipping_address_postcode] => 78701
[shipping_address_country_id] => US
)
UPDATE
I was able to get SQL logging to work and I get this entry...
Unfortunately it isn't much help since my date has no issue in it.
Where it says 2015- that is for the field [date_created]
, any ideas?
## 2015-08-24 20:22:32
EXCEPTION
exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
VALUES ('5761', '100005363', 'RUSH', '4355', '1', '2015-' at line 36' in /var/www/vhosts/neonandmore.com/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /var/www/vhosts/neonandmore.com/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /var/www/vhosts/neonandmore.com/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /var/www/vhosts/neonandmore.com/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /var/www/vhosts/neonandmore.com/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#4 /var/www/vhosts/neonandmore.com/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('insert into `ne...', Array)
#5 /var/www/vhosts/neonandmore.com/lib/Varien/Db/Adapter/Pdo/Mysql.php(420): Zend_Db_Adapter_Pdo_Abstract->query('insert into `ne...', Array)
#6 /var/www/vhosts/neonandmore.com/order-report/export-orders/magento-order-export123.php(413): Varien_Db_Adapter_Pdo_Mysql->query('insert into `ne...', Array)
#7 /var/www/vhosts/neonandmore.com/order-report/export-orders/magento-order-export123.php(106): exportOrdersAndOrderItems('glassbendingadv...')
#8 {main}
