Forums

*First Posted on January 24, 2009
*Updated by benblee January 24, 2009
*Updated by Max January 24, 2009 to upload images to the forums here
*next updater, please post username and date on this line

When you're done with this tutorial, see this post for how to add a search function to your form:
How to add a Search to a Connectivity form!

This is my tutorial on how I used ChronoForms and ChronoConnectivity to transfer data from a big Excel file into a viewable and editable database on Joomla. This includes using ChronoForms to create the database table, creating a downloadable CSV file link for the table, setting up ChronoConnectivity to retrieve and display the information, making all rows editable, and the ability to add new entries, all from the front end.

I'm using the latest ChronoForms 3.0 stable and ChronoConnectivity 1.2. The Joomla version is I'm working on is 1.5.9. Please also be aware of any minimum PHP or MYSQL system requirements recommended for any system or product you install...it may save some troubelshooting time later on down the road!
Joomla system requirements (please use Recommended if possible, minimum will work but may show issues)

Install ChronoForms
Install ChronoConnectivity
Please see the two attached files on the next post. These are patches and need to be applied to ChronoConnectivity. Simply download, unzip, then replace the files.


We are going to be using ChronoForms to create a table in the Joomla Database that will be used and editable later. I'm going to include code that I used to set up a database table that logs Skydiving Awards. The Awards are called the Star Crest Awards and have been going on since the Sixties. There's thousands upon thousands of entries that had to be loaded. This is how I did it.

The fields we will be using and what the form is set up for are as follows:
scrnum, lastname, firstname, middlei, city, state, date, time


Through Joomla Admin Backend, go to Components->Chrono Forms->Forms Management

Click on "New".
Now check the screenshot below for settings on the General tab:

[attachment=5]CF_tab_General.jpg[/attachment]


General Tab:
*Form Name: scr
*Email Results: Yes
*Form Method: Post
*Load Chronoforms CSS/JS Files?: Yes

Here's the Setup Emails tab showing the necessary settings. You want to click on the Envelope with the green arrow to start and then click and drag each part of the email you want over to the email area. Once you have the required minimum number of fields to send an email successfully, the border will turn green. Be sure to also check the Email Properties for each part and make sure things are set to "Yes" (Enabled).

[attachment=1]CF_tab_SetupEmails.jpg[/attachment]
[attachment=0]CF_tab_SetupEmails_EmailProperties.jpg[/attachment]


Setup Emails Tab:
At the minimum you need
*To:
*Subject:
*Fromname:
*FromEmail:

The Email Templates tab will then be filled in with a suggested layout once you hit "Apply" at the top.

Now visit the Form Code tab and click on the (+/-) next to "Form HTML:" line. This text box is where you enter in your form code.

[attachment=2]CF_tab_FormCode.jpg[/attachment]


For our example, we are going to enter the following:

<div class="form_item">
<div class="form_element cf_heading">
<h1 id="" class="cf_text">New Additions to SCR's</h1>
</div>
<div class="clear"> </div>
</div>

<div class="form_item">
	<div class="form_element cf_textbox">
	<label class="cf_label">SCR Number</label>
		<input class="cf_inputbox validate-number" maxlength="10" size="10" id="scrnum" name="scrnum" type="int(11)"><a onclick="return false;" 

class="tooltiplink"><img src="components/com_chronocontact/css/images/tooltip.png" class="tooltipimg" border="0" width="16" height="16"></a>
		<div class="tooltipdiv">SCR Number :: Numbers only please.</div>
</div><div class="clear"> </div></div>
	
<div class="form_item">
	<div class="form_element cf_textbox">
	<label class="cf_label">Last Name</label>
		<input class="cf_inputbox validate-alpha" maxlength="50" size="20" id="lastname" name="lastname" type="text"><a onclick="return false;" 

class="tooltiplink"><img src="components/com_chronocontact/css/images/tooltip.png" class="tooltipimg" border="0" width="16" height="16"></a>
		<div class="tooltipdiv">Last Name :: Letters only please.</div>
</div><div class="clear"> </div></div>

<div class="form_item">
	<div class="form_element cf_textbox">
	<label class="cf_label">First Name</label>
		<input class="cf_inputbox validate-alpha" maxlength="50" size="20" id="firstname" name="firstname" type="text"><a onclick="return false;" 

class="tooltiplink"><img src="components/com_chronocontact/css/images/tooltip.png" class="tooltipimg" border="0" width="16" height="16"></a>
		<div class="tooltipdiv">First Name :: Letters only please</div>
</div><div class="clear"> </div></div>

<div class="form_item">
	<div class="form_element cf_textbox">
	<label class="cf_label">Middle Initial</label>
		<input class="cf_inputbox" maxlength="2" size="2" id="middlei" name="middlei" type="text"><a onclick="return false;" class="tooltiplink"><img 

src="components/com_chronocontact/css/images/tooltip.png" class="tooltipimg" border="0" width="16" height="16"></a>
		<div class="tooltipdiv">Middle Initial :: Letter only please</div>
</div><div class="clear"> </div></div>

<div class="form_item">
	<div class="form_element cf_textbox">
	<label class="cf_label">City</label>
		<input class="cf_inputbox validate-alpha" maxlength="50" size="20" id="city" name="city" type="text"><a onclick="return false;" class="tooltiplink"><img 

src="components/com_chronocontact/css/images/tooltip.png" class="tooltipimg" border="0" width="16" height="16"></a>
		<div class="tooltipdiv">City :: Letters only please.</div>
</div><div class="clear"> </div></div>

<div class="form_item">
	<div class="form_element cf_textbox">
	<label class="cf_label">State</label>
		<input class="cf_inputbox validate-alpha" maxlength="50" size="20" id="state" name="state" type="text"><a onclick="return false;" class="tooltiplink"><img 

