Update data using checkbox ( php and mysql)

Lowyat.NET Lowyat.NET Rules and Regulations

FAQ Help Search Members Calendar

Outline · [ Standard ] · Linear+

Update data using checkbox ( php and mysql), How to update data using checkbox

Share on

| Track this topic | Email this topic | Print this topic

code_newbie

Oct 22 2009, 12:31 PM

Show posts by this member only |This post's rating (0+, 0-) | Post #1

Newbie

Group: New Member
Posts: 4
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Oct 2009

Hi there,

I am a php newbie. Currently i am working on a project and i get stuck with updating data using checkbox.

The system that i build could update all data correctly if i check all the checkbox and then click submit button.

But there is a problem when i randomly select a row from table to update data. The data is updated according to the first row of data and not the data that i keyed in(i.e, the data is updated same with the value of first row in the table). Below is the codes. Hope someone could enlighten me

//manage_form.php
<?php
$sql = "SELECT * FROM warranty";
$sresult=mysql_query($sql) or die(mysql_error());
if(mysql_numrows($sresult))
{

while($row = mysql_fetch_row($sresult))

{
$id = $row[0];

$orderid=$row[1];

$cust_name=$row[2];

$email = $row[11];

$product = $row[13];

$arrival_date=$row[16];
$fault_desc = $row[28];
$status=$row[29];

$tracking_num=$row[30];

echo '<tr bgcolor="#e9e6ed"><td><input type=checkbox name=box[] id=box[] value="'.$id.'"</td>';
echo '<td align=center ><a href="index_.php?orderid='.$orderid.'&boxaction=display_form" target="_blank">'.$orderid.'</td>'; ?>
<td align=center><?php echo $cust_name;?></td><td align=center><?php echo $email; ?></td><td align=center><?php echo $product; ?></td>
<td width=150><?php echo $fault_desc; ?></td><td align=center><?php echo $arrival_date; ?></td>
<td align="center"><select name=status[]><option value=pending <?php if($status=="pending") echo "selected";?>>Pending</option><option value=processing <?php if($status=="processing") echo "selected";?>>Processing</option><option value=sent <?php if($status=="sent") echo"selected";?>>Sent</option></select></td><td align=center><textarea name=trackingnumber[]><?php echo $tracking_num; ?></textarea>
</td></tr>
<?php
}
echo '<tr><td><input type="submit" name="submit" Value="Update"></td><td><input type="submit" name="submit" Value="Delete" onclick="ConfirmChoice();return false;"></td></tr>';
}

else{

print("<tr><td><B>No record found<B></td><tr>");
}

?>

//update.php

<?php
include('auth.php');
include('database_access_param.php');

$con = mysql_connect($hostname,$dbuser,$dbpassword)or die(mysql_error());
$db = mysql_select_db($dbname,$con)or die(mysql_error());

$box=$_POST['box'];
$track=$_POST['trackingnumber'];
$status_value=$_POST['status'];

for($i=0; $i<count($box); $i++){
$up_id = $box[$i];

$tracks = $track[$i];
$statuss = $status_value[$i];
$sql = "UPDATE warranty SET status='".$statuss."', tracking_num='".$tracks."'WHERE id='".$up_id."'";
$result = mysql_query($sql) or die ("Error running MySQL query");

}

include('manage_form.php');

?>

Azriq007

Oct 22 2009, 01:40 PM

Show posts by this member only |This post's rating (0+, 0-) | Post #2

Getting Started

Group: Junior Member
Posts: 59
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Aug 2009

hey...
for reminder, if you are posting code snippet please use [code] tags when u put your code..
so that will be much easier to be read..
and can you give some output example?...
it more easier for us to help with your problem...

code_newbie

Oct 22 2009, 02:15 PM

Show posts by this member only |This post's rating (0+, 0-) | Post #3

Newbie

Group: New Member
Posts: 4
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Oct 2009

QUOTE(Azriq007 @ Oct 22 2009, 01:40 PM)

hey...
for reminder, if you are posting code snippet please use [code] tags when u put your code..
so that will be much easier to be read..
and can you give some output example?...
it more easier for us to help with your problem...

I have attached a screen shot of the project. In the picture, there is 2 row of data. It is work fine if i want to update the first row data or update 2 rows of data at the same the time.

