Trying to update multiple rows

[kclark]

The following code is on a form page that lists all of my products. I want to sen it over to my process page, and update all records in one lick. But I have no idea how to do arrays. My second code posted is my process page. As you will see in my second code, I am using this process page to add new products, update product info, delete products, and trying to get it to update multiples, so I will have a single process page instead of having 4. Everything works except for the update multiples

<form action='processproducts' method="post" name="FormName">
<input type="submit" name="productform" value="Update Pictures">
<?php
$db = JFactory::getDBO();

$result = mysql_query("select * from products order by materialid, price desc")
	or die(mysql_error());
echo "<table border='1'>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
	// Print out the contents of each row into a table
	echo "<tr><td>"; 
	echo $row['model'];
	echo "</td><td>"; 
	echo $row['picture'];
echo "</td><td>";
        echo $row['company'];
echo "</td><td>";
        echo $row['casketid'];echo "
</td><td>";
        echo $row['material'];
	echo "</td></tr>"; 
} 
echo "</table>";
?>
<input type="submit" name="productform" value="Update Pictures">
</table>
</form>

<?php
$db = JFactory::getDBO();

$form=$_POST['casketform'];//figuring out which form is submitted

$casketnumber=$_POST['casketid'];//getting case #

$casketcompany2=$_POST['casketcompany'];
if($casketcompany2 == "1")
$companyname = "aurorapics";//Aurora
elseif($casketcompany2 == "2")
$companyname = "batesvillecaskets";//Batesville
elseif($casketcompany2 == "3")
$companyname = "capitalcity";//Capital City
else
$companyname = "matthews";//Matthews
//line 17
$material=$_POST['material'];
if($material == "Solid Bronze") $materialid = "1";//Solid Bronze
elseif($material == "Solid Copper") $materialid = "2";//Solid Copper
elseif($material == "Basic Stainless Steel") $materialid = "3";//Basic Stainless Steel
elseif($material == "Premium Stainless Steel") $materialid = "3";//Premium Stainless Steel
elseif($material == "16 Gauge Steel") $materialid = "4";//16 Gauge Steel
elseif($material == "18 Gauge Steel") $materialid = "4";//18 Gauge Steel
elseif($material == "19 Gauge Steel") $materialid = "4";//19 Gauge Steel
elseif($material == "20 Gauge Steel") $materialid = "4";//20 Gauge Steel
elseif($material == "Wood") $materialid = "5";//Wood
else $materialid = "6";//Cremation
//line29
//Getting materialid field
if($material == "Solid Bronze") $materialdir = "bronze";
elseif($material == "Solid Copper") $materialdir = "copper";
elseif($material == "Basic Stainless Steel") $materialdir = "stainless";
elseif($material == "Premium Stainless Steel") $materialdir = "stainless";
elseif($material == "16 Gauge Steel") $materialdir = "16gauge";
elseif($material == "18 Gauge Steel") $materialdir = "18gauge";
elseif($material == "19 Gauge Steel") $materialdir = "19gauge";
elseif($material == "20 Gauge Steel") $materialdir = "20gauge";
elseif($material == "Wood") $materialdir = "wood";
else $materialdir = "cremationcasket";

//Material Description (Carbon Caskets Only)
if($material == "Solid Bronze") $gauge = "0";//Solid Bronze
elseif($material == "Solid Copper") $gauge = "0";//Solid Copper
elseif($material == "Basic Stainless Steel") $gauge = "0";//Basic Stainless Steel
elseif($material == "Premium Stainless Steel") $gauge = "0";//Premium Stainless Steel
elseif($material == "16 Gauge Steel") $gauge = "16";//16 Gauge Steel
elseif($material == "18 Gauge Steel") $gauge = "18";//18 Gauge Steel
elseif($material == "19 Gauge Steel") $gauge = "19";//19 Gauge Steel
elseif($material == "20 Gauge Steel") $gauge = "20";//20 Gauge Steel
elseif($material == "Wood") $gauge = "0";//Wood
else $gauge = "0";//Cremation
//line 53

if($casketcompany2 == "1")
$prefix = "a";//Aurora
else
$prefix = "";//the rest

$photo = "images/$companyname/$materialdir/$prefix$casketnumber.jpg";

//update record
if($form == "Edit Casket")
$query ="UPDATE caskets SET casketid='$casketnumber', material='$_POST[material]', color='$_POST[color]',
 intcolor='$_POST[intcolor]', picture='$photo', model='$_POST[model]', price='$_POST[price]', feature1='$_POST[feature1]', 
 feature2='$_POST[feature2]', feature3='$_POST[feature3]', feature4='$_POST[feature4]', materialid='$materialid', 
 interiordesc='$_POST[interiordesc]', pricerange='$_POST[pricerange]', corners='$_POST[corners]', cappanel='$_POST[cappanel]', 
 materialdesc='$gauge', casketcompany='$casketcompany2', gender='$_POST[gender]', oversize='$_POST[oversize]', 
 theme='$_POST[theme]', active='$_POST[active]' where casketid = '$casketnumber'";
//line 65
//Update Pictures
elseif($form == "Update Pictures")
foreach($_POST as $aKey=>$aValue) {
$query  = "UPDATE caskets SET casketid='$casketnumber', material='$_POST[material]', picture='$photo', model='$_POST[model]', 
 materialid='$materialid', materialdesc='$gauge', casketcompany='$casketcompany2', where casketid = '$casketnumber'";
}
//Delete Record
elseif($form == "Delete Casket")
$query = "DELETE from caskets where casketid = '$casketnumber'";
//insert record
else
$query ="INSERT INTO caskets (casketid, material, color, intcolor,
 picture, model, price, feature1, feature2, feature3, feature4, materialid, interiordesc, pricerange, corners, cappanel,
 materialdesc, casketcompany, gender, oversize, theme, active)
