Page 1 of 1

SQL string error with PHP

Posted: Sun Oct 29, 2006 2:49 pm
by 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

Posted: Sun Oct 29, 2006 3:12 pm
by volka

Code: Select all

mysql_query($query) or die(mysql_error().': '.$query);
what does that print?


Strings have to be marked for mysql as well as for php
insert into foo (b,a,r) values (1,2,3)
insert into foo (b,a,r) values ('x','y','z')

Posted: Sun Oct 29, 2006 8:51 pm
by gmrobert
I am using a browser form to model the http POST process I will implement for a handheld computer.

My FORM POST takes info into various text fields ($UserID, $RecID, $Dirty, $Deleted, $Fields, $Data) and then POSTS it to a PHP.


In my PHP code if I try and build the query like this:

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


and echo of $query gives me:

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


But when I try and run the query I get:

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 'data, Fld2 data)' at line 1 --------- Array



If I re-code the PHP to:

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


the echo of $query gives me:

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


I get the following error:

Query failed: Column count doesn't match value count at row 1 --------- Array



Is there anyway I could POST the VALUES into a single comma separated sting in a PHP variable ($Data) and build a larger SQL statement?


Thanks


Greg

Posted: Sun Oct 29, 2006 8:59 pm
by gmrobert
If I try to deliminate in the FORM the text in $Data 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

Posted: Mon Oct 30, 2006 12:20 am
by ianhull
try this

Code: Select all

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

Posted: Mon Oct 30, 2006 1:20 am
by timvw
Apart from INT you have to encapsulate other values in a query with quotes, eg:

Code: Select all

INSERT INTO table (col1, col2, col3) VALUES ('col1', 'col2', 'col3');
Before i build my query i would make sure my data is prepared to be used in a mysql query be using mysql_real_escape_string, eg:

Code: Select all

$col3 = mysql_real_escape_string($_POST['col3']);
But since that makes it hard to remember what and what not has been prepared for use in a mysql query i usually make an array $mysql that contains the prepared values so that becomes:

Code: Select all

$mysql = array();
$mysql['col3'] = mysql_real_escape_string($_POST['col3']);
And then i can build my query as following:

Code: Select all

$query = "INSERT INTO {$table} (col3) VALUES ( '{$mysql['col3']}')";