...

View Full Version : mysql update error



rafiki
06-03-2007, 07:39 PM
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 'dob=`''1969-12-31'' WHERE userid = 5' at line 1
php code ==
$sql .= "`dob =`'$dob' WHERE userid = {$_SESSION['userid']}";
its worth rep :) for the first correct solution

twomt
06-03-2007, 07:41 PM
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 'dob=`''1969-12-31'' WHERE userid = 5' at line 1
php code ==
$sql .= "`dob =`'$dob' WHERE userid = {$_SESSION['userid']}";
its worth rep :) for the first correct solution


$sql .= "SET `dob` = '$dob' WHERE `userid` = {$_SESSION['userid']}";

might be me, but I would have the quote before the = and not after

rafiki
06-03-2007, 08:05 PM
no i have the same error
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 'dob` = ''1969-12-31'' WHERE userid = 5' at line 1


$sql = "UPDATE users set ";
foreach($_POST as $key=>$val)
{
if(in_array($key, $validFields))
{
$updateArray[] =" $key = ".sqlProtect($val);
}
}
$dob = sqlProtect($dob);
$sql .= implode(", ", $updateArray);
$sql .= "`dob` = '$dob' WHERE userid = {$_SESSION['userid']}";
$result = mysql_query($sql);
is the whole query

mr e
06-03-2007, 08:20 PM
It looks like $dob is a string that's already surrounded by single quotes, so surrounding it again is giving you an error, I would try removing the single quotes around it in your query


$sql .= "`dob` = $dob WHERE userid = {$_SESSION['userid']}";

rafiki
06-03-2007, 08:45 PM
:( removed the single quotes and still getting error :(

mr e
06-03-2007, 09:42 PM
Change $result = mysql_query($sql); to $result = mysql_query($sql) or die(mysql_error()); and show us what the actual query looks like, I bet it has something to do with the array you're imploding

rafiki
06-03-2007, 09:55 PM
still showing me the same error with die(mysql_query())

mr e
06-03-2007, 10:00 PM
Ah duh sorry, this is what I meant to post


$result = mysql_query($sql) or die(mysql_error() ."\n". $sql);


What's it say $sql is?

rafiki
06-03-2007, 10:13 PM
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 'dob` = '1969-12-31' WHERE userid = 5' at line 1 UPDATE users set education = 'highschool16', employment = 'unemployed', ms = 'single', children = '0', income = '<10'`dob` = '1969-12-31' WHERE userid = 5
thats what it says :(

PappaJohn
06-03-2007, 10:16 PM
Missing a comma


$sql .= ", `dob` = '$dob' WHERE userid = {$_SESSION['userid']}";

mr e
06-03-2007, 10:21 PM
Now what you need to do is make sure there isn't a comma there if $updateArray is empty, or it'll break again

rafiki
06-03-2007, 10:26 PM
rep added accordingly

twomt
06-03-2007, 11:36 PM
function updateUsers( $userID, $updateArray ) {

$sql = "update users set ";

foreach( $updateArray as $column=>$value ) {
$value = addslashes($value);

if ( is_string($value) && (strlen($value) > 1) && ($value[0] == "@") ) {
$updateSQL[] = "$column=". substr($value, 1);
} else {
$updateSQL[] = "$column='$value'";
}
}

$sql.= implode(", ", $updateSQL) . " where userid=$userID";
return mysql_query($sql);
}

Now you can call this as follows:


updateUsers( {$_SESSION['userid']}, "dob"=>"$dob" );
should work..... ;)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum