Forums

Overwrite previous submission/entry?

Bullfn33 26 Jul, 2009
Is there a way to have form submissions overwritten in the table if the same user already submitted an entry? If so, how can I go about this? I searched the forums for an example but came up empty. thanks
Max_admin 26 Jul, 2009
yes, you need to load this record's primary key value in the form and get it submitted with other data, say your table key is cf_id and the value of this record you want to update is 44 then you should have this in your form code:

<input type="hidden" name="cf_id" value="44" />


cheers
Max
Max
ChronoForms developer...
Did you try ChronoMyAdmin for managing your Joomla database tables ?
Bullfn33 26 Jul, 2009
But will this overwrite the record of whoever happens to submit the form or just the one record? What I'm trying to do is have one record per user in the table. So if a user submits the same form a second time, his second submission will overwrite his first. But I want it to be able to recognize if any given user submits the form a second time.
GreyHead 27 Jul, 2009
Hi bullfn33,

A primary key has to be unique so there can only be one entry for each primary key. If you use the User_ID as a primary key (or make sure that there is a 1-1 relationship between the primary key and the User_ID) than this will work as you want.

It depends a bit on how your table is set up, if you created it with ChronoForms then you will probably need to alter it in PHPMyAdmin to define a User_ID field as a primary key.

Bob
Max_admin 27 Jul, 2009
Hi bullfn33,

you may add a counter field to your form code and update it when the form is reloaded, so you can see how many times the users submitted this form!

Max
Max
ChronoForms developer...
Did you try ChronoMyAdmin for managing your Joomla database tables ?
Bullfn33 27 Jul, 2009

Hi bullfn33,

A primary key has to be unique so there can only be one entry for each primary key. If you use the User_ID as a primary key (or make sure that there is a 1-1 relationship between the primary key and the User_ID) than this will work as you want.

It depends a bit on how your table is set up, if you created it with ChronoForms then you will probably need to alter it in PHPMyAdmin to define a User_ID field as a primary key.

Bob



Bob,

I created a new table for my form and went to phpmyadmin and changed the primary key from cf_id to cf_user_id but the table doesn't collect any data. What could be the reason for this?
nml375 28 Jul, 2009
Hi,
That is actually an issue with the JTable objects CF uses for DB connections. I did encounter a similar issue writing a custom form app. that would add posts to a FireBoard forum. The issue at hand is, if the value of the primary key is not 0, JTable::store() will actually try to do an update of the post with the current value of the primary key, rather than insert a new post. But since we're trying to create a new item, there's nothing to update.

