Update multiple MySQL table rows using one HTML form

Question

I'm using an HTML form within PHP to try and simultaneously update multiple rows in my MySQL table. The PHP seems to run (kind of) but updates my table with 'Array' in all the affected columns. Can someone please fill in the gaps to my code or explain what I've missed?!

P.S. I know I need to escape my PHP to prevent injection attacks ... that'll come later :)

HTML and PHP code below.

HTML form within PHP echo statement

<form action='edit.php' method='POST' id='scheduler'>

<input type='hidden' id='identifier' name='identifier[]' value='".  $row['id'] . "'>
<input type='hidden' id='assoc_to_username' name='assoc_to_username' value='" .     $schedule['assoc_to_username'] . "'>

<input id='assoc_to_date' name='assoc_to_date[]' type='text' value='" .     $schedule['assoc_to_date'] . "'/>

<select name='assoc_to_start_time[]' id='assoc_to_start_time'>
<option selected disabled='disabled' value='" . $schedule['assoc_to_start_time'] . "'>" . $schedule['assoc_to_start_time'] . "</option>
<option disabled='disabled'>----------</option>
<option value='All day'>All day</option>
</select>

<select name='assoc_to_end_time[]' id='assoc_to_end_time'>
<option selected disabled='disabled' value='" . $schedule['assoc_to_end_time'] . "'>" . $schedule['assoc_to_end_time'] . "</option>
<option disabled='disabled'>----------</option>
<option value=''>No end time</option>
<option value=''>All day</option>
</select>

<input id='taskname' name='taskname[]' type='text' value='" . $schedule['taskname'] . "'/>

<input id='submit' name='submit' class='submit' type='submit' value='Edit' type='submit' />

</form>

PHP process code

require_once('../inc/database-config.php');

$id1 = $_POST['identifier'];

$assoc_to_username = $_POST['assoc_to_username'];

$assoc_to_date = $_POST['assoc_to_date'];
$assoc_to_start_time = $_POST['assoc_to_start_time'];
$assoc_to_end_time = $_POST['assoc_to_end_time'];
$taskname = $_POST['taskname'];

foreach ($_POST['identifier'] as $id1)

{
    $sql=mysql_query("UPDATE schedule SET assoc_to_date= $assoc_to_date, assoc_to_start_time= $assoc_to_start_time, assoc_to_end_time= $assoc_to_end_time, taskname=$taskname WHERE assoc_to_username=$assoc_to_username");

header("Location: ../admin.php?action=edited"); 
;}


if (!mysql_query($sql,$dbcon))

{   die (header("Location: ../error.php"));   }

mysql_close($dbcon);

Please help!

which multiple rows you want to update or which multiple values you want to save.? — Sougata Bose
– Sougata Bose, Commented Jul 31, 2014 at 10:42
Remove all the [] from the name attributes of each of your inputs. — Mark Miller
– Mark Miller, Commented Jul 31, 2014 at 10:42

Adarsh Rajput · Accepted Answer · 2016-02-01 12:31:05Z

2

I think the problem is in foreach block... other POST var are also arrays, how you can use those directly?? change you loop like this and try:

for($i =0;$i <count($_POST['identifier']);$i++)
{
    $sql=mysql_query("UPDATE schedule SET assoc_to_date= ".$assoc_to_date[$i].", assoc_to_start_time= ".$assoc_to_start_time[$i].", assoc_to_end_time= ".$assoc_to_end_time[$i].", taskname=".$taskname[$i]." WHERE assoc_to_username=".$assoc_to_username[$i].";");
    if (!mysql_query($sql,$dbcon)){
        die (header("Location: ../error.php")); 
    }
}

also add [] to name='assoc_to_username[]' input field.

edited Feb 1, 2016 at 12:31

answered Jul 31, 2014 at 11:07

Adarsh Rajput

1,27614 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Samuel Ramzan Over a year ago

It shed light on an issue I was having with multiple columns and multiple rows updating... Than You Adarsh

Mark · Accepted Answer · 2014-07-31 10:44:50Z

1

By using square brackets [] on the input names, PHP is trying to pick up multiple inputs with that same name, even if you have one field with that name (which you do), PHP will still store that information as an array with one element. Remove the square brackets from your name attributes and you should be good to go.

answered Jul 31, 2014 at 10:44

Mark

1,8723 gold badges18 silver badges31 bronze badges

6 Comments

user3895205 Over a year ago

I removed the square brackets from my name attributes, and now get the error message "Warning: Invalid argument supplied for foreach() in /home/xxx/public_html/process/edit.php on line 13". Any ideas?

Mark Over a year ago

You can't run a foreach on a variable, that function is for arrays, which you are no longer using. Simply remove the foreach from your code.

Mark Over a year ago

In your question you said you're trying to upgrade multiple ROWS in your database? Do you have multiple forms with the same information on your page? When you submit the form you provided you are ONLY submitting that form, any other information on that page is not being posted through to PHP, I think you're trying to pick up all that information on the page when in fact you're only posting the information you're submitting in your form.

user3895205 Over a year ago

Your last comments are correct - removing the foreach allows me to successfully update all rows in the database but with identical values to whatever was entered in row id 1 of that table. I need to ensure row 1 updates with row 1's form data, row 2 with row 2's form data, etc. Edit: I am also using a PHP while statement wrapped around the HTML form to display each row from the database as a row in an HTML table.

Mark Over a year ago

That won't work, if you want ALL information on the page available you need to submit ONE FORM, at the moment you have countless amounts of forms. **WHEN YOU SUBMIT A FORM YOU ARE ONLY SUBMITTING THE INFORMATION WITHIN THAT FORM.

|

Peter Noble · Accepted Answer · 2014-07-31 11:05:28Z

-1

First of all, try correcting your query, so that the variables are passed:

$sql=mysql_query("UPDATE schedule SET assoc_to_date= ".$assoc_to_date.", assoc_to_start_time=".$assoc_to_start_time.", assoc_to_end_time= ".$assoc_to_end_time.", taskname=".$taskname." WHERE assoc_to_username=".$assoc_to_username."");

answered Jul 31, 2014 at 11:05

Peter Noble

68610 silver badges22 bronze badges

1 Comment

tbddeveloper Over a year ago

You likely got down voted because that wasn't the answer. In PHP you don't have to escape out of the string to output the variable. Try writing a string with variables as per the OPs post, you'll see the behavior.

Collectives™ on Stack Overflow

Update multiple MySQL table rows using one HTML form

3 Answers 3

1 Comment

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related