Binary Data in MySQL

How do I store binary data in MySQL?

This question and answers originated from www.stackoverflow.com
Question by (8/1/2008 5:09:55 AM)

Answer

For a table like this:

CREATE TABLE binary_data (
id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
description CHAR(50),
bin_data LONGBLOB,
filename CHAR(50),
filesize CHAR(50),
filetype CHAR(50)
);

Here is a PHP example:

<?php

// store.php3 - by Florian Dittmer <dittmer@gmx.net>
// Example php script to demonstrate the storing of binary files into
// an sql database. More information can be found at http://www.phpbuilder.com/
?>

<html>
<head><title>Store binary data into SQL Database</title></head>
<body>

<?php
// code that will be executed if the form has been submitted:

if ($submit) {

    // connect to the database
    // (you may have to adjust the hostname,username or password)

    MYSQL_CONNECT("localhost","root","password");
    mysql_select_db("binary_data");

    $data = mysql_real_escape_string(fread(fopen($form_data, "r"), filesize($form_data)));

    $result=MYSQL_QUERY("INSERT INTO binary_data (description,bin_data,filename,filesize,filetype) ".
        "VALUES ('$form_description','$data','$form_data_name','$form_data_size','$form_data_type')");

    $id= mysql_insert_id();
    print "<p>This file has the following Database ID: <b>$id</b>";

    MYSQL_CLOSE();

} else {

    // else show the form to submit new data:
?>

    <form method="post" action="<?php echo $PHP_SELF; ?>" enctype="multipart/form-data">
    File Description:<br>
    <input type="text" name="form_description"  size="40">
    <input type="hidden" name="MAX_FILE_SIZE" value="1000000">
    <br>File to upload/store in database:<br>
    <input type="file" name="form_data"  size="40">
    <p><input type="submit" name="submit" value="submit">
    </form>

<?php

}

?>

</body>
</html>
Answer by
Find More Answers
Related Topics  mysql  database  binary-data  data-storage
Related Questions

  • Binary data in MySQL

    I need to store binary data, such as 1110000 , in MySQL. When I select it, I need the return value to be the same 1110000 again. What data type should I use? Can I use bit? Or would varbinary be …

  • Set binary data using setblob in mysql connector/c++ causes crash

    I tried to use setBlob() as follows: class DataBuf : public streambuf { public: DataBuf(char * d, size_t s) { setg(d, d, d + s); } }; char b[20]; DataBuf buffer((char*)b, 20); istream s…

  • storing binary data in mysql

    I have a PDF file on a local machine. I want to upload this file into a BINARY BLOB on a SQL database. Other approaches mentioned here [ http://efreedom.com/Question/1-17/binary-data-in-mysql ] all …

  • Store images in database as binary data

    What's the best way to store an image in a database in binary format, and how can I implement it using C#? Thanks in advance for any help.

  • MySql raw binary data files copy 5.0 > 5.1

    We are in the process of preparing a data migration of some fairly big DB (200GB), InnoDB tables from our old server to the new servers. The data is very index heavy and trying to migrate the data v…

  • Why store binary data in MySQL?

    I'm a little confused - what are the pros of storing binary data in DB? Is it for security reasons, or there are some more complicated motives i don't see? Thanks for your time.

  • mysql how to concat/append binary data type

    I am trying to write a stored function which will return BINARY(20) type. I would like to format this return value, by putting string, itn, float values in it. But I couldn't figure it out how can I…

  • Searching Binary Data in Ruby

    Using only pure ruby (or justifiably commonplace gems) is there an efficient way to search a large binary document for a specific string of bytes? Deeper context: the mpeg4 container format is…

  • Storing and matchig binary data in mysql

    I'm trying to store different mutiple select field states in the db. So one select list would be: <option value="1">a1</option> <option value="2">a2</option> <option va…

  • Would you store binary data in database or in file system?

    This is a question which has been asked before ( large-text-and-images-in-sql ) but mainly for data which will be changed. In my case the data will be stored and never changed. Just seems sensible t…