FRONT END Display TableResults for Public Access??

Hi,

We use Chronoforms for lots and lots of registration sort of stuff.

We would like to allow user, ALL users, public and or registered to see their name and the name of all others that are registered after clicking SUBMIT on the bottom of the registration form.

Is there a way to to this?

I know how to get the form fields to show on the Staic content page, {chronocontact}Ref_Scorer_Registration{/chronocontact} , but am looking to have the RESULTS show in a web page. Almost like the table display you see on the BACKEND, but also SHOW on the FRONT END?

Make sense?

Ideas? Easy? Hard?

P.S. A PHP programmer I am not.

Thanks!

Mike
<!-- w --><a class="postlink" href="http://www.myccvc.com">www.myccvc.com</a><!-- w -->
[email]malloy0@msn.com[/email]

Post edited by: malloy0, at: 2007/11/24 02:30<br><br>Post edited by: malloy0, at: 2007/11/24 02:31

Hi malloy0,

Yes, I'm sure that you can do this. It's fairly easy to pull the data out of the database and display it. What may be a little more complex is to filter out any rubbish entries.

When I'm back home next week I'll write a little tutorial on showing a data table in the front end. You can find most of the code in the back end if you want to look.

Bob

Bob,

Thank you. Have a safe weekend.

Mike
<!-- w --><a class="postlink" href="http://www.myccvc.com">www.myccvc.com</a><!-- w -->
[email]malloy0@msn.com[/email]

Hi malloy0,

Let's have a go at presenting some data from a ChronoForms database table in the front end. To keep things simple I'm going to use the basic test form we have here which just has 'name' and 'email' inputs and a submit button. The demo should extend into more complex forms fairly easily with perhaps a few wrinkles around array values (checkboxes and multiple selects for instance).

First we need to know where the database table is so look at the ChronoForms 'AutoGenerated code' tab. For my form it looks like this

<?php 
$database =& JFactory::getDBO();
	
