HI all,
I spent a lot of time figuring this out but it works great. I hope it helps others out.
This is a chronoforms example of linking 4 tables for view only display, no update
table 1 is a registration table jml_chronoforms_registration
it has these fields
cf_id = unique primary auto-increment
uid = userid from jml_users
recordtime = timestamp from jml_users
ipaddress = from jml_users
cf_user_id = unique key = id from jml_users
other fields as needed, like f_name, l_name, add_1, add_2, etc
table 2 is a special information table that will be linked to table 1 in my case it is a 1 to 1 relationship to the jml_chronoforms_registration form. I will call it jml_my_main
it has these fields
my_id = unique primary auto-increment
id_link = unique for linking to the jml_chronoforms_registration table cf_id
my_item = unique field for linking to table 3
my_level_link = for linking to table 4
other fields as needed like field_1, field 2 field 3
table 3 is a special transaction table that will be linked to table 2 in my case it is a 1 to Many relationship to the jml_my_main table. I will call it jml_my_history - This is the MANY for the 1 to many relationship.
it has these fields
tran_num = unique primary auto-increment
tran_item_link = INDEXED (not unique) field for linking back to table 2
other fields like tran_1, tran_2 , tran_3, etc
tran_level_link to link to table 4
table 4 is a lookup table for additional information I call it jml_my_level
it has these fields
level_id = unique primary auto-increment links to tran_level_link in table 3
level_description
other fields like level_1, level_2
If you want to demo this you will have to create the tables yourself, careful with the names.
You will create 3 forms
1 = the chronoforms registration replacement form = I called it *registration* and followed the standard chronoforms instructions using the jml_chronoforms_registration table the registration and captcha plugins
2 = the profile form = I called it *profile* , again a standard chronoforms form using the jml_chronoforms_registration table the profile plugin
3 = the history form = i called it *history* BUT, Then paste this into the FORM CODE tab in the FORM HTML (+-) section. You do not have to add any plugins or select any database connections as this code does it all.
It will help to prepopulate the databases With common information in the linking fields so that you can see the results. Be sure to load several records into the jml_my_history database using the same tran_item_link. Also make sure that you fill in a *1* for the my_level_link and the tran_level_link so that it will pull the lookup info from the levels table.
I want to thank BOB on the forum for helping me along. Now its my turn to give back.
I made this generic by using find and replace from my working code, so sorry if I missed something. Please reply if you need some help.
the_fitz
HERE IS THE CODE:
I spent a lot of time figuring this out but it works great. I hope it helps others out.
This is a chronoforms example of linking 4 tables for view only display, no update
table 1 is a registration table jml_chronoforms_registration
it has these fields
cf_id = unique primary auto-increment
uid = userid from jml_users
recordtime = timestamp from jml_users
ipaddress = from jml_users
cf_user_id = unique key = id from jml_users
other fields as needed, like f_name, l_name, add_1, add_2, etc
table 2 is a special information table that will be linked to table 1 in my case it is a 1 to 1 relationship to the jml_chronoforms_registration form. I will call it jml_my_main
it has these fields
my_id = unique primary auto-increment
id_link = unique for linking to the jml_chronoforms_registration table cf_id
my_item = unique field for linking to table 3
my_level_link = for linking to table 4
other fields as needed like field_1, field 2 field 3
table 3 is a special transaction table that will be linked to table 2 in my case it is a 1 to Many relationship to the jml_my_main table. I will call it jml_my_history - This is the MANY for the 1 to many relationship.
it has these fields
tran_num = unique primary auto-increment
tran_item_link = INDEXED (not unique) field for linking back to table 2
other fields like tran_1, tran_2 , tran_3, etc
tran_level_link to link to table 4
table 4 is a lookup table for additional information I call it jml_my_level
it has these fields
level_id = unique primary auto-increment links to tran_level_link in table 3
level_description
other fields like level_1, level_2
If you want to demo this you will have to create the tables yourself, careful with the names.
You will create 3 forms
1 = the chronoforms registration replacement form = I called it *registration* and followed the standard chronoforms instructions using the jml_chronoforms_registration table the registration and captcha plugins
2 = the profile form = I called it *profile* , again a standard chronoforms form using the jml_chronoforms_registration table the profile plugin
3 = the history form = i called it *history* BUT, Then paste this into the FORM CODE tab in the FORM HTML (+-) section. You do not have to add any plugins or select any database connections as this code does it all.
It will help to prepopulate the databases With common information in the linking fields so that you can see the results. Be sure to load several records into the jml_my_history database using the same tran_item_link. Also make sure that you fill in a *1* for the my_level_link and the tran_level_link so that it will pull the lookup info from the levels table.
I want to thank BOB on the forum for helping me along. Now its my turn to give back.
I made this generic by using find and replace from my working code, so sorry if I missed something. Please reply if you need some help.
the_fitz
HERE IS THE CODE:
<?php
/*
This is a chronoforms example of linking 4 tables for view only display, no update
table 1 is a registration table jml_chronoforms_registration
it has these fields
cf_id = unique primary autoincrement
uid = userid from jml_users
recordtime = timestamp from jml_users
ipaddress = from jml_users
cf_user_id = unique key = id from jml_users
other fields as needed, like f_name, l_name, add_1, add_2, etc
table 2 is a special information table that will be linked to table 1 in my case it is a 1 to 1 relationship to the jml_chronoforms_registration form. I will call it jml_my_main
it has these fields
my_id = unique primary autoincrement
id_link = unique for linking to the jml_chronoforms_registration table cf_id
my_item = unique field for linking to table 3
my_level_link = for linking to table 4
other fields as needed like field_1, field 2 field 3
table 3 is a special transaction table that will be linked to table 2 in my case it is a 1 to Many relationship to the jml_my_main table. I will call it jml_my_history - This is the MANY for the 1 to many relationship.
it has these fields
tran_num = unique primary autoincrement
tran_item_link = INDEXED (not unique) field for linking back to table 2
other fields like tran_1, tran_2 , tran_3, etc
tran_level_link to link to table 4
table 4 is a lookup table for additional information I call it jml_my_level
it has these fields
level_id = unique primary autoincrement links to tran_level_link in table 3
level_description
other fields like level_1, level_2
If you want to demo this you will have to create the tables yourself, careful with the names.
You will create 3 forms
1 = the chronoforms registration replacement form = I called it *registration* and followed the standard chronoforms instructions using the jml_chronoforms_registration table the registration and captcha plugins
2 = the profile form = I called it *profile* , again a standard chronoforms form using the jml_chronoforms_registration table the profile plugin
3 = the history form = i called it *history* BUT, Then paste this into the FORM CODE tab in the FORM HTML (+-) section. You do not have to add any plugins or select any database connections as this code does it all.
It will help to prepopulate the databases with common information in the linking fields so that you can see the results. Be sure to load several records into the jml_my_history database using the same tran_item_link. Also make sure that you fill in a *1* for the my_level_link and the tran_level_link so that it will pull the lookup info from the levels table.
I want to thank BOB on the forum for helping me along. Now its my turn to give back.
I made this generic by using find and replace from my working code, so sorry if I missed something.
the_fitz
*/
$user =& JFactory::getUser();
$curr_id = $user->id;
$db =& JFactory::getDBO();
$query = "SELECT cf_id from jml_chronoforms_registration WHERE cf_user_id = $curr_id";
$db->setQuery($query);
$cf_id = $db->loadResult();
$query = "SELECT my_item, field_1, my_level_link from jml_my_main where id_link = $cf_id";
$db->setQuery($query);
$my_main = $db->loadAssoc();
$my_item = $my_main['my_item'];
$my_level_link = $my_main['my_level_link'];
$field_1 = $my_main['field_1'];
$query = "SELECT level_description, level_1 from jml_my_level where level_id = $my_level_link";
$db->setQuery($query);
$my_level = $db->loadAssoc();
$level_description = $my_level['level_description'];
$level_1 = $my_level['level_1'];
$query ="SELECT COUNT(*)
FROM ".$db->nameQuote('jml_my_history')."
WHERE ".$db->nameQuote('tran_item_link')." = ".$db->quote($my_item)."; ";
$db->setQuery($query);
$count = $db->loadResult();
$query = "SELECT tran_1, tran_2, tran_3, tran_4 from jml_my_history WHERE tran_item_link = '$my_item'";
$db->setQuery($query);
$row = $db->loadAssocList();
?>
<div align="center">
<!-- a lot more info can be added to this display just by adding more fields -->
<h2>History for my item <?php echo $my_item?></h2>
<p>Current information: <?php echo $field_1?> </p>
<p><?php echo $level_description?> my level </p>
<p>You reach the next level at <?php echo $level_1?> units</p>
<h3>Viewing <?php echo $count ?> records.</h3><br />
<table width="100%" border="1" cellpadding="1" cellspacing="1" id="usagehist">
<tr>
<th scope="col">my info 1 </th>
<th scope="col">my info 2</th>
<th scope="col">my info 3</th>
<th scope="col">my info 4</th>
</tr>
<?php
for ( $i = 0; $i <= $count-1; $i++ ) {
?>
<tr>
<td><?php echo $row[$i]['tran_1']?></td>
<td><?php echo $row[$i]['tran_2'] ?></td>
<td><?php echo $row[$i]['tran_3'] ?> units </td>
<td><?php echo $row[$i]['tran_4'] ?></td>
</tr>
<?php
}//end for
?>
</table>
</div>