Binary data handling

[mm00]

Hi...

I have a little problem with handling binary data in PHP and a MySQL database. I am trying something like this:

$result = mysql_db_query ("database1","SELECT * FROM table1 WHERE name = \"$name\"");
$row = mysql_fetch_array ($result);
$data = $row[bin_data1];
$result = mysql_db_query ("database1","UPDATE table1 SET name=\"$name\", bin_data1=\"$data\" WHERE name=\"$name\"");

The problem now is, PHP regards $data as a string. And this gives me an SQL error if $data contains special characters (not unlikely in binary data).

Is there a way to force PHP to handle $data as binary object instead of a string in the UPDATE query?

So long,
Marc...

[Sphynx]

For that to happen, you must store the data in the table as a binary type. Blob would suffice, I think, or maybe Longblob.

Is it images you are storing? If so then remember that you'll have to specify the content type as you display them on the page, i.e. Header( "Content-type: image/gif")....whatever's appropriate for your type of binary data.

[mm00]

bin_data1 is a LONGBLOB and the image type is stored. I have no problems storing new images. The images are displayed correctly on the webpage as well, no problem here.
The problem starts at updating. As long as the primary key remains unchanged, the binary data can be accessed. But in this database it may happen that the value of the primary key field changes with all the other information staying the same. So I need to retrieve those informations first, and update the complete set.
As for the example: the name is the primary key, and it changes from "1" to "2", but the associated binary data stays the same. so I try this:

$result = mysql_db_query ("database1","SELECT * FROM table1 WHERE name = \"1\"");
$row = mysql_fetch_array ($result);
$data = $row[bin_data1];
$result = mysql_db_query ("database1","UPDATE table1 SET name=\"2\", bin_data1=\"$data\" WHERE name=\"1\"");

Maybe I am just too dumb to see the problem, but as long as there is no binary data involved, everything works fine. Just if the binary data is there and perhaps only if there is a special character sequence (i.e. \", ), I get an SQL error.
So I need a way to copy the binary data from the BLOB as binary object in a variable and store the content of this binary variable in the BLOB associated with the new entry name="2".
Perhaps I just don't get something there... If so, please correct me. 😉

So long,
Marc...