2. PHPRunner General questions
  3. insert record into another table
This topic is locked

insert record into another table

 		12/11/2007 6:51:30 AM
PHPRunner General questions
G
 garethp authorDevClub member

Help please!!!!
I thought this would be easy but I am obviously doing somethng quite wrong!!
I have two table
part (part, description, auditdate)

pidata (`date`, `user`, `part`, `system_qty`, `count_qty`)
They are set up with part as the (master) to pidata (detail) linked on part filed.
When I add a new record to pidata I would like part.audit to be populated with the current date.
I have set the after record added event with the script

//** Insert a record into another table ****

global $conn;

$strSQLInsert = "insert into part (auditdate) values (now())";

db_exec($strSQLInsert,$conn);


However I get the following error an di can not understand why - I ahve tried so many things!!!
PHP error happened
Technical information

Error type 256

Error description Duplicate entry '' for key 1

URL www.****.co.uk//pidata_add.php?

Error file e:\domains\i\\user\htdocs\**\include\dbconnection.php

Error line 26

SQL query insert into `pidata` (`date`, `user`, `part`, `system_qty`, `count_qty`) values ('2007-12-11 11:46:19', 'garethp', 'CON-F1054', 1, 2)

Solution It seems that you add duplicate key fields.
Please check data you enter on the ADD or REGISTER page.
Can you offer any help? Every thing works eprfectly without this event.
J
 		Jane 12/11/2007

Hi,
you need to use update statement in your event, not insert.

Here is a sample code:

//** Insert a record into another table ****

global $conn;

$strUpdate = "update part set auditdate=now() where part=".$values["part"];

db_exec($strUpdate,$conn);
G
 		garethp authorDevClub member 12/11/2007

Jane
Thanks so much for the reponse which I have tried but I now get the following error - any helpwould be greatfully appreciated
PHP error happened
Technical information

Error type 256

Error description Unknown column 'CON' in 'where clause'

URL www..co.uk//pidata_add.php?

Error file e:\domains\i\i.co.uk\user\htdocs\\include\dbconnection.php

Error line 26

SQL query insert into `pidata` (`date`, `user`, `part`, `system_qty`, `count_qty`) values ('2007-12-11 16:04:29', 'garethp', 'CON-010802A-BLUE', 10, 10)
A
 		alang 12/11/2007

Try putting single quotes around the value:
$strUpdate = "update part set auditdate=now() where part='".$values["part"]."'";
G
 		garethp authorDevClub member 12/12/2007

Just brilliant - this worked - you are a genius.
THANKS SO MUCH FOR YOU HELP