The problem is, when i wanna update the second row, the data will not updated as the data that i keyed in the textarea(tracking number column) instead it will updated with the data as the first row. I have tested will 5 row of data before this, all the subsequent rows data will be replaced with the value same as the first row.

I guess there is a problem with looping problem, but I do not know how to solve it.

Thanks

Attached thumbnail(s)

Azriq007

Oct 22 2009, 05:20 PM

Show posts by this member only |This post's rating (0+, 0-) | Post #4

Getting Started

Group: Junior Member
Posts: 59
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Aug 2009

i have tried a simple application

first , As you can see in my code I have:

CODE

//send mysql a query to select records from the products table where the id's match. If the query fails exit
if (!($result = mysql_query("SELECT * FROM customers where cid= '$cid'"))){
echo '&message=query+failed&';
exit;
}

and in your case u have juz to change cid to orderid

next my code is

CODE

//get number of record base on query executed
$num_results = mysql_num_rows($result);
if($num_results <= 0) {//if record not found then add new record
$query = "INSERT INTO customers (cid, Name, LName, Age, Maritial_Status, Email)
VALUES ('$cid','$fname', '$lname', '$Age', '$MStatus','$Email')";
if (mysql_query($query , $conn))//check out either the information successfully added or not
echo "&message=the+customer+'$Name'+'$LName'+has+been+successfully+added&";
else
echo '&message=the+insert+was+not+successful&';
}else { //customer exists so update existing customer
$update = "update customers set FName='$fname'; LName='$lname'; where customerid='$cid'";
if(mysql_query($update, $conn))
echo "&message=the+details+have+been+updated&";
else
echo "&message=update+not+successful&";
}

this code wil successfully added the data.... you juz have to replace variables of mine with ur own

hope this will help

code_newbie

Oct 27 2009, 05:56 PM

Show posts by this member only |This post's rating (0+, 0-) | Post #5

Newbie

Group: New Member
Posts: 4
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Oct 2009

QUOTE(Azriq007 @ Oct 22 2009, 05:20 PM)

i have tried a simple application

first , As you can see in my code I have:

CODE

//send mysql a query to select records from the products table where the id's match. If the query fails exit
if (!($result = mysql_query("SELECT * FROM customers where cid= '$cid'"))){
echo '&message=query+failed&';
exit;
}

and in your case u have juz to change cid to orderid

next my code is

CODE

//get number of record base on query executed
$num_results = mysql_num_rows($result);
if($num_results <= 0) {//if record not found then add new record
$query = "INSERT INTO customers (cid, Name, LName, Age, Maritial_Status, Email)
VALUES ('$cid','$fname', '$lname', '$Age', '$MStatus','$Email')";
if (mysql_query($query , $conn))//check out either the information successfully added or not
echo "&message=the+customer+'$Name'+'$LName'+has+been+successfully+added&";
else
echo '&message=the+insert+was+not+successful&';
}else { //customer exists so update existing customer
$update = "update customers set FName='$fname'; LName='$lname'; where customerid='$cid'";
if(mysql_query($update, $conn))
echo "&message=the+details+have+been+updated&";
else
echo "&message=update+not+successful&";
}

this code wil successfully added the data.... you juz have to replace variables of mine with ur own

hope this will help

I still cannot update data using checkbox. Anyway, really thanks for your suggestion.

puaka_astro

Oct 28 2009, 12:15 AM

Show posts by this member only |This post's rating (0+, 0-) | Post #6

Getting Started

Group: Junior Member
Posts: 130
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Jun 2007

Dude try this.. do a comparison..see if you can spot the problem

Using this table structure

CODE

