Page 1 of 1

Error with SQL syntax when trying to Update an array

Posted: Thu Dec 21, 2006 4:14 am
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?

Posted: Thu Dec 21, 2006 6:06 am
by Skittlewidth
Put $inv in quotes?

Posted: Thu Dec 21, 2006 9:43 am
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));

geeeeee

Posted: Thu Dec 21, 2006 2:07 pm
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

Posted: Thu Dec 21, 2006 2:35 pm
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());

so

Posted: Thu Dec 21, 2006 2:54 pm
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

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

Posted: Thu Dec 21, 2006 3:04 pm
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());

Re: so

Posted: Thu Dec 21, 2006 3:21 pm
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.

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

Posted: Thu Dec 21, 2006 4:06 pm
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:

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

Posted: Thu Dec 21, 2006 4:12 pm
by Christopher
Jcart wrote:I think you meant implode() instead of explode() :wink:
Excellent catch (BOOM!)

Posted: Thu Dec 21, 2006 8:58 pm
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?)

Posted: Thu Dec 21, 2006 9:50 pm
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());