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

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 ;).

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.