$database->setQuery( "
  INSERT 
    INTO #__chronoforms_6 
    VALUES  ('' , 
      '". date('Y-m-d')." - ".date("H:i:«»s"«»)."',   
      '".$_SERVER['REMOTE_ADDR']."' , 
      '".$_POST['name']."' , 
      '".$_POST['email']."');" );
if ( !$database->query() ) {
  echo "<script> alert('".$database->getErrorMsg()."'); 
    window.history.go(-1); </script>";
}
?>

Note: I've cleaned the layout up a bit to make it easier to understand.

The table name is in the INTO line and is '#__chronoforms_6' (to decode this Joomla replaces the '#_' with the database prefix for the site, the default is 'jos' and the '6' is the ChronoForms Form ID).

We want to read the data not write to it so we need some slightly different MySQL. For the moment we'll just pull out and display all of the data. The MySQL is very simple:

SELECT * FROM #__chronoforms_6;

but this needs to be wrapped in some Joomla PHP to work, then it is:

<?php
// initialise the Joomla database code
$database =& JFactory::getDBO();

// set up the database query
$sql = "SELECT * FROM #__chronoforms_6;";
$database->setQuery($sql);

// retrieve the data from the database
$rows = $database->loadObjectList();

// debug code to show the results 
echo "rows: ";print_r($rows);echo "<br />";
?>

To see the out put I can paste this code into the 'On Submit - after sending email' field in the Forms Manager | Form Code tab. If I resubmit the form then the results look like this

rows: Array ( 
  [0] => stdClass Object ( 
    [cf_id] => 1 
    [recordtime] => 2007-11-27 - 14:48:32 
    [ipaddress] => 192.168.0.8 
    [name] => Bob [email] => bob@example.com ) 
  [1] => stdClass Object ( 
    [cf_id] => 2 
    [recordtime] => 2007-11-27 - 14:48:45 
    [ipaddress] => 192.168.0.8 
    [name] => John 
    [email] => john@example.com ) 
  [2] => stdClass Object ( 
    [cf_id] => 3 
    [recordtime] => 2007-11-27 - 14:48:56 
    [ipaddress] => 192.168.0.8 
    [name] => Alice 
    [email] => alice@example.com ) 
  [3] => stdClass Object ( 
    [cf_id] => 4 
    [recordtime] => 2007-11-27 - 14:49:10 
    [ipaddress] => 192.168.0.8 
    [name] => Janet 
    [email] => janet@example.com 
  ) 
)

again this has been re-formatted for clarity. You can see that the results are returned in the form of an array called $rows. This is an array of data objects, one for each saved record.

Knowing this we can pull out any data for display on the page. Let's just go for a simple table of name, email and ip address. We just need to replace the debug output with a loop and some html

<table>
<?php
foreach ( $rows as $record ) {
  echo "<tr><td>".$record->name."</td>
      <td>".$record->email."</td>
      <td>".$record->ipaddress."</td></tr>";
}
?>
</table>

which gives us the neat little table shown in this image.



That's all there is to it. Of course you can elaborate the selection of records and data either in the MySQL or by filtering in PHP, you can choose a more friendly place to put the output, and you can make the layout a lot more elegant.

Bob<br><br>Post edited by: GreyHead, at: 2007/11/27 16:17

Bob,

Thanks. I'll need to spend a little more time digesting this info.

Perhaps it is simple when you good at this stuff, of which I am not.

My biggest challendge wil lbe where does this CODE get input / entered and or changed.

Thank you for your help and quick response.

Mike
<!-- w --><a class="postlink" href="http://www.myccvc.com">www.myccvc.com</a><!-- w -->
[email]malloy0@msn.com[/email]

Hi malloy0,

It does look a bit daunting but it's not as bad as it seems🙂 Feel free to ask more questions here.

The code snippet I've put below can go just about anywhere, I don't think that there is anything in it that is specific to ChronoForms or requires being on a ChronoForms page.

However, the simple way to start is probably to create a dummy form in ChronoForms and put this code in the HTML code box. That way you can easily see what happens - ChronoForms will add form tags but they won't get in the way.

Bob

Bob,

Hi this is my 2nd post and the 1st didn't take...so instead I fussed some more with the code and DID did get it to work. The code is below.

Of course this look is not quite what I was hoping for...so:

1. How might I go about formatting this output page, given you are using the echo command?

2. I would like to add a link to the home page that says VIEW PEOPLE REGISTERED so anyone can view this at any time NOT just after the submit.

Anyway what I have is below for the code within the ON Submit PHP box.

Thanks in advance.

Mike
[email]malloy0@msn.com[/email]
<!-- w --><a class="postlink" href="http://www.myccvc.com">www.myccvc.com</a><!-- w -->

-----------------------------------------------------------

<?php 
	global $database;
	
$database->setQuery( "INSERT INTO #__chronoforms_11 VALUES  (
'' , '". date('Y-m-d')." - ".date("H:i:«»s"«»)."', '".$_SERVER['REMOTE_ADDR']."' , '".$_POST['FirstName']."' , '".$_POST['LastName']."' , '".$_POST['Team']."' , '".$_POST['Session']."');" );
if (!$database->query()) {
echo "<script> alert('".$database->getErrorMsg()."'); window.history.go(-1); </script>
";
}
?>

<?php

// initialise the database code
global $database;

// set up the database query
$sql = "SELECT * FROM #__chronoforms_11;";
$database->setQuery($sql);
    
// retrieve the data from the database
$rows = $database->loadObjectList();

// debug code to show the results 
//echo "rows: ";print_r($rows);echo "<br />";
?>

<table>
<?php
foreach ( $rows as $record ) {
  echo "<tr>
    <td>".$record->FirstName."</td>
    <td>".$record->LastName."</td>
    <td>".$record->Team."</td>
    <td>".$record->Session."</td>
  </tr>";
}
?>
</table>

<br><br>Post edited by: GreyHead, at: 2007/11/29 11:57

Hi Mike,

First off, you don't need the database 'INSERT' query there, that was just to find out info about the table. Here's your code without it:

<?php
// initialise the database code
global $database;
$sql = "SELECT * FROM #__chronoforms_11;";
$database->setQuery($sql);
$rows = $database->loadObjectList();
echo "<table><tr>
  <td>Name</td>
  <td>Team</td>
  <td>Session</td></tr>";
foreach ( $rows as $record ) {
  $name = $record->FirstName." ".$record->LastName;
  echo "<tr>
    <td>".$name."</td>
    <td>".$record->Team."</td>
    <td>".$record->Session."</td>
  </tr>";
}
echo "</table>";
?>

I've removed the comments and tidied up the results table a little.

You can use this code on any page where you can insert the PHP. One way to do this would be to create a simple ChronoForms form using this code and to put it into a content page using the ChronoForms mambot.

Note: Although ChronoForms is designed to 'do forms' it doesn't it will work perfectly well with php+html that doesn't include any form code. Set 'Email results' to 'No' and complete the minimum entries to let ChronoForms validate.

This is using a sledge-hammer to crack a nut, but if the sledge-hammer is handy . . .

Bob

Bob,

Thanks. So awesome. I appreciate your help.

Tried the mambot thing in a Static Content page, it's easy to do. {chronocontact}Registration_List{/chronocontact}

Go here...

http://www.myccvc.com/content/view/178/328/

Meanwhile, after you take a look you'll see why I have yet another question.

Any ideas on how to format the Static Content page that the mambot is on? So I can put spaces, sort the list etc? Maybe too much to try to sort, but if it's relatively easy to share lemme know.

Again, thanks.

Mike
<!-- w --><a class="postlink" href="http://www.myccvc.com">www.myccvc.com</a><!-- w -->
[email]malloy0@msn.com[/email]<br><br>Post edited by: malloy0, at: 2007/11/30 03:06

Hi Mike,

That's a 'How long is a piece of string?' question. You can do pretty much anything with the data once you've got it into a database.

You have two points of control (there is a third that I'll come back to).

1) You can filter and sort the info you pull out from the database in the 'SELECT' statement.

For example "SELECT * FROM #__chronoforms_11 WHERE Team = '12-1';" will just select out that subgroup.

And "SELECT * FROM #__chronoforms_11 ORDER BY Team;" will sort the data by team or "SELECT * FROM #__chronoforms_11 ORDER BY Team, FirstName;" will sort on these two fields.

2) The second point of control is in the PHP. You are running a foreach loop that goes through each record one by one so it's quite simple to add in extra PHP to format the output.

