ClockInfo.com

The Scriptaculous InPlaceEditor and Autocompleter are very useful, and I needed a combination of them: when you click on a field to edit it, the Autocompleter comes up with a list of options, then after you choose an option and click “ok”, the ID of the selected element is sent to the server and the new text is sent back for display.

Here is the code used in my view:

// data to be displayed for editing
$fk_data = $input_data[$ForeignModelName][$foreign_column_name];

$editor_data =
“<td $display_style>\n
<div id=\”{$ModelName}_{$ForeignModelName}_
{$foreign_column_name}_{$rowId}\”>$fk_data</div>\n
<div id=\”{$ModelName}_{$ForeignModelName}_
{$foreign_column_name}_{$rowId}_ac_choices\”
class=\”autocomplete\”
style=\”opacity: 1; display: none;\”>
</div>
<script type=\”text/javascript\”>
var editor=new Ajax.InPlaceEditor(
‘{$ModelName}_{$ForeignModelName}_
{$foreign_column_name}_{$rowId}’,
‘/devadmin/{$controllerName}/editInPlaceDropDown/{$ForeignModelName}/
{$foreign_column_name}/{$ModelName}/{$fieldName}/{$rowId}’,
{cols: $size,
callback:function(form,value){
var request=\”value=\”+id;
return request;
}
}
);
Object.extend(editor, {
_createEditField: editor.createEditField,
createEditField: function() {
this._createEditField();
new Ajax.Autocompleter(
this.editField,
‘{$ModelName}_{$ForeignModelName}_
{$foreign_column_name}_{$rowId}_ac_choices’,
‘/devadmin/{$controllerName}/autocomplete/{$ForeignModelName}/
{$foreign_column_name}’,
{paramName: ‘q’ ,minChars: 2, frequency: 0.2,
afterUpdateElement: function(txt, li)
{
id = li.id.replace(’auto_’,”);
}
});}
});
</script>
</td>\n”;

$output .= $editor_data;

Here is the function editInPlaceDropDown, which is located in appController.php. It receives the ID of the row being edited as part of the URL, and receives the new value as the request variable ‘value’.

function editInPlaceDropDown($ForeignModelName, $foreign_column_name, $ModelName, $col_name, $rowId)
{

$ForeignModelName = text_input_filter($ForeignModelName);
$foreign_column_name = text_input_filter($foreign_column_name);
$ModelName = text_input_filter($ModelName);
$column_name = text_input_filter($col_name);
$rowId = integer_input_filter($rowId);
$value = integer_input_filter($_REQUEST[’value’]);

$this->layout = ‘ajax’;

if (!$this->RequestHandler->isAjax()) {
return true;
}

$this->$ModelName->id = $rowId;

$this->$ModelName->saveField($column_name, $value, true);

$new_value = $this->$ModelName->find(”$rowId = $ModelName.id”);

$new_value = $new_value[$ForeignModelName][$foreign_column_name];

$this->set(’new_value’, $new_value);
$this->render(’../common/ajax_update_field’);
}

Here is the autocomplete function, located in appController.php. It receives parameter “q” and returns the selection list, in the format documented in the comment.

function autocomplete($ForeignModelName, $foreign_column_name)
{

$ForeignModelName = text_input_filter($ForeignModelName);
$foreign_column_name = text_input_filter($foreign_column_name);

$this->layout = ‘ajax’;

if (!$this->RequestHandler->isAjax()) {
return true;
}

$text = text_input_filter($_REQUEST[’q']);

$text = strtolower($text);
$data = $this->$ForeignModelName->findall(”lower($foreign_column_name) LIKE ‘%$text%’”, ”, “$ForeignModelName.$foreign_column_name ASC”);

$new_value = “<ul>\n”;
foreach ($data as $row=>$value) {
$new_value .= “<li id=\”auto_{$value[$ForeignModelName][’id’]}\”>
{$value[$ForeignModelName][$foreign_column_name]}</li>\n”;
}
$new_value .= “</ul>\n”;

/*Sample output:
“<ul>
<li id=\”auto_12\”>Hi Bill</li>
<li id=\”auto_3\”>Hi Hairy!</li>
<li id=\”auto_5\”>Hi Holly</li>
<li id=\”auto_14\”>Hi Lovey</li>
</ul>”; */

$this->set(’new_value’, $new_value);
$this->render(’../common/ajax_update_field’);
}

The first <div> displays the text to be edited. The second <div> is used to display the drop down option list (by autocompleter). It has an initial style to not be visible.

The basic operation is this: Moving the cursor over the text field with the ajax InPlaceEditor causes it to be highlighted in yellow. Clicking on it changes it to an input form and activates the ajax autocomplete, via the object.extend, which causes inPlaceEditor’s internal function createEditField to be overridden by the one we define here (thanks to reference 1 for this approach!). The autocompleter sends parameter q, containing the text that has been typed into the input field, to the autocomplete function on the server; which returns the option choices in the following format:

<ul>
<li id=\”auto_12\”>Hi Bill</li>
<li id=\”auto_3\”>Hi Hairy!</li>
<li id=\”auto_5\”>Hi Holly</li>
<li id=\”auto_14\”>Hi Lovey</li>
</ul>

The afterUpdateElement function called by autoComplete gets the ID of the selected <li>. When “ok” is clicked, the callback function in inPlaceEditor sends a parameter in the form of value=ID to the editInPlaceDropDown function on the server, which saves the field in the database, then reads the new text value and returns it to the inPlaceEditor for display.

Many thanks to the following sources for their help:

1. inplaceeditor + autocompleter on Ruby on Rails: Spinoffs
2. Ajax Autocomplete With Scriptaculous by MetaPundit
3. Extending Prototype.js by Elf M. Sternberg
4. Ajax.In Place Editor on Scriptaculous Wiki
5. Ajax.Autocompleter on Scriptaculous Wiki
6. Ajax.In Place Collection Editor on Scriptaculous Wiki

I’m making a clock model database. Naturally enough, I named one of my tables “models”. Using CakePHP, I could enter data into the table, but neither data validation nor table associations would work. I renamed the table to “item” and everything is fine. Likewise, I suppose, a table should not be named “views” or “controllers”. I wonder what other names will not work.

If you want multiple controllers to be able to share a view, do this:

1. Put the shared views in the views/common directory
2. Call a shared view like this: $this->render(’../common/view_name’);

I am using Cakephp 1.1.11.4064 and a Postgresql database. I was having trouble in my application where the value returned from getNumRows() was incorrect in certain places.

The problem turned out to be Cake’s query caching. Example code

$sql_1 = “sql statement 1;”;
$parent_result_1 = $this->query($sql_1);
.
.
.
Lots of code here
.
.

$sql_2 = “sql statement 2;”;
$parent_result_2 = $this->query($sql_2);
$num_rows_2 = $this->getNumRows();

$sql_1 = “sql statement 1;”;
$parent_result_1 = $this->query($sql_1);
$num_rows_1 = $this->getNumRows();

During the execution of the application, the query for $sql_1 is put in the cache. Later on, the query for $sql_2 executes. Then when the query for $sql_1 in encountered again, the query is not executed, but the results are pulled from the cache. Unfortunately, however, the getNumRows function returns the number of rows from the previous database query, which happens to be the query for $sql_2. Thus, $num_rows_1 ends up being the same as $num_rows_2, which breaks my application.

The solution I found is to disable query caching for any query that will be followed by a getNumRows command, as follows:

$sql_1 = ” sql statement 1;”;
$parent_result_1 = $this->query($sql_1, ‘false’);
$num_rows_1 = $this->getNumRows();