Forums

Hi,
I'm looking to make relational tables and need Connectivity to possibly SELECT multiple tables. Is it possible?

thanks.

I am also needing Chrono Connectivity to be able to connect to multiple (at least two) tables. I notice when setting up a connection it lists TableName(s), which indicates to me that the functionality is there, and that I just do not know how to use it. I have tried CTRL clicking on additional tables with no luck. Any help would be greatly appreciated. Thanks.

Hi hilltopper06,

I don't believe it is possible in the current version; it would have to allow more than a simple list selection to be useful. Maybe in a future version an advanced box will allow more complex SQL.

Bob

Hi hilltopper06,

I don't believe it is possible in the current version; it would have to allow more than a simple list selection to be useful. Maybe in a future version an advanced box will allow more complex SQL.

Bob

Hi Bob,
Is it possible to align two separate connection outputs on the one joomla content page as a work around this multiple table issue? [can create a custom menu delegated template - maybe?]
[my challenge is to provide a listing over view for Sobi2 directory where they have two DB tables utilised for the field data ]
I am a novice to this area as well I might add.

Hi ozzie,

I've no idea. But there are a couple of possible solutions. Since this thread someone posted a hacked version of ChronoConnectivity here that will allow complex queries.

It may also be possible to do a MySQL query from inside the Body box - that's a bit query intensive but can let you get round the single table problem.

Bob

Thanks Bob...I shall do some more research...

There are a couple of ways to work around this limitation.

1. Create a MySQL VIEW across two or more tables (more efficient solution than 2)

VIEWs require field names across all of the tables in the view to be unique. So for this to work you will need to use phpMyAdmin (or similar) to rename the standard Chronoforms field names in the other table(s) to something else. E.g. add a "table2_" prefix or similar like this:

table2_cf_id
table2_uid
table2_recordtime
table2_ipaddress
table2_cf_user_id

In phpMyAdmin, use the SQL command line to create the VIEW:

CREATE VIEW #__chronoforms_MYVIEWNAME AS SELECT * FROM #__chronoforms_TABLE1NAME LEFT JOIN #__chronoforms_TABLE2NAME ON #__chronoforms_TABLE1NAME.TABLE1FIELDNAME=#__chronoforms_TABLE2NAME.TABLE2FIELDNAME

Check the results using:

SELECT * FROM #__chronoforms_MYVIEWNAME

You should now be able to see the second table record details added to the end of each of the first table record details.

You can now use #__chronoforms_MYVIEWNAME in Chronoconnectivity as if it was a table.

Note 1: Chronoconnectivity looks for a primary key but a VIEW does not have one (VIEWs rely on the primary keys of the tables in the VIEW). The following error message can be ignored where Chronoconnectivity is displaying data only (i.e not updating):

"The connected table does NOT have a primary key, please add a primary key to your table or some functions will not work correctly."

Note 2: In the connection "FrontEnd Settings" tab, do not enable group or user permissions for update functions (New Record, Edit Record, Delete Record) otherwise you will get the following error if you are signed in with update privileges:

"Fatal error: Cannot access empty property in /home/SITENAME/public_html/components/com_chronoconnectivity/libraries/connection.php on line 312"

References: CREATE VIEW instructions are at: http://dev.mysql.com/doc/refman/5.0/en/create-view.html

2. Perform additional query or queries inside Chronoconnectivity to include data from other table(s) (not especially efficient but will get you across the line)

Example 1 (multiple results expected):

  <?php
    //Get Photos
    $db =& JFactory::getDBO();
    $db->setQuery("SELECT Property,FileName FROM #__chronoforms_Photos WHERE Property = '$MyRow->PropertyID'");
    $results = $db->loadObjectList();
    foreach($results as $result){
      echo "<img src='images/stories/$result->FileName' alt='Photo' /><br /><br />";
    }
  ?>

Example 2 (single result expected):

  <?php
    //Get Agent Details
    $db =& JFactory::getDBO();
    $db->setQuery("SELECT AgencyID,AgencyLogo2 FROM #__chronoforms_Agency WHERE AgencyID = '$MyRow->Agency'");
    $result = $db->loadObjectList();
    if (isset($result[0]->AgencyLogo2)) {
      $agencylogo = $result[0]->AgencyLogo2;
    }
  ?>

