sql error

Hi again,
I thought I had this one licked, but once I moved my forms to my live site, I ran into it again. When submitting data to be saved in my database, I get the following error:
Tablecomprofiler::store failed - Duplicate entry '0' for key 1 SQL=INSERT INTO `jos_comprofiler` ( `user_id`,`cb_xbox`,`cb_branch`,`cb_rank`,`cb_officernotes`,`cb_lastactive`,`cb_lastpromotiondate` ) VALUES ( '69','me bbq man','Maelstrom','Sergeant','','2011-10-09','2011-08-23' )
I tried doing a 'DROP INDEX on what I assume is key 1, but it didn't work. The data doesn't even get saved there, so I am really at a loss as to what to try next.

The more I think about it, the less I like the 'INSERT INTO' being done here. My data is just updating what is already in the table, not creating a new entry. I don't think this is the right command for what I'm trying to do.

I'm thinking I have to do a custom code here, maybe this?

$con = mysql_connect("localhost","db_user_name","db_password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("db_name", $con);

mysql_query("UPDATE jos_comprofiler SET cb_xbox = $form->data['cb_xbox'], cb_branch = $form->data['cb_branch'], cb_rank = $form->data['cb_rank'], cb_lastactive = $form->data['cb_lastactive'], cb_lastpromotiondate = $form->data['cb_lastpromotiondate'], cb_officernotes = $form->data['cb_officernotes'];
WHERE user_id = $form->data['user_id'];

mysql_close($con);

Thanks

So, after many hours of screwing around with this and reading every post I can find, I came up with the following code to do a sql update during the submit event

$db =& JFactory::getDBO();
$sql = "UPDATE jos_comprofiler SET `cb_xbox` = '{$form->data['cb_xbox']}', `cb_branch` = '{$form->data['cb_branch']}', `cb_rank` = '{$form->data['cb_rank']}', `cb_lastactive` = '{$form->data['cb_lastactive']}', `cb_lastpromotiondate` = '{$form->data['cb_lastpromotiondate']}', `cb_officernotes` = '{$form->data['cb_officernotes']}'
WHERE `user_id` = '{$form->data['user_id']}'";
echo $sql;
$db->setQuery($sql);
$db->query();
echo $db->getQuery();
echo $db->getErrorMsg();

My output is
UPDATE jos_comprofiler SET `cb_xbox` = 'me bbq man', `cb_branch` = 'Maelstrom', `cb_rank` = 'Sergeant', `cb_lastactive` = '2011-10-09', `cb_lastpromotiondate` = '2011-08-23', `cb_officernotes` = 'test' WHERE `user_id` = '69'UPDATE jos_comprofiler SET `cb_xbox` = 'me bbq man', `cb_branch` = 'Maelstrom', `cb_rank` = 'Sergeant', `cb_lastactive` = '2011-10-09', `cb_lastpromotiondate` = '2011-08-23', `cb_officernotes` = 'test' WHERE `user_id` = '69'Data Array:

So no errors, I should be golden. NOPE. Still isn't saving the data to the table. What gives?

Hi busterocaps,

ChronoForms (actually the Joomla! database code) will automatically use UPDATE if the table has a numeric primary key and there is a matching value in the form data. If there is no primary key, or no match then it will do an INSERT.

Bob

Hi Bob,
The problem I'm running into seems to run counter to your reply. When using the DB save function, I get this error:
Tablecomprofiler::store failed - Duplicate entry '0' for key 1 SQL=INSERT INTO `jos_comprofiler` ( `user_id`,`cb_xbox`,`cb_branch`,`cb_rank`,`cb_officernotes`,`cb_lastactive`,`cb_lastpromotiondate` ) VALUES ( '69','me bbq man','Maelstrom','Sergeant','test','2011-10-09','2011-08-23' )

So it would seem that the db save is trying to do an insert, and isn't able to because the field user_id is already present in the table.

Thanks,
Mike

Hi Mike,

That looks like a problem with the index setting on the table. Please see (for example) this post from the Joomla! forums. It's talking about a different table but I think the problem is probably the same.

Bob