Solved

Update MySQLi with checkbox - mutliple rows

Posted on 2013-05-15
6
868 Views
Last Modified: 2013-05-25
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

0
Comment
Question by:nikaotech
  • 3
  • 2
6 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39169938
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.
0
 

Author Comment

by:nikaotech
ID: 39169944
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...
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39170092
There are so many moving parts to your question that I am tempted to write an article about it, and I will try to do that after I get back from the West Coast.  In the mean time, it's abundantly clear to me that you need some foundation in how PHP and MySQL work together, and this book can help with that.
http://www.amazon.com/PHP-MySQL-Web-Development-Edition/dp/0672329166/

PHP has some online learning resources, too.
http://php.net/tut.php

What you've got here is not really a question with a succinct answer -- it's more of a cry for help, and for that you would be well-advised to hire a professional developer.  The things you need to know (or your developer needs to know) include...
1. How do HTML forms interact with PHP scripts?
2. How do I avoid the security risks inherent in using the PHP extract() function?
3. How do I write an UPDATE query; what are the appropriate uses of WHERE and LIMIT clauses?
4. How do I test for query success and what do I do if the query fails?

It's not hard to learn these things, but it takes time and practice.  And that is why your choices are to either hire a developer or take the time and effort to learn all of the things a developer already knows.  My guess is that hiring a professional will get you better results faster.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 33

Expert Comment

by:Slick812
ID: 39170336
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.
0
 

Author Comment

by:nikaotech
ID: 39196589
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...
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39196688
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
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
This article discusses how to create an extensible mechanism for linked drop downs.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now