src="components/com_chronocontact/css/images/tooltip.png" class="tooltipimg" border="0" width="16" height="16"></a>
		<div class="tooltipdiv">State :: Letters only please.</div>
</div><div class="clear"> </div></div>

<div class="form_item">
	<div class="form_element cf_textbox">
	<label class="cf_label">Date</label>
		<input class="cf_inputbox" maxlength="10" size="10" id="date" name="date" type="varchar(255)"><a onclick="return false;" class="tooltiplink"><img 

src="components/com_chronocontact/css/images/tooltip.png" class="tooltipimg" border="0" width="16" height="16"></a>
		<div class="tooltipdiv">Date :: MM/DD/YYYY</div>
</div><div class="clear"> </div></div>

<div class="form_item">
	<div class="form_element cf_textbox">
	<label class="cf_label">Time</label>
		<input class="cf_inputbox" maxlength="8" size="8" id="time" name="time" type="varchar(255)"><a onclick="return false;" class="tooltiplink"><img 

src="components/com_chronocontact/css/images/tooltip.png" class="tooltipimg" border="0" width="16" height="16"></a>
		<div class="tooltipdiv">Time :: HH:MM AM/PM</div>
</div><div class="clear"> 

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

</div></div></div>

Now click "Save" at the top and you should be back at the "Forms Management" page. From here, check the box of the form you need to create a Database table for and then click on "Create Table" as shown below:

[attachment=4]<!-- ia4 -->CF_addDBtable_function.png<!-- ia4 -->[/attachment]


This reads in all the columns mentioned in your code, so if you're missing one, you may want to go back over your code and make sure everything is named right within it. Also, be aware that ChronoForms inserts several columns before the ones in your code and they are already checked. It's best to leave these checked and then check the rest of yours and hit "Save Table" at the bottom.

Now you have your table!

Go back into the form you are working on through the "Forms Manager".

Click on the DB Connection tab and choose "Yes" from the dropdown and then find your new table click it to highlight it.
Your table will have the prefix "jos_chronoforms_" and then followed by your form name. For us it looks like the image below:

[attachment=3]CF_tab_DB_Connetion.jpg[/attachment]


Now click "Save" at the top and the rest of the tabs will be taken care of...unless you have a more complicated form, but we won't be covering that here just yet.

This form can be left "unpublished" as we are going to be using ChronoConnectivity later to view and edit it.

Congratulations, you now have your form and database table set up.
You can run the next step if you want to fill up the table with sample data. If you're not familiar with phpMyAdmin, you don't have to worry about this step.
I've included a test csv file that has the same entry repeated 50 times just for testing purposes.

*Download it and unzip.
*Open up phpMyAdmin, click on your Joomla Database, then click on the database table we just created in the left column menu.
*Chose the "Import" tab and Browse for the included CSV file.
*Under: Format of imported file: chose the following,
-CSV[/i]
--Fields terminated by: , (this is just a comma, no spaces before or after)
--Fields enclosed by: (delete everything in this entry)
--Fields escaped by: , (this is just a comma, no spaces before or after)
Leave everything else and hit "Go" at the bottom right.
*Your table should now have some data in it and viewable if you click the "Browse" tab.

Now to create a version that is downloadable in CSV format.

From your "Forms Management" area, click "New" and go through the first steps above of naming your form, but you don't need to worry about email here. I usually try to choose a name that lets me know what forms are are downloads of others, so in this instance if my first form from above was named "form1" I would name this one "form1_download". It's generally a good idea to not leave spaces in name of anything if you can avoid it. The easiest way to make a space and not lose any functionality is by way of an underscore. I am also in the habit of marking "Load Chronoforms CSS/JS Files?" TO "Yes" as I've had better results when I'm displaying anything.

Under the "Form Code" tab, you want to pasted the code mentioned below:
This next part is taken from a post by Max found here:
How to make a form to export some table to CSV file

replace jos_tablename with your real table name!
and
edit this line of code to select whatever records if you don't want a full backup of the entire table (recommended to leave it as is):

$database->setQuery( "SELECT * FROM ".$tablename."" );

The code you need to edit and use is here:

<?php
global $mainframe;
	$database =& JFactory::getDBO();

	include_once JPATH_BASE.'/components/com_chronocontact/excelwriter/'."Writer.php";
	//echo $_POST['formid'];
	/*$formid = JRequest::getVar( 'formid', array(), 'post', 'array');
	$database->setQuery( "SELECT name FROM #__chrono_contact WHERE id='".$formid[0]."'" );
	$formname = $database->loadResult();*/
	
	$tablename = 'jos_tablename';
	$tables = array( $tablename );
 	$result = $database->getTableFields( $tables );
	$table_fields = array_keys($result[$tablename]);
	
	$database->setQuery( "SELECT * FROM ".$tablename."" );
	$datarows = $database->loadObjectList();
	
	$titcol = 0;
	foreach($table_fields as $table_field){
		if($titcol){$csvline .=",";}
		$csvline .= $table_field;
		$titcol++;
	}
	$csvline .="\n";
			
	$datacol = 0;
	$rowcount = 1;
	foreach($datarows as $datarow){
		foreach($table_fields as $table_field){
			if($datacol){$csvline .=",";}
			$csvline .= '"'.addslashes($datarow->$table_field).'"';
			$datacol++;
		}
		$csvline .="\n";
		$datacol = 0;
		$rowcount++;
	}
	
	if (ereg('Opera(/| )([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
		$UserBrowser = "Opera";
	}
	elseif (ereg('MSIE ([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
		$UserBrowser = "IE";
	} else {
		$UserBrowser = '';
	}
	$mime_type = ($UserBrowser == 'IE' || $UserBrowser == 'Opera') ? 'application/octetstream' : 'application/octet-stream';
	@ob_end_clean();
	ob_start();

	header('Content-Type: ' . $mime_type);
	header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT');

	if ($UserBrowser == 'IE') {
		header('Content-Disposition: inline; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.csv"');
		header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
		header('Pragma: public');
	}
	else {
		header('Content-Disposition: attachment; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.csv"');
		header('Pragma: no-cache');
	}
	print $csvline;
	exit();
?>

Now hit "Save" up top and then be sure the form is "Published".
When you view your "Forms Manager", look for the link listed to the right of the form name you just entered and this is what you want to use as the download link...whether it be a menu item or within an article. Give it a try...but remember that there isn't any data in there yet.

If you have Data that needs to be loaded from a CSV file into the database table, now is the time to do it. We will cover the basics of getting an Excel file into a database table in another tutorial. A quick note though is to remember that you have extra columns added by ChronoForms in the table so be sure to account for them in your Excel or CSV file.
Please always make full backups of not only these tables, but your full site and database.



Now it's time for the ChronoConnectivity part.

In the Joomla Admin Backend, visit Components->Chrono Connectivity

Choose "New", then look at the General tab.
You may want to try hitting "Apply" intermittently so your session doesn't time out on you for these next steps. You will need to have a minimum number of steps completed before it will let you do this, so don't panic if it doesn't let you the first time you try it...just keep plugging away!

You'll need to fill out the following:
General
*Name: SCR_awards
*TableName: jos_chronoforms_scr (note that this is the default name because the form name was "scr")
*Header:

The SCR Awards Recipient Database.  We will be adding search functions soon.
<br />
{new_record}<br />
<table style="text-align: left; width: 100%;" border="0" cellpadding="2" cellspacing="0">
  <tbody>
    <tr>
      <td style="font-weight: bold; width: 10%;">SCR#</td>
      <td style="font-weight: bold; width: 15%;">Last Name</td>
      <td style="font-weight: bold; width: 15%;">First Name</td>
      <td style="font-weight: bold; width: 5%;">M.I.</td>
      <td style="font-weight: bold; width: 15%;">City</td>
      <td style="font-weight: bold; width: 15%;">State</td>
      <td style="font-weight: bold; width: 10%;">Date</td>
      <td style="font-weight: bold; width: 15%;">Time</td>
    </tr>
  </tbody>
</table>
<div class="clr" style="border-bottom: 3px solid #222; padding: 3px; margin-bottom: 10px;"></div>

*Body:

<table style="text-align: left; width: 100%;" border="0" cellpadding="2" cellspacing="0">
  <tbody>
    <tr<?php if ($i % 2) echo ' style="background-color: #EBEBEB;"';?>>
      <td style="width: 10%;">{edit_record} {scrnum}</td>
      <td style="width: 15%;">{lastname}</td>
      <td style="width: 15%;">{firstname}</td>
      <td style="width: 5%;">{middlei}</td>
      <td style="width: 15%;">{city}</td>
      <td style="width: 15%;">{state}</td>
      <td style="width: 10%;">{date}</td>
      <td style="width: 15%;">{time}</td>
    </tr>
  </tbody>
</table>
<?php $i++ ?>

*Footer:

<div class="clr" style="border-bottom: 3px solid #222; padding: 3px; margin-bottom: 10px;"></div>
    <script type="text/javascript">
    window.addEvent('domready', function() {
       var limit_select = $('limit').options;
       if ( !limit_select ) return;
       for ( var i = 0; i < limit_select.length; i++ ) {
            if ( limit_select[i].value == 0 ) {
               limit_select[i].value = 250;
               limit_select[i].innerHTML = '250';
            }
       }
    });
    </script>
<div align="center">{pagination}</div>
<br />

Note: In the code just above there is a rewrite for the last Pagination option to change it from "all" to "250". If you'd like to change that to a different number, just change it in both places above.
*Body Loop: Inside Body Loop
*Header Title: SCR Awards Recipients
*Edit Link Code: <u>Edit</u>
--(note that you can use html here so using an image the size of a favicon could work really well)
*New Link Code: <h4><u>Add New Entry</u></h4>
*List Limit: 25
--(this was included in one of the patches mentioned at the top so if you don't see it, it's because you didn't install the patch)

Front Permissions Tab

*Record Edit Template:

<h1>Add or Edit an entry in the SCR Awards</h1>
<br /><br />
<table>
     <tr><td>SCR Number:</td><td><input type="text" name="scrnum" size="10" value=""></td></tr>
     <tr><td>Last Name:</td><td><input type="text" name="lastname" size="30" value=""></td></tr>
     <tr><td>First Name:</td><td><input type="text" name="firstname" size="30" value=""></td></tr>
     <tr><td>M.I.:</td><td><input type="text" name="middlei" size="30" value=""></td></tr>
     <tr><td>City:</td><td><input type="text" name="city" size="30" value=""></td></tr>
     <tr><td>State:</td><td><input type="text" name="state" size="30" value=""></td></tr>
     <tr><td>Date:</td><td><input type="text" name="date" size="30" value=""></td></tr>
     <tr><td>Time:</td><td><input type="text" name="time" size="30" value=""></td></tr>
</table>
<input type="hidden" name="id" value="">

*"New Record" user Groups: highlight (using "control" click) all the user groups you want to have permission to add a new entry to the database table. If you highlight "Author" only, it does not automatically include all the user groups above author, you have to highlight each and every one you want to have permission to us this function.
*Editors user Groups: same as "New Record" directions
*"Delete Record" user Groups: Also same as "New Record" directions, however I chose not to allow it on my because I have a HUGE ordered list and I didn't want to lose data on a mouse click mistake.

Admin Config Tab

*Data View Fields Name: scrnum,lastname,firstname,middlei,city,state,date,time
*Data View Columns titles: SCR Number, Last Name, First Name, Middle I, City, State, Date, Time
*Record Edit Template:

<h1>Add an entry to the SCR Awards</h1>
<br /><br />
<table>
     <tr><td>SCR Number:</td><td><input type="text" name="scrnum" size="10" value=""></td></tr>
     <tr><td>Last Name:</td><td><input type="text" name="lastname" size="30" value=""></td></tr>
     <tr><td>First Name:</td><td><input type="text" name="firstname" size="30" value=""></td></tr>
     <tr><td>M.I.:</td><td><input type="text" name="middlei" size="10" value=""></td></tr>
     <tr><td>City:</td><td><input type="text" name="city" size="10" value=""></td></tr>
     <tr><td>State:</td><td><input type="text" name="state" size="10" value=""></td></tr>
     <tr><td>Date:</td><td><input type="text" name="date" size="10" value=""></td></tr>
     <tr><td>Time:</td><td><input type="text" name="time" size="10" value=""></td></tr>
</table>
<input type="hidden" name="id" value="">

*OnSaveJSCode:

if(0){				
		} else {
		submitform( pressbutton );
		}

Now...Finally...hit "Save" at the top!


Now for the fun part...

In the Chrono Connectivity->Forms Manager, use the link given to the right of the form name to view your form. This is also the link you will want to use for any menu items you set up. Remember what you set your permissions to and then login with an account of the same permissions to see the "Add New Entry" and "Edit" links.



Final notes: I made sure that the "scrnum" column was "INT(11)" so that I could order the database by the numbers in that column. This was a setting I double checked through phpMyAdmin.

These are the two patches to ChronoConnectivity 1.2 mentioned elsewhere on this forum and used in this example. They provide the ability to set a default list limit when viewing the database table from the Joomla Frontend.

I just wanted to say a big thankyou for adding this example/tutorial. I have not had a chance to read it thoroughly but you can be assured I will! I am hoping to use CF for a new project and am currently looking at CC and the various doors it opens for the future, however couldn't find much info on CC - so this is a massive help for me and I'm sure lots of others too.

Thanks again
Simon

Hi benblee,

Thank you very much for the great tutorial, Some images was not displayed because of the paths so I attached them to the post here!

Regards,
Max

Thanks for your reply,..

Where to put this codes???

You want to find this line:

$tablename = 'jos_tablename';

and put your table name in place of jos_tablename like so:

$tablename = 'jos_yourtablename';

jos_ is the standard prefix used for Joomla tables so that part will only be different if you know how all that works and changed it yourself.

Hi,

To be strictly correct use

$tablename = '#__yourtablename';

and Joomla will look after the prefix.

Bob

Hi,
Nice tutorial. I am not sure if it is apropriate here. Can anyone add on how is it different (from which step onwards) to edit a community builder profile using chonoform? I know that chronoconectivity is to be used and Max said something about using hidden fields which was not that clear to me in this post. Please anyone can shed some light on this?

Awaiting curiously.

Hi ranjeev,

to edit some record in Chronoconnectivity V1.2 you may not need the hidden field trick as long as your table has a primary key, so just do it as the tutorial!

Cheers
Max

I'm using chronoconectivity and chrono forms, and my doubt is how to make an update.

I create a new table, my form put some data in the Database, i can get the data from the table, but when i'm trying to update some of the data inserted, i got some problems.

i tested the sql with phpmyadmin, and it works,

my code its something like this:

<?php
$editor =& JFactory::getEditor();
$sql = "UPDATE #__chronoforms_readings SET type=9";
$database->setQuery($sql);
$database->query();
echo "Errors: " . $database->getErrorMsg();
?>

my problem is where i put this code.

best regards

Bruno Paulette

Hi Bruno,

I think you need to load the database with $database =& JFactory::getDBO();

I think that you need a WHERE clause in your sql to say which record to update.

I'm not sure where the code goes because I don't know what you are trying to do here.

Bob

thanks for the reply.

I have a table with some readings and i need to make a chart, one of the field of the table is the type of chart that the user can choose.

For now i just want to make one user (admin) to change all the records in that table, for example set the typeofchart = 1.

this is my table
cf_id
uid
recordtime
ipaddress
cf_user_id
date
typeofchart
value

i need to code this areas in Connections Management->general
WHERE SQL:
Header:
Body:
Footer:

but i dont want to present any information back, so i think that they, may be blank, and the WHERE SQL should be blank to, because the changes are for all the table.

With this information where do you think that i should put this code, or something like it.

<?php
$editor =& JFactory::getDBO();
$sql = "UPDATE #__chronoforms_readings SET type=9";
$database->setQuery($sql);
$database->query();
echo "Errors: " . $database->getErrorMsg();
?>

thanks a lot by the reply

Hi Bruno,

If it's admin by a site admin then the easy answer to this is to use the Admin Config tab in ChronoConnectivity to allow the field to be edited in the back-end.

Bob

i'm just playing with SQL and joomla with chronoforms and chronoconnectivity, when i can do this things, i think that all the rest is something like it.

i was trying and i use your advice, in the code that you said to change. and it works fine, no errors occur. but when i go to phpmyadmin to see the result, this code acts in a strange way.


In that table all the elements are from the same user, but in the future that will not be like this, each user will have 1 register that provides the type of chart to the application.

i inserted this code in chrono form-> form code -> Form HTML

<?php
$database =& JFactory::getDBO();
$sql = "UPDATE #__chronoforms_type SET type='$_POST[gratype]' WHERE cf_user_id=0;";
$database->setQuery($sql);
$database->query();
?>

the problem now that this update creates a new register instead with the right value and changes all the rest. In future i want only one register by user.

One more time, thank you for your help

Hi Bruno,

where do you use this code ? in Chronoforms or in Connectivity ? if its in Connectivity then tell me what are you trying to do because there may be an easier way to do this!

Cheers
Max

hi,

i'm using it in in Chronoforms. but if its more easier to use it in chrono connectivity, please tell me.

chrono forms-> form code -> Form HTML

with chrono connectivity for now i just want to present some data

thanks

Hi Bruno,

Honestly I cant understand how your application works, why do you use this code in the form HTML ? this means that it will run once the form is opened not when submitted, is this what you need ?

also why trying to use WHERE cf_user_id=0 ? do you have only 1 user ? the update statement will never create new records too, it will run only if the WHERE case is achieved!

tell me please in brief what are you trying to update, you said you list some data from some table using connectivity, that's good, what about the update ?

Regards
Max

Hi, thanks for the replys.

I think that i solved my problem.
My "work" is/was "play" a little with joomla and database to see if this makes what i need for my application.

i will have more than one user, but for now and for my research its enough test it with just one user.

my error was executing my code in html form, the update should be in On Submit code - after sending email:

now i can do an update to one user, for another should be easy.

Thanks and keep up with the good work😀

Great news!! no problems! 🙂

Max

Hi,

This Tutorial is great; yet it doesn't work for me.

First a thing on the side: there is an error in the code for the body in the Chronoconnectivity:

  <tr<?php if ($i % 2) echo ' style="background-color: #EBEBEB;"';?>>

Should be

  <tr><?php if ($i % 2) echo ' style="background-color: #EBEBEB;"';?>

I've followed the Tutorial to the letter, twice, but still I keep gettings this problem:
In stead of showing the entered data the output on both front and and back end shows:
SCR# Last Name First Name M.I. City State Date Time
edit {scrnum} {lastname} {firstname} {middlei} {city} {state} {date} {time}

Also, after a click on edit the text boxes are not filled with the data entered but with the { } tags.

Next, when I tried to save any data (even if I leave the fields blank I'm getting this error:

Tablechronoforms_dataname::store failed - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE cf_id='1'' at line 1 SQL=UPDATE `oud_chronoforms_dataname` SET WHERE cf_id='1'

I've looked into the forum and tried the various solutions like hacking the connection.php files in both admin and front end, changing the " to ' but nothing helps.

I'm using:
Joomla! 1.5.8
PHP 5.1.2
And downloaded all Chrono files yesterday: latest versions.
I've also included the files you mentioned in your second post.

Hope you can help. Since I really want to understand this first before I start messing around with my own database/forms.

Danny

Since I don't want to edit my first posting because somepeople might encounter the same issue...here's the "solution"

As simple as it can be: when creating the database the system does NOT tick all fields that need to be created be default. Just the absolute needed fields to create a table are created. So before hitting save: click to select all fields and all will run fine.

The tutorial does mention to see if all fields that needs to be created are available but the "selected y/n" thing is easy to forget to check.

Works perfectly for me now.

Hi Dannyh,

Great, I was just reading your post and wondering what the answer is !!

Bob

BTW I think that the css is probably correct - the style should be inside the <td. . .> - it certainly won't work if it between the <tr> and the following <td>

True!..."suddenly" the code was displayed in a table.....messing around with the css options in general will do that...post is less important now:)

Hi! I've followed the instructions to the letter twice and both times I got the following front-end output errors:


1) Notice: Undefined variable: i in C:\wamp\www\mysamplesite.com\components\com_chronoconnectivity\chronoconnectivity.html.php(176) : eval()'d code on line 3
>
2) Notice: Undefined variable: i in C:\wamp\www\mysamplesite.com\components\com_chronoconnectivity\chronoconnectivity.html.php(176) : eval()'d code on line 15

What could I have done wrong and what should I do to correct it?

Thanks a lot in advance!!

Did you apply these patches?

http://www.chronoengine.com/forums.html?cont=posts&f=5&t=12324&st=0&sk=t&sd=a#p21889

I'm using this on Joomla 1.5.9. What are you using?

Hi hel-g,

These are PHP 'Notices' no real problem except that they show up. Try setting Error Reporting for your site to 'System Default' , 'Simple' or 'None' in the site Global Configuration and they should disappear. (You may need to try a couple of settings depending on how PHP is set up.)

Bob

Thank you very much, Benblee and Bob, for the replies and instructions!

Did you apply these patches?
http://www.chronoengine.com/forums.html?cont=posts&f=5&t=12324&st=0&sk=t&sd=a#p21889
I'm using this on Joomla 1.5.9. What are you using?

Hi benblee,
First, thanks for the great tutorials! They are such big help! Especially for someone like me who's a total newbie in joomla. I now see the many potential things that can be accomplished with chronoforms and chronoconnectivity.

Yes, I downloaded the patches and copied and replaced these onto their respective folders. I'm using Joomla 1.5.10. Could this be the reason?
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Hi hel-g,
These are PHP 'Notices' no real problem except that they show up. Try setting Error Reporting for your site to 'System Default' , 'Simple' or 'None' in the site Global Configuration and they should disappear. (You may need to try a couple of settings depending on how PHP is set up.)
Bob

Hi Bob,
I changed the Error Reporting setting to "None" but the error still comes out. The notices still come out:
Notice: Undefined variable: i in C:\wamp\www\mysamplesite.com\components\com_chronoconnectivity\chronoconnectivity.html.php(176) : eval()'d code on line 6>
Notice: Undefined variable: i in C:\wamp\www\mysamplesite.com\components\com_chronoconnectivity\chronoconnectivity.html.php(176) : eval()'d code on line 21

When I do removed the following PHP Tag:

<?php if ($i % 2) echo ' style="background-color: #EBEBEB;"';?><?php $i++ ?>

however, the notices don't appear anymore. Apart from the row bg colors, What other effects can this removal have on the rest of the codes (cuz I have very little knowledge on PHP.)?

Again, Thanks so much for all your help!

Tiffany

Hi Tiffany,

If you add

<?php $i = 0; ?>

in the header block all should be OK. That line just gives different backgroudn colours to alternating rows in the table. The problem is that $i isn't defined before it is first used - which, in this case is no problem apart from the message :-(

Bob

@Bob,

If you need to edit the code in the first post, feel free. I was just hoping to get the grunt work of all the typing out of the way so you guys can tweak it to what it needs to be 😀 .


@Tiffany,

Thank you for the thanks! I really don't know this stuff too well myself, I just kept notes as I went through the forum and pieced together all the info I found. Bob helped a ton as I worked my way through too.

I'm really glad you found it so helpful. 😀

Hi Bob,

Thanks so much for the code you gave! It worked! I no longer get the notices and the rows now have alternating background colors!! 😀 😀 😀

Tiffany

Thanx for the wonderful Tutorial.
Everything is working except {new_record} functionality. Edit/Delete functions are working but new records are not getting saved to the database. The blank form opens up on clicking the link but the data does not get saved on clicking save.
Pls advise.
Richa

Hi Richa,

Please post in Connectivity's forums, there are many topics there too, you may not get lots of answers there now because I'm alittle busy with Chronoforms 😟

Regards,
Max

Very Nice!, i read this guide, and i was wondering how did the final product looked, didnt see any ss. How the database looks like for users to view, edit,and create.
Thanks Dre

Sorry, don't have a screenshot for this. The client I did it for ended up not wanting a site :? . Bummer!

Glad you liked the tutorial though.

Well i decided to give this a try, so i did all the steps untill Front Permissions Tab couldnt find the section or tab that i need to put the code in, and i was lost past Front Permissions tab couldnt find the other places to put the code in ethier.any help wouldbe great

DreCat, that's because the last version was changed a little, you may add the code now to Chronoforms forms and choose them, I will prepare a new fresh tutorial shortly!

Regards
Max

Alright, thank for the feedback ill await the tutorial, would u know when it would be finished?

Hi, next week I hope!

Regards
Max

Hi there,

I made a form in Chrono and would like to be able to view/ edit/ display the information therein. I know I have to use Chrono Connectivity and I have installed it already (newest version).

However I always get stuck when using the manual above as some fields just do not exsist in in Chrono connectivity.

can anyone help me please?

Hi, as promised to many users, I will prepare a tutorial for the newest version ASAP!

Regards
Max

Thanks Max,

I will be waiting for the manual, as I cannot move until I have it.

Any idea by when you will have it done?

Michael

No idea sorry, I do all development and support work here alone because Bob is off for few days, so I'm extremely busy! 😟

Max

Hi Max,

I do understand. I read the current manual online, however always get stuck because I cannot figure out where to put the codes for the OnSaveJSCode, Admin Config Tab *Record Edit Template and Front Permissions Tab *Record Edit Template. If you can tell me where I need to put this data in I can figure out the rest, as without these steps I cannot seem to save and use it.

Michael

This is easy, you now have the option to choose a form for front editing and admin editing, if you understand Chronoforms then you must know that every form has its own JS code and onSave code, so the form code will replace all these missing boxes, for example, the onSave JS code is now can be used in the JS code box of the form used for admin editing!😉

Max

hello
i did the src form but when i´ll create the table, the scr number columm is missing... i added all columms and then i made the connectivity part but when i clicked save ocurred these following errors:

does anyone can help me? what should i do?

ps: i have already installed the connectivity patchs

thanks

igor

TableChronoConnectivity::store failed - Unknown column 'edit_template' in 'field list' SQL=INSERT INTO `jos_chronoconnectivity` ( `id`,`name`,`tablenames`,`SQL`,`head`,`body`,`footer`,`edit_template`,`front_edit_template`,`OnSaveCode`, `OnSaveJSCode`,`OnSaveCodefront`,`OnSaveJSCodefront`,`paramsall`,`Config`,`ccCache1`,`ccCache2` ) VALUES ( '0','SCR_awards','jos_chronoforms_scr','',' The SCR Awards Recipient Database. We will be adding search functions soon.\r\n
\r\n {new_record}
\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n
SCR# Last Name First Name M.I. City State Date Time
\r\n
',' \r\n \r\n >\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n
{edit_record} {scrnum} {lastname} {firstname} {middlei} {city} {state} {date} {time}
\r\n ','
\r\n \r\n
{pagination}
\r\n
','

Hi igor,

This looks like mixed versions. The older version of ChronoConnectivity used the 'edit_template' column but the newer versions uses ChronoForms for edit forms and so doesn't have this column.

It looks as though the newer version was used to create the database table but you have since over-written the files with the older version? Is this possible?

If you add the missing columns to the table this will probably work OK.

Bob

Hey been a few weeks now just checking if max has got the tutorial up?Let me know

Hi DreCat11, not yet sorry, few problems wasted my time😟 will release a new Connectivity version and then a nice tutorial for a nice application!

Regards
Max

Hey Guys,
Great Tutorial. I got the "Print Form" to work by pasting the code in the "Body" field. Works pretty slick!!

One thing...
When I open the Excel spreadsheet, all of my apostrophes are "escaped". Is there someplace I can put a list of characters not to encode? I really don't want my customer to have to do a "search and replace"

J1.5.14, Forms v3.1 RC5.5, CC V2.0 RC3

Firefox and IE7 both do the same thing (just in case that matters)

Thanks for all the hard work.

Great tutorial on a great product. Even I'm new to chronoengine community, i can feel the amazing future from that the principle and the robust design of CC and CF. And the support from this forum. Keep working ! Guys !

Please Help

Got a form made with Chronoforms with a 20 fields table, and make connection1 with a list of the most important fields like "Name", "Age", ", Email", "Address", and put a links "Edit", "Delete" and "Details", also make a connection2 to show the 20 fields of the users.

Now, when i click over "Details" to show the complete information of connection2 only show the information of the first record.

Please Help me

remrom, please show me a screenshot for the first and 2nd tab in your connection settings!

Max

How can i do a Search form for a table where i can do a search by parameters input by de user, got a table that have 4 fields about document uploads, Name (title of the doc), Author (writer of doc), year (") an type fields to browser de doc.

what i want is, after that the docs has been upload for the maker, any user can, by a search form input any parameters, name or author or year, and the results shows a list with the coincidence and the document link beside.

Thanks......

Hi remrom,

I think that there is an example about searching in the connectivity forums, you simply need to make the WHERE statement selects records with value = $_POST['search_parameter_name']

Regards,
Max

Looking at Chronoconnectivity for the first time, I followed this tutorial, adapting it to the new releases as far as I can & when I add a new record it gets posted twice.

Did the updated tutorial ever get done?

jb

Hi jb,

There's no new tutorial as far as I remember. There are a couple of recent threads discussing the double posting -- with some fixes I think.

Bob

Hi

I just followed this great tutorial.

But when i press Edit Record, the form show up with no data.

Regards Erik

I've installed CF and CC, latest versions. I want to create a list/update function on a table I've created in mySQL. I have used 2 tutorials I found on the ChronoEngine as the basis, however I notice these are based on an older release of CC. Is there an updated tutorial already?

Regards, Johan

Hi Johan,

There haven't been many changes to CC - I think that the latest tutorial is probably the long post by BenBlee in one of the CC forum threads.

Bob

Thanks Bob

I have taken BenBlee's Example as the basis for my first CC application, however find myself stuck on a warning msg, as explained in another post...
http://www.chronoengine.com/forums.html?cont=posts&f=12&t=17798

Can you "hint" me in the right direction?

Rgds, Johan

We hit the wall just doing the install. We will go through tutorial and see if we can solve our problem.

Installer::install: SQL-feil. DB function failed with error number 2006
MySQL server has gone away SQL=DELETE FROM `jos_components` WHERE `option` = 'com_chronocontact';
SQL =
DELETE FROM `jos_components` WHERE `option` = 'com_chronocontact';
Komponent Installer: SQL-feil. DB function failed with error number 2006
MySQL server has gone away SQL=DELETE FROM `jos_components` WHERE `option` = 'com_chronocontact';
SQL =
DELETE FROM `jos_components` WHERE `option` = 'com_chronocontact';
Error Loading ModulesMySQL server has gone away SQL=SELECT id, title, module, position, content, showtitle, control, params FROM jos_modules AS m LEFT JOIN jos_modules_menu AS mm ON mm.moduleid = m.id WHERE m.published = 1 AND m.access <= 2 AND m.client_id = 1 ORDER BY position, ordering

Hi ladyferret,

The message "MySQL server has gone away " is a sign that your site can't access the MySQL server. Most often this seems to be because you are on a shared server and some other user is hogging the shared resources.

YOu may have better luck at a different time of day.

If not then check with / complain to your ISP or change hosting company.

Bob

I wish all tutorials were this helpful and thorough!

Very useful. Thanks so much for that.

I agree with many of the other comments... very thorough tutorial. Probably worth paying for! Thanks for your help.

Great job by the way in explaining and showing the forms and connectivity example from start to finish. Everything is clearly pointed out and should make for easy implementation. Thank You.

This post has helped me go through the details of creating forms and establishing connectivity. Good Job.

Thanks to everyone who helped solve this problem.

Regards, Seedar.

Thanks for your help.

Hi,

To be strictly correct use

$tablename = '#__yourtablename';

and Joomla will look after the prefix.

Bob

Good tip..

i have been avoiding this forum for too long and chronoforms too. i have many joomla sites and not always use jos prefix...

Just found this post now. thanks a lot. This is very helpful!

nice tutorial , im not sure if i will be able to follow all the steps but i will try🙂

thanks for the tutorial

When I select my form and press the create table button, a page comes up showing 5 fields. Then there is a Plus link to add more form fields. There are probably 100 fields in my form. I don't want to have to add each field one by one. Is there a way to have the fields created automatically? If so, how?

Hi katandmouse,

ChronoForms tries to recognise the inputs from the Form HTML and includes entries for those it finds. What do you have in your Form HTML box?


Bob

Here's the form: http://californiahealthinsurance-quotes.com/employee-benefits-quote-group-insurance

thanks!

Hi katandmouse,

I think that the problem is some slighly broken HTML:

<input name="Name" size="25" maxlength="35">

This input (the first I looked at) is missing the 'type' attribute. Probably type='text' here. I don't remember if ChronoForms checks this without digging in the code.

Also the unquoted value in the DOB input

<INPUT size=8 value=00/00/00 name=DOB_1>

will probably break the parser :-(

Bob

I'm operating with version 3.1 RC5.1 and am having troubles downloading into CSV; I've looked through loads of posts and threads and everyone else seems to get it straight away so it must be me !

I've created a table and I've connected it to my form and have tested the form; however, when I go to the form manager, click on the link to the table and then select "Backup to Excel" or "Backup to CSV", I doon't seem to be able to get to the data in the fields of the form.

Am I missing something completely obvious please ?

Hi bellevue,

From that it looks as though the table doesn't have any columns to save the form data in. Is that possible???

Bob

Hello Bob

Thanks for that, yes, that makes sense.

I did list all the field names in "Extra dataview columns fields names" in Db connection; am I supposed to put them somewhere else ?

Thanks

Hi bellevue ,

Please check which columns you actually have in the table.

Bob

Thanks Bob

When you say "table", do you mean the table that I've created to be connected to the form ?

If so, below is a partial screen view of the fields I've got in that form (the field names shown are exactly the same as shown (under "Properties") in the wizard.

I hope this makes sense !

[attachment=0]Chronoform2.JPG[/attachment]

Hi bellevue ,

It's a bit hard to tell from the posts but that doesn't look like the same table as you are trying to export?

Bob

Hello Bob

Sorry for the delay.

I'm not 100% sure what I've done here and whether it's right; I've uploaded below screen prints of each stage I went through in case you can see if/where I've gone wrong...

First of all, I created my form in the wizard and you'll see that in the circle is the field name "leadmembername"; I completed the steps and the form is then shown in the Form Manager.

[attachment=2]Steps1and2.JPG[/attachment]

I then ticked "BookingFormEN" in the Form Manager and then clicked on "Create Table" as circled and the table shows up in the Form Manager as connected (shown circled in red):

[attachment=1]Steps3and4.JPG[/attachment]

When I click on the link to the connected table, it brings this up which is what I sent in the last message:

[attachment=0]ChronoStep5.JPG[/attachment]

I hope you can help.

Thanks

Hi bellevue,

I'm more confused now, The columns in the table you created don't appear to match up with the columns in the exported file. That explains why the entries are blank but not why the wrong columsn are there. I suspect that the export is from a different table, maybe one with a very similar name???

Bob

Hello Bob

Thanks for that.

I'm not sure I understand about the difference in the columns but maybe that'll become clear at some point.

In order to possibly solve what you're saying, I've attached a screen print of the DB Connection tab and it shows three tables in there:

bel_chronoforms_BookingFormEN - this is connected to the form "BookingFormEN"

bel_chronoforms_BookingFormUK - this is one I created (god knows why...maybe when I was testing things), it's of no use but I don't know if I need to or how to get rid of it

bel_chronoforms_Reservations - this is (I guess) a bit like "bel_chronoforms_BookingFormUK", eventually I'll be linking that to a chronoform I've created called "Reservations" but it's of no use at the moment and can be deleted if needs be

I hope this helps.

Hello Bob/Anyone

Can anyone help please ?

Thanks

Hi bellevue,

The table you are showing with he exported data has different column names to the table you say you are trying to export. So my best quess is that they are different tables. There isn't enough info to say anything much else here. This is a process that usually just works when you click the Export icon.

Bob

Thanks Bob

I have to say this is really frustrating ! OK, so I shall try and trouble shoot this:

1) With regard to the process I completed (per the post of 02/04/12) have I done that correctly ?
If not, could you tell me where I've gone wrong please ?

2) With regard to the column names (that you've said are different), per screenprint number 1 of 02/04/12 which shows the wizard, it shows one of the column names to be "leadmembername"; in screenprint number 3 (of the same post), the table shows the current name "leadmembername". Therefore, I can't understand what you mean when you say the column names are different ?

3) With regard to your thought "I suspect that the export is from a different table, maybe one with a very similar name???" Could one of the three tables shown in the screenprint of 03/04/12 be the offending article ? If so, which one is it and what do I need to do ?

I look forward to hearing from you.

Thanks

Hi bellevue,

Here are the two sets of column names that I see from your posts, as you can see there are no names that are the same.

Bob

Thanks for that Bob, yes, I do understand that the column names are not the same, you've mentioned that a few times.

I included a few questiuons in my last post about what I did (i.e. the process) and asked you (in order to troubleshot this) if I'd done that correctly but I don't seem to see a reply to those questions.

Anyway, I've spent hours trying to figure it out myself and have had some success. However, when I go to the connected table only some of the data is getting through.

For example, below is some form code for two fields on the form:

<div class="form_item">
<div class="form_element cf_textbox">
<label class="cf_label" style="width: 225px;">Lead Member Name:</label>
<input class="cf_inputbox required" maxlength="150" size="30" title="" id="text_0" name="leadmembername" type="text" />

</div>
<div class="cfclear"> </div>
</div>

<div class="form_item">
<div class="form_element cf_textbox">
<label class="cf_label" style="width: 225px;">Address:</label>
<input class="cf_inputbox required" maxlength="150" size="30" title="" id="text_59" name="address" type="text" />

</div>
<div class="cfclear"> </div>
</div>

The data from the "leadmembername" field gets through but not "address" (see below):

[attachment=0]ChronoTable160412.JPG[/attachment]

The code includes both their text numbers (e.g "text_0" or "text_59") but I see that the column heading for leadmembername is leadmembername but the heading for address is text_59. How do I fix that please ?

Also, the data in the table isn't in the same order that it was input, how do I change this please ?

Thanks

Hi bellevue,

The process you posted looks Ok - though the important step when you specified which columns to add isn't there.

In your last post address presumably isn't being saved because there is no 'address' column in the table.

Bob

Thanks for that, yes, I worked it out myself that the specifying which columns bit hadn't been done so that sorted that.

However, the issue with the address is still occurring. You'll see from the snippet of code below that the address field is also called "text_59":

<div class="form_item">
<div class="form_element cf_textbox">
<label class="cf_label" style="width: 225px;">Address:</label>
<input class="cf_inputbox required" maxlength="150" size="30" title="" id="text_59" name="address" type="text" />

</div>
<div class="cfclear"> </div>
</div>

You'll also notice that "text_59" is shown as a column heading in the table but it doesn't have the address data in that column ? Why is that ?

Also, the column order in the table seems to be text fields first then select fields; is there any way of those columns being sorted into a different order ?

Thanks

Hi bellevue,

Well no, inputs only have one name and in this case that is 'address'; 'text_59' is the id which isn't used by ChronoForms except as a unique identifier in some custom JavaScript.

The column order is set in the Create table dialogue, ChronoForms simply searches through the form HTML looking for input, select and textarea tags and generates a row for each of them. You can change the order then or, probably more easily, using PHPMyAdmin after the table is created.

Bob

Hi,
Nice tutorial. I am not sure if it is apropriate here. Can anyone add on how is it different (from which step onwards) to edit a community builder profile using chonoform? I know that chronoconectivity is to be used and Max said something about using hidden fields which was not that clear to me in this post. Please anyone can shed some light on this?