Update mysql with multiple drop down and checkbox value

Dev Shed Forums Sponsor:

October 22nd, 2012, 07:38 AM

pcraig23

Registered User

Join Date: Aug 2012

Location: Redditch, UK

Posts: 7

Time spent in forums: 2 h 12 m 32 sec
Reputation Power: 0

PHP-General - Update mysql with multiple drop down and checkbox value

Hi all

I'm new to the forum and php,

I'm trying to update a mysql TABLE (A) with data from the output of another table TABLE (B). I'm using a checkbox on each row and a select option (ID) generated from a column from table TABLE (A). when the user selects a row (using the checkbox) or multiple rows and hits the update button, I'd like to have the drop down option (ID) they choose to update the appropriate row in TABLE(A).

I think I've solved most of it but the problem I have is that the select option drop down is on each row and so has the same name, so just sends an array which is fine but I need the individual value for each row associated with each checkbox ID.

Heres my code if you have any ideas. I'd like to apologise if it's a bit rough around the edges.

At the moment I'm not UPDATING the data I'm just trying to display the data to see how it's working.

Heres the code for processing the form:

PHP Code:


		
			
<?php

 

 if ((isset($_POST["MM_update4"])) && ($_POST["MM_update4"] == "updatefactorycheckbox")) {

    

 if(isset($_POST['checkbox'])){$checkbox = $_POST['checkbox'];





$var2 = $_POST['checkbox'];

foreach($var2 as $item){

    



$Query =    "SELECT * FROM sales__factory_quotes WHERE primary_ID='$item'";

mysql_select_db($database_Intranet, $Intranet);

$Result1 = mysql_query($Query, $Intranet) or die(mysql_error());



while ($row_Recordset2=mysql_fetch_assoc($Result1)){ 



    

echo '<table><tr>';



if(isset($_POST['project_id'])){$prod = $_POST['project_id'];



echo '<td><span class="style5">';

echo $prod;

echo '</span></td>';

    

}

echo '<td><span class="style5">';

echo $row_Recordset2['project_name'];

echo '</span></td>';

echo '<td><span class="style5">';

echo $row_Recordset2['customer'];

echo '</span></td></tr></table>';



    

}}

}}



?>

Here's the code for creating the tables and data thats being submitted:

PHP Code:


		
			
<form name="factory" action="index-new-layout-sales_projects.php?action=updatefactorycheckbox"  method="POST">



<div class="DataTitle">Your Current Outstanding Factory Quotes</div>

<div class="DataCheckbox">

    <input class="buttonstyle2" type="button" onClick="SetAllCheckBoxes('factory', 'checkbox[]', true);" value="Select All">&nbsp;&nbsp;

    <input type="button" class="buttonstyle2" onClick="SetAllCheckBoxes('factory', 'checkbox[]', false);" value="Clear All">&nbsp;&nbsp;

    <input class="buttonstyle2" type="submit" name="activate" id="activate" value="Add Record To Project Database" />

    <input type="hidden" name="MM_update4" value="updatefactorycheckbox" /></div> <!--BUTTONS FOR CHECKBOX-->



</div><!--END OF DATATITLE-->



<div style="clear:both;"><!--DIV FOR DISPLAYING RECORDS-->



<?php



    // SELECT DATA TO DISPLAY FROM DATABASE AND CREATE TABLE



mysql_free_result($Recordset1);

mysql_select_db($database_Intranet, $Intranet);



if(isset($_GET['orderby'])){ 

$orderby = $_GET['orderby']; 

$query_Recordset1 = "SELECT * FROM sales__factory_quotes WHERE engineer_name='".$_SESSION['MM_Username']."' ORDER BY ".mysql_real_escape_string($orderby)." ASC"; 

} 

  //default query 

else{ 

$query_Recordset1 = "SELECT * FROM sales__factory_quotes WHERE engineer_name='".$_SESSION['MM_Username']."' ORDER BY primary_ID ASC";

} 









$Recordset1 = mysql_query($query_Recordset1, $Intranet) or die(mysql_error());

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

