Reading from one table & saving to another

I've been struggling with this for a few days now. I'll try to make this as simple as possible.

The table(s) have the typical fields (user_id, etc.). But I'll just talk about 3 for simplicity. I want to pull 2 fields into the dropdown (which I've accomplished). The fields showing in the dropdown are formfile and tracking. But, when someone selects the dropdown, I want to save 3 fields to a database tabe (created with 'create table' in the form manager) from that record (formfile, tracking, and company). It seems that I've only been able to save up to 2 fields in the database (the 2 that are associated with the dropdown field). Is there a way to associate more than 2 fields with the dropdown so that I can save more than 2 fields in this new database that I'm writing to?

On a side note... the 'create table' does not seem to be working as expected. I have 2 dropdowns, save form, click 'create database' and it does not automatically put the dropdown field names in like it did before.

Hi tim92109,

Sorry but I don't understand your questions here :-(

What do you mean by ' I want to pull 2 fields into the dropdown' ?

You should be able to save any number of data items into the table provided that there are entries in the $form->data[''] array with names that match the column names.

The Create Database icon scans the form HTML - see the Code tab - to identify the inputs in the form. In my experience it gets those pretty accurately provided that the HTML is valid. In any case, you can add (or remove) any columns and the column Types in the Create Database page.

Bob

Hi Bob,

Sorry, it was very hard to put my question into words. Let me put it a different way....

When saving form data into the database, it's easy. Like you said, the column names just need to match the field names. But what about data from the DB Read action? If I pull 4 fields from the DB using DB Read called a, b, c, and d... I can't just create database columns for them like the form fields. I can imagine that I would need a custom HTML action that says something like... <?php $form->data['a'] = the database field ?>

As far as the 'create database' issue, it's not a big deal. Please don't waste your time on this as it's just as easy to create the columns myself. I'll check the HTML... maybe that's not being creating correctly for dropdown fields.

Hi Tim,

The DB Read adds the data you read into the $form->data[''] array along with all the other form data so it can be saved in the same way. If the column names don't match, or if you are using Model IDs, then you may need some custom code to map/transfer the data so that it has the correct column-matching name.

<?php
$form->data['from_db_read'] = $form->data['for_db_save'];
?>

Bob

Thanks for that info, Bob. I didn't realize that... actually very cool. As usual, I'm making things more complex than they need to be haha.

Cup of coffee? I owe you like a large can of pure Colombian coffee by now, Bob. I'll get you back one of these days when I have it. Thanks again!

Ya know... I thought for sure it would work with the new info. But, I just can't get it to.

Before the DB Save action in the 'on submit' section, If I create a custom html action that says:

<?php
$form->data['company'] = "Google";
?>

It works...

If I create one like:

<?php
$form->data['company'] = $form->data['profile']['company'];
?>

I get an empty field in the database.

The field with dynamic data turned on and working fine is called 'company'. The database column is called 'company'. I don't get it. Been working on this for like 4 days and tested it like 100 times with no good results. I've tried with and without a model id... I've pretty much tried variations of everything I could think of and no results in the DB.

Hi Tim,

Please drag a Debugger action into the On Submit event, then submit the form and post the debug - including the 'dummy emails' results here. That way we can see exactly what values you have.

Bob

Okay... thanks. I can say that whenever I use the syntax suggested to put the model ID in PHP (like $form->data['profile']['company']), I get PHP errors. By the way, I've put everything back to exactly the way it was when it wasn't saving the field to the database.... Now it's not even creating a record at all.

From Debugger:

Data Array
Array
(
[chronoform] => sendform
[event] => submit
[option] => com_comprofiler
[view] => userprofile
[Itemid] => 114
[task] => userprofile
[profile] => TJ Inc.
[button2] => Submit
[sendform] =>
)
Array
(
)
Errors
Array
(
)
Debug Info
Array
(
)

Warning: Illegal string offset 'company' in /home/tjohns5/public_html/8130-3/administrator/components/com_chronoforms5/chronoforms/actions/custom_code/custom_code.php(20) : eval()'d code on line 1

Warning: Illegal string offset 'user_id' in /home/tjohns5/public_html/8130-3/administrator/components/com_chronoforms5/chronoforms/actions/db_save/db_save.php on line 93

