Trying to allow user to upload a profile pic to a mysql database

Question

I'm attempting to make a simple web app. I'm at the stage of creating the user profile section of it, and I'm stuck at allowing the user to upload their own profile picture. please see below for the query I'm using.

$username = $_SESSION['username'];

$insertImage = $db->prepare("UPDATE `members` SET `profile_pic` = ('$dbDirectory') WHERE `username` = $username");

$insertImage->execute($imageArray);

I can't work out how to add the username session in to the query correctly. Currently I just get the error

"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'VALUE' in 'where clause'".

If I change my query to this.

$insertImage = $db->prepare("UPDATE `members` SET `profile_pic` = ('$dbDirectory') WHERE `profile_pic` = `profile_pic`");

The same image directory path is inserted into every users 'profile_pic' row.

I'm most probably missing something extremely small here, but I just can't resolve this issue, so would greatly appreciate any guidance/advice. Thanks in advanced

Answer 1

Text variables should be wrapped in quotes ''

$insertImage = $db->prepare("UPDATE `members` 
                             SET `profile_pic` = '$dbDirectory'
                             WHERE `username` = '$username'");

But you should really be using parameters in your prepared queries, to avoid SQL Injection, then you dont need to worry about quoting text variables as it all gets dont by the PDO class, for example

$stmt = $db->prepare("UPDATE `members` 
                             SET `profile_pic` = :pic
                             WHERE `username` = :uname");

$stmt->execute( array(':pic'=> $dbDirectory, ':uname'=>$username') );

Or

$stmt = $db->prepare("UPDATE `members` 
                             SET `profile_pic` = :pic
                             WHERE `username` = :uname");

$stmt->bindParam(':pic', $dbDirectory, PDO::PARAM_STR);
$stmt->bindParam(':uname', $username, PDO::PARAM_STR);

$stmt->execute();