Connecting to two tables in the database

I am trying to write to an additional table in the database. There is an auto-increment set for the product_id field and I would like to fetch the newly generated product_id that's created in the connection that I have set up in chronoforms and write that into the second table along with a category_id which will be pulled from the form.

Here is what I have right now in the "custom code - before email" section but I can't seem to get it to work:

<?php
$con = mysql_connect("localhost","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);

$pid = mysql_query("SELECT mysql_insert_id(product_id) FROM c7stn_redshop_product");

$cat = mysql_real_escape_string($_POST[category_id]);

$sql="INSERT IGNORE INTO c7stn_redshop_product_category_xref (category_id, product_id)
VALUES
('$cat','$pid')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";

mysql_close($con)
?> 

I'm sure I'm not writing the code correctly but I have no idea what is incorrect... I keep trying different things but to no avail.

Can anyone help?
Matt

Hi Matt,

The ChronoForms DB Save action makes the info from the new record available to you (check the Help tab for info) so you don't need to look it up. You may need a little custom code snippet to copy it to the correct $form->data entry to match the column name in the second table.

Bob

PS There was a bug using two DB Saves - please check the CFV4 bugs post to see if there is a fix needed.

Thanks Bob,

I think I've figured out how to connect the second table in the DB Save section. Just trying to figure out where I need to put the code... I have two fields that I'm trying to pass into the second table (product_id and category_id). I was researching that the mysql_insert_id function will pull the last auto-increment generated but it had to be put right after the last insert (not sure if I even need to use this function). Should I still put the code in the "Before Email" section of chronoforms or somewhere in the "Load JS" section? Can I just use a "$_POST" to the form? Forgive me, I'm still new to PHP.

Matt

HI Matt,

The Load JS action is for JavaScript so that won't help.

You need to use the Custom Code actions to add PHP.

Please check my last post - ChronoForms makes the data you are looking for available to you.

Bob

Hi Bob,

I was looking for the way to get the last insert id from a table, and you mentioned that is was detailed in the "help". But there is no "Help" tab on the "db save" event. So I was wondering if this would work???

$form->data['invoice'] = mysql_insert_id(); 

Or if there is another way???

Also, I saw no mention of a 2 DB save bug.

Thanks in advance
MrFitz

Hi Matt,

The ChronoForms DB Save action makes the info from the new record available to you (check the Help tab for info) so you don't need to look it up. You may need a little custom code snippet to copy it to the correct $form->data entry to match the column name in the second table.

Bob

PS There was a bug using two DB Saves - please check the CFV4 bugs post to see if there is a fix needed.

Hi MrFitz,

Please see this FAQ

Bob