form input to 2 databases not working

Hey Guys
I have a form that connects to 2 tables. The tables are part of a cart script and I need to avoid changing the column names (or else dig through the code and make changes there as well..).

Here's my prob

Table A and Table B both have a "product_id" column. I need product_id in Table A updated (which works fine with my form), but not "product_id" in Table B, but I do have other columns in table B that need to be updated.

In my form I have one hidden input field for product_id

The product_id column in Table A updates fine. None of the columns in Table B will update. Also, product_id in Table B is set to PRIMARY, auto_increment. Not sure if that has anything to do with it

Anyway, been trying for hours to make this work and am about to lose it.

Thanx always for your help.

Mark

Hi marklandry,

Sorry but I don't see any way round this except to hand-code one of the database saves. There is no way to tell ChronoForms "this data belongs to 'product_id' in this table and that column belongs to 'product_id' in that table" :-(

Bob

So I disconnected the other table, and i'm trying to only update the "product_id" column in table b and nothing happens - I get no error, what seems to be "success" from the front end - but no update in the table.

The column is set to PRIMARY, auto-increment, but I should still be able to update it right? Even if I remove the auto-increment it still won't do anything....

(edit/addition)

I ran the following in phpMyAdmin and it worked fine....

INSERT INTO `XXXXXXXXXX`.`jos_hikashop_product` (
`product_id` ,
`product_code`
)
VALUES (
'3000', '3000'
);

Also, here's the query with debug turned on, I ran this in phpMyAdmin and got nothing (I don't see how what's below can work...

UPDATE `jos_hikashop_product`
  SET `product_code`='3000'
  WHERE product_id='3000'

If I get rid of the "product_id" input in my form and replace it with a different column, i get the following query which works fine

INSERT INTO `jos_hikashop_product` ( `product_name`,`product_code` )
  VALUES ( '3000','3000' )

I figured maybe Chronoforms was acting differently because I was trying to update a primary column, so I removed the key and auto increment from product_id, added the product_id input back into the form, cleared my cache, and got the following query:

#
UPDATE `jos_hikashop_product`
  SET `product_id`='3000'
  WHERE product_code='3000'

totally stumped noob over here... basically, if I try to update any other column other than product_id this works, as soon as I try to do anything with product_id it fails. I've cleared every cache I can think of...

Hey Guys,
I still have no idea what's going on with this - seems like a chronoforms issue???

Hi Mark,

Sorry, I've been skipping past this :-(

I don't think that you can update/over-write the value of an auto-increment column. It kind of defeats the purpose of the auto-increment.

What are you trying to achieve here?

Bob

Hi Bob,
That's what's weird, even if I remove the auto increment I still get the different query in the debug code - that's what's got me stumped. Why would there be a totally different query when I have an item that updates the "product_id" column? Again, if I remove the "product_id" field from my form, I get the right query and it works.

Also, if I run the right sql in PhpMyAdmin, it will allow me to update the column when it's set to auto increment.

These might all be MySql questions and totally not related to CF, but I appreciate your help.

Thanx man

Mark