Submit Form results to external database

I need the data from the form to be written to the site's db (so far so good), and the same data should be written to an external db.

I figure all I need is insert some PHP code to 'On Submit code' area that will insert the code to the other database. but my PHP knowledge isn't enough for this task.

This is what I got so far:

<?php
$date_1 = $_POST['date_1'];
$date_2 = $_POST['date_2'];
$text_3 = $_POST['text_3'];

mysql_connect ('some_host', 'db_user', 'db_pass') or die ('Error:' . mysql_error());
mysql_select_db("db_table");

$query="INSERT INTO jos_chronoforms_webapp4 (cf_user_id, date_1, date_2, text_3)VALUES ('NULL', '".$date_1."', '".$date_2."', '".$text_3."')";
mysql_query($query) or die ('error updating database');
echo "database was updated with: " .$date_1." ".$date_2." ".$text_3;

// close connection
mysql_close();
?>

It worked with some made up data that isn't related to the form so I know it should work, but it doesn't.
I put that code into: "On Submit code - before sending email"

Got it!
It worked after putting it into "On Submit code - after sending email" for a weird reason, I don't have any emails that should be sent on this form.

For future reference:
date_1, date_2, text_3 - the names of the fields in your form and the name of the columns in your db table.

But truobles are not over, I get the field names but for me it's crucial to have the user id as well.
I tried to get it with:

$user = JFactory::getUser();

And put

echo $user->id;

in the line instead of NULL, but no luck.

any help?

Well, got that too. here is the fixed code. this will get the fields from your form (date_1, date_2, text_3 in my case) to the same column in the database you specify.
make sure to put this in "On Submit code - after sending email"

<?php
$date_1 = $_POST['date_1'];
$date_2 = $_POST['date_2'];
$text_3 = $_POST['text_3'];

$user =& JFactory::getUser();
$usr_id = $user->get('id');


mysql_connect ('hostname', 'username', 'password') or die ('Error:' . mysql_error());
mysql_select_db("db_table");

$query="INSERT INTO jos_chronoforms_webapp5 (cf_user_id, date_1, date_2, text_3)VALUES ('".$usr_id."', '".$date_1."', '".$date_2."', '".$text_3."')";
mysql_query($query) or die ('error updating database');

// close connection
mysql_close();
?>

BUT
It is sure fun to help myself along the way, but my troubles aren't over, and this time I really need your help (Max? Bob? anyone?)

Now that adding a table to the database is done twice, I need the Edit and Delete buttons from ChronoConnectivity to work twice the same way. sadly, there isn't option to enter custom code like CF, so I have to do it manually. I found the function in /components/com_chronoconnectivity/chronoconnectivity.php around line 90, but I don't know how the code should look like and where should it go.

So any help is greatly appriciated

Hi Yigal,

I'm glad you managed to advance with it alone, Bob is in vacation and I was crazy busy the last 3 days!

It's correct, its hard to execute any custom code with connectivity operations, you need to hack the core file, something is not suggested of course!

but why not adding similar code to connect to the database and a DELETE statement instead of INSERT to delete that extra record ?

Regards,
Max

Hi Max,

You're right, it should be simple. I have the code already, I'm just not sure where exactly to put it.

Thanks in advance!

Hi Yigal,

I thought you said you know which file and function to put the code in ?

Regards,
Max

I'm not sure about it. I guess I can figure out the code, but not the place to put it, the code I'm seeing at the spot I specified above is not clear to me at all.

Once again, I managed to get the code in place, and it goes like that:
This is to delete an existing record in both current and different database.
find the following piece of code (around line 110):

	$database->setQuery( "DELETE FROM ".$MyConnection->connectionrow->tablenames." WHERE ".$primary." IN ($cids)" );
	if (!$database->query()) {
		JError::raiseWarning(100, $database->getErrorMsg());
		$mainframe->redirect( JRoute::_("index.php?option=com_chronoconnectivity&connectionname=".$MyConnection->connectionrow->name) );
	}

and place this right after:

	mysql_connect ('hostname', 'user', 'pass') or die ('Error:' . mysql_error());
	mysql_select_db("db_table_name");
	$query="DELETE FROM ".$MyConnection->connectionrow->tablenames." WHERE ".$primary." IN ($cids)";
	mysql_query($query) or die ('error updating database');
	mysql_close();

and before this line:

	$mainframe->redirect( JRoute::_("index.php?option=com_chronoconnectivity&connectionname=".$MyConnection->connectionrow->name) );

BUT, This is my last issue before marking a big V all over this mission:
When I edit the connection, the form writes twice to the external database.
So to sum it up:
If I make a new record - same record gets written to both databases.
If I delete - same record deleted from both databases.
If I edit - One database is all good (the default one), the external databse get TWO entries: the existing entry and the edited entry.

Help is appreciated, most of all I need to know where to put an extra code, and how should it look like.

I can't get to the bottom of this.

Is there a better code to put in the onsubmit field, that will automatically check to see if there's an existing data and edit it instead of writing a new one?
I see in the code that you don't even use the regular SQL queries, you do it with different Joomla functions which I'm not familiar with.

Can anyone help me out with this?

Hi yigal,

Catchingt back up to this a bit late.

Joomla has a Table class with some useful methods including 'check', 'store', and 'save' (I forget the exact definitions without looking them up). If used correctly these methods will do what you need - update a record if the id already exists or add a new record if the id is new.

The Joomla docs wiki is probably the place to look for more info though Fredrik has also posted about these methods here and you can see them in use in the ChronoForms autogenerated code tab.

Bob