2. PHPRunner General questions
  3. [SOLVED]  Updating multiple selected records based on a combobox v
This topic is locked
[SOLVED]

 Updating multiple selected records based on a combobox v

 		4/1/2010 3:31:24 PM
PHPRunner General questions
F
 FunkDaddy author

I've looked through the posts (and online manual) and found lots of good help on updating multiple records, however, I still have not been able to figure out how to do the following:
Update multiple records selected on a list page, where a field in the selected records (in this case the field called "school_grade") gets updated to a combobox (also called "school_grade") shown on the list page (which I would have to add near the "update selected" button).
Basically, want to select a value in combo box then select records on that same page to be updated to the same value in the combo box. I found this posting: http://www.asprunner.com/forums/topic/13704-bulk-edit-and-update/pagep47420hlupdate%20selectedfromsearch1&#entry47420 which gives me insight into using a javascript box to prompt for a value, however, I need it to be a combo box to make it more intuitive for my users to enter the right value (since they don't usually see the index ID of the combo box value and wouldn't know what correct value to enter in the javascript box example shown in the forum solution).
Again, thanks in advance for your help!
Best,
M
J
 		Jane 4/2/2010

Marcelo,
you can create dropdown in the custom event.

Here is just a sample:

http://xlinesoft.com/phprunner/docs/add_dropdown_list_box_with_values_for_search.htm
Then pass value selected in this dropdown in your 'update' button:

<INPUT class=button onclick="var form = $('#frmAdmin1')[0]; form.a.value=document.getElementById("dropdown1").value; form.submit(); return false;" value="Update selected" type=button>
F
 		FunkDaddy author 4/2/2010

Jane,
I tried but it didn't work. I believe the problem is two fold:

  1. The code button you gave me in the previous post captures the value of the combo box, however it fails to trigger the code provided in the online manual which is as follows:

global $dal;
//delete records
if(@$_POST["a"]=="delete")
return true;
//update records
if(@$_POST["a"]=="update")
{
// set ReportsTo field to 'Bob Smith'
$sql = "Update employees set ReportsTo='Bob Smith' where " . $where;
CustomQuery($sql);
}
return false;


Notice the action will only take place if the $_POST["a"] is equal to "update". Since we are grabbing the values from the combobox which was created with this code:

<INPUT class=button onclick="var form = $('#frmAdmin1')[0]; form.a.value=document.getElementById("dropdown1").value; form.submit(); return false;" value="Update selected" type=button>

it will turn the $_Post["a"] value into whatever I selected in my combobox. The problem is I can't predict what that value will be since the user can choose from a variety of values.
2. The second issue that is part of this problem is that the code show in the online manual for updating multiple records on a list page doesn't allow for dynamic update. Notice the line

$sql = "Update employees set ReportsTo='Bob Smith' where " . $where;

