[SOLVED] error in sql statment, someone spot it o rknow why?

[jesushax]

hi here is my code

 

$ID = $_POST["CompanyID"];
$TSQL = "UPDATE tblDirectory SET";
foreach($_POST as $key => $value){
    $TSQL .= " $key='".str_replace("'","''",$value)."'";
}
$TSQL = $TSQL." WHERE CompanyID='$ID'";

mysql_query($TSQL) or die (mysql_error());

 

debug

 

UPDATE tblDirectory SET CompanyID='65' CompanyName='AJ Limited' CompanyType='Sole Trader' CompanyTel='01482 123456' CompanyAddress='123 there' CompanyAge='' CompanyReg='' PLLimit='1,000,000' ELLimit='' RiskIns='0' DesIndem='0' PlanIndem='0' PrevTurn='Other' DomTurn='80' CIS='C' TB1='Federation of Small Business' TB2='test' TB3='' SiteUni='Y' TJioner='Y' TGeneral='Y' AdminEmp='0' TradeEmp='0' SubEmp='0' WHERE CompanyID='65'

 

here is the error message:

 

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 'CompanyName='AJ Limited' CompanyType='Sole Trader' CompanyTel='01482' at line 1

 

thanks

[trq]

You need a comma between each different field.

 

Also note that mysql does not use '' to escape chars so there is no point replacing single quotes with two single quotes. Use mysql_real_escape_string instead.

[jesushax]

ahhhh seen, cant belive i didnt see it lol

it was probably cos of the foreach loop

first time ive used this method

 

thankyou

[jesushax]

oh if i put the comma in the loop

 

ID = $_POST["CompanyID"];
$TSQL = "UPDATE tblDirectory SET";
foreach($_POST as $key => $value){
    $TSQL .= " $key='".mysql_real_escape_string($value)."', ";
}
$TSQL = $TSQL." WHERE CompanyID='$ID'";

mysql_query($TSQL) or die (mysql_error());

 

then the last value which doesnt need a comma after it, how do i get rid of it when im looping?

 

eg my code now reads

 

where soemthin='something', WHERE ID=...... i dont need that extra comma

[trq]

ID = $_POST["CompanyID"];
$TSQL = "UPDATE tblDirectory SET";
foreach($_POST as $key => $value){
    $TSQL .= " $key='".mysql_real_escape_string($value)."', ";
}
$TSQL = rtrim($TSQL,',');
$TSQL .= " WHERE CompanyID='$ID'";

mysql_query($TSQL) or die (mysql_error());

[jesushax]

rtrim does that stand for righttrim, and will look for the last comma on the right? that how that works?

[jesushax]

that didnt work the comma is still there :S

[trq]

Maybe because there is a space after it, try...

 

$TSQL = rtrim($TSQL,', ');

[jesushax]

that got it

 

thanks alot