Warning: array_values() expects parameter 1 to be array, string given in /home/tjohns5/public_html/8130-3/libraries/cegcore/libs/model.php on line 1393

Warning: Illegal string offset 'uniq_id' in /home/tjohns5/public_html/8130-3/administrator/components/com_chronoforms5/chronoforms/actions/db_save/db_save.php(73) : eval()'d code on line 9

Warning: Illegal string offset 'created' in /home/tjohns5/public_html/8130-3/libraries/cegcore/libs/model.php on line 1433

Warning: Invalid argument supplied for foreach() in /home/tjohns5/public_html/8130-3/libraries/cegcore/libs/model.php on line 1446

Warning: Illegal string offset 'id' in /home/tjohns5/public_html/8130-3/administrator/components/com_chronoforms5/chronoforms/actions/db_save/db_save.php on line 98

I just tried to create the very simplest version of this and now get a PHP error:
Fatal error: Cannot redeclare class GCore\Models\radio in /home/tjohns5/public_html/8130-3/administrator/components/com_chronoforms5/chronoforms/actions/db_save/db_save.php(73) : eval()'d code on line 4

Designer Section:
I have a submit button and 1 field with the following values:
field name: radio
field id: radio
dynamic data enabled: yes
data path: radio
value key: user_id
text key: cb_com_company

In 'On Load' area of Setup Section:
DB Read Action:
enabled: yes
table name: 2sc__comprofiler
multi read: yes
enable model id: yes
model id: radio
fields: user_id,cb_com_company

in 'On Submit' area of Setup Section:
DB Save Action:
enabled: yes
table name: 2sc_chronoengine_chronoforms_datatable_test
save under model id: yes
multi save: no
model id: radio
force save: yes
update conditions: blank

I just changed the model ID throughout to 'model' instead of 'radio'. That PHP error went away and now I get this one:


Notice: Undefined index: model in /home/tjohns5/public_html/8130-3/administrator/components/com_chronoforms5/chronoforms/actions/custom_code/custom_code.php(20) : eval()'d code on line 2

Notice: Undefined index: model in /home/tjohns5/public_html/8130-3/administrator/components/com_chronoforms5/chronoforms/actions/db_save/db_save.php on line 82

And when I remove the model ID, the errors go away. I HATE HATE HATE the model ID thing. I'm 99% sure that's why I couldn't get this working a week ago.

Hi tim92109,

It looks like most of these PHP errors and notices are coming from your custom code /custom_code.php(20) : eval()'d code on line 2 Can you copy and paste the Custom Code here please, that will make it possible to see here the error is coming from.

The Model ID / Data Path does sometimes need a cold-towel. Basically it is a way to 'mark out' a sub-set of the form data so that it can be handled in a different way. In coding terms it represents a sub-array of the $form->data array. If you have a Model ID of xxx then it will be in $form->data['xxx'] Similalry if you use a Model ID of yyy to Read data it will be loaded to $form->data['yyy']

Bob

I actually decided to just try writing the DB code manually. It kind of scares me... I don't like putting the database credentials in any custom HTML field, module, etc. in Joomla as I don't know how secure it is. It does work. Maybe I should be using JDatabase instead though. I'll paste my custom HTML code for reference. Of course, I removed the DB credentials from it...

<?php
$user = JFactory::getUser();
$userid = $user->id;

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

$field_array = mysql_query("SELECT user_id,created,tracking,name13,name14,company FROM 2sc_form8130 WHERE cf_pdf_file = '$formfile'");
$field = mysql_fetch_array($field_array);

$tocompany = $form->data['profile'];
$formfile = $form->data['formfile'];
$userid = $field['user_id'];
$tracking = $field['tracking'];
$created = $field['created'];
$profile = "/index.php?option=com_comprofiler&user=".$field['user_id']."&tab=24";
$name = $field['name13'].$field['name14'];
$origcompany = $field['company'];

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "INSERT INTO 2sc_sendform (user_id,created,tracking,tocompany,formfile,name,profile,origcompany)
VALUES ($userid,'$created','$tracking','$tocompany','$formfile','$name','$profile',
'$origcompany')";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

Hi tim92109,

You should use the JDatabase methods. Here's my pro-forma for setting up a query

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

For a save you need to set up the query then do $db->execute();