Lets say that you have your data sorted by Team and want to put a header row for each team. Your code might look like:

$last_team = "";
foreach ( $rows as $record ) {
 $name = $record->FirstName." ".$record->LastName;
  // it there's a change in team name then output a header row
  if ( $record->Team == $last_team ) {
    echo "<tr><td style="color:blue;" colspan= '2'>".$record->Team."</td></tr>";
  }
  $last_team = $record->Team;
  echo "<tr>
    <td>".$name."</td>
    <td>".$record->Session."</td>
  </tr>";
}

By using 'if' clauses you can mark out almost any kind of data this way. You can also re-format stuff though this might be easier to do with the third control point.

3) You can also change your data before it is saved using either the OnSubmit - before email or after email fields. Possibly after is better here.

For example I notice that some of the names a formatted differently. You could have a little php to clean up the names:

<?php
$_POST['FirstName'] = ucwords($_POST['FirstName']); 
?>

will do this.

It would probably also help to divide the layout of the session fields as you actually have four different pieces of information there: FUll/RECERT, time slot, date & location. Ideally each of these would go in a separate database field to give you more flexibility in handling the data afterwards. (But at that point we're getting dangerously near to talking about relational tables so I'll stop.)

I hope this gives you some idea of what is possible, please come back if you have specific needs or ideas that you'd like to follow up.

