Link to home
Create AccountLog in
Avatar of nikaotech
nikaotech

asked on

Update MySQLi with checkbox - mutliple rows

Hey Experts, I'm having trouble imagining what to do here and looking for some help.  I have a page that runs a simple query to show products that we give away at our donation center.  The query lists the product category, name and (availability for testing). I'll include "WHERE available=1" later to simplify.  What I'm lacking is this; I want to have a checkbox at the end of each row.  If that box is checked, my UPDATE query will update the products table and set "available=0" and "ordered=1" for each item where the checkbox is checked.  Since this page lists several items we'll be updating several rows with one update.  

I've placed "echo "<td><input type='checkbox' value='1' name='addtocart'/></td>";" to get the checkbox where I want it, but it's the implementation of the checkbox and the incomplete UPDATE query that have left me at a loss...any ideas?  Thanks in advance!

Here's the complete code:
<?php
$db_host = "localhost";
$db_name = "db";
$db_user = "user";
$db_word = "pass";

$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    die($err);
}

$query="SELECT 
  products.id AS p_id
, products.category_id AS cat_id
, products.name AS p_name
, products.available AS available
, products.expected AS expected
, products.ordered AS ordered
, categories.id AS cat_id
, categories.name AS cat_name

FROM products, categories 
WHERE products.category_id = categories.id
ORDER BY cat_name ASC";

$result = $mysqli->query( $query );

$num_results = $result->num_rows;

if( $num_results ){
?>
<form action='#' method='post' border='0'>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <th colspan="6" valign="top" scope="col">  
<?php  

echo "<table valign='top' WIDTH='100%' BORDERCOLOR='cccccc' BORDER='0' cellpadding='1' cellspacing='1'>";
echo "<tr bgcolor='#cccccc'>";
echo "<th><b>Category</b></th>";
echo "<th><b>Product Name</b></th>";
echo "<th><b>Available</b></th>";
echo "<th><b>Expected</b></th>";
echo "<th><b>Ordered</b></th>";
echo "<th><b>Add To Cart</b></th>";
echo "</tr>";

$num = 0;
while( $row = $result->fetch_assoc() ){
$color= ($num % 2 == 0) ? '#eeeeee' : '#dddeee';
$num++;
extract($row);

echo '<tr style="background-color:'.$color.';">';
echo "<td>{$cat_name}</td>";
echo "<td>{$p_name}</td>";
echo '<td>' . ($row['available'] ? 'Yes' : 'No') . '</td>';
echo '<td>' . ($row['expected'] ? 'Yes' : 'No') . '</td>';
echo '<td>' . ($row['ordered'] ? 'Yes' : 'No') . '</td>';
echo "<td><input type='checkbox' value='1' name='addtocart'/></td>";
echo "</tr>";
}		
echo "</table>";
?>

</th>
    
<?php
$action = isset($_POST['action']) ? $_POST['action'] : "";
if($action=='create'){ 

$query = "
UPDATE products
SET
ordered = 1,
available = 0
WHERE
........
";
 
if( $mysqli->query($query) ) {
	
    echo "<script> window.location.replace('orders.php') </script>" ;
	 }else{
         echo "Database Error: Unable to create record.";
		 var_dump($query);
     }
     $mysqli->close();
 }
?>
    </tr>
  <tr width="100%">
  	<td colspan="6" align="left" bgcolor="#cccccc"><b>Please enter client information below:</b></td>
  	</tr>
  <tr width="100%">
  	<td width="119" bgcolor="#eeeeee"><b>First Name:</b></td>
  	<td width="161" align="left" bgcolor="#eeeeee"><input type='text' name='firstname' /></td>
  	<td colspan="7" rowspan="3" align="left" bgcolor="#dddeee"><input type='hidden' name='action' value='create' /><input name='submit' type='submit' value="CHECKOUT & PRINT VOUCHER" /></td>
  	</tr>
    <tr width="100%">
  	<td width="119" bgcolor="#dddeee"><b>Last Name:</b></td>
  	<td align="left" bgcolor="#dddeee"><input type='text' name='lastname' /></td>
  	</tr>
    <tr width="100%">
  	<td width="119" bgcolor="#eeeeee"><b>Birthdate:</b></td>
  	<td bgcolor="#eeeeee"><input type='text' name='birthdate' /></td>
  	</tr>

  <tr width="100%">
  	<td bgcolor="#cccccc" colspan="6" align="left">&nbsp;</td>
  </tr>
</table></form>

<?php
}else{

	echo "No records found.";
}
?>