Hi Neil,

Excellent, thank you. I thought that VIEW might work but never found the time to test it.

Bob

Hello to all,
I am Giuliano from Italy and this is my first time here. First thing: excuse my English, I am autodidact in English and ... PHP !!!
I like very much Chrono Comps and I use its in "my" web site, http://www.biketrialitalia.it. From some times I am on job to create a "system" to administrate the Classification and Ranking of the BikeTrial Italian Championship. I must take data from multiple tables and I follow the suggestion of Ozneilau (first method). The PhpMyAdmin View is very simple and fast to create and it is very efficent. Data are ready and available to use, very entusiastic thing. BUT there a problem, View is not a Table and is not editable.
So I thought: is not a problem because I can modify the record of a single table coming to "View". ... I try to explain:

ANAG_TABLE (semplified):
id_anag / name

RESULTS_TABLE (very semplified) :
id_result / id_event / point / id_anag

RESULTS_VIEW (in DB create with PhpMyAdmin):
id_anag /name / id_event / id_result / point

I create a Chronoconnect than take data on "RESULTS_VIEW" and the data are visible. It works !

Now I can point on a single record with <a href='index.php?option=com_chronoconnectivity&connectionname=results_list&task=editrecord&cids={cf_id}&Itemid=263'>Edita</a>

But there a error: "You are not authorized to view this page, Row Edit Error"
I think is this a advise from "Permission" module ...

Can you help me ?!?
Thank you.
Giuliano

Hi Giuliano,

Do you have the necessary "Editors user Groups" permissions set on the ChronoConnectivity "Front End Settings" tab?


Bob

Hello Bob, thank you very much for your very fast reply.
Now I am in my house (here we are 22 o'clock) and I reply from BlackBerry.
Of course, I try all group permission and individual permission. Attention: I must deselect the individual permission on delete, New and edit because the connected table by the form is a "not table" and if I select editor user the form go down ...
It is possible the problem is ganerated from the 'session' ?

Hi Giuliano,

As you have discovered, the view works well to display data but you must edit tables individually without using the view.

I'm sure you will have already checked this, but is your ChronoConnection published?

Neil.

Hello Ozneilau, nice to 'met' you,
yes. I select single table and my list is published.
Giuliano

Hi Giuliano,

If I understand correctly, Neil is saying that you can't edit a View - you can only display the results.

Bob

Hello Bob,
yes. I understand; but I am aware of this. I try to explain better (for me this is the first difficult because my English is "auto generated", I was study French at the school ... many years ago ...).
I explain:
1) My connection-list is runing; the "list" is populated with the record of the VIEW. In the end of row are "Edit". Edit is a link and go to the "edit form" (a chronoform).
2) The Editor pick up the data from the "Results Table" not from VIEW.
3) But When I click on link to open the Editor form is error.
Is a permission error

IF you paste in url this link:
http://www.biketrialitalia.it/index.php?option=com_chronoconnectivity&connectionname=BRUTTO&task=editrecord&cids=12&Itemid=263
you find the login page.
If I am loged and I paste the same url I find the "global error message".
BUT if I am in the "brutto" Connectivity form (brutto is a simple cronoconnectivity list I generated for test and take data from Table) all is ready.
Now:
I think is possible chronoform (the editor) make a controll to check if the GET data is from a specific form and in this case don't give me permission to entry.

Hi Giuliano,

I suggest that you don't use the {edit_record} links. Instead build a direct link to your form and you will bypass the ChronoConnectivity permissions test (you can add your own if you need one).

<a href='index.php?option=com_chronoforms&chronoformname=XXX&cids={cf_id}&Itemid=263'>[ Edit details ]</a>

Bob

Hello Bob,
Yes, this is my first method. But don't run. I open the edit form but is empty.
Giuliano

Hi Giuliano,

Hmmm . . . then something else is wrong because I use this method quite often.

Bob

Hello dear Bob, thank for your patience,
I think in the end of this theme is necessary for you not a beer but a bottle of wine (Italian of course) ...

I think your link is not:

<a href='index.php?option=com_chronoforms&chronoformname=XXX&cids={cf_id}&Itemid=263'>[ Edit details ]</a>

