...

View Full Version : Multiple row update



dan-dan
11-14-2011, 09:38 PM
Hey. I thought this was going to be simple, but I can't seem to figure out how to go about updating all rows at once.

Here is my form

// Search results


echo "<form method='post' action='multiItem.php'>
<input type='submit' name='saveChanges' value='Save' />

<table border='1' cellspacing='1' id='searchResults'>
<th>SKU</th><th>Location</th><th>Backstock</th><th>B/C</th>
";

while($row = mysql_fetch_assoc($searchresult)) {


if ($row[ItemName] == "") {
$row[ItemName] = "No description available";
}

echo "

<tr>
<input type='hidden' name='sku' value='$row[LocalSKU]' />
<td title='$row[ItemName]' >$row[LocalSKU]</td>
<td>$row[Location]&nbsp;</td>
<td><input type='text' size='60%' name='currentlocation' value='$row[Backstock]' /></td>
<td>$row[Barcode]&nbsp;</td>
</tr>

";

}

echo "</table>
<input type='submit' name='saveChanges' value='Save' />
</form>
<br />";


All I want to do is update currentlocation for each row from the users input. I'm guessing I need to use a loop, but can't construct it.
I have attempted it many times with no luck :(



if($_POST['saveChanges']) {

$updatedCurrentLocation = mysql_real_escape_string(strtoupper(trim(preg_replace( '/\s+/', ' ', ($_POST['currentlocation'])))));
$query = " ";
}

Old Pedant
11-14-2011, 11:50 PM
You have named *ALL* your fields <input name="currentlocation">

PHP can't handle multiple fields with the same name unless they are named to look like an array: <input name="currentlocation[]">

But that's not enough: You need to be able to associate *EACH* of those currentlocation fields with proper SKU.

You probably *could* do that by also doing <input type='hidden' name='sku[]'...
and then traversing the two arrays in parallel, but personally I prefer doing something like this:


<input size="60%" name="currentlocation_$row[LocalSKU]" value="$row[Backstock]" />

(Which also means you can get rid of the hidden fields.)

Then, in the PHP code that processes the form, you need to traverse all the $_POST values looking for those that have a key of the form "current_location_xxx".

I am *NOT* a PHP programmer, at all, but I *THINK* it would be something like this:


foreach ( $_POST as $key => $value)
{
if ( substring($key, 0, 17) == "current_location_" )
{
$sku = mysql_real_escape_string( substring($key,17) ); // the sku found in the key
$newloc = mysql_real_escape_string($value);
$sql = "UPDATE yourtablename SET Backstock = '$newloc' WHERE LocalSKU = '$sku'";
... execute that sql ...
}
}


I'm assuming that $_POST can be treated the same as other associative arrays. Again, ask a PHP guru for help if that doesn't work.

dan-dan
11-15-2011, 08:15 PM
Thanks a lot, it's almost there!

I had to change a couple of things but here is my code now


// Update location

if($_POST['saveChanges']){

foreach ( $_POST as $key => $value)
{
if (substr($key, 0, 16) == "currentlocation_" ){
$sku = mysql_real_escape_string(substr($key,16) ); // the sku found in the key
$updatedCurrentLocation = mysql_real_escape_string(strtoupper(trim(preg_replace( '/\s+/', ' ', ($value)))));
$sql = "UPDATE inventory SET Backstock = '$updatedCurrentLocation' WHERE LocalSKU = '$sku'";
$results = mysql_query($sql);

}}

if(!$results) {
echo "Error " . mysql_error() . "<br />";
}

else {
echo "<script>
window.alert('Update successful');
location.reload(true);
</script>";
}

}


The problem I now get is that my first 5 entries won't update, and I'm assuming that it's because they're beginning numerically (well that's the only thing that stands out anyway).
examples:
3.5-3.5
3.5-EXT-5M

Any ideas?

Thanks again, greatly appreciated :)

Old Pedant
11-15-2011, 11:36 PM
Add some debug:


$sql = "UPDATE inventory SET Backstock = '$updatedCurrentLocation' WHERE LocalSKU = '$sku'";
echo "DEBUG: " . $sql . "<br/>\n";
$results = mysql_query($sql);

see if that shows the problem.

And/or show me the <form> as it appears in the browser. That is, bring up the page in the browser, click VIEW menu, click SOURCE (or PAGE SOURCE) menu item. Find those 5 problem children in the HTML and copy/paste them here (along with maybe 2 that don't cause problems).

dan-dan
11-16-2011, 06:26 PM
And the problem shows itself! My dots are being replaced by underscores! Read up on it and some sources say these can't be used in variables, however my sister program - allowing only single row update - has allowed this :confused:

Is there a way to work around this?

SQL


DEBUG: UPDATE inventory SET Backstock = 'A' WHERE LocalSKU = '3_5-3_5'<br/>
DEBUG: UPDATE inventory SET Backstock = 'B' WHERE LocalSKU = '3_5-EXT-5M'<br/>
DEBUG: UPDATE inventory SET Backstock = 'C' WHERE LocalSKU = '3_5-MINI'<br/>
DEBUG: UPDATE inventory SET Backstock = 'D' WHERE LocalSKU = '3_5-RCA-1M'<br/>
DEBUG: UPDATE inventory SET Backstock = 'E' WHERE LocalSKU = '3_5-RCA-5M'<br/>
DEBUG: UPDATE inventory SET Backstock = 'F' WHERE LocalSKU = 'ALLKIT-APPLE'<br/>
DEBUG: UPDATE inventory SET Backstock = 'G' WHERE LocalSKU = 'APPLE-AV'<br/>


Form results



<tr>
<td title='3.5mm Jack to Jack Stereo Lead - Black 1.2m' >3.5-3.5</td>
<td>P01-070&nbsp;</td>
<td><input type='text' size='60%' name='currentlocation_3.5-3.5' value='1' /></td>
<td>&nbsp;</td>
</tr>



<tr>
<td title=' 3.5mm Jack Extension Cable - 5 Metres' >3.5-EXT-5M</td>
<td>B-24&nbsp;</td>
<td><input type='text' size='60%' name='currentlocation_3.5-EXT-5M' value='3' /></td>
<td>&nbsp;</td>
</tr>



<tr>
<td title='Mini-USB to Female 3.5 Adaptor' >3.5-MINI</td>
<td>P01-049&nbsp;</td>
<td><input type='text' size='60%' name='currentlocation_3.5-MINI' value='HELLO' /></td>
<td>&nbsp;</td>
</tr>



<tr>
<td title='3.5mm Jack to RCA Audio Cable - 1m' >3.5-RCA-1M</td>
<td>C-100&nbsp;</td>
<td><input type='text' size='60%' name='currentlocation_3.5-RCA-1M' value='HELLO' /></td>
<td>&nbsp;</td>
</tr>



<tr>
<td title='3.5mm Jack to RCA Audio Cable - 5m' >3.5-RCA-5M</td>
<td>C-106&nbsp;</td>
<td><input type='text' size='60%' name='currentlocation_3.5-RCA-5M' value='HELLO' /></td>
<td>&nbsp;</td>
</tr>



<tr>
<td title='No description available' >ALLKIT-APPLE</td>
<td>P01-028&nbsp;</td>
<td><input type='text' size='60%' name='currentlocation_ALLKIT-APPLE' value='F' /></td>
<td>89616775968&nbsp;</td>
</tr>


<tr>
<td title='Apple MB128ZA/B Component AV Cable for iPhone & iPod' >APPLE-AV</td>
<td>&nbsp;</td>
<td><input type='text' size='60%' name='currentlocation_APPLE-AV' value='G' /></td>
<td>5027141582306&nbsp;</td>
</tr>

Old Pedant
11-16-2011, 09:12 PM
Ugh...that's some sort of PHP thing.

It's not HTML doing the replacement.

And when I coded up a quicky test in ASP, it worked just fine!

Okay, so we introduce a hack: Replace the periods with an "escape" mechanism when you create the HTML (something like '$$'?).

And then replace the escape with a period when processing the <Form>.

That is:


$fixSKU = str_replace(".","$$",$row[LocalSKU]);
echo '<input size="60%" name="currentlocation_$fixSKU" value="$row[Backstock]" />\n';

or equivalent.

And then:


if (substr($key, 0, 16) == "currentlocation_" ){
$sku = substr($key,16);
$sku = str_replace("$$",".",$sku)
$sku = mysql_real_escape_string($sku);
$updatedCurrentLocation = mysql_real_escape_string(strtoupper(trim(preg_replace( '/\s+/', ' ', ($value)))));
$sql = "UPDATE inventory SET Backstock = '$updatedCurrentLocation' WHERE LocalSKU = '$sku'";
...


If $$ doesn't work, find some weird character combination that does.

************

Thinking about it, this ALMOST makes sense. PHP automatically converts $_POST and $_GET names to variables for you. And so of course illegal variable names have to be converted to legal ones. Okay, that's an excuse. If you don't use the automatic conversions, why would they need to muck with the raw name when you use $_POST?? Oh, well. PHP's method of handling multiply-named fields (requiring [] as part of the field name) is brain dead, so why shouldn't this be, too?

dan-dan
11-16-2011, 10:28 PM
Absolute genius! Works exactly as intended :thumbsup:

I'm pretty new to PHP and MySQL so it would have taken me forever to solve this alone.

Here's the working code:


// Update location

if ($_POST['saveChanges']){

foreach ($_POST as $key => $value)
{
if (substr($key, 0, 16) == "currentlocation_" ){
$sku = substr($key,16);
$sku = str_replace("$$",".",$sku);
$sku = mysql_real_escape_string($sku);
$updatedCurrentLocation = mysql_real_escape_string(strtoupper(trim(preg_replace( '/\s+/', ' ', ($value)))));
$sql = "UPDATE inventory SET Backstock = '$updatedCurrentLocation' WHERE LocalSKU = '$sku'";
$results = mysql_query($sql);

}}

if (!$results) {
echo "Error " . mysql_error() . "<br />";
}

else {
header("Location: success.php");
}

}


Form:


echo "<form method='post' action='multiItem.php'>
<input type='submit' name='saveChanges' value='Save' />

<table border='1' cellspacing='1' id='searchResults'>
<th>SKU</th><th>Location</th><th>Backstock</th><th>B/C</th>
";

while($row = mysql_fetch_assoc($searchresult)) {

if ($row[ItemName] == "") {
$row[ItemName] = "No description available";
}

$fixSKU = str_replace(".","$$",$row[LocalSKU]);

echo "
<tr>
<td title='$row[ItemName]' >$row[LocalSKU]</td>
<td>$row[Location]&nbsp;</td>
<td><input type='text' size='60%' name='currentlocation_$fixSKU' value='$row[Backstock]' /></td>
<td>$row[Barcode]&nbsp;</td>
</tr>

";

}

echo "</table>
<input type='submit' name='saveChanges' value='Save' />
</form>
<br />";


Thanks again Old Pedant.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum