SQL string error with PHP

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
gmrobert
Forum Newbie
Posts: 22
Joined: Wed Oct 04, 2006 9:07 am

SQL string error with PHP

Post 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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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')
gmrobert
Forum Newbie
Posts: 22
Joined: Wed Oct 04, 2006 9:07 am

Post 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
gmrobert
Forum Newbie
Posts: 22
Joined: Wed Oct 04, 2006 9:07 am

Post 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
ianhull
Forum Contributor
Posts: 310
Joined: Tue Jun 14, 2005 10:04 am
Location: Hull England UK

Post by ianhull »

try this

Code: Select all

$insertinto = "INSERT INTO TestData (UserID, RecID, Dirty, Deleted, Fld1, Fld2) VALUES (12345, 95683, 1, 0, $Fld1data, $Fld2data")
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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']}')";
Post Reply