but

<a href='index.php?option=com_chronocontact&chronoformname=XXX&cids={cf_id}&Itemid=263'>[ Edit details ]</a>

Is so or is my mistake ?

If I use first link don't run, if I use second link (my first method) I can open the form but don see the data from the selected record by {cf_id}.
When I open form from Chronoconnectivity, for example my "anagrafica" list, when I click on "edit" the instanced link is:
/index.php?option=com_chronoconnectivity&connectionname=bta_anagrafe_list&task=editrecord&cids=517

and it run. This instance call the chronoconnectivity component, bta_anagrafe_list connectionname, TASK editrecords and record 517;
BUT if the connected table is a "VIEW" the system don't run.

Please, follow me, please:
in Phpmyadmin:
FIRST TABLE:

CREATE TABLE IF NOT EXISTS `aaa_anag` (
  `id_anag` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id_anag`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `aaa_anag` (`id_anag`, `name`) VALUES
(1, 'Giuliano'),
(2, 'Bob');

SECOND TABLE:

CREATE TABLE IF NOT EXISTS `aaa_bike` (
  `bike_id` int(11) NOT NULL AUTO_INCREMENT,
  `brand` varchar(50) NOT NULL,
  `anag` int(11) NOT NULL,
  PRIMARY KEY (`bike_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

INSERT INTO `aaa_bike` (`bike_id`, `brand`, `anag`) VALUES
(1, 'Montesa', 1),
(2, 'Ossa', 2),
(3, 'Bultaco', 2),
(4, 'Sherco', 3);

THE VIEW:

CREATE VIEW aaa_bike_VIEW AS SELECT
aaa_anag.anag_id,
aaa_anag.name,
aaa_bike.bike_id,
aaa_bike.brand,
aaa_bike.anag
FROM aaa_anag
INNER JOIN aaa_bike
ON aaa_anag.anag_id = aaa_bike.anag

2) In Chronoconnectivity create a new connection named AAA_LIST, connected table are "aaa_bike_VIEW";
- In "Body": {name} {brand} {edit_record};
- in front end Group Permission: Administrator, Administrator, Administrator, Administrator;
- Record Edit form: nothing;
- In FrontEnd Per User Permission: Giuliano (ONLY IN VIEW, no select new,edit,delete);
- In Backend Data View Settings: all;
- I ignore this:
The connected table does NOT have a primary key, please add a primary key to your table or some functions will not work correctly ;

- I publish the connection;
3) make a new menu voice call AAA_LISTA and call the connection "AAA_LIST";

4) I see the result
Giuliano Montesa
Bob Ossa
Bob Bultaco

And (naturally) I don't see the link for "editor".

5) In Chronoform create the "insert form for anagraphe", named aaa_bikes :

<div class="form_item">
  <div class="form_element cf_textbox">
    <label class="cf_label" style="width: 150px;">Bike ID</label>
    <input class="cf_inputbox" maxlength="150" size="30" title="" id="text_3" name="bike_id" type="text" />
  
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_textbox">
    <label class="cf_label" style="width: 150px;">Brand</label>
    <input class="cf_inputbox" maxlength="150" size="30" title="" id="text_2" name="brand" type="text" />
  
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_textbox">
    <label class="cf_label" style="width: 150px;">Anag ID:</label>
    <input class="cf_inputbox" maxlength="150" size="30" title="" id="text_1" name="anag_id" type="text" />
  
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_button">
    <input value="Submit" name="button_2" type="submit" />
  </div>
  <div class="cfclear"> </div>
</div>

NOW THE BET:
Click on Edit on the list and open the aaa_bikes forms with editable data.

My first idea is this, in AAA_LIST:

<p> {name} {brand} <a href='index.php?option=com_chronocontact&chronoformname=aaa_bikes&cids={cf_id}'>[ Edit details ]</a> </p>

... and don't run ...
Giuliano

Hi juliusgg,

Sorry for the delay, I got busy with some other projects.

I think that the prolbem here is that you can only *read* the values from a View - which is fine in the ChronoConnectivity listing. But you can't write the changed values back to the View. Instead you have to save the values back to the underlying tables and then updatet he View before re-displaying the Listing.

Bob