saving multiple records help

[samoht]

Hello,

 

I am not sure the best way to approach saving the data that I am displaying.

My display=

 

<?php 
$pprice = get_pprice($ProductId);
for($n = 0; $n < $nClients; $n++)
{
	echo "\n\t<tr class=\"d".($n & 1)."\">\n\t";
	if($pprice[$n]['ClientPriceCode']=='R1'){echo "<td>Retail</td>";}else {echo "<td>Compass</td>";}
	echo "<td><input size=\"7\" name=\"PriceRetail\" type=\"text\" id=\"PriceRetail\" style=\"text-align:right\" value=\"".$pprice[$n]['PriceRetail']. "\"></td>";
	echo "<td><input size=\"7\" name=\"PriceSell\" type=\"text\" id=\"PriceSell\" style=\"text-align:right\" value=\"".$pprice[$n]['PriceSell']. "\"></td>";
	echo "<td><input size=\"7\" name=\"PriceHold\" type=\"text\" id=\"PriceHold\" style=\"text-align:right\" value=\"".$pprice[$n]['PriceHold']. "\"></td>";
	echo "<td><input size=\"7\" name=\"Cost\" type=\"text\" id=\"Cost\" style=\"text-align:right\" value=\"".$pprice[$n]['Cost']. "\"></td>";
	echo "</tr>";
}

 

To save do I need to loop through the prices?

Do I need a unique id on each input??

 

please help  ???

[MadTechie]

change

name=\"PriceRetail\"

to

name=\"PriceRetail[$n]\"

 

same with PriceSell, PriceHold & Cost

 

 

when use $_GET['PriceRetail'][$n] or $_POST['PriceRetail'][$n] to retieve

[samoht]

so how does this know to save the multiple records?

<?php
#Update productprice table
$SQL = sprintf("UPDATE productprice SET Cost=%s, PriceRetail=%s, PriceSell=%s, PriceHold=%s, PriceDate=%s WHERE ProductId=%s AND Pcode=%s",
				GetSQLValueString($_POST[PriceRetail][$n],		"decimal"),
				GetSQLValueString($_POST[PriceSell][$n],		"decimal"),
				GetSQLValueString($_POST[PriceHold][$n],		"decimal"),
				GetSQLValueString($_POST[Cost][$n],			"decimal"),
				GetSQLValueString(date('YmdHis'),			"text"));
$Result = mysql_query($SQL, $connection1)or die(mysql_error());

or did I do it wrong?

[MadTechie]

ok try this

$SQL = "";

for($n=0,$n<count(),$n++)
{
$SQL .= sprintf("UPDATE productprice SET Cost=%s, PriceRetail=%s, PriceSell=%s, PriceHold=%s, PriceDate=%s WHERE ProductId=%s AND Pcode=%s;",
				GetSQLValueString($_POST[PriceRetail][$n],"decimal"),
				GetSQLValueString($_POST[PriceSell][$n],"decimal"),
				GetSQLValueString($_POST[PriceHold][$n],"decimal"),
				GetSQLValueString($_POST[Cost][$n],"decimal"),
				GetSQLValueString(date('YmdHis'),"text"));
}
//echo $SQL; //uncomment this and comment the below one to debug
$Result = mysql_query($SQL, $connection1)or die(mysql_error());

 

note the ; at the end Pcode=%s; you need that

basically you have lots of statements seperated by semicolons ( ; ) each statement is processed as its own.

[samoht]

I get this warning

 

 

Warning: count() expects at least 1 parameter, 0 given in C:\websites\Clients\BCDist\html\admin\productsedit.php on line 187

 

I filled in the prices for the record too?

[MadTechie]

opps

for($n=0,$n<count($_POST[PriceRetail]),$n++)

[samoht]

Ok  -

 

I thought I had it working fine. While debugging it looked like my sql was just what I wanted but when I tried it I got this error:

 

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 ';UPDATE productprice SET PriceRetail=2.00, PriceSell=2.00, PriceHold=2.00, Cost=' at line 1

 

I can't see anything wrong??

[samoht]

Interestingly enough the error was on the second pass ?

 

This is my total string:

 

UPDATE productprice SET PriceRetail=3.00, PriceSell=3.00, PriceHold=3.00, Cost=1.00 WHERE ProductId=2 AND ClinetPriceCode='R1';UPDATE productprice SET PriceRetail=2.00, PriceSell=2.00, PriceHold=2.00, Cost=1.00 WHERE ProductId=2 AND ClinetPriceCode='W1';

 

I wonder if the ending ';' is the problem?

 

[MadTechie]

try this

$SQL .= sprintf("UPDATE productprice SET (Cost='%s', PriceRetail='%s', PriceSell='%s', PriceHold='%s', PriceDate='%s') WHERE ProductId=%s AND Pcode=%s;",
GetSQLValueString($_POST[PriceRetail][$n],"decimal"),
GetSQLValueString($_POST[PriceSell][$n],"decimal"),
GetSQLValueString($_POST[PriceHold][$n],"decimal"),
GetSQLValueString($_POST[Cost][$n],"decimal"),
GetSQLValueString(date('YmdHis'),"text"));

 

added ( and '