PDA

Click to See Complete Forum and Search --> : Updating/Editing Multiple Rows within a Database Table

noboost4you
06-30-2008, 03:15 PM
My table populates rows with checkboxes. If the checkboxes are 'checked' and the Edit button is hit, the edit.php loads with all the rows that were checked.

Edit.php then allows me to edit certain text fields. However, if multiple rows were edited and the 'Update' button was hit, only the last row will update.

Here is edit.php ====
<?php

$con = mysql_connect("host", "user", "pass");
if (!$con)
{
die('Could not connect: ' .mysql_error());
}

mysql_select_db("dbname", $con);
echo "<body bgcolor='#E1E1E1'><font face='arial'>";
echo "<style type='text/css'>
body { background: #E1E1E1 url('bg.png');
background-attachment: fixed;
background-repeat: no-repeat;
background-position: center;
}
</style>";



if ($_POST['edit'])

{

foreach($_POST['edit'] as $key => $RequestNum)

{

// Get data from user with the specified id
$result = mysql_query("SELECT * FROM request WHERE RequestNum='".(int)$RequestNum."'") or die(mysql_error());

$row = mysql_fetch_array($result);

echo "<form method='post' action=''>
<table width='450'>
<tr>
<td>Request Number:</td>
<td><input type='text' name='requestnum' id='requestnum' value='$row[RequestNum]' size='10' readonly /></td>
</tr>
<tr>
<td>Part Number:</td>
<td><input type='text' name='partnumber' id='partnumber' value='$row[PartNumber]' readonly /></td>
</tr>
<tr>
<td>Customer:</td>
<td><input type='text' name='customer' id='customer' value='$row[Customer]' readonly /></td>
</tr>
<tr>
<td>Quantity:</td>
<td><input type='text' name='quantity' id='quantity' value='$row[Quantity]' readonly /></td>
</tr>
<tr>
<td>Date Required:</td>
<td><input type='text' name='daterequired' id='daterequired' value='$row[DateRequired]' readonly /></td>
</tr>
<tr>
<td><b>Confirmed Date:</b></td>
<td><input type='text' name='confirmed' id='confirmed' value='$row[Confirmed]' /></td>
</tr>
<tr>
<td><b>Delivery Method:</b></td>
<td><input type='text' name='delivery' id='delivery' value='$row[Delivery]' /></td>
</tr>
<tr>
<td colspan='2'></td>
</tr>
</table>";


}

echo "<br /><input type='submit' name='Update' value='Update' /> </form>";

}




if ($_POST['Update']) {
$update="UPDATE request SET Confirmed='$_POST[confirmed]', Delivery='$_POST[delivery]' WHERE RequestNum='$_POST[requestnum]'";


mysql_query($update) or die ('Error Updating Data! <br />' .mysql_error());

echo "<center>Update successful</center>";
echo "<META HTTP-EQUIV='Refresh' CONTENT='1; URL=result.php'>";

}


if (!$_POST['edit'])

{

//echo "<center>Please select at least one row to edit";
echo "<META HTTP-EQUIV='Refresh' CONTENT='1; URL=result.php'>";

}


?>


Any ideas? I've wrapped my brain around this far too long with little progress.
tfk11
07-01-2008, 01:33 PM
if you append "[]" to the names of your form inputs php will parse the posted values into an array. eg.

<input name="requestnum[]" type="text" value="1" />
<input name="requestnum[]" type="text" value="0" />

$_POST['requestnum'] === array("1","0");

put a loop around your update query to handle all the data in the arrays.

