Jump to content

MySQL / PHP Bizzare Insert problem


MRGRIM
 Share

Recommended Posts

Hello,

I am honestly stumped by my problem and have spent several hours looking into it.

I have developed a custom CSV PHP Uploaded for our CRM system. I took a copy of our 'live' database and hosted it on my development laptop via XXAMP. The script works a treat and after testing decided to put it live. Since putting the script live I am getting really stupid errors.

The only difference between my development system and the live system is the version of MySQL (Development is 5.0.41 and live is 4.1.20) I will try and install 4.1.20 on my development system over the weekend to try and rule the version difference out.

Error 1

INSERT INTO sales_orders (sales_orders.id, sales_orders.date_entered, sales_orders.shipping_address_street, sales_orders.shipping_address_city, sales_orders.shipping_address_postalcode, sales_orders.terms, sales_orders.amount, sales_orders.billing_address_street, sales_orders.billing_address_city, sales_orders.billing_address_postalcode, sales_orders.so_number, sales_orders.tracking_reference, sales_orders.description, sales_orders.billing_account_id, sales_orders.shipping_account_id, sales_orders.created_by) VALUES ('130709', '2007-01-02', 'Saltend Cogeneration Compnay L Saltend hedon Road England', 'Hull','HU12 8GA','','£0.00','SALTEND COGENERATION CO SALTEND HEDON ROAD EAST YORKSHIRE','HULL','HU12 8YG','10824','LO-UK-UK','Wednesday 2nd January 2007','d0962755-3fb8-958c-7b85-46f78918034e','d0962755-3fb8-958c-7b85-46f78918034e','1')

Duplicate entry '130709' for key 1

Please note that sales_orders is a completly blank table (before and after I try instering records) if I run that command directly on the MySQL server (via PHPMYADMIN) then it will insert the record no problem

CREATE TABLE `sales_orders` (n  `id` varchar(36) NOT NULL default '',n  `prefix` varchar(14) default NULL,n  `so_number` int(11) NOT NULL default '0',n  `date_entered` datetime NOT NULL default '0000-00-00 00:00:00',n  `date_modified` datetime NOT NULL default '0000-00-00 00:00:00',n  `modified_user_id` varchar(36) NOT NULL default '',n  `assigned_user_id` varchar(36) default NULL,n  `created_by` varchar(36) default NULL,n  `deleted` tinyint(1) NOT NULL default '0',n  `name` varchar(100) default NULL,n  `opportunity_id` varchar(36) default NULL,n  `so_stage` varchar(40) NOT NULL default '',n  `purchase_order_num` varchar(100) default NULL,n  `tracking_reference` varchar(100) default NULL,n  `due_date` date NOT NULL default '0000-00-00',n  `delivery_date` date NOT NULL default '0000-00-00',n  `show_list_prices` tinyint(1) default '1',n  `show_components` varchar(40) default '',n  `billing_account_id` varchar(36) default NULL,n  `billing_contact_id` varchar(36) default NULL,n  `billing_address_street` varchar(150) default NULL,n  `billing_address_city` varchar(100) default NULL,n  `billing_address_state` varchar(100) default NULL,n  `billing_address_postalcode` varchar(20) default NULL,n  `billing_address_country` varchar(100) default NULL,n  `shipping_account_id` varchar(36) default NULL,n  `shipping_contact_id` varchar(36) default NULL,n  `shipping_address_street` varchar(150) default NULL,n  `shipping_address_city` varchar(100) default NULL,n  `shipping_address_state` varchar(100) default NULL,n  `shipping_address_postalcode` varchar(20) default NULL,n  `shipping_address_country` varchar(100) default NULL,n  `currency_id` varchar(36) default NULL,n  `exchange_rate` double default NULL,n  `shipping_provider_id` varchar(36) default NULL,n  `description` text,n  `related_quote_id` varchar(36) default NULL,n  `amount` double NOT NULL default '0',n  `amount_usdollar` double default NULL,n  `terms` varchar(25) NOT NULL default '',n  `tax_information` varchar(150) default NULL,n  PRIMARY KEY  (`id`),n  KEY `idx_so_number` (`so_number`),n  KEY `idx_so_name` (`name`)n) ENGINE=MyISAM DEFAULT CHARSET=utf8

I'll post the PHP script that I have developed to run the SQL command as I am honestly stumped as to where the error could be.

@mysql_connect("localhost:3306", "user", "password") or die("Failed to connect.");
@mysql_select_db("db") or die("Failed to select.");

                        $query = "INSERT INTO sales_orders (sales_orders.id, sales_orders.date_entered, sales_orders.shipping_address_street, sales_orders.shipping_address_city, sales_orders.shipping_address_postalcode, sales_orders.terms, sales_orders.amount, sales_orders.billing_address_street, sales_orders.billing_address_city, sales_orders.billing_address_postalcode, sales_orders.so_number, sales_orders.tracking_reference, sales_orders.description, sales_orders.billing_account_id, sales_orders.shipping_account_id, sales_orders.created_by) VALUES ('".addslashes($data[1])."', '".$date."', '".addslashes($shippingaddress)."', '".addslashes($data[18])."','".addslashes($data[20])."','".addslashes($data[30])."','".$data[42]."','".addslashes($billingaddress)."','".addslashes($data[47])."','".addslashes($data[49])."','".addslashes($data[70])."','".addslashes($trackingreference)."','".addslashes($data[82])."','".addslashes($accountid)."','".addslashes($accountid)."','".addslashes("1")."')";
                        
                        echo("<br><b>".$query."</b>");
                        
                        mysql_query($query);
                        
                        if (mysql_error())
                        {
                            echo("<br>sales_orders: ".mysql_error());
                            $error="true";
                        }

Error 2

INSERT INTO sales_orders_cstm (sales_orders_cstm.id_c, sales_orders_cstm.pilorderno_c, sales_orders_cstm.collectcharge_c, sales_orders_cstm.ccy_c) VALUES ('130709', '10824',' 60', 'GBP')

sales_orders_cstm: Unknown column 'sales_orders_cstm.collectcharge_c' in 'field list'

CREATE TABLE `sales_orders_cstm` (n  `id_c` varchar(36) NOT NULL default '',n  `pilorderno_c` varchar(50) default NULL,n  `dateordraised_c` date default NULL,n  `collectcharge_c` varchar(50) default NULL,n  `ccy_c` varchar(50) default NULL,n  PRIMARY KEY  (`id_c`)n) ENGINE=MyISAM DEFAULT CHARSET=utf8

Again, if I run the SQL statement directly on the server it will insert the record fine.


Please will someone help me, this has got to be something basic that I have done wrong! I belive both errors stem from a common problem however please let me know which error you are refering to when you reply (if you reply  ;D)

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...