How to get DB Save Update Conditions to work.

Dear Max, Greyhead and others,

I have a form that has a DB Save function in it. And I must say setting it up to save to the database was a absolute breeze. However to update a existing record I can't find any info on it in the documentations or on YouTube. Maybe I look wrong but still I can't find it.

I attached the form as it is. And as you can see in the Event > DB Save > Update Conditions I wrote:

<?php
// Attempt update query execution
mysqli_query("UPDATE y39kd_secretary_subjects SET (asset_id, business, state, catid, number, gender, firstname, lastname, street, zip, location, country, phone, email, lat, lng, upload, created_by, created, checked_out, checked_out_time, modified, fields, template) WHERE id=34");
?>

Which obviously returns a error message:

Warning: mysqli_query() expects at least 2 parameters, 1 given in

But the code I normally would use:

mysqli_query($conn, $sql)

I can't because I don't know how Chronoforms uses $conn or if it is been used at all.


I am not asking to fix the above code I merely am asking for a good example on what to fill in to update data in the database. I would really love it if a Youtube video could be made on how to update the data created in this video:

https://www.youtube.com/watch?v=l4Nd-3FIO6Q

I think it would be helpful for everyone. If the video is a no go then please show me the code on how to update all the fields in the table.

The table consists out of:

CREATE TABLE IF NOT EXISTS `y39kd_secretary_subjects` (
  `id` int(11) NOT NULL,
  `asset_id` int(11) NOT NULL,
  `business` int(11) NOT NULL,
  `state` int(11) NOT NULL DEFAULT '1',
  `catid` int(11) NOT NULL,
  `number` varchar(31) DEFAULT NULL,
  `gender` int(4) NOT NULL,
  `firstname` varchar(255) NOT NULL,
  `lastname` varchar(255) NOT NULL,
  `street` varchar(255) NOT NULL,
  `zip` varchar(11) NOT NULL,
  `location` varchar(255) NOT NULL,
  `country` varchar(64) NOT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `lat` float(10,6) NOT NULL,
  `lng` float(10,6) NOT NULL,
  `upload` varchar(30) NOT NULL,
  `created_by` int(11) NOT NULL,
  `created` date NOT NULL,
  `checked_out` int(11) NOT NULL,
  `checked_out_time` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `fields` text,
  `template` int(10) NOT NULL DEFAULT '0'
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8;

I hope to achieve the following

- The user is logged in (form only available when logged in).
- The user can then see this form.
- The form checks the Joomla user details for the email and where that email matches the email address in the secretary table it displays all the values from that record into the form. (values like firstname, lastname etc.)
- The form is then altered by the user and new details(not yet present) are added.
- On submit the form UPDATES all records in the secretary table where joomla user email is the same as secretary email.

Hi hennysmafter,

if you want to supply an update condition then you can add the following code to the update conditions box in the db save:

<?php
$user = JFactory::getUser();
return array("email" => $user->email);

Best regards,
Max

Hi hennysmafter,

ChronoForms + Joomla! will handle all of the MySQL for you, All that goes in the Conditions box is code to build a WHERE statement.

Please see this FAQ for the way to do this.

Max has suggested that you use the email address as a key - it might be more robust to use the User ID if you have saved that in your table.

Bob

Hey Max,

Thank you for that code. Sadly it returns for me:

0 Cannot use object of type JUser as array

But I got a slightly modified code working see the reply on the main post.

Hey GreyHead,

Thanks for the link to the FAQ so far I got a working code. See the results below to the main post.

Hey Max and GreyHead,

I got it partially working now with this:

<?php
return array( 'email' => $form->data['email'] );
?>

Still a slight issue. This form requires the user to already have it filled in as it no longer adds records just updates them.

How can I make it add if it does not exist and if the email exists it updates?

Hi hennysmafter ,

I restored the form backup you posted but I can't work out what this form is actually supposed to do either from the form or from the notes in your first post :-(

Bob

How can I make it add if it does not exist and if the email exists it updates?

You will need a "db read" with the same condition, in the "found" use the "db save" with the condition to update, in the "not found" use the "db save" without the condition to save.

Best regards,
Max

Hey GreyHead,

I send you a PM and in there it basically says what this form should do but as it is the form is by no means complete. I do however have a complete Quote/Audit form but that one has not yet the fields in it that are in this test form.

The reason I built this form was just to get the DB stuff working. Which sadly I haven't got it to work. Really hope you are in need of Coffee :-)

Thanks Max,

I just tested it and it works for the `Found` part but by the `Not Found` it does not create a new record. I renamed both the `Data` section so they would not have the same name. See the attachment if you want.