Solved

Update MySQLi with checkbox - mutliple rows

Posted on 2013-05-15
6
962 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 110

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 110

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 34

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 110

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article discusses four methods for overlaying images in a container on a web page
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

752 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