Building and displaying relationships between tables

Hi. Can ChronoForms/ChronoConnectivity help me build and display simple relationships between tables? I'm still quite new to Sql and Chrono-world but I'd hoped this component would help me in my quest to build a simple form with subforms. I've worked through various tutorials but I can's see relationships within the tables I've built.

For example (this is not what I want to do but if it can do this, I'll work out how to do my thing) - could it build a form which asks:

Your name: text field
Number of kids: text field

then a subform which asks:

Kids Name:
Kids age:
Kids name:
Kids age:

....ad infinitum?

In Access I can do this, but I wanted a component which will help me do this within Joomla.

Can ChronoForms/ChronoConnectivity do this?

James

Hi James,

Yes . . . . up to the 'ad infinitum' bit, then I get lost????

If you want a form that shows fields for 4 children if I put 4 in the first step, then Yes you can do that. ChronoForms won't do it automatically but with some coding it's certainly possible.

Bob

Sorry, I was totally unclear. Its sunday.

Can it help me build a relational database, for example with two tables, on a one-to-many relationship? And if so, can you recommend any tutorials that would help me do this? I want to build a database with 2 tables (A and B) where table A has general address information on an archaeological site, and table B has none/one/two/thousands of entries of finds made at the archaeological site which are related to a single entry in table A.

If so, are there any tutorials you'd recommend, or pages on the web that might explain how to do this? I'm fairly ignorant of SQL/PHP but know joomla a bit.

Thanks for getting back to me so quick!
James

Hi James,

Ok - that makes more sense to me.

ChronoForms can certainly help with that. It's a good tool for creating forms in Joomla and letting you specify the back end processing. You can use a group of ChronoForms to build a little application like the one you are describing.

Off the top of my head it sounds like one form to input/edit sites; a second form it input/edit finds - how you link the two would depend on the work-flow. I imagine that a batch of finds comes from a single site to you could pre-load a form for that particular site, from a menu, or a listing.

ChronoConnectivity will let you list records - sites or finds - and build in links to let you view more details or edit individual records.

There are some practical limits in the published version of CC that limit queries to a single database table i.e. sites OR finds. I have a heavily hacked version developed for a client that allows compound queries on more thatn one table so that is possible.

I imagine that the place to start is by creating simple forms for each of the two inputs, connecting them to database tables and findign out what is missing. The basic tutorials from the Tutorials tab above will take you through most of that,

By default Joomla uses MySQL tables - I think that it is possible to link an Access table into MySQL, (maybe through a OBDC link?). I think we discussed that here a few months ago.

Bob

Hello again - sorry to keep posting but I want to get my head around the basics then I'll leave you alone promise!

So - yes, you're right. On a single archaeology site, I want volunteers to enter in all the finds they make - ie many finds to a single site.

So I've built two basic forms in the menu (see: http://charity-shop.net/archaeology/index.php?option=com_chronocontact&Itemid=46)

So how do I arrange for each entry on the 'Finds' form to link to a single site? Is this a relation within the tables (ie in the Finds table, theres a SiteID which links toa single entry in the Sites table)?

If so, when the volunteer is entering info into the Finds table, how can I auto-fill the SiteID (see example). Can you tell me exactly what I do in the back-end as I really don't get this, but I'm sure Chronoforms/Chronoconnectivity can do it! Else, is there a demo you can point me to? I've already used your demo for displaying results of a single table but cant see how that helps me in this case.

Sorry if I'm asking too many questions, as I know this is all free time you're using. If you don't have time to answer in detail I'll understand.

James

Hi James,

Yes, you absolutely need id fields set as primary keys for the tables - both sites and finds. If you use the ChronoForms Create Table it will offer to create a cf_id field. In the following code I've used 'id'.

I'd build a drop down for the 'site id' field in the finds form. Run a query against the site table and build the drop-down options from that. This code goes into the FormHTML. The <select>. ..</select> bit needs to replace the current text input for the site name.

<?php
$db =& JFactory::getDBO();
$query = "
  SELECT `id`, `site_name`
    FROM `jos_sites` ;
";
$db->setQuery($query);
$sites = $db->loadObjectList();
?>
<select name='site_id' id='site_id' >
  <option value=''>--?--</option>
<?php
foreach ( $sites as $site ) {
  echo "<option value='".$site->id."'>".$site->name."</option>";
}
?>
</select>

This needs some prettying up - you might want to sort the sites for instance - but as bare-bones code it should work OK.

Bob

You are a genius! I've fiddled a bit (and even learned a little PHP!) and...it works! Thanks so much! The greyhead is clearly wisdom rather than age. Thank you!!