Forums

How do I save a single field from my form to a different table to the others, but in the same database?

Hi abasel,

Use a second DB Save action for the second table. ChronoForms will save data where the column and input names match.

Bob

I am almost there but what I was actually wanting was not just to save the data to a different table but rather to update a particular field in a different table.

Hi abasel,

Then you need to have the id for the record to be updated as well. It might be easier to hand-code the query to do this - especially if both tables use the same primary key name.

Bob

I presume that I use a custom code block with a MySQL entry something like

update tablename set columnname = "newdata" where columnname = value;

But what Joomla code do I use for
[code]mysql_connect("localhost","username","password");[
mysql_select_db("db") /code]

Or will it already know this as it is used for the site already?

Hi abasel,

I have this on a macro:

$db =& JFactory::getDBO();
$query = "
    SELECT ``
        FROM `#__`
        WHERE `` = '' ;
";
$db->setQuery($query);
$data = $db->loadObjectList();
//$data = $db->loadResult();

It needs to be in <?php tags and you won't be using s SELECT query.

Also you need to add $db->query(); to execute the query.

Bob

Thanks for the following... I just am trying to understand the code properly but the 2nd to last line leaves me cold... what does it do and then do I use the $data variable somewhere?

$db =& JFactory::getDBO();
$query = "
    SELECT ``
        FROM `#__`
        WHERE `` = '' ;
";
$db->setQuery($query);
$data = $db->loadObjectList();
$db->query(); 

I am obviously missing something.

I want to update fields used and user_invoice in wrunj_chronoforms_data_Vouchers where the field voucher is equal to $form->data['token']. All the $form values are coming through correctly but my syntax/logic is wrong.

<?php
$form->data['used']=date('Y-m-d');
$form->data['user_invoice']=$form->data['invoiceno'];


$db =& JFactory::getDBO();
$query = "
    UPDATE `used,user_invoice`
        FROM `#__chronoforms_data_Voucher`
        WHERE `voucher` = $form->data['token'] ;
";
$db->setQuery($query);
$data = $db->loadObjectList();
$db->query();

?>

Hi abasel.

Please try:

<?php
$db =& JFactory::getDBO();
$query = "
  UPDATE `#__chronoforms_data_Voucher`
   SET `used` = '".date('Y-m-d')."', `user_invoice` = '{$form->data['invoiceno']}'
   WHERE `voucher` = '{$form->data['token']}' ;
";
$db->setQuery($query);
$db->query();
?>

Bob

Still not updating the database

The Custom code is now

<?php

if (!($form->data['voucher']=='')) {
$db =& JFactory::getDBO();
$query = "
  UPDATE `#__chronoforms_data_Voucher`
   SET `used` = '".date('Y-m-d')."', `user_invoice` = '{$form->data['invoiceno']}'
   WHERE `voucher` = '{$form->data['voucher']}' ;
";
echo $query;
$db->setQuery($query);
$db->query();
}
?>

The echo line returns: UPDATE `#__chronoforms_data_Voucher` SET `used` = '2013-02-08', `user_invoice` = 'TD8876C' WHERE `voucher` = 'PD9956D' ;

The table below however is not updated

CREATE TABLE IF NOT EXISTS `wrunj_chronoforms_data_Vouchers` (
  `cf_id` int(11) NOT NULL AUTO_INCREMENT,
  `cf_uid` varchar(255) NOT NULL,
  `cf_created` datetime NOT NULL,
  `cf_modified` datetime NOT NULL,
  `cf_created_by` int(11) NOT NULL,
  `cf_modified_by` int(11) NOT NULL,
  `cf_ipaddress` varchar(255) NOT NULL,
  `cf_user_id` int(11) NOT NULL,
  `voucher` varchar(255) NOT NULL,
  `value` varchar(255) NOT NULL,
  `expiry` varchar(255) NOT NULL,
  `used` varchar(255) NOT NULL,
  `user_invoice` varchar(255) NOT NULL,
  PRIMARY KEY (`cf_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `wrunj_chronoforms_data_Vouchers`
--

INSERT INTO `wrunj_chronoforms_data_Vouchers` (`cf_id`, `cf_uid`, `cf_created`, `cf_modified`, `cf_created_by`, `cf_modified_by`, `cf_ipaddress`, `cf_user_id`, `voucher`, `value`, `expiry`, `used`, `user_invoice`) VALUES
(2, 'b1f30c9aba15ed04528f4893963c11f7', '2013-02-06 03:01:21', '0000-00-00 00:00:00', 676, 0, '101.98.137.16', 676, 'DG3615X', '25', '2013-03-23', '0000-00-00', ''),
(3, 'aede4a573eb143735d4153e866cce3ff', '2013-02-08 04:19:27', '0000-00-00 00:00:00', 676, 0, '101.98.137.16', 676, 'PD9956D', '30', '2013-02-28', '0000-00-00', '');

Hi abasel,

It looks as though the table name needs an extra 's' in your query: UPDATE `#__chronoforms_data_Vouchers`

Bob

Hi Bob, I can't believe I keep falling for those sort of errors... thanks it works all 100%

One more question along these lines and I think that I will have it sorted.

I am trying to work out how one uses custom code to read in data from another table..... with out using the DB record loader option.

I thought that the echo command would display the results of the query as an array but nothing except the word "Test" shows, together with the values of $$query and $form->data['code'].

<?php
$db =& JFactory::getDBO();
$query = "
    SELECT `title,coursecode,cost, description,startdate,enddate`
        FROM `#__chronoforms_data_Programmes`
        WHERE `coursecode` = '{$form->data['code']}' ;
";
$db->setQuery($query);
$db->query();
$data = $db->loadResult();
echo $form->data['code'].'/'.$query;
echo '<p>Test: '.$data.'</p>';
?>

Hi abasel,

You need to fix the quotes in the SELECT line, they should be round each column name (or none at all, but I try to add them).

$query = "SELECT `title`, `coursecode`, `cost`, `description`, `startdate`, `enddate`

You don't need the $db->query() here as the query will be executed by the next command . . .

You've use $db->loadResult() which will only load a single data point e.g. the result of a COUNT query. You need to use $db->loadObjectList() or $db->loadAssocList() depending on on the for you want the results in. See here for a list of the methods and what they do.

Bob