Bob<br><br>Post edited by: GreyHead, at: 2007/11/30 09:52

Thanks GreyHead. you're really helping

Hi,

thanks for the tutorial above. Its exactly what i was looking for.
But i have some further demands.

How can i manage that only registered users can fill a form which must be editable afterwards by the user who filled the form?

If you could give us a tutorial about that i will be your greatest fan🤣

hajopaj

Hi hajopaj,

You can test for registered users by using $my (at least in Joomla 1.0.x, may be different in Joomla 1.5).

Use a code chunk like:

<?php
global $my, $database;
if ($my->id != 0 ) {
 . . . show form . . .
} else {
  . . . show some other message to visitors
}
?>

To make the form reditable you need to save the data in a database table. If you save the user id as a field then you can use this to read the data from the table again and set the values for the form fields:

<?php
global $my, $database;
$sql = "SELECT name, email FROM #__chronoforms_nn WHERE id=".$my->id;
$database->setQuery($sql);
if (!$database->query()) {
  echo "<script> alert('".$database->getErrorMsg()."'); window.history.go(-1); </script>";
}
$form_info = $database->loadObject();

?>
<input name="name" value="<?php echo $form_info->name; ?>" />
<input name="email" value="<?php echo $form_info->email; ?>" />
. . .

Bob

Edited quoting in $sql<br><br>Post edited by: GreyHead, at: 2008/02/04 00:09

Hi GreyHead,

thank you very much for this great component!!! I'm just testing a little bit Chrono and it seems, that it satisfy my wishes🙂

But i have one problem: I read from different tables information and want to display the results.

Some tests with select-fields are working - but now - i want to display the user-data, which are stored in the chronoperfilador table.

I tried your code:

<?php
global $my, $database;
$sql = "SELECT firstname, lastname, cb_companyname FROM #__chronoperfilador_2 WHERE user_id=$my->id";
$database->setQuery($sql);
if (!$database->query()) {
  echo "<script> alert('".$database->getErrorMsg()."'); window.history.go(-1); </script>";
}
$form_info = $database->loadObject();

?>
<input name="firstname" value="<?php echo $form_info->firstname; ?>" />
<input name="lastname" value="<?php echo $form_info->lastname; ?>" />
<input name="cb_companyname" value="<?php echo $form_info->cb_companyname; ?>" />

... but i always get the following error:

Warning: Missing argument 1 for loadobject() in /users/235156685/joomla/includes/database.php on line 451

Can you help me out with this problem?

Thank you very much in advance!

Best regards
Mark

Hi Mark,

From the error it looks like the results array is empty. I think that the sql may need better quoting:

$sql = "SELECT firstname, lastname, cb_companyname FROM #__chronoperfilador_2 WHERE user_id=".$my->id;

or possibly

$sql = "SELECT firstname, lastname, cb_companyname FROM #__chronoperfilador_2 WHERE user_id='".$my->id."'";

Try this and see if it does the trick, if not put a debug line in:

print_r($sql);echo "<br />";

and try the resulting sql in PHPMyAdmin to see if it runs OK.

Bob

Hi Bob!

wow ... also working on sunday?😉

thank you very much for your answer. I tried the different sql statements with the same error unfortunately ...😟

If i put in the

print_r($sql);echo "<br />";

.... it displays:

SELECT firstname, lastname, cb_companyname FROM #__chronoperfilador_2 WHERE user_id=66

I really don't understand it ... i'm :blink:

Thank you again .... and best regards
Mark

Hi Mark,

The sql looks OK. Please put another debug line just before the ?>

print_r($form_info); echo "<br />";

Bob

Hi Bob ...

thank you again🙂

Something interesting:

If i use this code:

$form_info = $database->loadObject();

print_r($form_info); echo "<br />";

