Buy Now
Sign in

Chronoconnectivity to save to 2 tables

kwok , March 30 2015
K
kwok
I was trying to find some sample from the many great articles in the regular forum but in vain.

I need help to do the following:

1. Made a form displaying list of item id, item image and description out of data from 2 tables. Table #1 contains the item description while Table #2 contains the image path. There will be a checkbox on each row.

2. Upon submit, some form data (hidden) is firstly saved to a header file where mysql auto increment assigns a unique id. Then, the newly assigned id is obtained and saved to another table with the item id and description.

Thanks,
Kwok
GreyHead
Hi Kwok,

You can do this using ChronoForms.

I'd use a Custom Code action on the form ON Load event to get the list of images using a MySQL query with a JOIN

Then use a Custom Code element in the Preview tab to generate a table from the results.

Then two DB Save actions in the form On Submit event to save to the two tables. After the first one ChronoForms adds the new id to the form data so you can use that for the second DB Save.

Bob
ChronoForms technical support
If you'd like to buy me a coffee or two, thank you very much
admin
Hi,

One more note here, if the 2 tables are defined as 2 related models under your CCv5 connection then the save process should be automatic, and the primary key value from the main table will be stored in the foreign key field in the 2nd one.

Regards,
Max
Max
If your main question got answered then please mark the answer using the button!​
​Please let us know if you have any problems with the new forums text editor, we appreciate your feedback!
K
kwok
Hi,
I'll try CF and CC respectively.
The image path is stored in table images as fields = path, name, ext.
How to add a custom column heading as "Item Image" and present rows of image graphic?
How to associate a dynamic link (by item number) to each image graphic such that onClick the item detail is shown in a lightbox?
How to only save rows that have been selected (checkbox)?
Thanks,
Kwok
K
kwok
Hi Max,

I also tried to follow this link
http://www.chronoengine.com/faqs/72-ccv5/5207-view-and-edit-actions.html
to edit a field of a table.

How to open the edit page in a lightbox after clicking the EDIT link?

How to set the Action/edit to allow selection of radio button for "Reject" and "Accept" which is to be stored in a status field (OfferHeader[status])?

The table also contains a timestamp field. I set it to the following and no date value is added to that field during a SAVE:
<input type="hidden" name="OfferHeader[date_action]" value='<?php echo date('Y-m-d H:i:s'); ?>' /><br>

I coded the Save button as follows:
<input type="hidden" name="OfferHeader[id]" /><br>
<input type="submit" value="Save" /><br>

Sometimes, user might want to exit without actually updating anything, so I added a cancel button as follows:
<input type="submit" name="cancel" value="Cancel" /><br>

However, it seems pressing the Cancel button still triggers an update action.

Thanks,
Kwok
admin
Hi kwok,

The cancel button can be just a link, with class attribute = "btn btn-default"

The data and status field should work according to your code, this is the main model or the 2nd one ?

Editing rows directly in the list requires the "save_list" feature, there is a tutorial on this in the CCv5 FAQs section.

Regards,
Max
Max
If your main question got answered then please mark the answer using the button!​
​Please let us know if you have any problems with the new forums text editor, we appreciate your feedback!
K
kwok
"GreyHead":
Hi Kwok,

You can do this using ChronoForms.

I'd use a Custom Code action on the form ON Load event to get the list of images using a MySQL query with a JOIN

Then use a Custom Code element in the Preview tab to generate a table from the results.

Then two DB Save actions in the form On Submit event to save to the two tables. After the first one ChronoForms adds the new id to the form data so you can use that for the second DB Save.

Bob



Hi Bob,

1. I created custom code (buyer_items) in Designer layout session as follows:

<? php
// get buyer user id
$user = JFactory::getUser();
// get buyer's items
$db = JFactory::getDBO();
$query =
"SELECT b.path, b.name, b.ext, a.intro_desc, a.id ".
"FROM #__items a ".
"LEFT JOIN #__item_images b ON a.id=b.item_id ".
"WHERE a.user_id=".$user->id;
$db->setQuery($query);
$items = $db->loadObjectList();
?>

