CODEWITHSUNDEEP
phpmysqlsqlblob
Mitch
Mitch

Reputation: 1

Update blob in MySQL using PHP

I can't figure out what is wrong with my SQL. Here is the PHP script that generates the query:

function SaveData($data,$id,$file)
{
    $handle = fopen($file['file']['tmp_name'], 'r');
    $datafile = fread($handle, filesize($file['file']['tmp_name']));
    $datafile = mysql_real_escape_string($datafile);
    fclose($handle);        
    $query= "UPDATE data SET Text='" . $data['Text'] . "', Binary='$datafile', Tag='" . $data['Tag'] . "', name='" . $file['file']['name'] . "',type='" . $file['file']['type'] . "', size='" . $file['file']['size'] . "' WHERE Object_ID=".$id;

    mysql_query($query,$this->connection) or die(mysql_error());
}

If 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 'Binary='%PDF-1.5\r%âãÏÓ\r\n37 0 obj\r<

Can anyone give me some pointers?

Upvotes: 0

Views: 4550

Answers (3)

NullUserException
NullUserException

Reputation: 85468

In addition to the syntax error pointed out by the other answers, you can't just shove a variable with binary data into an SQL query like that. You'll have to convert it to a hex string first. 

function SaveData($data,$id,$file)
{
    $handle = fopen($file['file']['tmp_name'], 'rb'); // note the b here
    $datafile = fread($handle, filesize($file['file']['tmp_name']));
    $contents = bin2hex(($datafile)); // no need to escape this
    fclose($handle);        
    $query= "UPDATE data SET Text='" . $data['Text'] . "', `Binary`=x'$contents'; 
    // rest of the code trimmed
}

Also note that your field must be a BLOB to accept binary data.

Upvotes: 0

Shakti Singh
Shakti Singh

Reputation: 86406

Binary is the reserved word

Try to enclose it in back quotes 

`Binary`='$datafile',

Here is the list of keywords Mysql Keywords

Upvotes: 0

Pekka
Pekka

Reputation: 449663

BINARY is a reserved word in mySQL.

You need to use backticks or change the column name.

`Binary` = '$datafile'

Upvotes: 1

Related Questions