[SOLVED] Insert Problem (Bizzare)

[MRGRIM]

Hello,

 

This is my first post so please go easy, 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  )

[revraz]

Set your ID fields to INT instead of VARCHAR.  They are numbers right?

 

Why use the table name in front of the field name when you're only working with one table?

[fenway]

First, why are you inserting the UID at all? Sounds like your script (or the insert) is running twice)?  I don't know about your second issue.

[MRGRIM]

Set your ID fields to INT instead of VARCHAR.  They are numbers right?

 

Why use the table name in front of the field name when you're only working with one table?

 

I will try this (why would it work on the newer version of MySQL?), No reason for the tablename, its the way I learnt to do it (I will remove these and try)

 

First, why are you inserting the UID at all? Sounds like your script (or the insert) is running twice)?  I don't know about your second issue.

 

Because they UID needs to be linked to tables that already exisit. Script is only running once and only 'attempting' to insert once (logic behind that is that there is NO records in the table before and after the insert) - Again why would it work on a development system but not on the live system?

[revraz]

Only thing I can guess is that they are not setup the same.  Turn off unique setting and see what happens.

[fenway]

You can't get a duplicate error on the PK unless there are two such id.

[MRGRIM]

You can't get a duplicate error on the PK unless there are two such id.

 

Indeed, which is why there is something seriosuly wrong here, when the script runs that SQL I get the error, if I then run the same statement (thats echo'd - $query) on the server it works.

[fenway]

I still say it's running twice... you can turn on loggin and check.

[MRGRIM]

I don't see what the problem is if the script (for some strange reason is running twice)

 

The first time it runs it should at least insert something  ??? So I should only get this error the second time the script runs, the problem is it's not actually inserting any data, the table is completly blank!

[fenway]

I wish I could be of more help... but if the same commands work from another script / phpmyadmin / CLI, then it's not the db per se.

[MRGRIM]

Hello All

 

Thank you all for your comments, I belive I have progressed the problem.

 

I have installed MySQL 4.1.22 on another development system and taken a backup of my 'problematic' live system and restored it to the development box (originally to rule out the compatability issues of 4.1.22 to 5.0) however when I browse the database on the development system the records I inserted are now showing up in sales_orders, where I go back to the live system the table is showing as empty.

 

Hopefully someone will know or have seen this problem before and will be able to advise me, I'll look for a solution myself and If I find one I'll post it here.

 

Regards

[MRGRIM]

 

Thanks for all the help, this proved to be a really stupid (simple) problem.

 

Turns out I was connecting to the wrong database  I tried localhost (script located on my hosts file server) and also tried connecting to the domain name, both Admin and Query Browser allowed me to connect using these host names, and my database actually exists on these hosts, however the CRM software and phpMyAdmin (provided by my hosting company) where pointing to some linux based hostname.

 

I have asked the question - why / how am I able to connect to the secondary database e.g. why does it exisit - they are unable to provide me with an answer