2. PHPRunner General questions
  3. Add information to two tables
This topic is locked

Add information to two tables

 		11/26/2007 3:31:07 PM
PHPRunner General questions
hfg author

I have two tables with a single add page and I have everything working exept that I need the Auto Inc. Primary Key from one table to go into the 2nd table
Tables

Main

-ID (key)

-Name
Info

-ID (key)

-ID2 (the key field from Main)

-Field1

-Field2

-Field3
my code
global $conn;

$str = "insert into info (ID2,Field1,Field2,Field3) values ('".$values["ID"]."','".$values["Field1"]."','".$values["Field2"]."','".$values["Field3"]."')";

db_exec($str,$conn);
unset($values["Field1"]);

unset($values["Field2"]);

unset($values["Field3"]);
I get a 0 in Info.ID2 but all of the other info goes in correctly
How do I get it to grab the ID field from Main and put it in the ID2 space in Info?
thanks
A
 		alang 11/26/2007

You should be able to use the MYSQL function LAST_INSERT_ID(). The PHP function call for this is mysql_insert_id.
J
 		Jane 11/27/2007

Hi,
use mysql_insert_id() function in your After record added event:

global $conn;

$id = mysql_insert_id();

$str = "insert into info (ID2,Field1,Field2,Field3) values ('".$id."','".$values["Field1"]."','".$values["Field2"]."','".$values["Field3"]."')";

db_exec($str,$conn);
hfg author 11/28/2007

Didn't work, I got this message
<<< Record was NOT added >>>
Unknown column 'field1' in 'field list'
note, field1 does not exist in the main table, only in the 2nd table that I am adding the information to
I had been using the Before Record added event type and it would inseart everything exept the ID (which goes in ID2) correctly.
Thanks for the help
A
 		alang 11/28/2007

You may need to add code to before AND after as follows:
BEFORE:

  • Copy table 2 fields to a temporary session variable
  • Unset the table 2 fields
  • return TRUE to allow PHPRunner to save table 1 record
    AFTER:
  • generate your ID2 using mysql_insert_id()
  • save your table 2 records using ID2 and fields saved in the session variable
Alexey admin 11/29/2007

In the Before record addedevent remove all fields that don't appear in main table from $values array.

unset($values["Field1"]);
hfg author 11/29/2007

You may need to add code to before AND after as follows:

BEFORE:

  • Copy table 2 fields to a temporary session variable
  • Unset the table 2 fields
  • return TRUE to allow PHPRunner to save table 1 record
    AFTER:
  • generate your ID2 using mysql_insert_id()
  • save your table 2 records using ID2 and fields saved in the session variable


Sorry I don't know SQl or PHP very well so I need more help. Alan says to copy the fields to a tempoary session, how do I do that?
I may also need help then saving those saved records in the after section
Thanks