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

?
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()

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()

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());