Two Methods to Populate Form Fields from a Select List One of the most common things you want to do in a form is pre-fill some of the fields from another source after making some sort of a selection. As in, you select from a select list and one or more fields gets updated from a table. I have found two methods to accomplish this, a Simple Version and an AJAX Version. Please note that these are probably not the only ways or even necessarily the best ways of doing this. These are simply 2 ways that I know work. I am certain the real coders out there have better ways but, for us amateurs, these methods work and they are fairly easy to do. Each method will start with a basic overview. Then there is a more detailed description with step by step instructions. Code snippets are included. Before each snippet, I will indicate where to put the code. What needs to change for the code to work for you follows each snippet. The code is in a fixed font and has a border around it so you can easily identify it, copy it and insert it into wherever you need it. Simple Version Overview As the name implies, this method uses a very simply approach. First you populate the select list then, using PHP, you create a Javascript that effectively holds the data from your select list table. The JS script is essentially an IF statement for each row from the table. That IF statement simply calls a JS function to populate your form fields with a parameter list containing the data for each field in your form. The IF statement simply compares the selectedIndex of your select field and calls the populate function. Because it works on the selectedIndex, the value parameter in the select list (e.g. 0;Title 1;value 1) is not important. However, you should populate this value with something to insure your select list will work. This is a fairly compact approach and it will hold quite a lot of data (anything up to 2,000 rows of 250 or so characters can be easily accommodated). Nonetheless, it is not recommended for very large tables or very large numbers of fields. Step 1 Start by loading your select list. This is adequately explained elsewhere, so I will simply include a code snippet here. This PHP code snippet goes in the Advanced/More options/Form pieces/Before Form text area of your form. Note that you can initialize any number of select lists this way. However, the second code snippets are used only for those tables that will populate fields in the form. Your SELECT must include whatever field you’re matching to (or retrieve the whole table), the description part of the select list (what a user will select, the select_Field table column in the example below), the primary key of the table (frequently an integer column named id but it could be anything) and all of the fields you will need to populate your form (the Fld_xx table columns in the example below). In this example, I am searching for rows in a table that were input by a specific user id. this->execPieceByName('ff_InitLib'); // get the ff_ library, only needed once $user = &JFactory::getUser(); // get the user via JFactory $userid = $user->id; // you may need to use a different value for this or none // Lookup the rows in the table $rows = ff_select("SELECT id, user_id, select_Field, Fld_01, Fld_02, Fld_03, Fld_04, Fld_05, Fld_06, Fld_07, Fld_08, Fld_09, Fld_10, Fld_11, Fld_12 FROM your_table WHERE user_id = '$userid' ORDER BY select_Field"); $listdata = "0;Please make a selection;0\n"; for ($i = 0; $i < count($rows); $i++) { $listdata .= "0;{$rows[$i]->select_Field};{$rows[$i]->id}\n"; } // Populate the select list field ff_setSelectList('selectFormField', $listdata); // start setSelectList function ff_setSelectList($name, $value) { global $ff_processor; for ($r = 0; $r < $ff_processor->rowcount; $r++) { $row =& $ff_processor->rows[$r]; if ($row->name==$name) $row->data2 = $value; unset($row); } // for } // ff_setSelectList For this to work for you, you will need to change your_table to the actual name of your table. You will need to change id, user_id, select_Field and the Fld_xx fields for the actual column names in your table. It is important to retrieve only those fields you need for the select list and the other form fields you wish to populate (I’m populating 12 form fields in this example as represented by the table column names Fld_01 through Fld_12). You will also need to change selectFormField for the name of your select field in your form. You can write one of these code snippets for every table that populates a select list on your form. Step 2 Step 2 is to write the PHP code that will create a JS script to retrieve and store your data [storeData() function] and call a function to populate your form fields [populateFields() function]. This code can immediately follow the setSelectList function described in Step 1. // start of JS script print(""); // end of JS script In this code snippet, you need to change the selectFieldName for the name you use on your form. You will also need to substitute the Fld_xx fields for your table column names. You can make one of these functions for each table that will supply data for your form. Step3 You’ll need a function to populate the form fields. It is called from the storeData() function. Put this function in the Advanced/More options/Scripts/Initialization Script text area of the form. Click on Create code framework (yes, it’s a button) and add your code after the generated function. function ff_yourForm_init() // set up by the generate code button { } // ff_yourForm_init function populateFields(f01, f02, f03, f04, f05, f06, f07, f08, fo9, f10, f11, f12) { ff_getElementByName('formField_01').value = f01; ff_getElementByName('formField_02').value = f02; ff_getElementByName('formField_03').value = f03; ff_getElementByName('formField_04').value = f04; ff_getElementByName('formField_05').value = f05; ff_getElementByName('formField_06').value = f06; ff_getElementByName('formField_07').value = f07; ff_getElementByName('formField_08').value = f08; ff_getElementByName('formField_09').value = f09; ff_getElementByName('formField_10').value = f10; ff_getElementByName('formField_09').value = f11; ff_getElementByName('formField_10').value = f12; } This bit of Javascript simply takes the data and moves it to the form fields. You will need to change the formField_xx names with the actual field names on your form. Step 4 Finally, this code snippet goes in the Advanced/Actionscript text are of the select list field for Blur and Change (select the Custom radio button and click on “Create code framework” - which is actually a button). Its only purpose is to call the StoreData() function which was defined in the Before Form piece (with PHP). That function will retrieve the right row of data based on the selectedIndex of the selectFieldName field. function ff_selectFieldName_action(element, action) { switch (action) { case 'blur': case 'change': StoreData(); break; default:; } // switch } // ff_selectFieldName_action In my limited experience with Breezing Forms, I have learned to keep these scripts short (they are easier to read in the Manage Forms window). The only change to this code snippet is to replace selectFieldName with the name of the select list field in your form (which will happen automatically when you click on Create code framework). You will need to do this with every select list field that supplies data to populate any of your other form fields. That’s it for the Simple Version. AJAX Version In those instances where the amount of data held for the form is too large (large row size, many rows or just several select lists, each adding to the total), you may need to resort to this method. It is not the best approach, by any means, and I’m sure some very clever coders out there can find better, more secure ways. Nonetheless, it will work and that is what your client is paying you for. Step 1 This step is very similar to the Simple Version except you only need to return the field you’re matching to (or retrieve the whole table), the description part of the select list (what a user will select, select_Field in the example below) and the primary key of the table (usually an integer column named id but it could be anything). This code snippet goes in the Advanced/More options/Form pieces/Before Form text area of your form. In this example, I am searching for rows in a table that were input by a specific user id. this->execPieceByName('ff_InitLib'); // get the ff_ library, only needed once $user = &JFactory::getUser(); // get the user via JFactory $userid = $user->id; // you may need to use a different value for this or none // Lookup the rows in the table $rows = ff_select("SELECT id, user_id, select_Field FROM your_table WHERE user_id = '$userid' ORDER BY select_Field"); $listdata = "0;Please make a selection;0\n"; for ($i = 0; $i < count($rows); $i++) { $listdata .= "0;{$rows[$i]->select_Field};{$rows[$i]->id}\n"; } // Populate the select list field ff_setSelectList('selectFormField', $listdata); // start setSelectList function ff_setSelectList($name, $value) { global $ff_processor; for ($r = 0; $r < $ff_processor->rowcount; $r++) { $row =& $ff_processor->rows[$r]; if ($row->name==$name) $row->data2 = $value; unset($row); } // for } // ff_setSelectList For this to work for you, you will need to change your_table to the actual name of your table. You will need to change id, user_id and select_Field for the actual column names in your table. You will also need to change selectFormField for the name of your select field in your form. You can write one of these code snippets for every table that populates a select list on your form. Step 2 This code snippet goes in the Advanced/More options/Scripts/Initialization Script text area of the select list field for Blur and Change (select the Custom radio button and click on “Create code framework” - which is actually a button) and insert the appropriate code (see example below). function ff_selectFormField_action(element, action) { switch (action) { case ‘blur’: case 'change': rec_id = ff_getElementByName('selectFormField').value; getAJAXrec(rec_id); break; default:; } // switch } // ff_selectFormField_action In my limited experience with Breezing Forms, I have learned to keep these scripts short (they are easier to read in the Manage Forms window). All this function does is retrieve the rec_id and call another function to retrieve the row from the table (using MySQL and AJAX). You will need to change the selectFormField to your actual select list form field name (which will happen automatically when you click on Create code framework). Step 3 Create the populateFields function. This is very similar to the function in Step 3 of the Simple Version except you need to use JSON coding to retrieve the table column values (e.g. fdata.f01). It goes in the Advanced/More options/Scripts/Initialization Script text area of the form. Click on Create code framework (yes, it’s a button) and add your code after the generated function. function ff_yourForm_init() // set up by the generate code button { } // ff_yourForm_init function populateFields(fdata) { ff_getElementByName('formField_01').value = fdata.f01; ff_getElementByName('formField_02').value = fdata.f02; ff_getElementByName('formField_03').value = fdata.f03; ff_getElementByName('formField_04').value = fdata.f04; ff_getElementByName('formField_05').value = fdata.f05; ff_getElementByName('formField_06').value = fdata.f06; ff_getElementByName('formField_07').value = fdata.f07; ff_getElementByName('formField_08').value = fdata.f08; ff_getElementByName('formField_09').value = fdata.f09; ff_getElementByName('formField_10').value = fdata.f10; ff_getElementByName('formField_09').value = fdata.f11; ff_getElementByName('formField_10').value = fdata.f12; } This bit of Javascript simply takes the data and moves it to the form fields. You will need to change the formField_xx names with the actual field names on your form. Step 4 Create the AJAX function. This function will use AJAX to send the rec_id to a standalone PHP process. That process (see Step 5 below) will return the row of data requested. This is a straightforward function. It goes in the Advanced/More options/Scripts/Initialization Script text area of the form under the populateFields() function. function getAJAXrec(rec_id) { // start fdata = ''; // clear the data record $.ajaxSetup({async:false}); // set up for a synchronous exchange $.post("customphp/fdata.php",{"rec_id":rec_id}, // post rec_id from the action script function(fdata){ populateFields(fdata); // call the function to populate the form fields }, "json" // use JavaScript Object Notation (JSON) to return the data ); return fdata; // receive the returned record } // end getAJAXrec In this example, I have used $ as a substitute for JQuery (i.e. $.post and JQuery.post are equivalent). Either notation should work. You will need to point to your PHP routine by changing customphp/fdata.php. Step 5 In a separate file, write PHP script to retrieve the table row and return it to the getAJAXrec() function for further action. I chose to include the JConfig object to assist in the MySQL coding. You could include the JFactory::getBDO object if you chose but JConfig provides everything needed. Put the file somewhere in your environment (you may be able to do an upload into a folder or you may need to FTP the file, depending on your hosting environment). In this example, I have chosen to put my PHP code in a folder called “customphp” but you can put the file right in your root folder if you wish. <dbprefix . $table; $host = $jconfig->host; $dbuser = $jconfig->user; $dbpswd = $jconfig->password; // Somehow this value is hidden in the JConfig object... $dbname = $jconfig->db; $link = mysql_connect($host, $dbuser, $dbpswd); // host/username/password if(mysql_select_db('db_name')) // database name { $idstr = mysql_real_escape_string($_POST["rec_id"]); // remove special characters as a security measure $qry = "SELECT id, user_id, select_Field, Fld_01, Fld_02, Fld_03, Fld_04, Fld_05, Fld_06, Fld_07, Fld_08, Fld_09, Fld_10, Fld_11, Fld_12 FROM $tab_name WHERE id = $idstr"; $result = mysql_query($qry, $link); // grab the row if($result && mysql_num_rows($result) > 0) // if a row is found { $row = mysql_fetch_row($result); $obj = array( // set up JSON object "id" => $row[0], "user_id" => $row[1], "selectField" => $row[2], "f01" => $row[3], "f02" => $row[4], "f03" => $row[5], "f04" => $row[6], "f05" => $row[7], "f06" => $row[8], "f07" => $row[9], "f08" => $row[10], "f09" => $row[11], "f10" => $row[12], "f11" => $row[13], "f12" => $row[14] ); print(json_encode($obj)); // return the row } // endif else { print("No rows."); } // end else - no row found which should never happen } // endif else { print("DB Error"); } // end else - no connection made mysql_close($link); ?> You will need to change the your_table name and the various table column names with your information. A Note on Security and Error Handling By including the JConfig object in your PHP script, you don’t have to hard code important data directly into the code, things like the database user password, for example. Not a perfect security solution but it helps. The line of code - $idstr = mysql_real_escape_string($_POST["rec_id"]); …is intended to strip special characters from the rec_id field in case someone tries to sneak a “DROP db_name;” command in on you. Also, if you do use an Integer as the primary key of your table, you can add a test to make certain a number is being passed to you and reject any other values. You may even want to add a results_field to the end of your data object with a value you can test to make sure everything ran correctly and if not, you can trigger an appropriate error. The important thing to remember is that the select list was based on the contents of this table, so the likelihood of not finding a row is low. Nonetheless, there are circumstances which can cause this to happen so be ready for it. Final Thoughts These 2 methods can be used to populate form fields from a select list. The Simple Version stores your table until a selection is made and the AJAX Version retrieves the row you want only once a selection is made. Both approaches work. Neither approach is ideal. I am certain there are other and perhaps better ways of accomplishing this. Perhaps someone (a real coder) can explain how this is done. There is any number of possibilities available with these methods. Cascading select lists (i.e. you make a selection in the first field and it populates the contents of another select list field accordingly) become possible. I even suspect one could develop code that allows a user to create, read, update and delete (CRUD) rows in tables without using a Submit button (although, if you haven’t looked into what Content Builder can do for this, you should). I am certain the creative folks out there can come up with many other uses. DISCLAIMER I would be remiss by not saying that you use the methods described herein at your own risk and that I take no responsibility for your results should you try them. Consider the descriptions and code snippets as guidelines (especially as I had to change my actual code to generalise the examples which may have inadvertently introduced syntax or other errors). If you are unfamiliar with the coding techniques described above, it is in your best interest to seek assistance from someone better qualified. Good luck and I hope you find this article useful (especially for us “amateurs”)…