There are a few ways around this though, you could keep cf_id as the primary key, but before the db storage, do a manual query to see if there's any posts from this user before (check the cf_user_id value of the table). If there's a match, add the cf_id to the submitted fields (so that it's added to the JTable object), and proceed as usual. If there's no match, make sure cf_id is not set.

Another way 'round is a customized JTable and highly customised code, which requires you to first load the data if you'd like an update, otherwise it'd be treated as an "insert".

Other ways might be to manually add an empty record with the corresponding primary key before the database save.. Or do the db storage by hand completely.

/Fredrik
Bullfn33 28 Jul, 2009
I'm a bit confused. It sounds like this won't work unless the cf_id is the primary key but the cf_id isn't unique to each user.

There are a few ways around this though, you could keep cf_id as the primary key, but before the db storage, do a manual query to see if there's any posts from this user before (check the cf_user_id value of the table). If there's a match, add the cf_id to the submitted fields (so that it's added to the JTable object), and proceed as usual. If there's no match, make sure cf_id is not set.


This sounds viable but beyond my capabilities.

Other ways might be to manually add an empty record with the corresponding primary key before the database save..


I think I tried this right but it is not overwriting. I had the table set up with the cf_user_id as the primary key and added an empty record. Then I submitted the from data with
<input name="cf_user_id" type="hidden" id="63" />
in the form. I don't know, I'm kind of lost.
nml375 28 Jul, 2009
Hi,
I know the first option might seem overwhelming, and it's easy for me as a coder to say that it's easy. Hopefully the skeleton code below will be helpful, just make sure this code is executed before the actual database save is done:

<?
$db &= JFactory::getDBO(); /* Retrieve the database object, this allows us
                            * to send custom SQL-queries further down */
$user &= JFactory::getUser(); /* Also retrieve the user object, we'll need this for the user id */

$query = sprintf('SELECT `cf_id` FROM %s WHERE `cf_user_id` = %i',  /* Our skeleton query */
  $db->nameQuote('#__tablename'),                                   /* Our database table name,
                                                                     * you'll need to edit this */
  $user->id                                                         /* The user id */
);
$db->setQuery($query);       /* Load the query into the database object */
$result = $db->loadResult(); /* Then run the query and get the result */

JRequest::setVar('cf_id', $result ? $result : 0); /* If we got a valid result, set cf_id to that value,
                                                   * otherwise set cf_id to 0 */
?>


/Fredrik
Bullfn33 28 Jul, 2009
nml375,

Thanks for the code. So far, I haven't had any luck to get it to overwrite. The only things different seems to be the data is collecting in the reverse order(newest on top). What I did was enter the table name in the code where indicated and placed the code in the "On Submit code - before sending email" text field. I'm probably missing something..will keep trying.
nml375 30 Jul, 2009
Hi,
My apologies, a few sloppy errors managed to sneak into the code 😶

The following will work (as long as it is executed before the Autogenerated code is):
No comments in this one though...
<?
$db =& JFactory::getDBO();
$user =& JFactory::getUser();

$query = sprintf('SELECT `cf_id` FROM %s WHERE `cf_user_id` = %d',
  $db->nameQuote('#__onlyonce'),
  $user->id
);
$db->setQuery($query);
$result = $db->loadResult();

JRequest::setVar('cf_id', $result ? $result : 0);
?>

Just remember to edit the 'onlyonce' to match your database table..

/Fredrik
Bullfn33 31 Jul, 2009
It's working great now..thanks so much Fredrick! I think this will help out more than just me.

For others looking to overwrite previous entries from each user so that there is only one from each user in your table, copy the code supplied above, place the code in the On Submit code - after sending email text field and type in your table name where it says "onlyonce." I tried it in other places and it didn't seem to work for me until I placed it in that text field.
DharmaMix 14 Aug, 2009
It definitely helped. Once inserted into the Form Code, it immediately meant new entries could be created but any located previous entries from a user would be updated instead. I ended up slightly modifying it as a hidden input. Just change the table name and insert at the end of the Form Code:

<input id="hidden_1" name="cf_id" type="hidden" value="<?php
$db =& JFactory::getDBO();
$user =& JFactory::getUser();

$query = sprintf('SELECT `cf_id` FROM %s WHERE `cf_user_id` = %d',
  $db->nameQuote('name_of_database_table'),
  $user->id
);
$db->setQuery($query);
$result = $db->loadResult();

echo ($result ? $result : 0);
?>" />


If you have another hidden field with id "hidden_1", just be sure to change this code's hidden ID to make sure it is unique. Anyways, as this has proven extremely useful, I wanted to share the mod. Mad gratitude for the info as well as for the question. Cheers!
nml375 14 Aug, 2009
Hi DharmaMix,
Using hidden inputs like that should work well.
Just a word of caution though, as this approach expects your users to play nice (it's not difficult to alter the value of a hidden field from within the browser itself). Also, in the case of the notorious double-clickers, you might end up with multiple records for the same user.

/Fredrik
DharmaMix 17 Aug, 2009
Great info! Good to know now, while still in dev, instead of be surprised at a later time. Thanks again! 8)
laustin 18 Sep, 2009
Thank you for the code & good explanation!
I was trying to figure this out for a while!!!
This topic is locked and no more replies can be posted.