How to save 'binary' data in MySQL with PHP?

Posted on 2008-11-05
Medium Priority
Last Modified: 2013-12-13
Hi all!

   For a project I must save some binary data in the database. I start with some hexadecimal values like

A5 FF B3 23 DF

etc. This can be anything and the string can get quite long. From this string I take 2 chars every time and translate this hex code into a character. So I get a long string of strange looking chars (mostly displayed as squares on the screen). This is what I want to save into the MySQL database.

So I made a nice update query, nothing fancy... just UPDATE xyz SET abc = [BINARY STRING]
But the thing doesn't seem to work... I don't get an error message and when I echo the query and copy/paste it in MySQL administrator it seems to work fine (although the data is incorrect, because it's trying to insert the squares and all that stuff, not the actual char/code).

I tried escaping it, I've tried base64 stuff etc... the thing just won't update.
Is there anything I could have missed? Are there special ways to insert this kind of data into a MySQL database with PHP?

If more information is needed, please ask; I'll provide ;).

Question by:HidDS

Accepted Solution

Mahdii7 earned 1000 total points
ID: 22891162
What is your table structure? You can store binary data in a BLOB - are you trying to put this in a VARCHAR? Also it would be helpful to have the error message you are receiving.

Are you using phpMyAdmin? If you you can change the table field that you store binary data in to a BLOB by selecting the table, clicking the "structure" tab, selecting the check box next to the appropriate field, and clicking the pencil icon to modify it. Then just change the "Type" drop down to BLOB.

Hopefully I didn't misunderstand you, cheers.
LVL 11

Assisted Solution

NoiS earned 1000 total points
ID: 22891262
2 ways to do it.

For TEXT fields (max 65535 bytes), LONGTEXT fields (max 4GBytes) you must convert to base64 in PHP

$binary_data = file_get_contents($file);
$string_data = base64_encode($binary_data);

and save the contents to the column
$sql = "INSERT INTO table (text_field) VALUES('$string_data')";

For BLOB fields (MAx 65535 bytes) or LONGBLOB (Max 4 GB) just put the contents directly to the SQL
$sql ="INSERT INTO table (text_field) VALUES('".$binary_data ."')";

Have some issues about put binary content in a SQL statement. Sometimes, the binary contents simulate the char QUOTE.
Its better don't use mysal_query function. If you use some Abstraction Layer Like PDO or ADODB, it will escape correctly the content to prevent this kind of trouble.

NOTE FROM the MySQL manual: The maximum size of a BLOB or TEXT object is determined by its type, but the largest value you actually can transmit between the client and server is determined by the amount of available memory and the size of the communications buffers. You can change the message buffer size by changing the value of the max_allowed_packet variable, but you must do so for both the server and your client program

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month16 days, 7 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question