Update table data

Hi folks...
I'd like chronoforms to update an already existing record in my table.
For doing this i've tried to set a query to find the requested cf_id of my record:

<?php
$db =& JFactory::getDBO();
$query = "
SELECT `cf_id`
FROM `#__chronoforms_my_table` 
WHERE `check4` != '' AND `radio0` = 'F' AND `cat_barattoli` = 'D' AND `rifiuto_barattoli` = ''AND `edizione_anno` = '$anno_impostato'
ORDER BY somma_pts_barattoli DESC, date_int ASC
LIMIT 1
";
$db->setQuery($query);
$cf_id = $db->loadResult();
?>

It works...

Now I want chronoforms to select from my table the record where cf_id = $cf_id and update the column class_gen_barattoli_dd with a value (100).

I've tried to write down this but it doesn't work.

<?php
$db =& JFactory::getDBO();
$query = "
UPDATE `#__chronoforms_iscrizioni_olimpiadi`
SET `class_gen_barattoli_dd` = '100'
WHERE `cf_id` = '$cf_id'
";
?> 

Every time i submit my form it always creates a new record and saves only $cf_id.

Any idea?

Thanks.

Now i got it...

I found the solution on this forum at this link:
http://www.chronoengine.com/forums/index.php?option=com_chronoforums&cont=posts&f=2&t=18992&p=56165&hilit=query+update#p56165

Sorry for posting a question already answered but i didn't see it before...

Federico

I thought my problem was solved but the code i found ain't enough.
I need to assign a value to the first 10 place of a ranking (1° place = 100, 2° place = 75, etc.)

<?php
// 1° place pts

$query = "
    SELECT `pts`
        FROM `#__chronoforms_pts`
            WHERE `place` = '1';
            
";
$db->setQuery($query);
$pts_1place = $db->loadResult();

// 1° place cf_id

if ( !$mainframe->isSite() ) { return; }
$db =& JFactory::getDBO();
$query = "
SELECT `cf_id` 
FROM `#__chronoforms_my_table` 
WHERE `check4` != '' AND `radio0` = 'F' AND `cat_barattoli` = 'D' AND `rifiuto_barattoli` = ''AND `edizione_anno` = '$anno_impostato'
ORDER BY somma_pts_barattoli DESC, date_int ASC
LIMIT 1
";
$db->setQuery($query);
$cf_id_selected = $db->loadResult();

// 1° place cf_user_id ('cause i don't want it to be changed)

$query = "
    SELECT `cf_user_id`
        FROM `#__chronoforms_my_table`
            WHERE `cf_id` = '$cf_id_selected';
            
";
$db->setQuery($query);
$cf_user_id_selected = $db->loadResult();

// writing obtained data

if ( $cf_id_selected != '') {
  echo "
<input value='".$cf_user_id_selected."' id='hidden_55' name='cf_user_id' type='hidden' />
<input type='hidden' name='cf_id' id='cf_id' value='".$cf_id_selected."'/>
<input type='hidden' name='class_gen_barattoli_dd' id='class_gen_barattoli_dd' value='$pts_1place' />";
}
?>

For the first place it works but when I copy this code and edit it for finding and writing other positions it gets stuck.
I guess it happens 'cause when i copy the code for other positions it finds more than one field with the same name.
Could you help me? Is there a solution?
Thanks.

Federico

HI federico85,

I'd get all ten points and results, sorting both in the correct order into a results array and then use a foreach loop to match the two up.

Bob

Hi Bob... Thanks for your reply...

As you know i don't know much about php and so i'm not sure of what i've done. I've tried that way but with no success. It always creates a new empty record instead of editing the existing.

<?php
if ( !$mainframe->isSite() ) { return; }
$db =& JFactory::getDBO();
$query = "
SELECT `cf_id` 
FROM `#__chronoforms_my_table` 
WHERE `check4` != '' AND `radio0` = 'F' AND `cat_barattoli` = 'D' AND `rifiuto_barattoli` = ''AND `edizione_anno` = '$anno_impostato'
ORDER BY somma_pts_barattoli DESC, date_int ASC
LIMIT 10
";
$db->setQuery($query);
$cf_id = $db->loadResult();

$query = "
    SELECT `cf_user_id`
        FROM `#__chronoforms_iscrizioni_olimpiadi`
            WHERE `cf_id` = '$cf_id';
            
";
$db->setQuery($query);
$cf_user_id = $db->loadResult();

