update query for user profiles not working

Question

I'm trying to allow users to update their profiles and im using the following functions to do so:

function update_user($update_data){
            global $session_MemberID;
            $update = array();
            array_walk($update_data, 'array_sanitize');

            foreach($update_data as $field=>$data){ //loop through update data in update_info.php
                $update[] = '`' . $field . '` = \'' . $data . '\''; 
            }
            //print_r($update);
            //die();
            mysql_query("UPDATE `oddjob` SET " . implode(', ', $update). " WHERE `MemberID` = $session_MemberID") or die (mysql_error());

}

if (logged_in() ===true) {
    $session_MemberID = $_SESSION['MemberID'];//grabbing value from login
    $user_data= user_data($session_MemberID,'MemberID','Name','Address','Postcode','DOB','Mobile','CoinsAvailable','Email','Password','RepeatPassword','OddJobName','Description','DaysAvailable','profile');
    exit();
    }

this is the update page.(Relevant code only)

if (isset($_POST['OddJobName']) && isset($_POST['Description']) && isset($_POST['DaysAvailable']) && empty($errors) === true){//if (empty($_POST) === false && empty($errors) === true) { 
            $daysavailable='';
            foreach ($_POST['DaysAvailable'] as $value)
            {

            $daysavailable .=$value." ";
            }

            $update_data = array (
                'MemberID'      => $MemberID,
                'OddJobName'    => $_POST['OddJobName'],
                'Description'   => $_POST['Description'],
                'DaysAvailable' => $daysavailable, 

                );

                update_user ($update_data);

                if(success){
                 header('Location: member.php?username='.$username);
                 exit ();
                }
            } else if (empty($errors) === false){
                //otherwise output errors
                echo output_errors($errors);
            }


?>

When I enter new info for the user I get the following 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 '' at line 1

when I print out $update I get:

Array ( [0] => `MemberID` = '30' [1] => `OddJobName` = 'test' [2] => `Description` = 'test' [3] => `DaysAvailable` = 'Friday ' )

Which seems fine so im not sure whats wrong with my query. If I put an update query into phpmyadmin like :

UPDATE `oddjob` SET `OddJobName`= test,`Description`=test,`DaysAvailable`=Friday, WHERE `MemberID` = 30

I get the error

#1064 - 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 'WHERE MemberID = 30' at line 1

(The MemberID in the oddjob table is a foreign key. MemberID is a primary key in the member table.)

I am not gifted at this SQL stuff...as you can tell, so please help if you can.

Just as a hint: `$update[] = '\`' . $field . '\` = \'' . $data . '\'';` Could be written as `$update[] = "\`{$field}\`" = '{$data}';` for better readability. — sybear, Apr 07 '13 at 14:00
And encase string-typed data in quotes: `UPDATE oddjob SET OddJobName= 'test',Description='test',DaysAvailable='Friday' WHERE MemberID = 30` — sybear, Apr 07 '13 at 14:02

score 1 · Answer 1 · edited May 25 '19 at 14:24

1

First of all: Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

About your problem, you are using global $session_MemberID, even if you already have that ID in your $update_data array. I bet $session_MemberID is empty, and that's why your first query fails. Try using this query in your code:

$sql = "UPDATE `oddjob` SET " . implode(', ', $update). " WHERE `MemberID` = ".intval($update['MemberID']);

Regarding phpMyAdmin query, there's an error, a trailing comma right before WHERE, try using this query:

UPDATE `oddjob` SET `OddJobName`= 'test',`Description`='test',`DaysAvailable`='Friday' WHERE `MemberID` = 30

edited May 25 '19 at 14:24

Zoe

27,060
21
118
148

answered Apr 07 '13 at 14:06

ulentini

2,413
1
14
26

It seems there is no problem in his code. (No comma before `WHERE`) – sybear Apr 07 '13 at 14:09
Not in the first query, in the query he runs on phpMyAdmin – ulentini Apr 07 '13 at 14:11
Of course, I see that. But he said as well that he get an error when running the script. – sybear Apr 07 '13 at 14:12
I know, that's why I wrote *regarding phpMyAdmin query*. The problem with his script is probably an empty `$session_MemberID` value, as I said – ulentini Apr 07 '13 at 14:15
@Uby your query for phpmyadmin works great thank you. I have tired to print out the query in my `update_user` function before but nothing actually prints, can you recommend a way I could print my query out. Ive tried: echo `"UPDATE `oddjob` SET " . implode(', ', $update). " WHERE `MemberID` = $session_MemberID";` Ive also tired `$query = mysql_query("UPDATE `oddjob` SET " . implode(', ', $update). " WHERE `MemberID` = $session_MemberID") or die (mysql_error()); print_r($query);` – Lairds Apr 07 '13 at 14:30
I added a possibile solution to my answer, try using that query in your code. – ulentini Apr 07 '13 at 15:39

update query for user profiles not working

1 Answers1