Open in new window

Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Checkboxes that are not checked do not appear in the request at all.  Unlike type=text fields that are present but empty, the unchecked checkbox is simply not there.  A workaround for this oddity is to put a type=hidden input control immediately before the checkbox, with the same name= attribute as the checkbox.  If the checkbox is checked, that name will contain the value attribute of the checkbox.  If not, that name will contain the value attribute of the hidden input tag.

The UPDATE query would probably need the id field so it could create a WHERE clause.  You might be able to pass this via a hidden input control, too.
Avatar of nikaotech
nikaotech

ASKER

I've replaced the checkbox with this:
echo "<td>";

    if($ordered)
    {
        echo "<input type='checkbox' name='cb_order' value=Yes checked=true/>";       
    }
    else
    {
        echo "<input type='checkbox' name='cb_order' />";       
    }

echo "</td>";

Open in new window

And now I have a working checkbox that pulls the correct value currently in the "ordered" field, but I can't figure out how to "Update" the records so that if the user checks the "ordered" box it sets ordered=1 and available=0...
ASKER CERTIFIED SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I will give you my suggestion, unlike some Form Inputs, the checkboxes  are NOT set in the $_POST if they are Not checked, so the first ting I would do is to give All Inputs (checkboxes in your case) a Unique NAME, by including a number, next add a hidden input with the total number of Rows displayed, maybe like this -
$num = 0;
while( $row = $result->fetch_assoc() ){
$color= ($num % 2 == 0) ? '#eeeeee' : '#dddeee';
$num++;
extract($row);

echo '<tr style="background-color:'.$color.';">';
echo "<td>{$cat_name}</td>";
echo "<td>{$p_name}</td>";
echo '<td>' . ($row['available'] ? 'Yes' : 'No') . '</td>';
echo '<td>' . ($row['expected'] ? 'Yes' : 'No') . '</td>';
echo '<td>' . ($row['ordered'] ? 'Yes' : 'No') . '</td>';
echo "<td><input type='checkbox' value='1' name='addtocart{$num}'/></td>";// $num starts at one here
echo "</tr>";

}		
echo "</table>";
echo "<input type='hidden' name='numRows' value='{$num}' />";// record the total rows here

Open in new window


and in your $_POST code something like -
if($action=='create'){
$numRows = (int) $_POST['numRows'];
if ($numRows > 1) {
    for ($i = 1; $i < $numRows;++$i) {
        if (isset($_POST['addtocart'.$1])) {
            // Code here for a check being checked on ROW $i in Form
            }
       } 
    } else {
           // code here for some ERROR as the is less than 2 Rows
           }
}

Open in new window


this is just code I typed in and did not test, and may have typing errors, I hope it showed you how to use checkboxes and test to see if they are checked.
You acually need to use more complex code, since your Form rows are not identified in your $_POST to be able to update anything, Maybe add a hidden -
echo "<input type='hidden' name='updateID{$num}' value='{$row['p_name']}' />";

ask questions if you need more info.
Slick, thanks for the suggestion, but I'm taking Ray's advice to go back to the beginning and get a better understanding of the basics of php. I've been writing such basic php for so long that I just assumed I could step in to advanced development much quicker than I should have. Back to the books...
Thanks for the points - you'll love the book, and in a month of study you'll be a couple of years ahead of trial and error!  Best regards, ~Ray