CODEWITHSUNDEEP
phpmysqlpdosql-update
Kez
Kez

Reputation: 209

mysql UPDATE query not working for image upload via php

Trying to write an image upload to mysql via php,
trying to use the user id to UPDATE the row.
However, keeps giving this error:

SQLSTATE[42000]: Syntax error or access violation: 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 '(image_type,image, image_size, image_name) VALUES ('images/1459926006.png','png'' at line 1

Just can't see what is the Syntax error.

PHP:

   function upload(){
/*** check if a file was uploaded ***/
if(is_uploaded_file($_FILES['userfile']['tmp_name']) && getimagesize($_FILES['userfile']['tmp_name']) != false)
    {
    /***  get the image info. ***/
    $size = getimagesize($_FILES['userfile']['tmp_name']);
    /*** assign our variables ***/
    $type = $size['mime'];
    $imgfp = fopen($_FILES['userfile']['tmp_name'], 'rb');
    $size = $size[3];
    $name = $_FILES['userfile']['name'];
    $maxsize = 99999999;


    /***  check the file is less than the maximum file size ***/
    if($_FILES['userfile']['size'] < $maxsize )
        {
        /*** connect to db ***/
        $dbh = new PDO("mysql:host=localhost;dbname=table", 'username', 'password');

                /*** set the error mode ***/
                $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            /*** our sql query ***/
        $stmt = $dbh->prepare("UPDATE users (image_type,image, image_size, image_name) VALUES (?,?,?,?) WHERE user_id=?"); 

        /*** bind the params ***/
        $stmt->bindParam(1, $type);
        $stmt->bindParam(2, $imgfp, PDO::PARAM_LOB);
        $stmt->bindParam(3, $size);
        $stmt->bindParam(4, $name);
        $stmt->bindParam(4, $name);
        $stmt->bindParam(5, $_SESSION['user_id']);

        /*** execute the query ***/
        $stmt->execute();
        }
    else
        {
        /*** throw an exception is image is not of type ***/
        throw new Exception("File Size Error");
        }
    }
else
    {
    // if the file is not less than the maximum allowed, print an error
    throw new Exception("Unsupported Image Format!");
    }
}
?>

Upvotes: 1

Views: 626

Answers (2)

Saty
Saty

Reputation: 22532

Don't use update query as insert query syntax both are different

Change

$stmt = $dbh->prepare("UPDATE users (image_type,image, image_size, image_name) VALUES (?,?,?,?) WHERE user_id=?");

to

$stmt = $dbh->prepare("UPDATE users SET image_type=?, image=?, image_size=1 ,image_name=?  WHERE user_id=?");

And you are binding $stmt->bindParam(4, $name); two times

 $stmt->bindParam(1, $type);
        $stmt->bindParam(2, $imgfp, PDO::PARAM_LOB);
        $stmt->bindParam(3, $size);
        $stmt->bindParam(4, $name);
       // $stmt->bindParam(4, $name);// comment it
        $stmt->bindParam(5, $_SESSION['user_id']);

Check Update query syntax

Upvotes: 2

Gowri Shankar Balasubramaniam
Gowri Shankar Balasubramaniam

Reputation: 163

Your update SQL query syntax is wrong. Change your query as below:

From :

$stmt = $dbh->prepare("UPDATE users (image_type,image, image_size, image_name) VALUES (?,?,?,?) WHERE user_id=?");

Change to:

UPDATE `users`   
   SET `image_type` = ?,
       `image` = ?,
       `image_size` = ?,
       `image_name` = ? 
 WHERE `user_id` = ?

Try it now!!!!!

Upvotes: 1

Related Questions