foreach ($cf_id as $row) {
   echo "<input type='hidden' name='cf_id' id='cf_id' value='$row->cf_id'/>
         <input type='hidden' name='cf_user_id' id='cf_user_id' value='$row->cf_user_id'/>
         <input type='hidden' name='cf_id' id='cf_id' value='$row->pts'/>
";
    }

?>

What's wrong?

Thanks in advance

Maybe i didn't understand how to use arrays... Could you explain me?

Sorry for bothering you again... I tried all afternoon and maybe i'm about to solve my problem...

//
<?php 
// pts selection
$db =& JFactory::getDBO();
$query = "
    SELECT `pts`
        FROM `#__chronoforms_pts_table`
               ORDER BY position ASC
               LIMIT 10
";
$db->setQuery($query);
$pts = $db->loadObjectList();

// cf_user_id selection

if ( !$mainframe->isSite() ) { return; }
$db =& JFactory::getDBO();
$query = "
SELECT `cf_user_id` 
FROM `#__chronoforms_my_table` 
WHERE `check4` != '' AND `radio0` = 'F' AND `cat_barattoli` = 'D' AND `rifiuto_barattoli` = ''AND `edizione_anno` = '$anno_impostato'
ORDER BY somma_pts_barattoli DESC, date_int ASC
LIMIT 10
";
$db->setQuery($query);
$cf_user_id = $db->loadObjectList();

// cf_id selection

if ( !$mainframe->isSite() ) { return; }
$db =& JFactory::getDBO();
$query = "
SELECT `cf_id` 
FROM `#__chronoforms_my_table` 
WHERE `check4` != '' AND `radio0` = 'F' AND `cat_barattoli` = 'D' AND `rifiuto_barattoli` = ''AND `edizione_anno` = '$anno_impostato'
ORDER BY somma_pts_barattoli DESC, date_int ASC
LIMIT 10
";
$db->setQuery($query);
$cf_id = $db->loadObjectList();

// i tried to join obtained values in an array

$prova = array($pts, $cf_id, $cf_user_id);

foreach ($prova as $row) {
          echo 
         "<input value='$cf_id' type='text' name='cf_id'/>
          <input value='$pts' type='text' name='class_gen_barattoli_dd'/>
          <input value='$cf_user_id' type='text' name='cf_user_id'/>
";
    }
?>

It still doesn't work...http://www.chronoengine.com/forums/posting.php?mode=reply&f=2&t=19891#

Hi Federico,

Are you updating 1 record only every form submit or multiple ? if you are updating multiple ones then I think that you will need an UPDATE statement.

but if it's only 1 and you need to change some field's value then set it in the POST array through :

JRequest::setVar('column_title', value);

if it clears other columns then you must have an UPDATE statement I think.

Regards,
Max

Hi Max...
Thanks for your reply..
I need to update more than one record...
Now i'm able to find out the first 10 positions and points sorted in the correct order but i'm not able to join them into a results array and then use a foreach loop to match the two up as Bob said:

HI federico85,

I'd get all ten points and results, sorting both in the correct order into a results array and then use a foreach loop to match the two up.

Bob

Thanks in advance.

Federico

Hi Federico,

you can use the "loadObjectList" method to get all the records needed, the result will be an array of objects, you can then loop through the array updating each record using the ->id value from the object.

Regards,
Max

Hi Max...
Thanks for your reply...
Now i've been able to get my results as i wanted but i guess there's something else wrong:

<?php
$db =& JFactory::getDBO();
$query = "
SELECT `punteggio`
FROM `#__chronoforms_posizioni_punteggi` 
ORDER BY punteggio DESC 
LIMIT 10";
$db->setQuery($query);
$rows = $db->loadObjectList();

$db =& JFactory::getDBO();
$query = "
SELECT `cf_id` 
FROM `#__chronoforms_iscrizioni_olimpiadi` 
WHERE `check4` != '' AND `radio0` = 'F' AND `cat_barattoli` = 'D' AND `rifiuto_barattoli` = ''AND `edizione_anno` = '$anno_impostato'
ORDER BY somma_pts_barattoli DESC, date_int ASC
LIMIT 10
";
$db->setQuery($query);
$cf_id = $db->loadObjectList();
?>

But now i don't know how to make it write the obtained data into my fields...
I tried with array_merge to join $cf_id and $rows into $result but every time i try to open my form it writes me only cf_ids...

<?php
$result = array_merge ($cf_id, $rows);

foreach ($cf_id as $result) {

echo 
"
<input value='$result->cf_id' type='text' name='cf_id'/>
<input value='$result->punteggio' type='text' name='class_gen_barattoli_dd'/>
";
}
?>

Could you help me understand what's wrong... It's drivin' me crazy...
Thanks in advance...

Federico