mysql update error

[rafiki]

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 ==

PHP Code:

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


its worth rep for the first correct solution

[twomt]

Originally Posted by rafiki

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 ==

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]

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

PHP Code:

$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]

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

PHP Code:

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


[rafiki]

removed the single quotes and still getting error

[mr e]

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]

still showing me the same error with die(mysql_query())

[mr e]

Ah duh sorry, this is what I meant to post

PHP Code:

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


What's it say $sql is?

[rafiki]

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]

Missing a comma

PHP Code:

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


[mr e]

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]

rep added accordingly

[twomt]

PHP Code:

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:

PHP Code:

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


should work.....