There are some newer methods in J3 but this still works fine.

Bob

Believe it or not, I'm still working on this. I put it on hold until I needed things to be secure. I was able to use jDatabase for some of the queries, but haven't been able to figure it out for the more complex ones. I'm really sick of all this $db->quoteName stuff. Maybe you have enlightened me. Are you saying I can write a normal MySQL query instead of using the Joomla backwards method like:

$query->select($db->quoteName(array('user_id', 'created', 'tracking', 'name13', 'name14', 'company')));
$query->from($db->quoteName('#__form8130'));
$query->where($db->quoteName('cf_pdf_file') . ' = $formfile'));

That would be much easier to handle.

Thanks again!

Hi Tim,

Yes - I never got my head round those either. The old methods - as in my example - still seem to work fine.

Bob

Cool. One of the reasons I'm asking is it'b about to get even more complex. I now need to use MySQL JOIN to compare data in a second table before saving. One way around this is on my On Load actions... I have a database read that's retrieving some of the data. I was wondering... the 'relations' tab on the DB Read - is that basically JOIN INNER? Maybe I can filter the database rows it's grabbing to begin with. I searched the site and google... there's no documentation related to that tab if thats' the case, is there?

Hi Tim,

I've never used the Relations tab seriously so have never written it up. The Type drop-down lets you set the 'type' of relationship and the Join Conditions let you define the join.

I'd be inclined to test with a trial form and use a Debugger action to see exactly what was being loaded. That should make it easy to tune the settings.

Bob

I've played with this a little and was just too lost to continue. I've tried even putting the most minimal amount of data into the relations tab and can't get away from the errors. So, I've moved away from trying to use that tab. Seems 'experimental' to me. But, thank you for offering help on it.

Okay... I did continue. I tried to get advice from the Joomla forums, but nothing... not a single answer as to why my db code isn't working. Figured it can't hurt to post it here in case anyone wants to look at it...

<?php
$db = JFactory::getDbo();
$query = $db->getQuery(true);

$tocompany = $form->data['profile'];
$formfile = $form->data['formfile'];

$query
    ->select(array('user_id', 'created', 'tracking', 'name13', 'name14', 'company'))
    ->from($db->quoteName('#__form8130'))
    ->where($db->quoteName('cf_pdf_file')." = ".$formfile);

$db->setQuery($query);

$row = $db->loadAssocList();



$userid = $row['user_id'];
$tracking = $row['tracking'];
$user = JFactory::getUser();
$from = $user->id;
$created = $row['created'];
$name = $row['name13'].$row['name14'];
$profile = "/index.php?option=com_comprofiler&user=".$row['user_id']."&tab=24";
$origcompany = $row['company'];



// Create and populate an object.
$sendcert = new stdClass();

$sendcert->user_id = $userid;
$sendcert->tracking = $tracking;
$sendcert->from = $from;
$sendcert->created = $created;
$sendcert->tocompany = $tocompany;
$sendcert->formfile = $formfile;
$sendcert->name = $name;
$sendcert->origcompany = $origcompany;

$result = JFactory::getDbo()->insertObject('#__sendform', $sendcert);
?>

Hi tim92109,

I've read back through this thread and I have no idea what you are actually trying to do here - it seems to have got very complicated.

Let's go back to basics - what exactly do you need to do?

Bob

It is very complicated, Bob. I have 2 DB Read actions, multiple custom HTML actions (that create 'non-existent' form field entries), and am saving to two different DB tables and creating a PDF (saving to the server). I have it working with plain 'ol PHP+MySQL. There's basically a bug somewhere in the jDatabase code I was trying to use. I'm just hoping that keeping the database credentials separated and safe will be enough security. And I'm going way over my time-frame for this, but I might have to re-visit and re-address it in the near future. I'll leave this (and you) alone on this for now haha. But thank you, Bob!

If it ends up being a concern, I'll just get my client give me something for a donation for you and let you look at it. It's almost too complicated to explain here.

Hi tim92109,

OK - just as a note I find it much simpler to use the Old Style Joomla! DB code where you just build a query and execute it. If you turn site debugging on you should get a list of all the queries being executed that can help debug.

Bob

UNforutnately, nothing shows in the debugger. Probably becuase of the redirect action. I'll see if I can turn that off and get any results.

Thanks.