$totalRows_Recordset1 = mysql_num_rows($Recordset1);







//DISPLAY TABLE TITLES-->

?>

    <table width="" cellpadding="10" cellspacing="0" id="menuTable2">

    <tr class="quotation-heading">

    <td><span class="style4"><a href="<? echo $_SERVER['PHP_SELF']."?orderby=primary_ID";?>">Choose&nbsp;project&nbsp;to&nbsp;update</a></span></td>

    <td><span class="style5"><a href="<? echo $_SERVER['PHP_SELF']."?orderby=engineer_name";?>">Engineer&nbsp;Name</a></span></td>

    <td><span class="style5"><a href="<? echo $_SERVER['PHP_SELF']."?orderby=engineer_code";?>">Engineer&nbsp;Code</a></span></td>

    <td><span class="style5"><a href="<? echo $_SERVER['PHP_SELF']."?orderby=factory";?>">Factory</a></span></td>

    <td><span class="style5"><a href="<? echo $_SERVER['PHP_SELF']."?orderby=group";?>">CEF&nbsp;Group</a></span></td>

    <td><span class="style5"><a href="<? echo $_SERVER['PHP_SELF']."?orderby=branch";?>">CEF&nbsp;Branch</a></span></td>

    <td><span class="style5"><a href="<? echo $_SERVER['PHP_SELF']."?orderby=factory_quote";?>">Factory&nbsp;Quote&nbsp;Ref</a></span></td>

    <td><span class="style5"><a href="<? echo $_SERVER['PHP_SELF']."?orderby=value";?>">Value&nbsp;£</a></span></td>

    <td><span class="style5"><a href="<? echo $_SERVER['PHP_SELF']."?orderby=customer";?>">Customer</a></span></td>

    <td><span class="style5"><a href="<? echo $_SERVER['PHP_SELF']."?orderby=project_name";?>">Project&nbsp;Name</a></span></td>

    <td><span class="style5"><a href="<? echo $_SERVER['PHP_SELF']."?orderby=notes";?>">Notes</a></span></td>

    </tr>

    

   

    

    <?php do { ?>



    <?php

    

//DISPLAY TABLE DATA

 



$row_Recordset1['value'] = number_format($row_Recordset1['value'], 0.00);

                

echo '<tr class="quotation-body"><td>';



$query = "SELECT project_ID FROM sales__projects WHERE engineer_name='".$_SESSION['MM_Username']."' ORDER BY project_ID ASC";

$result = mysql_query($query) or die("Error in in query. ".mysql_error());



?>

<!--OPTION DROP DOWN TO SELECT ID TO UPDATE-->      

<select name = 'project_id[]'><option value = '<?=$option?>'>Select ID/New Project</option> 



<?php



while($reporter = mysql_fetch_object($result))

                    {                     

                    echo "<option value = '".$reporter->project_ID."'>".$reporter->project_ID."</option>";

                    } ?></select>

      </td>  

      

      <td><span class="style5"><?php echo $row_Recordset1['engineer_name']; ?></span></td>

      <td><span class="style5"><?php echo $row_Recordset1['engineer_code']; ?></span></td>

      <td><span class="style5"><?php echo $row_Recordset1['factory']; ?></span></td>

      <td><span class="style5"><?php echo $row_Recordset1['group']; ?></span></td>

      <td><span class="style5"><?php echo $row_Recordset1['branch']; ?></span></td>

      <td><span class="style5"><?php echo $row_Recordset1['factory_quote']; ?></span></td>

      <td><span class="style5"><?php echo $row_Recordset1['value']; ?></span></td>

      <td><span class="style5"><?php echo $row_Recordset1['customer']; ?></span></td>

      <td><span class="style5"><?php echo $row_Recordset1['project_name']; ?></span></td>

      <td><span class="style5"><?php echo $row_Recordset1['notes']; ?></span></td>

      <td>&nbsp;</td>

      <td>&nbsp;</td>

      <td>&nbsp;</td>

      

      <!--EDIT ROW FUNCTION-->

      

      <td class="style5">&nbsp;&nbsp;&nbsp;&nbsp;<img src="b_edit.png" width="16" height="16" title="Edit Project Row" style="cursor:pointer; padding: 2px;" onClick="EditProjectRow(<?php echo $row_Recordset1['primary_ID']; ?>, '<?php echo $row_Recordset1['project_name']; ?>','<?php echo $row_Recordset1['value']; ?>','<?php echo $row_Recordset1['notes']; ?>','<?php echo $row_Recordset1['factory_quote']; ?>');" /></td>

      

      <!--CHECKBOX WITH ID VALUE-->

      

      <td width="150"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<?php echo $row_Recordset1['primary_ID'] ?>" alt="Check to update to the projects database">&nbsp;Update&nbsp;</td>

      </td></tr>

      

<?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>

      </table> 



</form>

October 22nd, 2012, 09:40 AM

badger_fruit

Confused badger

Join Date: Mar 2009

Location: West Yorkshire

Posts: 760

Time spent in forums: 1 Week 4 Days 5 h 15 m 18 sec
Reputation Power: 339

Quote:

Originally Posted by pcraig23

I think I've solved most of it but the problem I have is that the select option drop down is on each row and so has the same name, so just sends an array which is fine but I need the individual value for each row associated with each checkbox ID.

Hi there
welcome to the forums; please forgive me as I'm not sure I understand the problem 100% ... have you tried using the checkbox ID as the select name?

For example in the loop that creates the SELECT / table ...

PHP Code:


		
			
echo "<select name='sel[{$checkbox_id}]'>";

echo "<option value='value1'>1</option>";

echo "<option value='value2'>2</option>";

echo "<option value='value3'>3</option>";

echo "</select>";

* substitute $checkbox_id with the actual variable that holds your checkbox ID and the values in the OPTION entries with your own

Then, when you submit the form, your $_POST['sel'] should have an array like:-

$_POST['sel'][0] = "value1"
$_POST['sel'][1] = "value2"
...
$_POST['sel'][n] = "value3"

(the actual value would be determined by which OPTION the user chose before they submitted the form)

You can use a foreach / for loop to grab the actual values from the $_POST['sel'] array.
Anyway, I might be waaaaaaay off the mark so apologies if I am!

EDIT > OK so I entered the code below into my webserver:-

PHP Code:


		
			
<?php



echo "<form name='multi' method='post'>";



for ($x = 0; $x < 10; $x++) {



echo "<select name='sel[{$checkbox_id}]'>";

echo "<option value='value1'>1</option>";

echo "<option value='value2'>2</option>";

echo "<option value='value3'>3</option>";

echo "</select><br /><br />"; 



}



echo "<input type='submit' name='submit' value='Submit'>";



echo "</form>";



if (isset($_POST['submit'])) {

    echo "<pre>";

    print_r($_POST);

    echo "</pre>";

}



?>

When I make some selections then click on Submit, this is what I get:-

Code:

Array
(
    [sel] => Array
        (
            [0] => value1
            [1] => value2
            [2] => value3
            [3] => value1
            [4] => value1
            [5] => value2
            [6] => value1
            [7] => value1
            [8] => value1
            [9] => value1
        )

    [submit] => Submit
)

Which I think is what you want?

__________________
The number for UK Emergencies is changing, the new number is 0118 999 881 999 119 7253

"For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
- George Orwell, 1984

Last edited by badger_fruit : October 22nd, 2012 at 09:52 AM. Reason: Corrected my poor HTML skillz and wrapped code in PHP tags, oops!

November 1st, 2012, 05:58 AM

pcraig23

Registered User

Join Date: Aug 2012

Location: Redditch, UK

Posts: 7

Time spent in forums: 2 h 12 m 32 sec
Reputation Power: 0

Hi bager_fruit

sorry for the delay

Thanks for the reply I think I understand what you did but I may have it completely wrong, what your code seems to do is to create each row with multiple select options according to how many rows there are in the first place. I tried it without the 'for' code but it still returns the last value. What I want to be able to do is select a value from the option drop down on the left then choose the row with the checkbox and insert all the data from that individual row into a new database table, because all the rows are generated automatically the select option on the left only returns the last value.

I'd like to include a screenshot but I can't include a url as a new user

so it's here: www*tamlite*co*uk/screenshot*png

Thanks for your patience

November 1st, 2012, 06:43 AM

badger_fruit

Confused badger

Join Date: Mar 2009

Location: West Yorkshire

Posts: 760

Time spent in forums: 1 Week 4 Days 5 h 15 m 18 sec
Reputation Power: 339

I don't get it, where is the SELECT box of which you speak? I see two TEXT boxes, one which reads "Select ID/Project" and the other which reads "10036".

Am I correct in thinking that you want a list of all projects to be shown to the user and, as in your screenshot, for each project the user has a pencil icon which, when clicked, will display the (currently hidden) text input boxes. When the user has made changes, they click in the "update" checkbox to indicate that 'I wish to save my changes to this record'.

Finally, the user has two additional options not shown on your screenshot:-
1. Add new project
2. Submit changes

When the user clicks on Save changes, you want to determine which projects have their "update" checkbox selected and only update those?

Am I right so far?

November 1st, 2012, 09:42 AM

pcraig23

Registered User

Join Date: Aug 2012

Location: Redditch, UK

Posts: 7

Time spent in forums: 2 h 12 m 32 sec
Reputation Power: 0

Sorry

the text boxes are drop down those are just two different values I selected.

The 2 rows you see on the screenshot are from table A (user specific) but the drop down on the left is generated from table B (main database).

When the user checks one or more checkboxes and hits update (which u cant see on the screenshot) it updates table B with the content using the ID (value) of the drop down shown.

the pencil was just an edit function for the user to update the row before submit. the code isn't included it works fine.

I hope that helps.

November 1st, 2012, 10:10 AM

badger_fruit

Confused badger

Join Date: Mar 2009

Location: West Yorkshire

Posts: 760

Time spent in forums: 1 Week 4 Days 5 h 15 m 18 sec
Reputation Power: 339

OK so what is in the $_GET or $_POST (depending on your form's METHOD)?
Add the line
print_r($_GET);

or

print_r($_POST);

into your processing page and then copy here the output,
ta

November 2nd, 2012, 03:51 AM

pcraig23

Registered User

Join Date: Aug 2012

Location: Redditch, UK

Posts: 7

Time spent in forums: 2 h 12 m 32 sec
Reputation Power: 0

Quote:

Originally Posted by badger_fruit

OK so what is in the $_GET or $_POST (depending on your form's METHOD)?
Add the line
print_r($_GET);

or

print_r($_POST);

into your processing page and then copy here the output,
ta

ok here's the output when I select both rows I added 10030 as the first option and 10031 as the second, you can see it only picks up the second number, the checkbox returns the id fine.

PHP Code:


		
			
Array ( [activate] => Add Record To Project Database [MM_update4] => updatefactorycheckbox [sel] => Array ( [{$checkbox_id}] => 10031 ) [checkbox] => Array ( [0] => 166 [1] => 167 ) ) 166

here's the amended code for the option drop down

PHP Code:


		
			
<select name = 'sel[{$checkbox_id}]'><option value = '<?=$option?>'>Select ID/New Project</option> 



<?php



while($reporter = mysql_fetch_object($result))

                    {                     

                    echo "<option value = '".$reporter->project_ID."'>".$reporter->project_ID."</option>";

                    } ?></select>

Thanks again

November 2nd, 2012, 06:19 AM

badger_fruit

Confused badger

Join Date: Mar 2009

Location: West Yorkshire

Posts: 760

Time spent in forums: 1 Week 4 Days 5 h 15 m 18 sec
Reputation Power: 339

OK, whenever you need multiple fields with the same name, suffix [] to the end of the name in your HTML code like so ...

echo "<input type='text' name='project_id[]'>";

Then, when you submit the form, $_POST['project_id'] is an array - you can see this if you use the code:-

PHP Code:


		
			
<?php



echo "<form name='test' method='post'>";

  for ($i = 0; $i < 3; $i++) {

    echo "<input type='text' name='project_id[]'> <br />";

  }

echo "<input type='submit' name='submit' value='submit'>";

echo "</form>";



if (isset($_POST['submit'])) {

  echo "<pre>";

  print_r($_POST);

  echo "</pre>";

}

?>

So once you have your arrays (e.g. "Project ID", "Customer", Project Name", "Notes") you can grab the values from them with a foreach loop, something like this ...

PHP Code:


		
			
foreach ($_POST['project_id'] as $key => $value) {

  ...

}

November 5th, 2012, 10:13 AM

pcraig23

Registered User

Join Date: Aug 2012

Location: Redditch, UK

Posts: 7

Time spent in forums: 2 h 12 m 32 sec
Reputation Power: 0

Hi badger_fruit.

Yeah I get that part about the [] I'm using that to get the id from the checkbox, what I cant seem to do is isolate each value of the drop down per row when the user updates more than one checkbox there's obviously an array but I want to select one option per checkbox ID not all which is what I currently get

PHP Code:


		
			
if ((isset($_POST["MM_update4"])) && ($_POST["MM_update4"] == "updatefactorycheckbox")) {

      



   

    

 if(isset($_POST['checkbox'])){$checkbox = $_POST['checkbox'];







$var2 = $_POST['project_id'];

//THIS IS THE OPTION DROP DOWN ARRAY

foreach($var2 as $prod_id){



print_r($_POST); //THIS JUST PRINTS THE ARRAY FOR ALL THE OPTION DROP DOWN SELECTIONS BECAUSE ITS WITHIN THE CHECKBOX ARRAY.



}

}}

I just cant seem to get it?

#10

November 5th, 2012, 01:50 PM

badger_fruit

Confused badger

Join Date: Mar 2009

Location: West Yorkshire

Posts: 760

Time spent in forums: 1 Week 4 Days 5 h 15 m 18 sec
Reputation Power: 339

Hmm, perhaps I may suggest some sample code here ....

PHP Code:


		
			
<?php

echo "<form name='mytest' method='post'>";

for ($i = 0; $i < 5; $i++) {

  echo "<select name='test_select[]'>";

    for ($z = 0; $z < 5; $z++) {

      echo "<option value='{$z}'>{$z}</option>";

    }

  echo "</select> ";



  echo "<input type='checkbox' name='checkbox[]' value=1><br />";

}

echo "<input type='submit' name='submit' value='submit'>";

echo "</form>";



if (isset($_POST['submit'])) {

  echo "<pre>";

  print_r($_POST);

  echo "</pre>";

}

?>

OK, you can see this demonstrated here --> http://badgerfruit.co.uk/examples/multi_form.php
In an example submission, this was the resulting $_POST ...

Quote:

Array
(
[test_select] => Array
(
[0] => 3
[1] => 2
[2] => 1
[3] => 4
[4] => 0
)

[checkbox] => Array
(
[0] => 1
[1] => 1
[2] => 1
)

[submit] => submit
)

This is of course, a problem! We can't say for sure which checkboxes were selected (we can only see that three were). So what's the solution? Well, as I see it you need to give the checkbox fixed names ... we can just change one line ...

EDIT : We need to give it a unique name, not fixed - sorry. We can derive this unique name by simply adding the number of the current loop iteration into it (in this case, $i) like so ...

PHP Code:


		
			
echo "<input type='checkbox' name='checkbox[{$i}]' value=1><br />";

See this demo'd here --> http://badgerfruit.co.uk/examples/multi_form2.php

Now, the $_POST variable contains some useful data ...

Quote:

Array
(
[test_select] => Array
(
[0] => 0
[1] => 1
[2] => 2
[3] => 3
[4] => 0
)

[checkbox] => Array
(
[1] => 1
[3] => 1
[4] => 1
)

[submit] => submit
)

From that, we know that we need to loop through the $_POST['checkbox'] array like so ..

PHP Code:


		
			
foreach ($_POST['checkbox'] as $key => $value) {

  echo "{$key} ~ {$value}<br />";

  echo "<b>{$_POST['test_select'][$key]}</b><br />";

}

Again, the result can be seen from the multi_form2.php linked to above but in summary, we can now find what checkboxes were set and what the appropriate select was set to!

Quote:

1 ~ 1
1
3 ~ 1
3
4 ~ 1
0

Ta dar

Comments on this post

pcraig23 agrees: I would have liked to up your reputation but for some reason it only lets me add 0...not very many!
lol

Last edited by badger_fruit : November 5th, 2012 at 02:01 PM. Reason: Clarification

#11

November 6th, 2012, 10:17 AM

pcraig23

Registered User

Join Date: Aug 2012

Location: Redditch, UK

Posts: 7

Time spent in forums: 2 h 12 m 32 sec
Reputation Power: 0

ah ok I get it. Thank you badger_fruit for your help!

I re-wrote my code and it works, I used the table row values as the array values for the checkbox, the foreach was the key, simple when it clicked in my head. Here's my code for future ref, sorry it's a bit raw.

PHP Code:


		
			
<?php

 

 if ((isset($_POST["MM_update4"])) && ($_POST["MM_update4"] == "updatefactorycheckbox")) {

      



   

    

 if(isset($_POST['checkbox'])){$checkbox = $_POST['checkbox'];



foreach ($_POST['checkbox'] as $key => $value) { 





  

mysql_select_db($database_Intranet, $Intranet);





$query_Recordset1 = "SELECT * FROM sales__factory_quotes WHERE (primary_ID ='".$value."')";



$Recordset1 = mysql_query($query_Recordset1, $Intranet) or die(mysql_error());

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

$totalRows_Recordset1 = mysql_num_rows($Recordset1);

 do { 

$fqr=$row_Recordset1['factory_quote'];

$val=$row_Recordset1['value'];



echo $val; 

echo $fqr;





$updateSQL2 = sprintf("UPDATE `sales__projects` SET `factory_quote_ref` = '$fqr', `value` ='$val' WHERE (project_ID ='".$_POST['test_select'][$key]."')");





  $Result1 = mysql_query($updateSQL2, $Intranet) or die(mysql_error());

  

  

$sql = sprintf("DELETE FROM sales__factory_quotes WHERE (primary_ID ='".$value."')");

  

  $Result2 = mysql_query($sql, $Intranet) or die(mysql_error());



} while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));







      





}  

}}



?>

#12

November 6th, 2012, 10:44 AM

badger_fruit

Confused badger

Join Date: Mar 2009

Location: West Yorkshire

Posts: 760

Time spent in forums: 1 Week 4 Days 5 h 15 m 18 sec
Reputation Power: 339

Hi there, really pleased you have it working and if you're the only one who works on the code then who really cares if it's messy

It may be wise though to at least TRY to keep it neat and make sure to put in lots of comments (so when your boss says to you after the Christmas do "can you change XY and Z please").

Now, the next thing you need to get around to is using PDO instead of the old "mysql" libraries. There's a bunch of tutorials out there and of course, this forum for when you get stuck (notice I said "when" and not "if" hehe).

Anyway, you're welcome regarding the help!

#13

November 6th, 2012, 10:54 AM

pcraig23

Registered User

Join Date: Aug 2012

Location: Redditch, UK

Posts: 7

Time spent in forums: 2 h 12 m 32 sec
Reputation Power: 0

Quote:

Originally Posted by badger_fruit

Now, the next thing you need to get around to is using PDO instead of the old "mysql" libraries. There's a bunch of tutorials out there and of course, this forum for when you get stuck (notice I said "when" and not "if" hehe).

Anyway, you're welcome regarding the help!

Yep probably get a book on it...I prefer books, I can make lots of sub-notes etc...by the way I tried to up your reputation, I presume I cant do that as it wouldn't let me

PHP-General - Update mysql with multiple drop down and checkbox value

Developer Shed Advertisers and Affiliates