Updating multiple records at once with checkboxes

[sheldon_cooper]

Hi folks,

 

I'm having a little trouble with a little script that I wrote to try and update dates in a user table. I'm still learning PHP as I go, but this has had me stumped for the past few days!! 

 

Basically, it selects a list of users from a table with their end dates (if they have one) and I should be able to update multiple users at once by entering dates or removing the values, ticking the checkbox and clicking 'Update' but it doesn't work! It either only updates the first record, or gives me multiple errors about '2015 not being a valid day for a date/time column'. If I run the SQL directly into the database, then everything works fine, I have a suspicion that its something to do for my for loop in the updateNow() function. 

 

Can anyone help !?

<?php
include 'connect.php';

ini_set('display_errors',1);
ini_set('display_startup_errors',1);
error_reporting(-1);

date_default_timezone_set('Europe/Berlin');


function multiupd(){

	$con = ingres_connect("testserver::testdb")
		or die("Could not connect");
	$sql="select name, expire_date from test_user order by name asc";
	$result = ingres_query($con, $sql);

?>

	<table width="400" border="0" cellspacing="1" cellpadding="0">
	<tr>
	<td><form name="form1" method="post" action="">
	<table width="400" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">
	<tr>
	<td bgcolor="#FFFFFF"> </td>
	<td colspan="4" bgcolor="#FFFFFF"><strong>Update multiple users: </strong> </td>
	</tr>
	<tr>
	<td align="center" bgcolor="#FFFFFF"><strong>Name</strong></td>
	<td align="center" bgcolor="#FFFFFF"><strong>Expiry Date</strong></td>
	<td align="center" bgcolor="#FFFFFF"><strong>Update Required</strong></td>
	</tr>

<?php
	while($rows=ingres_fetch_array($result)){
?>

	<tr>
	<td bgcolor="#FFFFFF"><?php echo $rows['name']; ?></td>
	<td bgcolor="#FFFFFF"><input name="expiry_date[]" type="text" id="expiry_date[]" value="<?php 
							if (!is_null($rows['expire_date'])) {
								$convert_date = strtotime($rows['expire_date']);
								if ($convert_date > 0) {
									echo date('d.m.Y', $convert_date);
								}
							} ; ?>"</td> 
	<td bgcolor="#FFFFF" align="center"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<?php echo $rows['name']; ?>"></td>
	</tr>

<?php
}
?>

	<tr>
	<td colspan="5" align="center" bgcolor="#FFFFFF"><input name="update" type="submit" id="update" value="Update"></td>
	<td><input type=hidden value="Update multiple users" name="userDBupdate"></td>
	</tr>

	<?php

		
	ingres_close($con);
} // end of multiupd


function updateNow(){

?>

	<form name="updateUsers" method="post" action="">
	<td><input type=hidden value="Update multiple users" name="userDBupdate"></td>
<?php
	
	for($i=0;$i<count($_POST['checkbox']);$i++){
		$userid = $_POST['checkbox'][$i];
                $date_to_upd = $_POST['expiry_date'][$i];
                $sql = "update test_user SET expire_date = ingresdate('" . $date_to_upd . "') where name = '" . $userid . "'";
                $con = ingres_connect("testserver::testdb")
                        or die("Could not connect");
     		$db_update = ingres_query($con, $sql);
		echo $date_to_upd;
		

                if (ingres_errno())
                {
                    echo ingres_errno() . "-" . ingres_error() . "\n";
                }

		echo $date_to_upd;
		echo $sql;
		if ($db_update == TRUE){
                        echo "<br>User <b>" . $userid . "</b>  has had expiry date updated until <b>" . $date_to_upd . "</b>";
                } else
                        echo "There has been an error processing your request.";
        }
        ingres_commit($con);
        ingres_close($con);
			

} // end of updateNow


#########################################################################################
########################################################################################
## Main Prog
##
##
##
########################################################################################
#########################################################################################

if (isset($_POST['update'])) {
        updateNow();
} elseif (empty($_POST['update'])) {
        multiupd();
} elseif (empty($_POST['checkbox'])) {
	echo "No users selected! Please check the box where an update is required";
} else {
        echo "Nothing to see here";
}

?>

</table>
</form>
</td>
</tr>
</table>

Can anyone help !? It's driving me crazy!

 

Cheers!

[mac_gyver]

only checked check-boxes are submitted and you have no relationship between the check-box and the text input field holding the updated date. you can see this is you examine the submitted post data. add the following debugging line of code to see what is actually being submitted - 

echo '<pre>',print_r($_POST,true),'</pre>';

your database table should have an autoincrement id (identifier) column. i recommend that you use the id as the form field array index for both the check-box and the text input field. this will serve to tie the check-box to it's corresponding text input field.