Updating multiple rows in mysql with checkboxes

how to update mysql table from multiple checked checkboxes

[Susie_Beth]

Hello!

I am quite new to php, and I am trying to update multiple rows in my table using checkboxes. However, when multiple checkboxes are selected, only the last checked record updates. Here is the code:

<form action="insert.php" method="post">
<table id="form">
 <tr>
 <td >Photographer's Name
 <input type="text" name="PhotgrapherName"/></td> 
 </tr>

 <?php
 $con = mysql_connect("localhost","root","root");
 if (!$con)
 {
 die('Could not connect: ' . mysql_error());
 }
 
 mysql_select_db("ACTC", $con);
 
 $result = mysql_query("SELECT * FROM event_schedule");
 
 echo "<table border=1>
 
 while($row = mysql_fetch_array($result))
 {
?>
 <tr>
<td><input type="checkbox" name="Events[]" value="<?php echo $row['id'] ?>" ></td>
 <td> <?php echo $row['Date'] ?> </td>
 <td> <?php echo $row['Time'] ?> </td>
 <td> <?php echo $row['Location'] ?> </td>
 <td width="300px"> <?php echo $row['EventDescription'] ?> </td>
<td> <?php echo $row['Photographer'] ?> </td>
 </tr>
 <?php
 } 
 ?>
 </table>
 <?php
 mysql_close($con);
 ?> 
 <tr>
 <td><br/><input name="submit" type="submit" value="Submit"> <input name="reset" type="reset" value="Reset"></td>
 </tr>
 </table>
 </form>

Code after the form is submitted (This is in a separate document called insert.php):

 <?php
 $con = mysql_connect("localhost","root","root");
 if (!$con)
 {
 die('Could not connect: ' . mysql_error());
 }
 
 mysql_select_db("ACTC", $con);
 
$PhotgrapherName = $_POST['PhotgrapherName'];

foreach ($_POST['Events'] as $entry)
{ 
 
 $query = "UPDATE event_schedule SET Photographer = '$PhotgrapherName' WHERE id = '$entry'" ;
}
 
 if (!mysql_query($query,$con))
 die('Error: '. mysql_error());
 
 echo "The schedule has been updated<br />";
 echo "<a href='index.php'>Back to table</a>";
 
 mysql_close($con);
 ?> 

Could anyone help me resolve this issue? Thanks in advance!

[glimbeek]

First of:

Line 18: echo "<table border=1>

That should be:
echo "<table border=1>";

Second,

put the:
"$con = mysql_connect("localhost","root","root");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("ACTC", $con);"

In a separate file which you include, so you don't have to change 1 thing in several locations.

And to test to see what happends:

echo the $query so you see what mysql statement you are running. This usually shows you what's going wrong.

[Matthew1980]

HI there Suzie_beth,

Welcome to webmaster world ;-p

With regards to the code you have supplied, your are missing the closing quote and semi colon from:-

echo "<table border=1>

while($row = mysql_fetch_array($result))

should be:-

echo "<table border=1>";

while($row = mysql_fetch_array($result))

Also, when putting $_POST globals into sql querys, make sure as they are sanitised first, as if they are left as they are you are open to malicious code injections, functions like mysql_real_escape_string() and strip_tags() will improve the immunity to this risk greatly:-

$PhotgrapherName = mysql_real_escape_string(strip_tags($_POST['PhotgrapherName']));

foreach (mysql_real_escape_string(strip_tags($_POST['Events'])) as $entry)

Good Luck!

Cheers,

MRb

edit:typo!

[Susie_Beth]

Thanks for the help!

I added the closing quote and semi colon. Thanks for catching that error. :)

Unfortunately, I am still having difficulty. When I check the first three records and echo $query this is what it displays:

UPDATE event_schedule SET Photographer = 'Name' WHERE id = '3'

It is only updating the last id selected and not all three. Also, I attempted to make the change to:

$PhotgrapherName = mysql_real_escape_string(strip_tags($_POST['PhotgrapherName']));

foreach (mysql_real_escape_string(strip_tags($_POST['Events'])) as $entry) 

but then I receive this message when the form is submitted:
"Error: Query was empty"

Any idea what the problem is? Again, thank you for helping!

[Matthew1980]

Hi there Suzie_Beth,

foreach ($_POST['Events'] as $entry)
{

$query = "UPDATE event_schedule SET Photographer = '$PhotgrapherName' WHERE id = '$entry'" ;
mysql_query($query, $con) or die(mysql_error());
}

I think that the mysql_query($query, $con) should be in the loop with the query, else the last one will be the only one actioned. For now remove the sanitising I suggested, lets get this updating correctly first ;p, worry about the other bits once its working...

Cheers,

MRb

[Matthew1980]

Hi Suzie_beth,

Just noticed something else too:-

if (!mysql_query($query,$con))
die('Error: '. mysql_error());

replace with:-

if (!mysql_query($query,$con)){
die('Error: '. mysql_error());
}

You have missed the braces off the if clause.. oops!

Cheers,

MRb

[Susie_Beth]

It works! It now updates all of the records that are checked. Thanks!

[Matthew1980]

Hi Suzie_beth,

Excellent!

Have fun with the rest of the site.

MRb

[glimbeek]

Good to hear that it works :)
Clean coding always does the trick :p