foreach( $_POST['requestnum'] as $key => $value ) {
UPDATE request SET partnumber[$key] = '$partnumber[$key], etc... WHERE requestnum = '$value'

you might also want to hide the requestnum from the form if you intend to use it as the primary key.
noboost4you
07-01-2008, 02:28 PM
Thanks for the reply.

Taking what you said, is the following code along the lines of what you were talking about? Note: I only want to edit Confirmed Date and Delivery Method.

<?php

$con = mysql_connect("host", "user", "pass");
if (!$con)
{
die('Could not connect: ' .mysql_error());
}

mysql_select_db("dbname", $con);
echo "<body bgcolor='#E1E1E1'><font face='arial'>";
echo "<style type='text/css'>
body { background: #E1E1E1 url('bg.png');
background-attachment: fixed;
background-repeat: no-repeat;
background-position: center;
}
</style>";



if ($_POST['edit'])

{

foreach($_POST['edit'] as $key => $RequestNum)

{

// Get data from user with the specified id
$result = mysql_query("SELECT * FROM request WHERE RequestNum='".(int)$RequestNum."'") or die(mysql_error());

$row = mysql_fetch_array($result);

echo "<form method='post' action=''>
<table width='450'>
<tr>
<td>Request Number:</td>
<td><input type='hidden' name='requestnum[]' id='requestnum' value='$row[RequestNum]' size='10' readonly /></td>
</tr>
<tr>
<td>Part Number:</td>
<td><input type='text' name='partnumber' id='partnumber' value='$row[PartNumber]' readonly /></td>
</tr>
<tr>
<td>Customer:</td>
<td><input type='text' name='customer' id='customer' value='$row[Customer]' readonly /></td>
</tr>
<tr>
<td>Quantity:</td>
<td><input type='text' name='quantity' id='quantity' value='$row[Quantity]' readonly /></td>
</tr>
<tr>
<td>Date Required:</td>
<td><input type='text' name='daterequired' id='daterequired' value='$row[DateRequired]' readonly /></td>
</tr>
<tr>
<td><b>Confirmed Date:</b></td>
<td><input type='text' name='confirmed[]' id='confirmed' value='$row[Confirmed]' /></td>
</tr>
<tr>
<td><b>Delivery Method:</b></td>
<td><input type='text' name='delivery[]' id='delivery' value='$row[Delivery]' /></td>
</tr>
<tr>
<td colspan='2'></td>
</tr>
</table>";


}

echo "<br /><input type='submit' name='Update' value='Update' /> </form>";

}




if ($_POST['Update']) {

$_POST['requestnum'] === array('$row[Confirmed]','$row[Delivery]');

foreach( $_POST['requestnum'] as $key => $value ) {
$update="UPDATE request SET confirmed[$key]='$confirmed[$key]', delivery[$key]='$delivery[$key]' WHERE requestnum='$value'";


mysql_query($update) or die ('Error Updating Data! <br />' .mysql_error());

}
echo "<center>Update successful</center>";
echo "<META HTTP-EQUIV='Refresh' CONTENT='1; URL=result.php'>";

}


if (!$_POST['edit'])

{

//echo "<center>Please select at least one row to edit";
echo "<META HTTP-EQUIV='Refresh' CONTENT='1; URL=result.php'>";

}


?>

Thanks again
noboost4you
07-01-2008, 02:37 PM
Error:
Error Updating Data!
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[0]='', confirmed[0]='', delivery[0]='' WHERE requestnum='14'' at line 1

It's being thrown off by the confirmed[$key]='$confirmed[$key]', delivery[$key]='$delivery[$key]'

I then removed the [$key] before the = and tried it again. This time it removed whatever was in those text fields resulting in a blank Confirmed and Delivery field. So that obviously wasn't a fix.
tfk11
07-01-2008, 03:17 PM
Sorry, appending [$key] to the field names was a mistake on my part. Removing them was correct. Try this.


$update = "UPDATE request SET confirmed = '{$confirmed[$key]}', delivery = '{$delivery[$key]}' WHERE requestnum = '$value' ";


All similar references should also be updated.

" value='$row[Customer]' " should be " value='{$row[Customer]}' "

As a safer query test just echo the queries or dump them into a text file rather than executing them so you can have a look. The usual input validation should also be done on every value before it is added to a query to protect the database from any undefined or malicious data.
noboost4you
07-01-2008, 03:50 PM
Very speedy with the replies, really appreciate that.

With what you just suggested, I am no longer receiving an error. Now, whatever information I put into the text fields returns empty fields upon hitting Update.

Code:
echo "<body bgcolor='#E1E1E1'><font face='arial'>";
echo "<style type='text/css'>
body { background: #E1E1E1 url('bg.png');
background-attachment: fixed;
background-repeat: no-repeat;
background-position: center;
}
</style>";



if ($_POST['edit'])

{

foreach($_POST['edit'] as $key => $RequestNum)

{

// Get data from user with the specified id
$result = mysql_query("SELECT * FROM request WHERE RequestNum='".(int)$RequestNum."'")

or die(mysql_error());

//mysql_query("SELECT * FROM request WHERE RequestNum='".(int)$RequestNum."'") or

die(mysql_error());

$row = mysql_fetch_array($result);

echo "<form method='post' action=''>
<table width='450'>
<tr>
<td></td>
<td><input type='hidden' name='requestnum[]' id='requestnum'

value='$row[RequestNum]' readonly /></td>
</tr>
<tr>
<td>Part Number:</td>
<td><input type='text' name='partnumber' id='partnumber'

value='$row[PartNumber]' readonly /></td>
</tr>
<tr>
<td>Customer:</td>
<td><input type='text' name='customer' id='customer'

value='$row[Customer]' readonly /></td>
</tr>
<tr>
<td>SO#:</td>
<td><input type='text' name='so' id='so' value='$row[SO]' readonly

/></td>
</tr>
<tr>
<td>Quantity:</td>
<td><input type='text' name='quantity' id='quantity'

value='$row[Quantity]' readonly /></td>
</tr>
<tr>
<td>Date Required:</td>
<td><input type='text' name='daterequired' id='daterequired'

value='$row[DateRequired]' readonly /></td>
</tr>
<tr>
<td><b>Confirmed Qty:</b></td>
<td><input type='text' name='confirmedqty[]' id='confirmedqty'

value='{$row[ConfirmedQty]}' /></td>
</tr>
<tr>
<td><b>Dock Date:</b></td>
<td><input type='text' name='confirmed[]' id='confirmed'

value='{$row[Confirmed]}' /></td>
</tr>
<tr>
<td><b>Delivery Method:</b></td>
<td><input type='text' name='delivery[]' id='delivery'

value='{$row[Delivery]}' /></td>
</tr>
<tr>
<td colspan='2'></td>
</tr>
</table>";



}

echo "<br /><input type='submit' name='Update' value='Update' /> </form>";

}




if ($_POST['Update']) {

$_POST['requestnum'] === array('$row[ConfirmedQty]','$row[Confirmed]','$row[Delivery]');

foreach($_POST['requestnum'] as $key => $value)

{

$update="UPDATE request SET confirmedqty='{$confirmedqty[$key]}', confirmed='{$confirmed[$key]}', delivery='{$delivery[$key]}' WHERE requestnum='$value'";


mysql_query($update) or die ('Error Updating Data! <br />' .mysql_error());

}

echo "<center>Update successful</center>";
echo "<META HTTP-EQUIV='Refresh' CONTENT='1; URL=result.php'>";

}


if (!$_POST['edit'])

{

//echo "<center>Please select at least one row to edit";
echo "<META HTTP-EQUIV='Refresh' CONTENT='1; URL=result.php'>";

}


?>

Also, it's almost as if $_POST['requestnum'] === array('$row[ConfirmedQty]','$row[Confirmed]','$row[Delivery]'); does not do anything. I completely removed it a minute after your last update, and it still returns the same empty fields.

Also added another field for editing/updating --> Confirmed Qty.
noboost4you
07-01-2008, 04:02 PM
$update = "UPDATE request SET confirmed = '{$confirmed[$key]}', delivery = '{$delivery[$key]}' WHERE requestnum = '$value' ";

Shouldn't I call out what $confirmed and $delivery equal in the code somewhere?
tfk11
07-01-2008, 04:25 PM
do a var_dump($_POST); to see what you're receiving from the form.
noboost4you
07-02-2008, 07:02 AM
Stick var_dump($_POST); at the end of the code before the ?> results in

array(2) { ["edit"]=> array(1) { [0]=> string(2) "20" } ["act"]=> string(4) "Edit" }
noboost4you
07-02-2008, 08:14 AM
The Winning Code

<?php

$con = mysql_connect("host", "user", "password");
if (!$con)
{
die('Could not connect: ' .mysql_error());
}

mysql_select_db("database", $con);
echo "<body bgcolor='#E1E1E1'><font face='arial'>";
echo "<style type='text/css'>
body { background: #E1E1E1 url('bg.png');
background-attachment: fixed;
background-repeat: no-repeat;
background-position: center;
}
</style>";



if ($_POST['edit'])

{

foreach($_POST['edit'] as $key => $RequestNum)

{

// Get data from user with the specified id
$result = mysql_query("SELECT * FROM request WHERE RequestNum='".(int)$RequestNum."'") or die(mysql_error());

$i = 0;

$row = mysql_fetch_array($result);

echo "<form method='post' action='' id='update' name='update'>
<table width='450'>
<tr>
<td></td>
<td><input type='hidden' name='requestnum[]' id='requestnum' value='$row[RequestNum]' readonly /></td>
</tr>
<tr>
<td>Part Number:</td>
<td><input type='text' name='partnumber[]' id='partnumber' value='$row[PartNumber]' readonly /></td>
</tr>
<tr>
<td>Customer:</td>
<td><input type='text' name='customer' id='customer' value='$row[Customer]' readonly /></td>
</tr>
<tr>
<td>SO#:</td>
<td><input type='text' name='so' id='so' value='$row[SO]' readonly /></td>
</tr>
<tr>
<td>Quantity:</td>
<td><input type='text' name='quantity' id='quantity' value='$row[Quantity]' readonly /></td>
</tr>
<tr>
<td>Date Required:</td>
<td><input type='text' name='daterequired' id='daterequired' value='$row[DateRequired]' readonly /></td>
</tr>
<tr>
<td><b>Confirmed Qty:</b></td>
<td><input type='text' name='confirmedqty[]' id='confirmedqty' value='$row[ConfirmedQty]' /></td>
</tr>
<tr>
<td><b>Dock Date:</b></td>
<td><input type='text' name='confirmed[]' id='confirmed' value='$row[Confirmed]' /></td>
</tr>
<tr>
<td><b>Delivery Method:</b></td>
<td><input type='text' name='delivery[]' id='delivery' value='$row[Delivery]' /></td>
</tr>
<tr>
<td colspan='2'></td>
</tr>
<br /><br />
</table>";

++$i;

}

echo "<br /><input type='submit' name='Update' value='Update' /></form>";

}

if ($_POST['Update'])

{

$size = count($_POST['requestnum']);
$i = 0;
while ($i < $size) {
$requestnum = $_POST['requestnum'][$i];
$confirmed = $_POST['confirmed'][$i];
$confirmedqty = $_POST['confirmedqty'][$i];
$delivery = $_POST['delivery'][$i];
$partnumber = $_POST['partnumber'][$i];

$update="UPDATE request SET Confirmed='$confirmed', Delivery='$delivery', ConfirmedQty='$confirmedqty' WHERE RequestNum='$requestnum' LIMIT 1";

mysql_query($update) or die ('Error Updating Data! <br />' .mysql_error());


echo "<center>Updated <b>".$partnumber."</b> successfully</center>";
echo "<META HTTP-EQUIV='Refresh' CONTENT='1; URL=result.php'>";

++$i;

}
}

if (!$_POST['edit'])

{

//echo "<center>Please select at least one row to edit";
echo "<META HTTP-EQUIV='Refresh' CONTENT='1; URL=result.php'>";

}


?>



Thanks again