MySQL / PHP Bizzare Insert problem

[MRGRIM]

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)

[HarshReality]

Dont misunderstand me I love puzzles and all but if I understand you... you have it working via phpmyadmin yet direct SQL injection is giving you issues.

Then use phpmyadmin and be done with it.

[jollyrancher82]

Using an MVC framework for PHP is really useful if you need quick, stable PHP/SQL apps.

[MMZ]

Why are you using the table.field notation on the inserts?