Forums

Connecting to two tables in the database

cre8tivemedia 25 Nov, 2011
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
GreyHead 25 Nov, 2011
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.
cre8tivemedia 26 Nov, 2011
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
GreyHead 29 Nov, 2011
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
the_fitz 10 Sep, 2012
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.

This topic is locked and no more replies can be posted.