SQL string error with PHP

gmrobert

Why do I get an error with:

$query = "INSERT INTO ".$Table." (UserID, RecID, Dirty, Deleted, ".$Fields.") VALUES (".$UserID.", ".$RecID.", ".$Dirty.", ".$Deleted.", ".$Data.")";

but not (when I add extra quotes around $Data):
$query = "INSERT INTO ".$Table." (UserID, RecID, Dirty, Deleted, ".$Fields.") VALUES (".$UserID.", ".$RecID.", ".$Dirty.", ".$Deleted.", '".$Data."')";

I would like to pass the fields in $Fields (ie. $Fields = "Fld1, Fld2, Fld3")

And pass the data in $Data (ie. $Data = "Field1Data, Field2Data, Field3Data")

I can pass the info in $Fields, but I can't figure out how to pass the same sting to $Data.

Can anyone help with this?

Thanks

Greg

ClarkF1

You need single quotes around the items in $Data, not $Data itself

You need single quotes round all the fields in the VALUES section of the query if they are non-numeric

$Fields = "Fld1, Fld2, Fld3";

$Data = "'Field1Data', 'Field2Data', 'Field3Data'");

$query = "INSERT INTO ".$Table." (UserID, RecID, Dirty, Deleted, ".$Fields.") VALUES (".$UserID.", ".$RecID.", ".$Dirty.", ".$Deleted.", ".$Data.")";

gmrobert

If I try to deliminate in the FORM the text in $Data with a ', I get an echo of:

INSERT INTO TestData (UserID, RecID, Dirty, Deleted, Fld1, Fld2) VALUES (12345, 95683, 1, 0, \"Fld1data\", \"Fld2data\")

But I still get and error of:

Query failed: 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 '\"Fld1data\", \"Fld2data\")' at line 1 --------- Array

Is there a way I can change the \" to '

Thanks

Greg

ClarkF1

The only thing I can suggest is storing them in an array and loop through the array delimiting each element and adding it to the query string

e.g

$Data = array("Field1Data", "Field2Data", "Field3Data");

$query = "INSERT INTO ".$Table." (UserID, RecID, Dirty, Deleted, ".$Fields.") VALUES ('".$UserID."', '".$RecID."', '".$Dirty."', '".$Deleted."',"; 

foreach ($Data as $key => $value)
{
  $query .= "'".mysql_real_escape_string($value)."'";
  if ($key != count($Data)-1)
    $query .= ",";  

}
$query .= ")";