Robert
|
| Posted: 09/13/2002, 1:58 PM |
|
- cc - php4 Template - mysql -
please excuse my terminology! (very new at this).
I have what I call an:
add-a-new-record form page, with this on the html template:
<select size="10" multiple name="robsexample_id[]">
I select 1 or a few from this list box (above), and then post this form to add a new record to the mysql db. I look at the newly created record in the mysql db and it shows the new record entry as "Array", instead of some "robsexample_id" number(s).
I'd like for that form to add a new record to the mysql db table record cell that will actually show all my selcted "robsexample_id" numbers; (ie: actually show: '1','2','3' in the table record cell - as if I submitted robsexample_id's: 1,2 and 3).
Also: Is the column Type "varchar(30)" ok for this particular record?
Also: I'd like to retrieve this data to show on a "search and grid" after I figure out this part!
I looked through a zillion threads...and I couldn't get enough from them. Any fresh info I'd GREATLY appreciate!
Rob
|
|
|
 |
Don Anderson
|
| Posted: 09/15/2002, 9:28 AM |
|
Hi There Rob,
I posted the following article to the codechargestudio.discussion newsgroup on news.codecharge.com on 15/07/02. You may find it helpful.
Cheers,
Don
Greetings everyone.
I write to share my experience getting multiple selection listbox updates
working in CCS. I had some guidance from CodeCharge Support for which I'm
thankful, but much of the solution is my own and edits files that Yes
Software probably wouldn't recommend.  PROCEED AT YOUR OWN RISK AND
ALWAYS MAKE BACKUP COPIES OF MODIFIED FILES.
My situaton called for the webuser to be able to make multiple selections in
a listbox and have these stored in a lookup table. When the update form is
shown options previously selected need to be taken from the lookup table and
the appear selected in the listbox.
Issues:
1. Listbox variable array.
2. Invalid variable type.
3. Fetching data from the lookup table.
4. Building the Listbox
5. Saving multiple options.
1. Listbox variable type.
=================
Unless you specify the Listbox as an array, eg. <select name="ListBox[]"
multiple size=8> each selected option will write over the previous one.
Accordingly, you have to inclde the square brackets at the end of the
listbox name [] so that all options will be preserved by the form.
Unfortunately, CCS doesn't like this, cannot find the original listbox and
keeps offering to delete it. You must always tell CCS no. For this reason,
I left the array[] specification to the very last step. Actually, I did
this step outside of CCS after the code had been generated. You edit the
html template file.
2. Invalid variable type.
================
The next problems that one encounters is that CCS automatically validates
variables and crunches arrays.
The first change requitred is in Classes.php around line 400. I modified
function/method clsControl in Class clsControl as follows.
###### Beginning of clsControl Function
function clsControl($ControlType, $Name, $Caption, $DataType, $Format,
$InitValue)
{
$this->Value = "";
$this->Text = "";
$this->Page = "";
$this->Parameters = "";
$this->CCSEvents = "";
$this->Values = "";
$this->Required = false;
$this->HTML = false;
$this->Errors = new clsErrors;
$this->Name = $Name;
$this->ControlType = $ControlType;
$this->DataType = $DataType;
$this->Format = $Format;
$this->Caption = $Caption;
// ## Begin Modification
// ## The orginal version lines below kills any $InitValue array (multiple)
that makes it this far
// ## This modification allows through arrays associated with ListBoxes
if (is_array($InitValue) && $this->ControlType == "ccsListBox") {
while (list ($key, $Value) = each ($InitValue)) {
if(strlen($aValue)) {
$this->Text = $aValue;
$this->Value[] = $this->GetParsedValue();
}
}
}elseif(strlen($InitValue)) {
$this->SetText($InitValue);
}
// ## End Modification
}
####### End of clsControl Function
The second issue is that variables are validated according to type before
processing them for a database insert or update. Unfortunately, there is
no "array" type. Accordingly, your Listbox array variable does not pass
validation. I spent time looking through the validation code and attempted
several mods, but finally decided the easiest solution was to comment out
the appropriate line in the "Validate Method" in the php file for the
multiple Listbox/s. See the "Code view" on your page, eg.
// $Validation = ($this->ListBox1->Validate() && $Validation);
This is not a great solution, but it works.
** Beware that once you modify a generated function/method by hand CCS
won't modify that function/method again, so it is advisable to make this
change after you have added everything to your page. **
** Might I suggest that Yes Software introduce an Integer Array variable
type with approriate looped validation. Most multiselect Listboxes should
be able to work with an integer type bound column. This would prevent the
need to manually bypass the validation functions.
3. Fetching data from the lookup table.
===========================
One has to query the appropriate lookup table to get any options selected.
This could be achieved by inserting a before show event or by including the
necessary code in the "Control Source" area of the Listbox Data Properties
tab.
I prefered the later optioin and decided to modify Common.php by adding the
following function to simplify the "Control Source" code:
//CCGetSelectList
function CCGetSelectList($db, $sql, $bound_column = "")
{
$values = "";
if(!strlen($bound_column))
$bound_column = 0;
$db->query($sql);
if ($db->next_record())
{
do
{
$values[] = $db->f($bound_column);
} while ($db->next_record());
}
return $values;
}
//End CCGetSelectList
USAGE for function CCGetSelectList is as follows:
CCGetSelectList($db, $sql, $bound_column)
where:
$db is the connection name you want to use,
$sql is the sql statement (including where
statement) to use
$bound_column is the name of the column containing the values to use
from the result set.
Here is an example of usage in the "Control Source" area for a ListBox Data
properties: <> values indicate substitutions.
CCGetSelectList($this->ListBox1_ds, "SELECT <bound_column_id> as
<bound_column> from <table_name> WHERE <other_lookup_table_column> = ".
CCGetParam('<lookup_value>',"0"), <bound_column>)
4. Building the Listbox
================
By default CCS inserts a "SELECT VALUE" selected option at the beginning of
every select listbox. This works with a non-multiple listbox because a
selected value overrides the initial default "SELECT VALUE" option. It does
not work for a multiple select ListBox.
I decided to add code to build ListBoxes dynamically. Again we are
modifying Classes.php.
In the original unmodified the Show function/method starts at line 493 and
the "case ccsListBox" code starts at line 553.
Here is what I did to the case ccsListBox code in the Show function/method
of Classes.php. It is not the flashest code but it worked for me.
case ccsListBox:
// BEGIN MOD
$Options = "";
## NOTE Your can change the default no selection message in the next line
## And if you set the value for List_Message earlier it will be used here.
if (!strlen($this->List_Message)) $this->List_Message = "No Selection";
$sel_list = "";
$unsel_list = "";
if (!is_array($this->Value)) $this->Value =
preg_split('/,|(%2C)/',$this->Value);
if(is_array($this->Values))
{
for($i = 0; $i < sizeof($this->Values); $i++)
{
$Value = $this->Values[$i][0];
$Text = $this->Values[$i][1];
if(in_array($Value,$this->Value)) {
$sel_list .= '<option value="' .$Value. '"
selected>' . $Text .'</option>\n';
} else {
$unsel_list .= '<option value="' .$Value. '">' .
$Text .'</option>\n';
}
}
}
if (!strlen($sel_list)) {
$sel_list .= '<option value = "">' .$this->List_Message.
'</option>\n';
}
$Options .= $sel_list .= $unsel_list;
$Tpl->SetVar($this->Name . "_Options", $Options);
break;
}
// END MOD
}
// END OF SHOW FUNCTION
Using this code one should edit the html view of the CCS generated listbox
code to remove the "SELECT VALUE" line for your ListBox control.
Your multiple ListBoxes should now be dynamically generated with all
selected options appearing at the beginning of the selection list.
5. Saving multiple options.
==================
This part is the most manual. It involves writing custom
AfterExecuteDelete, AfterExecuteInsert & AfterExecuteUpdate statements.
A Delete statement is straightforward. An Update statement is a Delete then
an Insert statement using the index value being updated.
Where you just do a custom Insert you have to first retrieve the new index
value that was created when the main CCS form did its insert.
Two things I found useful:
Calling $HTTP_POST_VARS
--------------------------------
I avoided array variables being mangled or rejected the validation code by
calling the selected options directly from $HTTP_POST_VARS,
Remember to insert the line:
global $HTTP_POST_VARS;
then you can fetch the array with code like this example:
$valuelist = $HTTP_POST_VARS[$form_field];
Obtaining the Last_ID value for subsequent INSERT.
-----------------------------------------------------
To get the most recent index value with MySQL I used the following code:
$db = $project->ds;
$thekey = CCGetDBValue("SELECT MAX(project_id) FROM ri_projects", $db);
I know this could fail under very heavy load with simultaneous inserts,
however, this should not be a problem with my application and I couldn't
figure out how to get mysql_insert_id() working.
UPDATE QUERY
--------------------
For the Upate insert I wrote a code that could be called as a function but I
settled on inserting it as necessary.
I set up an array that contained the parameters required for doing updates
for each control, then cycled through each.
global $DBconnection_name;
global $Form_name;
global $HTTP_POST_VARS;
/* $these = array(
array(
$lookup_table, // name of table being updated
$form_field // The form element name
$update_field, // name of field being updated with
distinct values
$thekey_field // the where field, probably similar to
*form* name
)
)
*/
// E.G.
$these = array(
array( ri_partners,
partners,
org_id,
project_id
),
array( ri_funders,
funders,
org_id,
project_id
)
);
$db = <Data_Connection_To_Use_ds>;
for($ix = 0; $ix < sizeof($these); $ix++)
{
list($lookup_table, $form_field, $update_field, $thekey_field) =
$these[$ix];
$thekey = "";
$valuelist = "";
$thekey = CCGetParam($thekey_field,"");
$valuelist = $HTTP_POST_VARS[$form_field];
if (!is_array($valuelist)) $valuelist =
preg_split('/,|(%2C)/',$valuelist);
for($i = 0; $i < sizeof($valuelist); $i++)
{
if ($valuelist[$i]) {
$sql = "";
$sql = "INSERT INTO ". $lookup_table ." ("
. $thekey_field .", "
. $update_field
. ") VALUES ("
. $thekey . ", "
. $valuelist[$i]
. ")";
$db->query($sql);
}
}
}
I trust this report will be helpful to somebody. I realise that it does not
cover the issue of creating a multiple select ListBox for a search form. The
issues are however very similar. You need to collect the variables and then
create a custom BeforeBuildSelect event to modify the WHERE code.
Regards,
Don Anderson
|
|
|
|
Pulp
|
| Posted: 12/19/2003, 8:00 AM |
|
(For PHP)
A simple way to access multiple listbox values in a custom code for example : fetch them in an array.
Do not use :
global $my_record;
$my_values = $my_record->my_listbox->GetValue();
... or you will get only the first value !
Instead, use :
global $my_record;
$my_values = $my_record->my_listbox->value;
You can then use the $my_values values.
Pulp
|
|
|
|
|
