Forums

I have built a joomla website and have a chronoform set up to dump into a chrono-connectivity database. The intent is to allow 7 registered users to update their info on the form and have a resulting web page that pulls data from the database to display updated information for all 7. I got data to display but it isn’t exactly what I need. I have done a little basic HTML and have been trying to learn PHP, but am unsure how that translates to the chrono-connectivity system. The first code from the body area inserted below was that simple initial attempt. It does display user input data, but in the format of a long list of form results verbatim as they were submitted.

<P>
<strong>{text_1}</strong>
<hr>
{text_2}
<hr>
{recordtime}
<hr>
<br>
</P>

What I need is a simple table that would have a heading row stretching horizontally across the page with columns labeled “Name”, “Location” and “Updated”. Below the heading row would be a line of data for each of the 7 users that would display the 3 desired data input fields from the database, name {text_1}, location {text_2} and date reporting {recordtime} for each of the 7 people. The result would be a small concise grid that would show the name location and check-in date of each person and fit on a small portion of one single page similar to a small spreadsheet. Submission of new form data would only update that existing table with anything new and continue to display the most recent updates for all 7 users. The second code attempt inserted below is probably grossly oversimplified and displays nothing when its page is loaded. I think it does give a hint as to what I’m going for though. I’m told that I need to use “limit” and “order by” in the query, but not sure how to employ that. Does anyone have ideas on coding that would achieve this?

<P>
<table bgcolor=#FFFFCC align="center" border=3 cellpadding=4 cellspacing=1>
  <tr>
  <th width="140" align="center">NAME</th>
  <th width="140" align="center">LOCATION</th>
  <th width="140" align="center">UPDATED</th>
  </tr>

  <tr>
  <td align="center">{text_1}</td>
  <td align="center">{test_2}</td>
  <td align="center">{recordtime}</td>
  </tr>

</P>

Thanks,
John

Hi John,

Yes you can do that. Rather than me spell it out here please see these tutorials particularly the 'formttign your list' one.

Bob

Thanks, that gave lots of good guidance. I got the table format working well and sorted correctly but wasn't able to place the "limit 1" on the name field to get the desired result of only one updated name entry per person as described above. It seems like it should be in the "Where SQL" box, but the code I've tried makes errors. Any thoughts on that?

Hi John,

I'm not sure what you are trying to achieve here. Normally you would make sure that there was only one record pèr person in the table. If there is more than one how do you distinguish between them?

Bob

Right. That's what I need - one record per person, but I don't know how to do that.

Hi John,

So, if there are several records for user x . . . how do you know which one to show?

Bob

PS LIMIT will only limit the total number of records returned, not the number per user.

Right. User X should have only the most recent entry displayed. And the same for all other users.

Hi John,

That's going to be tricky, you may need to do some experimentation with the SQL to get the result you want. Possibly using "ORDER BY `recordtime` GROUP BY `cf_user_id`" will do it. You should be able to put the blue coloured part of this into the Listing Order by box.

Bob

Thanks for the guidance, but it returns an error on the display page with that code in the “Order fields:” box – “Invalid argument supplied for each()… on line 300”. I tried several different variations including replacing “cf_user_id” with “text_1”. Both sort OK when entered alone without the “GROUP BY”, but as soon as “GROUP BY” is added (with any variation of code) the error returns. It seems to want only the one statement.

Hi John,

Hmmm . . . sorry that doesn't seem to work. I'll scratch my head and see if we can find another way.

Unfortunately ChronoConnectivity really isn't designed for this particular kind of filtering.

Bob

I had originally thought to try learning enough coding to write a query from scratch to access the data and put it in an article, but Joomla foiled me on my initial attempts at simple code experimentation. Joomla editor strips parts of the code away. With a little guidance I wouldn't be afraid to transplant some code into chronoconnectivity internals if you think it could help. Would it be possible/helpful to bypass the user interface input boxes and code a portion of the query manually? Just a thought.