VALUES
('$casketnumber','$_POST[material]','$_POST[color]','$_POST[intcolor]','$photo','$_POST[model]','$_POST[price]',
'$_POST[feature1]','$_POST[feature2]','$_POST[feature3]','$_POST[feature4]','$materialid','$_POST[interiordesc]',
'$_POST[pricerange]','$_POST[corners]','$_POST[cappanel]','$gauge','$casketcompany2','$_POST[gender]','$_POST[oversize]',
'$_POST[theme]','$_POST[active]')";

$result = mysql_query($query)
	or	die("Couldn't execute query.");

if($form == "Edit Casket")
echo "<center>The <b><u>$_POST[model]</b></u> record has been updated.</center>";

elseif($form == "Update Pictures")
echo "<center>Pictures Have been Updated.</center>";

elseif($form == "Delete Casket")
echo "<center>The <b><u>$_POST[model]</b></u> record has been deleted.</center>";

else
echo "<center>A record for <b><u>$_POST[model]</b></u> has been added.</center>";

?>

[Emma]

kclark;10954344 wrote:

elseif($form == "Update Pictures")
foreach($_POST as $aKey=>$aValue) {
$query  = "UPDATE caskets SET casketid='$casketnumber', material='$_POST[material]', picture='$photo', model='$_POST[model]', 
 materialid='$materialid', materialdesc='$gauge', casketcompany='$casketcompany2', where casketid = '$casketnumber'";
} 
<snip/>
$result = mysql_query($query) or die("Couldn't execute query.");

Your problem is not with the array. You are over-ridding $query with each set of post values before you do anything with it. Therefore only the last set of information will go to the mysql_query function.

[kclark]

How do I fix that?

[bradgrafelman]

Execute the $query string on each iteration of the loop rather than only once after the loop has finished.

[kclark]

What is the proper way to do that?
I have 3 php books. 1 is 3 inches thick and none help me out with this.

[bradgrafelman]

What, in all of those books, none of them explained cut-and-paste? :p

Just move the call to mysql_query() to be inside of the loop, meaning it gets called on each iteration of the loop where you're building the $query variable.

EDIT: Also note that user-supplied data should never be placed directly into a SQL query string else your code will be vulnerable to SQL injection attacks and/or just plain SQL errors. Instead, you must first sanitize it with a function such as [man]mysql_real_escape_string/man (for string data).

[kclark]

I don't think that I am understanding at all. New to php since March.

//Update Pictures
elseif($form == "Update Pictures")
//foreach($_POST as $aKey=>$aValue) {
$query  = "UPDATE caskets SET casketid='$casketnumber', material='$_POST[material]', picture='$photo', model='$_POST[model]', 
 materialid='$materialid', materialdesc='$gauge', casketcompany='$casketcompany2', where casketid = '$casketnumber'";
$result = mysql_query(foreach($_POST as $aKey=>$aValue))
	or	die("Couldn't execute query.");
}

[bradgrafelman]

Why did you change the argument for [man]mysql_query/man?

All you needed to do was move the original call to mysql_query() to be inside of the foreach loop. Inside that loop, you're creating a $query variable on each iteration. I assume you're doing that because you want to execute each of those queries... thus, you simply call mysql_query() every time you build a new query in the $query variable.

[kclark]

So is it this?

//Update Pictures
elseif($form == "Update Pictures")
foreach(
$query  = "UPDATE caskets SET casketid='$casketnumber', material='$_POST[material]', picture='$photo', model='$_POST[model]', 
 materialid='$materialid', materialdesc='$gauge', casketcompany='$casketcompany2', where casketid = '$casketnumber'";)

I am telling you I am lost on this one.

[bradgrafelman]

I think you need to read the manual page for [man]foreach[/man] (and possibly find a basic tutorial on Google) and understand how a loop works.

The basic structure of a foreach loop is:

foreach($array as $item_in_array) {
    // do stuff with $item_in_array
    // this variable gets updated on each iteration of the loop
}

[kclark]

Am I close with this?

elseif($form == "Update Pictures")
$query  = "UPDATE caskets SET casketid='$casketnumber', material='$_POST[material]', picture='$photo', model='$_POST[model]', 
materialid='$materialid', materialdesc='$gauge', casketcompany='$casketcompany2', where casketid = '$casketnumber'")
foreach($array = mysql_fetch_array($query));

[kclark]

I have looked for the past several hours and this is the best that I have come up with.

//Update Pictures
elseif($form == "Update Pictures")
$updatepictures;
$updatepictures["casketnumber"] = $casketnumber;

foreach($updatepictures as $casketnumber=>$material,$photo,$materialid,$materialdesc){
echo "Name: $material, Age: $photo, $materialid, $materialdesc <br />";
}

[kclark]

This is what I have come up with.
I thing I did what you said.

ll you needed to do was move the original call to mysql_query() to be inside of the foreach loop.

elseif($form == "Update Pictures")
$query  = "UPDATE caskets SET casketid='$casketnumber', material='$_POST[material]', picture='$photo', model='$_POST[model]',
materialid='$materialid', materialdesc='$gauge', casketcompany='$casketcompany2', where casketid = '$casketnumber'";
foreach($query as $casketnumber);