The error comes again with empty boxes - but nothing is written from the print_r statement.

If i modify the code to:

$form_info = $database->loadObject($form_info);

print_r($form_info); echo "<br />";

the result is: NO error anymore .... and only "1" (without quotes) is displayed with empty boxes.

Perhaps it is important: I use MySQL 4.1.22 ... and PHP Version 4.4.7

I'm sorry, for my questions and this problem ...

Best regards
Mark

Post edited by: blackhorse, at: 2008/02/04 01:32<br><br>Post edited by: blackhorse, at: 2008/02/04 01:32

Hi Bob,

i have found the error:

here is the solution, which works for me:

<?php
global $my, $database;
$sql = "
  SELECT firstname, lastname, cb_companyname 
    FROM #__chronoperfilador_2 
    WHERE user_id=".$my->id;
$database->setQuery($sql);
if (!$database->query()) {
  echo "<script> alert('".$database->getErrorMsg()."'); window.history.go(-1); </script>";
}
$form_info = NULL;

$database->loadObject($form_info);

?>

<input name="firstname" value="<?php echo $form_info->firstname; ?>" />
<input name="lastname" value="<?php echo $form_info->lastname; ?>" />
<input name="cb_companyname" value="<?php echo $form_info->cb_companyname; ?>" />

Thank you very much for your help!!

Best regards
Mark<br><br>Post edited by: GreyHead, at: 2008/02/04 11:20

Hi Mark,

If it works for you that's great . . . and this code

$form_info = NULL;
$database->loadObject($form_info);

looks weird to me. I'll install chronoperfilador and test it later today.

Bob

Hello everyone.
I've read this post and found it really helpful!
Now I can show results of my forms immediately after submiting it.
:)

My question is:
Is there some way to "format" the output table ?
Sorry for my dumb question, but my php knowledge is definitely beginner (instead in HTML I'm quite good).
Thank you in advance for answering.
Albuin


P.S. I have to add another question to this topic...
I noticed that after filling in the section
Form Code -> On Submit Code (after sending email)
records are no more correctly inserted in my tabel.

I receive no error messages...
The following is my code

<?php 
// initialise the database code 
global $database; 
$sql = "SELECT * FROM #__chronoforms_6;"; 
$database->setQuery($sql); 
$rows = $database->loadObjectList(); 
echo "<table border=\"0\" cellpadding=\"4\" cellspacing=\"0\" width=\"600\"><tr style=\"font-weight:bold\"> > 
  <td>time</td> 
  <td bgcolor=\"#FFFF99\">P1</td> 
  <td bgcolor=\"#FFFF99\">Army</td>
  <td bgcolor=\"#FFFF99\">BanW</td> 
  <td bgcolor=\"#FFFF99\">BanL</td> 
  <td bgcolor=\"#FFFF99\">BlocksL</td> 
  <td bgcolor=\"#99FF99\">P2</td> 
  <td bgcolor=\"#99FF99\">Army</td>
  <td bgcolor=\"#99FF99\">BanW</td> 
  <td bgcolor=\"#99FF99\">BanL</td> 
  <td bgcolor=\"#99FF99\">BlocksL</td>
  </tr>
";
foreach ( $rows as $record ) { 
  echo "<tr>
      <td>".$record->recordtime."</td> 
      <td bgcolor=\"#FFFF99\">".$record->P1."</td> 
      <td bgcolor=\"#FFFF99\">".$record->P1Army."</td> 
      <td bgcolor=\"#FFFF99\">".$record->P1BanW."</td> 
      <td bgcolor=\"#FFFF99\">".$record->P1BanL."</td> 
      <td bgcolor=\"#FFFF99\">".$record->P1BlocksL."</td> 
      <td bgcolor=\"#99FF99\">".$record->P2."</td> 
      <td bgcolor=\"#99FF99\">".$record->P2Army."</td> 
      <td bgcolor=\"#99FF99\">".$record->P2BanW."</td> 
      <td bgcolor=\"#99FF99\">".$record->P2BanL."</td> 
      <td bgcolor=\"#99FF99\">".$record->P2BlocksL."</td> 
 </tr>"; 
} 
echo "</table>"; 
?>

Can somebody help me?
Thank you again

Hi boccio,

Nothing there should stop the records being saved as far as I can see. Please post a Form Backup of the whole form for us to look at.

Bob

Here you can find the Form Backup. [file name=boccio_FORM.cfbak size=11864]http://www.chronoengine.com/images/fbfiles/files/boccio_FORM.cfbak[/file]

Hi boccio,

if records are no longer saved then the autogenerated code doesnt run, and this is because you have some error in your code above somewhere, please assure that your code above is parsed and executed till the last line fine to be sure that its error free

Cheers

Max

Hi,
First congratulations for your excellent work. I find all solutions for my problems in forum.
But i coludn't take some variables from form table database. This is my php code:

<?php 
// initialise the database code 
global $database; 
$sql = "SELECT * FROM #__chronoforms_1;"; 
$database->setQuery($sql); 
$rows = $database->loadObjectList(); 
echo "<table><tr> 
  <td>İsim</td> 
  <td>Katılan kişi</td> 
  <td>Katılan çocuk</td></tr>"; 
foreach ( $rows as $record ) { 
  echo "<tr> 
    <td>".$record->isim."</td> 
    <td>".$record->Katilim_eriskin."</td> 
    <td>".$record->Katilim_cocuk_6."</td> 
  </tr>"; 
} 
echo "</table>"; 
?>

I uploaded it to images directory. As ı call http://www.reflumvar.org/capa88/images/deneme.php adress with Firefox, ı take the answer
Fatal error: Call to a member function setQuery() on a non-object in /var/www/domains/reflumvar.org/docs/capa88/images/deneme.php on line 5.

I attached my table to the message.

I hope anyone find a solution.
Sincerely [file name=deneme.cfbak size=6243]http://www.chronoengine.com/images/fbfiles/files/deneme.cfbak[/file]

Hi drkoray,

The error message seems to relate to line 5 of your code:

$database->setQuery($sql);

and says that database isn't being found as an object.

I'm not exactly sure what's happening though. Why are you putting this code in a file in the images folder?

Bob

PS There are some problems with your form html - lots of missing quotes, many tags aren't closed e.g. no </option> tags. I suggest that you run this through an html validator. The 'special' characters also cause problems (they break the ChronoForms backup file for one); it may help to replace these with html entities.

From curiosity - What language is it? I recognise some Turkish words but I haven't seen so many non-roman characters in Turkish text.

Why are you putting this code in a file in the images folder?

I can't use ftp at office, so i used joomlaXplorer and because of user permissions i used image folder which CHMOD is 777.

There are some problems with your form html - lots of missing quotes, many tags aren't closed e.g. no </option> tags. I suggest that you run this through an html validator.

I think i solved the form problems except of special characters.

What language is it?

Türkish

You can now try http://www.reflumvar.org/capa88/deneme.php

The problem consists. Any more suggestion?

Hi drkoray,

The main problem is that the page isn't being called as a ChronoForms form or inside Joomla.

Bob

The prescription for my disease is:
MosQ component
http://support.ongetc.com/
Very very good component. I think, if someone has to do something with database values, than he (or she) should try this component.
I attached an example image to this message too.
Sincerely.

Hi drkoray,

Looks good, glad your problem is solved.

Bob

This is a good thread. Thanks Greyhead! Here's a twist on this post. Suppose I want use one form (or just import into mySQL) for some data - such as name, address, etc. and then have these fields come up as static info, but have fields out to the side that allow data entry related to those records. Suppose that I want somebody to pull up a form and enter weight and hair color for people who are already in the database?

I think you can do this with a form and chronoconnectivity to pull the database data!