Error with SQL syntax when trying to Update an array

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
Mythic Fr0st
Forum Contributor
Posts: 137
Joined: Sat Dec 02, 2006 3:23 am
Contact:

Error with SQL syntax when trying to Update an array

Post by Mythic Fr0st »

well im trying to update my array I dont know why I get this
(I labelled all my errors with numbers, so 92 is just my 92nd one)

Error: 92You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':2:{i:0;s:11:"Iron Dagger";i:1;s:6:"Ignite";} WHERE idcount=1'

Code: Select all

$inv=serialize($inv);

mysql_query("UPDATE chars SET inv = $inv
WHERE idcount=$id")or die('Error: 92'.mysql_error());
any help?
User avatar
Skittlewidth
Forum Contributor
Posts: 389
Joined: Wed Nov 06, 2002 9:18 am
Location: Kent, UK

Post by Skittlewidth »

Put $inv in quotes?
clinton
Forum Newbie
Posts: 2
Joined: Thu Dec 21, 2006 9:16 am

Post by clinton »

yes, putting $inv in quotes will fix it.

Code: Select all

$inv=serialize($inv);

mysql_query("UPDATE chars SET inv = '$inv'
WHERE idcount=$id")or die('Error: 92'.mysql_error());
if $inv will have any quotes in it itself, you will have to use addslashes too, then use stripslashes when you get it out of the database.

Code: Select all

$inv = addslashes (serialize ($inv));
Mythic Fr0st
Forum Contributor
Posts: 137
Joined: Sat Dec 02, 2006 3:23 am
Contact:

geeeeee

Post by Mythic Fr0st »

Gee, thanks, erm did I mension '$inv' is an array :P?
cus I got

Notice: Array to string conversion in c:\program files\easyphp1-8\www\mythic aeons\hp.php on line 37

so temporarily i removed the addslashes and stripslashes until someone tells me otherwise:P
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

addslashes and stripslashes are futile. Use database specific functions instead. For mysql that is mysql_real_escape_string.
It takes a string as parameter, not an array. serialize() returns a string. Therefore: first serialize() then mysql_real_escape_string, not the other way round.

Code: Select all

$inv=mysql_real_escape(serialize($inv));
$query = "UPDATE
		chars
	SET
		inv = '$inv'
	WHERE
		idcount=$id";

mysql_query($query)or die(__LINE__.' '.mysql_error());
Mythic Fr0st
Forum Contributor
Posts: 137
Joined: Sat Dec 02, 2006 3:23 am
Contact:

so

Post by Mythic Fr0st »

do that but use unserialize when loading the variable from MySQL, and when updating use serialize?

Code: Select all

$inv=mysql_real_escape(unserialize($inv));

$inv=mysql_real_escape(serialize($inv));
I kinda thought by what you said you wanted me to do this

$inv=serialize(mysql_real_escape($inv));

ill just follow the example
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Error with SQL syntax when trying to Update an array

Post by Christopher »

Code: Select all

$inv=serialize($inv);
$sets = array();
foreach ($inv as $name => $value) {
     $sets[] = "$name='" . mysql_real_escape($value) . "'";
}

mysql_query("UPDATE chars SET " . explode(', ', $sets) . " WHERE idcount=$id")or die('Error: 92'.mysql_error());
(#10850)
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Re: so

Post by volka »

Mythic Fr0st wrote:$inv=serialize(mysql_real_escape($inv));
And what does that do? It passes the array $inv to mysql_real_escape_string(), mysql_real_escape_string($inv). But mysql_real_escape_string() doesn't want an array, it wants a string. Cannot work, must be wrong.
serialize() can handle an array and will return a string. Therefore you must pass the array $inv to serialize() first, it must be serialize($inv). And then this string should be secured for usage in a mysql statement via mysql_real_escape_string(), mysql_real_escape_string(serialze($inv)) it is.

@arborint
$inv=serialize($inv);
$sets = array();
foreach ($inv as $name => $value) {
after the first line $inv is a string and cannot be used by foreach anymore.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Error with SQL syntax when trying to Update an array

Post by John Cartwright »

arborint wrote:

Code: Select all

$inv=serialize($inv);
$sets = array();
foreach ($inv as $name => $value) {
     $sets[] = "$name='" . mysql_real_escape($value) . "'";
}

mysql_query("UPDATE chars SET " . explode(', ', $sets) . " WHERE idcount=$id")or die('Error: 92'.mysql_error());
I think you meant implode() instead of explode() :wink:
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Error with SQL syntax when trying to Update an array

Post by Christopher »

Jcart wrote:I think you meant implode() instead of explode() :wink:
Excellent catch (BOOM!)
(#10850)
Mythic Fr0st
Forum Contributor
Posts: 137
Joined: Sat Dec 02, 2006 3:23 am
Contact:

Post by Mythic Fr0st »

$inv=serialize($inv);
$sets = array();
foreach ($inv as $name => $value) {
$sets[] = "$name='" . mysql_real_escape($value) . "'";
}

mysql_query("UPDATE chars SET " . implode(', ', $sets) . " WHERE idcount=$id")or die('Error: 92'.mysql_error());
Thanks, however, I dont get this? its not even my own variable its some other thing $sets???

whats the point of this? currently the array gets stored fine why does it matter?

and what exactly does $sets[] (the [] part) mean?)
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

[] appends to an array. All this does is build an array of strings like "myfield='myvalue'" and then combines them all with commas in between them. You could also do:

Code: Select all

$sets = '';
foreach ($inv as $name => $value) {
     if ($sets) {
          $sets .= ', ';
     }
     $sets .= "$name='" . mysql_real_escape($value) . "'";
}

mysql_query("UPDATE chars SET $sets WHERE idcount=$id")or die('Error: 92'.mysql_error());
(#10850)
Post Reply