0

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] => jghjn@123.com
    [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] => thomas123@123.com
    [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}
2

You have a comma spare in your $orderQuery just before the closing ):

 ...
 `shipping_address_country_id`,
 )
 VALUES (:order_id, :order_number, ....

Than one comma needs to be removed:

$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)";
| improve this answer | |
  • thanks this helped with that particular error. I guess I was thinking that last comma was allowed for some reason, maybe because it is in JS – JasonDavis Aug 27 '15 at 0:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.