Solved

Update MySQLi with checkbox - mutliple rows

Posted on 2013-05-15
6
883 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
object oriented programming comparison 5 54
Form not operating correctly. 1 22
Download a website to hdd 2 49
A form to still have contents even if some are wrong 10 46
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
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…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

910 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

16 Experts available now in Live!

Get 1:1 Help Now