offers a hard coded value for the SQL SET action (which in this case is ReportsTo = 'Bob Smith'.
What I am trying to figure out is how to add a combobox control that is dynamically populated like a lookup field (which I have done by slightly modifying the following code - modified it so that it doesn't react to the onchange action):

//create dropdown box
$str = "";
$str.= "<select onchange=\"window.location.href=this.options[this.selectedIndex].
value;\"><option value=\"\">Please select</option>";
//select values from database
global $conn;
$strSQL = "select company from tablename";
$rs = db_query($strSQL,$conn);
while ($data = db_fetch_array($rs))
$str.="<option value=\"tablename_list.php?ctlSearchFor=".$data["company"].
"&srchOptShowStatus=1&ctrlTypeComboStatus=0&srchWinShowStatus=0&a=integrated&id=1&criteria=and&type1=&value11=".
$data["company"]."&field1=company&option1=Contains&not1=a=search&value=1\">".$data["company"]."</option>";
$str.="</select>";
echo $str;


Then the combobox would serve as the value to be updated once I click the "update selected" button.
I apologize if I'm making this sound more complicated than it really is... it should be really simple. So let me summarize in as few words to make it clear for everyone to understand.
I want to:

  1. create a combobox on the list page that acts like a lookup field (able to filter using a WHERE condition as well)
  2. select a value in that combobox
  3. select multiple records on the list page.
  4. press "update selected" button which will then trigger an update to selected records where it updates them with the value selected in that combobox.
    I hope I've explained it well enough. Thanks in advance for anyone who can help me with this.
    Cheers,
    M
F
 		FunkDaddy author 4/5/2010

Just an update on this topic and issue.. the code Jane provided was great, but it took me a while to figure out the part where it says form.a.value=document.getElementById('dropdown1').value should be written with single quotes and not double quotations. Additionally, if you want to use the actual selected value of the index box (dropdown1) it should read form.a.value=document.getElementById('dropdown1').selectedIndex Otherwise, the form will $_POST a value of the entire dropdownbox1 HTML stirng... causing major errors. Just an update to help others out there! :-)
Then pass value selected in this dropdown in your 'update' button:

<INPUT class=button onclick="var form = $('#frmAdmin1')[0]; form.a.value=document.getElementById('dropdown1').selectedIndex; form.submit(); return false;" value="Update selected" type=button>



[/quote]
F
 		FunkDaddy author 4/6/2010

OK. Going a little nuts NOW!
In my above post about an hour ago I thought I had figured out how to get the correct value from a dropdownbox to be used in an update query once it was posted (activated via the "update seelected" button... I WAS WRONG. It turns out I was only getting the selectedIndex value of the dropdown which is quite different that the actual value!!! Coincidentally, my indexes happened to match my key column values for the dropdown control... thus I thought I had figured out my problem.
Does anyone know how to pass the value of the selected item in the dropdown control box so I can use it in an "update selected" action??? All I want to do is update mulile records (EX: assign a school teacher to multiple students) by selecting those students on the list page then, choosing the school teacher from dropdown box, and clicking on "update selected" button to update those records with the selected school teacher!
Should be very simple to do, but the code Jane provided does not pass the value... rather it send me the value of the HTML code used to search the values in the dropdownbox... which is unusable in an udpate query (which is what actually makes the update take place).
Someone please help!!!
Thanks
M
D
 		Dale 4/6/2010

Add the .value to your selected index.

you had form.a.value=document.getElementById('dropdown1').selectedIndex
try form.a.value=document.getElementById('dropdown1').selectedIndex.value;
Hope that works for you.
F
 		FunkDaddy author 4/6/2010

Thanks DAleM...
I actuallyhad tried that already and just tried it again... I get an error "Unknown column 'undefined' in 'field list'"
Using Firefox Firebug, i can see that the POST value for $_POST["a"] is being sent to the server as "undefined".
Thanks for trying to help though.
D
 		Dale 4/6/2010

Try this one, sorry but I havent tested it. Ive done something like this in a different project.
document.getElementById('dropdown1').options[document.getElementById('dropdown1').selectedIndex].text
F
 		FunkDaddy author 4/6/2010

The code you just gave me got me closer than ever before... the problem is that I need the actual key value being stored and not the text displayed. For example, I have a dropdown as a follows (which is dynamically generated using the code found here: http://xlinesoft.com/phprunner/docs/add_dropdown_list_box_with_values_for_search.htm
Index Value DisplayText

0 33 Jack Sparrow

1 37 Mike Murphy

2 15 Ben Jangles

3 22 Susan Blang

etc...
The problem now is that when I access the "value" of my drop down it returns the code shown in the post I just mentioned in this post. What I need is the value. So far I've been successful at getting the Index (selectedIndex) and the DisplayText (text).
DaleM... thanks a million for helpingme out... you have gotten me one step closer! Hopefully you can push me over the finish line.
Best,
M
D
 		Dale 4/6/2010

how about this one. Sorry for all the wacking but as I said I havent got a test bed to proof it.
document.getElementById('dropdown1').options[document.getElementById('dropdown1').selectedIndex].value
F
 		FunkDaddy author 4/6/2010

My bad... I should have mentioned in my last post that was exactly what I tried after I first tested what you suggested. When I do that it does return the value, the problem is that value is unusable since it returns this:
tablename_list.php?ctlSearchFor=".$data["company"]."&srchOptShowStatus=1&ctrlTypeComboStatus=0&srchWinShowStatus=0&a=integrated&id=1&criteria=and&type1=&value11=".$data["company"]."&field1=company&option1=Contains&not1=a=search&value=1\
Yup, believe it or not... this is the actual "value". It maks sense because the dropdown box is using the code shown here: http://xlinesoft.com/phprunner/docs/add_dropdown_list_box_with_values_for_search.htm to create those values.
I think my next best option is to figure out a way to read the value inside the string it is currently interpreting as the value... I basically need to extract the beginning part where it has my data as $data["company"]. I'm going to google for an answer... if you happen to know how to do this, then by all means please share it with me. Unless, of course, you have a better solution.
Thanks again,
M
F
 		FunkDaddy author 4/6/2010

OK. I finally figured out how to do this:
Update multiple records by selecting the records on list page, then a value you wish to update the selected records to by using a custom dropdownbox on list page, then clicking on a "update selected" button to run the update.
1st: read this http://xlinesoft.com/phprunner/docs/update_multiple_records.htm Then change this part of code shown

if(@$_POST["a"]=="update")



to this

if(@$_POST["a"]!="delete")


2nd: read this http://xlinesoft.com/phprunner/docs/add_dropdown_list_box_with_values_for_search.htm
3rd: follow steps in step 1 and replace this

$sql = "Update employees set ReportsTo='Bob Smith' where " . $where;

with this

$sql = "Update employees set ReportsTo=" . @$_POST["a"] . " where " . $where;


4th: follow steps in step 2 and replace this

//create dropdown box
$str = "";
$str.= "<select onchange=\"window.location.href=this.options[this.selectedIndex].
value;\"><option value=\"\">Please select</option>";
//select values from database
global $conn;
$strSQL = "select company from tablename";
$rs = db_query($strSQL,$conn);
while ($data = db_fetch_array($rs))
$str.="<option value=\"tablename_list.php?ctlSearchFor=".$data["company"].
"&srchOptShowStatus=1&ctrlTypeComboStatus=0&srchWinShowStatus=0&a=integrated&id=1&criteria=and&type1=&value11=".
$data["company"]."&field1=company&option1=Contains&not1=a=search&value=1\">".$data["company"]."</option>";
$str.="</select>";
echo $str;


with this



//create dropdown

$str = "";

$str.= "<select id=\"dropdown1\";\"><option value=\"\">Select Option</option>";
//select values from database

global $conn;
$strSQL = "select company from tablename";
$rs = db_query($strSQL,$conn);
while ($data = db_fetch_array($rs))
$str.="<option value =". $data["company "].">".$data["company "]."</option>";
$str.="</select>";
echo $str;


And don't forget to change the HTML code of the "update selected" button on the list page (which triggers the action of to update) to the following:

<INPUT class=button onclick="var form = $('#frmAdmin1')[0]; form.a.value=document.getElementById("dropdown1").value; form.submit(); return false;" value="Update selected" type=button>


You should now be able to update multiple records to whatever value you choose in the dropdown box!!!
Marcelo Ramagem