Edit Existing Records if exists.

Hi, I'm a HTML/PHP developer, but have just been handed a project with an existing Joomla/Chronoforms site. I'm new to Joomla and Chronoforms. Here's what I'm being asked to do. My client has a form that adds a record to a table. What we want, is for the record to record the user id in a user_id field, and then, if he returns to the same page (the form), it will recognize that he already has a record, populate the fields with the already submitted information, and allow him to edit that information, and resubmit it to update that existing record. Is this something that Chronoforms is capable of doing? I've already found how to access the user_id information, and where to enter my PHP code, what I can't seem tot find is how to tell the form to check for an existing record and use an UPDATE query instead of an INSERT query. This would be simple if it were simply PHP, but I'm trying not to create pages outside of Joomla.

Thanks in advance.

Hi mythx_lv,

If you use the ChronoForms DB Connection then it will update the record if the results to be saved include an existing Primary Key value from the table (only columns with data in the form $_POST array will be updatd, other columns will be left unchanged). Otherwise it will insert a new record.

If the table was created with the ChronoForms Create Table icon then the Primary Key is probably cf_id (and the user id is cf_user_id).

If you need to look up the Primary Key from the user-id then you can do that in the Form HTML box and include the result in a hidden input.

Bob

Bob,
Thanks for the reply. That makes a lot of sense. Based on what you said, I will have to modify the table so the field that stores the userid is the primary key, that'll be easy enough. How will I tell the form to check for an existing record and populate the fields with existing data? Right now it pulls up a blank form. I'm assuming there's some Joomla speak/code that'll do the trick.

Thanks

Jason

Hi mythx_lv,

Add a hidden input with the cf_id using a PHP snippet in the Form HTML:

<?php
if ( !$mainframe->isSite() ) { return; }
$db =& JFactory::getDBO();
$user = JFactory::getUser();
$query = "
  SELECT `cf_id` 
    FROM `#__some_table` 
    WHERE `cf_user_id` = ".$user->id." ;
";
$db->setQuery($query);
$d = $db->loadResult();
if ( $d ) {
  echo "<input type='hidden' name='cf_id' id='cf_id' value='".$d."' />";
}
?>

Bob

Thanks,
I'm giving it a shot now. Is that #__ preceding the table name required for any table, or is that just part of the sample you're giving?

Alright, I got that part working, it updates records if they already exist. The problem now is that it loads a blank form and all data needs to be reentered each time. How can I get it to load the entire record as an array? I'm sure it's gotta be something simple, or should I use standard PHP methods (i.e. mysql_fetch... etc....)

Thanks again

Hi mythx_lv,

The #__ prefix is Joomla code - it will be replaced by the prefix for your site (usually 'jos_').

You can extend my code to retrieve previous values

<?php
if ( !$mainframe->isSite() ) { return; }
$db =& JFactory::getDBO();
$user = JFactory::getUser();
$query = "
  SELECT *
    FROM `#__some_table`
    WHERE `cf_user_id` = ".$user->id." ;
";
$db->setQuery($query);
$d = $db->loadObject();
if ( count($d) ) {
  echo "<input type='hidden' name='cf_id' id='cf_id' value='".$d->cf_id."' />";
}
?>
. . .
<input . . . name='xxx' value='<?php echo $d->xxx; ?>' />
. . .

Bob

PS The Profile Plugin may be able to do some of this work for you.

Bob,
Thanks for all your help, this is exactly what I need and it's working beautifully.

Thanks again

Jason