<table>
<thead>
<tr><th>ITEM</th><th>DESCRIPTION</th><th>IMAGE</th><th>SELECT</th>
</thead>
<tbody>
<?php
foreach ($items as $row) {
echo "<tr><td>{$row['item_id']}</td><td>{$row['intro_desc']}</td><td>{$row['name']}</td><td><input type=\"checkbox\" name=\"checkbox\" value=\"\" id=\"checkbox\"></td></tr>";
}
?>
</tbody>
</table>



But got the following during testing:
d; $db->setQuery($query); $items = $db->loadObjectList(); ?> 
Notice: Undefined variable: items in C:\xampp\htdocs\xxx\administrator\components\com_chronoforms5\chronoforms\actions\html\html.php(262) : eval()'d code on line 22


Even the above works to display the table, I actually need the name row to display the item image (path/name.ext) which I have no idea how. In addition, I'm lost how to check which checkbox is selected and eventually save to the header and detail files:

  
CREATE TABLE IF NOT EXISTS `offer_header` (
   `id` int(11) NOT NULL auto_increment,
   `buyer_user_id` int(11) NOT NULL,
   `date_offer` timestamp NOT NULL default CURRENT_TIMESTAMP,
   `seller_item_id` int(11) NOT NULL,  
   `seller_item_intro_desc` text,
   `seller_user_id` int(11) NOT NULL,
   `offer_status` char(1) NOT NULL default '0',
   `seller_date_action` timestamp NOT NULL default '0000-00-00 00:00:00',   
   PRIMARY KEY (`id`)
  ) DEFAULT CHARSET=utf8

