Update checkboxes in mySQL via PHP

[frank5050]

Ok guys newbie here and stumped.

 

I am trying to update a field 'item_shipped' in mySQL if a checkbox is checked and I've run into a checkbox (1 or 0) update problem.

 

I have attached an image with my post so anyone can get a visual idea of how the checkboxes are laid out (customer info is blanked out).

 

Here is structure of the table to give you an idea of the table fields.

CREATE TABLE IF NOT EXISTS `om` (
  `myID` int(11) NOT NULL auto_increment,
  `order_id` varchar(50) NOT NULL,
  `order_status` varchar(50) NOT NULL,
  `product_name` varchar(10) NOT NULL,
  `item_shipped` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY  (`myID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=534 ;

 

 

 

I have two queries on the form page and both mySQL queries seems to be working ok. The first query outputs order related info which is then used in the second query to iterate results (line items) from individual orders.

 

 

Given below is the second query that includes checkbox field output, outputting a checkboxe per item on the fly.

$itemData = mysql_query('SELECT * FROM om WHERE order_id = ' . $row2['order_id']);
    while ( $itemRow = mysql_fetch_assoc( $itemData ) )
    {

$myID = $itemRow['myID'];
$chk =  $itemRow['item_shipped'] == 1 ? 'checked' : '';

print "<input type='hidden' name='myID[$i]' value='{$itemRow['myID']}' />";

echo "<input type='checkbox' name='$myID' id=box[] value='item_shipped[$i]' $chk>";

echo"<b>{$itemRow['product_name']}</b>";

}

<input type=\"submit\" value=\"Submit2\">
</form>

So with the help of the above code I have a series of checkboxe printed out which for each item displays 'checked' if the 'item_shipped' field is 1 in the database.

 

Since an order can have multiple items associated and hence a checkbox next to each item, what I want to be able to do is to be able to update the item_shipped field (via checkbox) for multiple items at the same time.

 

 

 

 

 

I already have an update.php page that updates the order_status and works just fine.

 

I would like to add the functionality of updating the checkbox as well (while updating the order status)

 

 

In other words, use just one submit button to update order_status (as it currently does), but also add code (possibly another query?) to update the checkbox.

 

I'm very much a newbie and would like someone to help me with the code for the checkbox array update.

 

 

update.php code is shown below:

 

<?

$date = date("Y-m-d G:i:s") ;

/* connection information */
$hostname =  "localhost";
$username =  "xxxx";
$password =  "xxxx";
$dbName =  "xxxx";
/* make connection to database */
MYSQL_CONNECT($hostname, $username, $password) OR DIE( "Unable to connect
to database");

@mysql_select_db("$dbName") or die(  "Unable to select database");

// find out how many records there are to update
$size = count($_POST['order_status']);

// start a loop in order to update each record
$i = 0;
while ($i < $size) {
// define each variable
$order_status= $_POST['order_status'][$i];
$order_id = $_POST['order_id'][$i];




// do the update and print out some info just to provide some visual feedback


$query = "UPDATE om SET order_status = '$order_status' WHERE order_id = '$order_id'";

mysql_query($query) or die ("Error in query: $query");

print "<font size=2 face=arial><font color=0000FF><b>$order_status</b></font> Updated for Order ID #$order_id</font><br />";
++$i;
}
mysql_close();
?>

Thanks in advance.

 

[attachment deleted by admin]

[fenway]

You're new -- next time, use code tags.