CREATE TABLE `warranty` (
`id` int(11) NOT NULL auto_increment,
`order_id` varchar(200) collate latin1_general_ci default NULL,
`cust_name` varchar(200) collate latin1_general_ci default NULL,
`email` varchar(100) collate latin1_general_ci default NULL,
`product_type` varchar(100) collate latin1_general_ci default NULL,
`submit_date` timestamp NOT NULL default CURRENT_TIMESTAMP,
`status` enum('processing','pending','sent') collate latin1_general_ci default 'processing',
`tracking_no` varchar(200) collate latin1_general_ci default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;

The Script

CODE

<?php
$hostname = 'localhost';
$username = 'root';
$password = '';
$database = 'test';

mysql_connect($hostname, $username, $password);
mysql_select_db($database);

if($_POST)
{
// Update
if(isset($_POST['update']))
{
if(isset($_POST['box']) && $_POST['box'])
{
foreach ($_POST['box'] as $id)
{
$status = $_POST["status".$id];
$tracking_no = addslashes(trim($_POST["trackingnumber".$id]));

$query = "UPDATE warranty SET status = '$status', tracking_no = '$tracking_no' ".
"WHERE id = '$id'";
$result = mysql_query($query) or die(mysql_error());
}
}
}

// Delete
if(isset($_POST['delete']))
{
if(isset($_POST['box']) && $_POST['box'])
{
foreach ($_POST['box'] as $id)
{
$query = "DELETE FROM warranty WHERE id = '$id'";
$result = mysql_query($query) or die(mysql_error());
}
}
}
}

$query = "SELECT * FROM warranty";
$result = mysql_query($query) or die(mysql_error());
$total_rows = mysql_num_rows($result);

$table = "<form name=\"frmWarranty\" id=\"frmWarranty\" action=\"\" method=\"post\">\n".
"<table align=\"center\" width=\"70%\" border=\"1\" cellpadding=\"4\" cellspacing=\"2\">";

while ($row = mysql_fetch_assoc($result))
{
putenv("TZ=Asia/Kuala_Lumpur");
$dt = new DateTime($row['submit_date']);
$arrival_date = $dt->format('d/m/Y');

$table .= "<tr bgcolor=\"#e9e6ed\">\n".
"<td>\n".
"<input type=\"checkbox\" name=\"box[]\" id=\"box$row[id]\" value=\"$row[id]\">\n".
"</td>\n".
"<td align=\"center\">\n".
"<a href=\"index_.php?orderid=$row[order_id]&boxaction=display_form\" target=\"_blank\">$row[order_id]</a>\n".
"</td>\n".
"<td align=\"center\">$row[cust_name]</td>\n".
"<td align=\"center\">$row[email]</td>\n".
"<td align=\"center\">$row[product_type]</td>\n".
"<td align=\"center\">$arrival_date</td>\n".
"<td align=\"center\">\n".
"<select name=\"status$row[id]\">\n".
"<option value=\"pending\" ";
if($row['status'] == 'pending')
{
$table .= " selected=\"selected\"";
}
$table .= ">Pending</option>\n".
"<option value=\"processing\" ";
if($row['status'] == 'processing')
{
$table .= " selected=\"selected\"";
}
$table .= ">Processing</option>\n".
"<option value=\"sent\" ";
if($row['status'] == "sent")
{
$table .= " selected=\"selected\"";
}
$table .= ">Sent</option>\n".
"</select>\n".
"</td>\n".
"<td align=\"center\">\n".
"<textarea name=\"trackingnumber$row[id]\" id=\"trackingnumber$row[id]\">$row[tracking_no]</textarea>\n".
"</td>\n".
"</tr>\n";
}

if($total_rows)
{
$table .= "<tr>\n".
"<td align=\"center\" colspan=\"8\">\n".
"<input type=\"submit\" name=\"update\" value=\"Update\">    \n".
"<input type=\"submit\" name=\"delete\" value=\"Delete\">\n".
"</td>\n".
"</tr>\n";
}
else
{
$table .= "<tr>\n".
"<td colspan=\"8\" align=\"center\" style=\"padding:20px;\">No record found</td>\n".
"</tr>\n";
}

$table .= "</table></form>";

echo $table;

?>

code_newbie

Nov 2 2009, 04:52 PM

Show posts by this member only |This post's rating (0+, 0-) | Post #7

Newbie

Group: New Member
Posts: 4
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Oct 2009

Thank you very much. It really helps a lot. Now I can update data using checkbox.

QUOTE(puaka_astro @ Oct 28 2009, 12:15 AM)

Dude try this.. do a comparison..see if you can spot the problem

Using this table structure

CODE

CREATE TABLE `warranty` (
`id` int(11) NOT NULL auto_increment,
`order_id` varchar(200) collate latin1_general_ci default NULL,
`cust_name` varchar(200) collate latin1_general_ci default NULL,
`email` varchar(100) collate latin1_general_ci default NULL,
`product_type` varchar(100) collate latin1_general_ci default NULL,
`submit_date` timestamp NOT NULL default CURRENT_TIMESTAMP,
`status` enum('processing','pending','sent') collate latin1_general_ci default 'processing',
`tracking_no` varchar(200) collate latin1_general_ci default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;

The Script

CODE

<?php
$hostname = 'localhost';
$username = 'root';
$password = '';
$database = 'test';

mysql_connect($hostname, $username, $password);
mysql_select_db($database);

if($_POST)
{
// Update
if(isset($_POST['update']))
{
if(isset($_POST['box']) && $_POST['box'])
{
foreach ($_POST['box'] as $id)
{
$status = $_POST["status".$id];
$tracking_no = addslashes(trim($_POST["trackingnumber".$id]));

$query = "UPDATE warranty SET status = '$status', tracking_no = '$tracking_no' ".
"WHERE id = '$id'";
$result = mysql_query($query) or die(mysql_error());
}
}
}

// Delete
if(isset($_POST['delete']))
{
if(isset($_POST['box']) && $_POST['box'])
{
foreach ($_POST['box'] as $id)
{
$query = "DELETE FROM warranty WHERE id = '$id'";
$result = mysql_query($query) or die(mysql_error());
}
}
}
}

$query = "SELECT * FROM warranty";
$result = mysql_query($query) or die(mysql_error());
$total_rows = mysql_num_rows($result);

$table = "<form name=\"frmWarranty\" id=\"frmWarranty\" action=\"\" method=\"post\">\n".
"<table align=\"center\" width=\"70%\" border=\"1\" cellpadding=\"4\" cellspacing=\"2\">";

while ($row = mysql_fetch_assoc($result))
{
putenv("TZ=Asia/Kuala_Lumpur");
$dt = new DateTime($row['submit_date']);
$arrival_date = $dt->format('d/m/Y');

$table .= "<tr bgcolor=\"#e9e6ed\">\n".
"<td>\n".
"<input type=\"checkbox\" name=\"box[]\" id=\"box$row[id]\" value=\"$row[id]\">\n".
"</td>\n".
"<td align=\"center\">\n".
"<a href=\"index_.php?orderid=$row[order_id]&boxaction=display_form\" target=\"_blank\">$row[order_id]</a>\n".
"</td>\n".
"<td align=\"center\">$row[cust_name]</td>\n".
"<td align=\"center\">$row[email]</td>\n".
"<td align=\"center\">$row[product_type]</td>\n".
"<td align=\"center\">$arrival_date</td>\n".
"<td align=\"center\">\n".
"<select name=\"status$row[id]\">\n".
"<option value=\"pending\" ";
if($row['status'] == 'pending')
{
$table .= " selected=\"selected\"";
}
$table .= ">Pending</option>\n".
"<option value=\"processing\" ";
if($row['status'] == 'processing')
{
$table .= " selected=\"selected\"";
}
$table .= ">Processing</option>\n".
"<option value=\"sent\" ";
if($row['status'] == "sent")
{
$table .= " selected=\"selected\"";
}
$table .= ">Sent</option>\n".
"</select>\n".
"</td>\n".
"<td align=\"center\">\n".
"<textarea name=\"trackingnumber$row[id]\" id=\"trackingnumber$row[id]\">$row[tracking_no]</textarea>\n".
"</td>\n".
"</tr>\n";
}

if($total_rows)
{
$table .= "<tr>\n".
"<td align=\"center\" colspan=\"8\">\n".
"<input type=\"submit\" name=\"update\" value=\"Update\">    \n".
"<input type=\"submit\" name=\"delete\" value=\"Delete\">\n".
"</td>\n".
"</tr>\n";
}
else
{
$table .= "<tr>\n".
"<td colspan=\"8\" align=\"center\" style=\"padding:20px;\">No record found</td>\n".
"</tr>\n";
}

$table .= "</table></form>";

echo $table;

?>

« Next Oldest · Codemasters · Next Newest »