Creating additional records when user adds one

[wreid]

Hi all,

I'm sorry to keep posting questions, I'm hoping this will be the last one

Basically I'm stuck again - what I'm trying to do is:

Upon adding something to a table I want to be able to automatically add a new entity to other tables

I figure this is a trigger, however I'm not sure how I use the dataface api to do it... I'm sure this isn't the first time something like this

function afterInsert(&$record){
// Enter the code here
}

my php i wrote to do this before was:

// insert data into db:
$insert = db_query('
INSERT INTO EPISODES
(
MATRIX_ID, EPISODE_TITLE, EPISODE_BLURB
)
VALUES
(
?, ?, ?
)',
$commandID,
"New Episode Title",
"Enter the new episode description here") or die("Error in insertion: " . mysql_error());

$epid = mysql_insert_id();

$insert = db_query('
INSERT INTO MATRIX_EPISODE_ITEM
(
EPISODE_NUMBER, ITEM_ID, ITEM_DESC
)
SELECT
EPISODE_NUMBER, ITEM_ID, ""
FROM
EPISODES, MATRIX_ITEM
WHERE
EPISODES.MATRIX_ID=MATRIX_ITEM.MATRIX_ID AND
EPISODES.EPISODE_NUMBER=?',
$epid) or die("Error in insertion: " . mysql_error());

echo("New episode $commandID $epid created.
");

This just creates a new episode and then creates a new "matrix_episode_item" for each item in the system that belongs to the same series (matrix)

I figure I need to embed this into the trigger, but for the life of mye I have no idea of an elegant solution for this.

Again, all help is greatly appreciated

[wreid]

This seems so easy and I can't figure out an elegant solution for it. If anyone has any clues i'd appreciate the help. Thanks!

[shannah]

The code that you posted should work ok.. just place it inside the afterInsert() method of your delegate class.
You just have to first obtain the proper commandid from the inserted record.

e.g.:

Code: Select all

function afterInsert(&$record){

    $commandID = $record->val('commandID'); // assuming commandid column is called 'commandID'
    // .. proceed to do your sql queries to add the new records.


}


[wreid]

Is there any sql type function to make the calls or should i use the one i created as well?

Not sure the best way to call the database through the system. If you have a simple example I'd appreciate it

Thanks,
Walter

[shannah]

You can use your own SQL function, or mysql_query(), or the dataface API has a df_query function.

To obtain the database resource handle, you can do:

Code: Select all

$app =& Dataface_Application::getInstance();
$db = $app->db(); // database resource handle...
    // now you can call mysql_query()
$res = mysql_query("select * from foo", $db);


Alternatively, you can use the df_query function:

Code: Select all

$res = df_query("select * from foo");


Or use your own function.