Forums

Is it possible to have a form update an existing database table? For example, I am using a Project Management component and am wanting some of the form submissions to update existing columns in the Project Management database tables. I enabled the database connection and selected the existing database table to connect to, but I'm not seeing how I can assiciate the form results to different columns. Is it possible and if so, what am I missing?

Ok, looking at some other posts it looks like I need to use SQL INSERT statements, however I'm still not sure I'm going to be able to do exaclty as I want.
Here is what I am looking to do, so let's see if I can explain it a little better.
I don't need all of the form data entered into the database, and I only would use the cf_id and cf_user_id columns.
The existing database has the following colums I am looking to insert into.
id - this is what I want cf_id to write to.
title - this would come from text_1 from my form
content - this would come from text_2 from my form
author - this would come from cf_user_id
project - this would be a static number such as 5

Am I looking at something that is possible, or am I asking for too much?

Hi emomoney,

ChronoForms does this out of the box.

Make sure that the field names for the inputs you want to save match up to the column names in the datbase table and that the table id is one of the fileds (add it in a hidden input if necessary). The use the DB Connection tab to link the form to the table.

If the id already exists the table will be updated, if not a new record will be added.

Bob

Thanks. That was easier than I thought. One more question. How would I get the email results to post to one of the columns into the databse?

For example:
We have been using CF to create a letter which is emailed. Then we were creating a task in my project management component and we would copy/paste the letter from the email into the comments section of the PM component. Thanks to you, the task is being created automatically, but I just need the letter to post to the comments column.

Hi emomoney,

Just took a look at the code and I don't think that the Email body is preserved after it is sent - and anyhow it has the html headers added by then.

I think you'd need to re-create that code in an onSubmit box to add the values into a new copy of the email template. Look at the sendemails(0 function from libraries/mail.php for the code from lines 88-103 mainly.

Bob

Thanks, I will play around with it a little.
Another question. Can somebody give me a suggestion on how to insert the current time into the database in Unix epoch timestamp?

Hi emomoney,

Try SET `column_name` = UNIX_TIMESTAMP()

Bob

Thanks. I assume I will need to add this to the on submit code, or can I add that directly in the form?

Hi emomoney,

You need to add that as part of a MySQL query - whereever you are writing to the database.

You probably don't need to if you are using ChronoForms to write as it adds a timestamp field anyhow (the format is yyyy-mm-dd hh:mm but you can always convert it if you need a UNIX version).

Bob

Hi emomoney,

You need to add that as part of a MySQL query - whereever you are writing to the database.

You probably don't need to if you are using ChronoForms to write as it adds a timestamp field anyhow (the format is yyyy-mm-dd hh:mm but you can always convert it if you need a UNIX version).

Bob

The PM component I am using uses Unix epoch time in the column I need to write to when submitting the CF. Or if you have an easier way to convert the date that is selected in your date/time picker to Unix epoch time, that would be easier.

<div class="form_item">
<div class="form_element cf_datetimepicker">
<label class="cf_label" style="width: 150px;">Date</label>
<input class="cf_datetime required" title="" size="20" id="date_1" name="cdate" type="text" />

I've tried numerous PHP scripts in the on submit code, but I'm not having any success. Any suggestions would be greatly appreciated.

$query SET cdate = UNIX_TIMESTAMP();

Hi emomoney,

It's a snippet from a MySQL command - it won't work on it's own.

The date Picker returns a field in a form set in the General tab so you could try 'U' to see if that will give you a Timestamp Otherwise use some PHP to break the date into parts and re-create a Unix Timestamp format.

Bob

Changing the U doesn't work. It simply places a U in the field after you select the date. And it wouldn't work as I need the email to include a m/d/y format.
This is the only code I was really able to insert the correct values, but this creates a new row with just the time created.

<?php
mysql_query("INSERT into jos_pf_tasks (cdate) VALUES ( UNIX_TIMESTAMP() ) ");  
?>

The other thing is when I place this in the OnSubmit code it creates this row first before it creates the row from the form submission. So, I thought I could use some redirects to a page that runs another script after the row that I need is inserted and have it just update the last row inserted, but I'm not having any luck here.

<?php
$run = mysql_query('select max(id) from id');
$lastid = mysql_fetch_row($run);
$lastid=$lastid[0];
mysql_query("UPDATE jos_pf_tasks SET cdate= 'UNIX_TIMESTAMP()' WHERE id = $lastid");  
?>

Hi emomoney,

Please try this: Add a hidden field to your form

<input type='hidden' name='udate' value='' />

Set the date form for the form to m/d/y which will give you a date like 08/06/2009

In the Onsubmit Before box add

<?php
$cdate =& JRequest::getVar('cdate', '', 'post');
JRequest::setVar('udate', strtotime($cdate), 'post');
?>

This should leave you with the m/d/y format in 'cdate' and the Unix timestamp in 'udate'. You'll need to change or add a table column name to save the udate version.

Bob

Thanks, but it's still not updating those columns. I have a workaround in place. I placed a script inside the form that displays the current epoch time, which they are instructed to copy the number into the field directly below it. Similar to captcha validation.