Forums

Hi,

This is question which is part from a more complex question I asked here, but I thought it is important enough to dedicate a post just to clarify this issue:

I try to INSERT/UPDATE a table where the unique index is made of two columns.

Looking at the submitted query (by turning the debugging on), I see that there are two identical SHOW FULL COLUMNS queries, in a raw (I have no idea why two are necessary) and then an UPDATE query, with only one of the PRI keys in the WHERE statement.

For example, here the index is made of the 'artist_id' + 'lang_content'. The form was suppose to create an INSERT query, but this is what I got by looking at the debugging info:

SHOW FULL COLUMNS
  FROM `jos_artist_details`

UPDATE `jos_artist_details`
  SET `artist_id`='8',`name`='Jaque',`description`='He is smart',`edu`='TBD'
  WHERE `lang_content`='fr'

I don't get any INSERT query at all.

Does CF4 can deal with multiple columns index and if yes, is there any necessary action we should take care of?

Thanks,
Emanuel.

Hi Emanuel,

I think that you'd have to hand-code the query to updated on a double index. ChronoForms uses the bind() and save() methods Joomla! database layer and - as far as I remember - the code in there only handles correctly a save/update on a table with a single numeric primary key.

Bob

Thanks Bob.
Yes, this is what I did since posting here.

But since in the logic of building the INSERT/UPDATE query, there is a check for the table structure (by using SHOW FULL COLUMNS, which point to the multiple column index),I thought it is not so complicated to generate the right UPDATE query which will work with such a common scenario.

To whom it may be helpful, this is how I did it:
The example includes 4 fields, where 'artist_id' and 'lang_content' created together a unique index.
I removed the DB SAVE action and in the ON SUBMIT event, I added a CUSTOM CODE action:

<?php
$id = $form->data['artist_id'];
$lang = mysql_real_escape_string($form->data['lang_content']);
$name = mysql_real_escape_string($form->data['name']);
$desc = mysql_real_escape_string($form->data['description']);

$db =& JFactory::getDBO();
$q = "INSERT INTO #__artist_details
 (artist_id
, lang_content
, name
, description
 VALUES ($id, '$lang', '$name', '$desc')
 ON DUPLICATE KEY UPDATE
 name='$name'
, description='$desc';";

$db->setQuery($q);
if (!$db->query()){
   $jAp =& JFactory::getApplicaion();
   $jAp->enqueueMessage(nl2br($db->getErrorMsg()),'error'); return;
}
?>

Pay attention to the ON DUPLICATE KEY UPDATE which together with the INSERT, crates a new row if the combination of the two fields is not in the database yet, or update the existing row if the combination is there (this approach is good only if there is only one unique index, single or multiple column).
Also, pay attention to mysql_real_escape_string() which add slashes to characters which may break the sql query. This one is specifically good for mysql database. If you use a different database, set the right escape method.

Any way, thanks again!
Emanuel.

Hi Emanuel,

Great, thank you.

Bob