CREATE TABLE IF NOT EXISTS `offer_detail` (
   `id` int(11) NOT NULL auto_increment,
`offer_id` int(11) NOT NULL,
   `buyer_item_id` int(11) NOT NULL,
   `buyer_item_intro_desc` text,  
`buyer_item_image text,      
   PRIMARY KEY (`id`)
  ) DEFAULT CHARSET=utf8

Would it be possible for you to show me step by step on how to set the chloroform?

Many thanks!
Kwok
admin
Hi kwok,

Please try to combine the first 2 code blocks, because the items variable is defined in the first one only.

Regards,
Max
Max
If your main question got answered then please mark the answer using the button!​
​Please let us know if you have any problems with the new forums text editor, we appreciate your feedback!
GreyHead
Hi Kwok,

A couple of things here.

+ As Max says $data is not saved between actions so you either need to merge the two together; or change $items to $form->data['items'] as that will be saved

+ In the database query you are using $db->loadObjectList(); - that will return an array of Objects. In the display code you then need $row->item_id (instead of $row['item_id']).

Bob
ChronoForms technical support
If you'd like to buy me a coffee or two, thank you very much
K
kwok
Hi Bob,

I have modified the php code accordingly:
<? php
// get buyer user id
$user = JFactory::getUser();
// get buyer's items
$db = JFactory::getDBO();
$query =
"SELECT b.path, b.name, b.ext, a.intro_desc, a.id ".
"FROM kx_djcf_items a ".
"LEFT JOIN kx_djcf_images b ON a.id=b.item_id ".
"WHERE a.user_id=".$user->id;
$db->setQuery($query);
$items = $db->loadObjectList();
?>

<table>
<thead>
<tr><th>ITEM</th><th>DESCRIPTION</th><th>IMAGE</th><th>SELECT</th>
</thead>
<tbody>
<?php
foreach ($items as $row) {
echo "<tr><td>{$row->item_id}</td><td>{$row->intro_desc}</td><td>{$row->name}</td><td><input type=\"checkbox\" name=\"checkbox\" value=\"\" id=\"checkbox\"></td></tr>";
}
?>
</tbody>
</table>


But still get the same error:

$db->setQuery($query); $items = $db->loadObjectList(); ?>
Notice: Undefined variable: items in C:\xampp\htdocs\kuxol\administrator\components\com_chronoforms5\chronoforms\actions\html\html.php(262) : eval()'d code on line 22

Warning: Invalid argument supplied for foreach() in C:\xampp\htdocs\kuxol\administrator\components\com_chronoforms5\chronoforms\actions\html\html.php(262) : eval()'d code on line 22

HELP!
GreyHead
Hi Kwok,

The MySQL looks OK to me, please try testing it in PHPMyAdmin to see if you get a result (replace 999 with a valid id)
SELECT b.path, b.name, b.ext, a.intro_desc, a.id 
FROM kx_djcf_items a
LEFT JOIN kx_djcf_images b ON a.id=b.item_id
WHERE a.user_id=999

The only other thing I can see that might cause a problem is the JFactory - because CC now uses a namespace you sometimes need to prefix this with a \ - \JFactory to access the root namespace. My recollection is that JFactory works OK in CF but CC might be different.

Bob
ChronoForms technical support
If you'd like to buy me a coffee or two, thank you very much
K
kwok
Hi Bob,

Per your advice, the following is performed successfully:

SELECT b.path, b.name, b.ext, a.intro_desc, a.id FROM kx_djcf_items a LEFT JOIN kx_djcf_images b ON a.id=b.item_id WHERE a.user_id=993


path, name, ext, intro_desc ,id
/components/com_djclassifieds/images/item/, 38_image, jpg, Donec ut quam felis. Cras egestas, quam in plac er...,38
/components/com_djclassifieds/images/item/, 37_image, jpg, Donec ut quam felis. Cras egestas, quam in plac er...,37

Many thanks.
Kwok
K
kwok
Hi Bob,

I found the following in forum:
https://www.chronoengine.com/forums/posts/f2/t99590/build-a-product-list.html?hilit=setQuery

and it mentions about this FAQ:
http://www.chronoengine.com/faqs/70-cfv5/5251-how-can-i-build-a-product-list.html

Do you find the above an alternative way to create the form that I need? But it seems it only retrieve data from one file and save data to one file; my form has to retrieve data from two tables and save to another two tables.....

Thanks,
Kwok
GreyHead
Hi Kwoc,

What do you actually need to do? The FAQ is an example of building a Product list in ChronoForms but this is a ChronoConnectivity question?

Bob
ChronoForms technical support
If you'd like to buy me a coffee or two, thank you very much
K
kwok
Hi Bob,

Sorry for the confusion.

I just want to get the job done well.

Either using CF or CC is both OK.

Thanks
Kwok
GreyHead
Hi Kwok,

Yes, the FAQ only saves data to one table; I answered the question about saving to two tables back in my first post in this thread.

I find it hard to follow what you are actually trying here.

Bob
ChronoForms technical support
If you'd like to buy me a coffee or two, thank you very much
K
kwok
Hi Bob,

I haven't tried the way you suggested to save to two files as I'm still stuck at the custom server side validation section.

I hope you can enlighten me about the following:

1. Inside custom code where a table is generated as follows:
<?php
foreach ( $form->data['buyerItems'
  • as $p ) {
    $itemid = $p['id'];
    echo "<tr>
    <td><<img src=..... /></td>
    <td><input type='checkbox' name=\"buyerItems
  • \" value={$p['id'
  • } /></td>
    </tr>";
    }
    ?>
    </tbody>
    </table>



  • On firebug, the checkbox shows this: <input type="checkbox" value="9" name="buyerItems">

    2. I added a Handle Arrays and Custom Code (for the custom server side validation) in the 'On Submit' section. As you see there can be multiple checkboxes. There is also a cash offer field on the same form. What should the code be like to validate the scenario which there is no checkbox checked and the cash offer field is blank or zero? And then how to return an error message back to the form?

    Thanks,
    Kwok
    K
    kwok
    Hi Bob,
    Please help other folks first. I'll try to figure this one out myself.
    Thanks,
    Kwok