Support NeoGAF

heavenly · Nov 18, 2004

How do I put a binary file such as a mp3, gif, or jpg file in MySql? I understand there are binary data types in MySql to hold sound or picture data, but I'm confuse on how you actually use them.

For example, let's say I created this table to hold mp3 files...

create table mp3 (
mp3ID int not null auto_increment primary key,
mp3_name MEDIUMBLOB
);

Now, let's say I created a SQL file to input the files into the database...

insert into table mp3 values
(NULL, [insert file here]);

My question is "What exactly do I put in the bracket where it says 'insert file here'?".

Thanks in advance.

heavenly · Nov 19, 2004

Oops! Sorry! Wrong board. I meant to post this in the Off-Topic forum.

hooo · Nov 19, 2004

try googling BLOB's. I don't know offhand, but I'd guess the binary stream encoded as some sort of text format.

heavenly · Nov 19, 2004

I thought that originally, but wouldn't that make the SQL file messy with the funky binary data translated to a text format?

Cherubae · Nov 19, 2004

I prefer making links to the data files (on the server) inside of the tables rather than throwing in the entire file.

heavenly · Nov 19, 2004

That's the advice I'm seeing on various sites after doing some research on it. And it's probably the route I'll take, but I would still like to know how to input a whole file into the MySQL database? Can you help with that?

maharg · Nov 19, 2004

Read the file, use the mysql function to make sure it's quoted properly (mysql_quote in php, I think, for example), and then just put it in like any other string.

Yes, that's how you do it. That means your files better not be very big or you'll be locking things in your database for longer than you should. And transfers will be fairly slow.

Using a separate file has its own problems, like ensuring a unique naming of the file and doing your own locking if it requires updating at some point and you want your site to be able to run on separate servers.

Phoenix · Nov 19, 2004

Get one big text field, base64 encode the file (turns it into nice text), shove that text in the field

Blackbird · Nov 19, 2004

heavenly said:
create table mp3 (
mp3ID int not null auto_increment primary key,
mp3_name MEDIUMBLOB
);

see the not-null?

heavenly said:
insert into table mp3 values
(NULL, [insert file here]);

and what do you try to insert? :-/

ourumov · Nov 19, 2004

Try this:

INSERT INTO MP3 (mp3ID,mp3_name) VALUES (NULL, [insert file here]);

It would be like this with the mp3 Super Mp3:

INSERT INTO MP3 (mp3ID,mp3_name) VALUES (NULL, 'Super Mp3');

heavenly · Nov 19, 2004

That's for the assistance and explanations. What's the benefits and disadvantages of having binary data stored in the database, rather than storing just the links or references. I know someone said that you would have to worry about having unique names and locking the file yourself when updating it. I mean, mp3 files, for instance, can be very huge when encoded into text, so that would make that 'insert 'SQL file very huge and messy looking, correct?

For example, I looked online and came across an 'insert' SQL file for storing employee's information along with their picture in the database, and this is how the file looked.

Code:

CREATE TABLE emps (
  emp_id int(11) NOT NULL auto_increment,
  dep_id int(11) default NULL,
  name varchar(100) default NULL,
  title varchar(50) default NULL,
  work_phone varchar(50) default NULL,
  home_phone varchar(50) default NULL,
  cell_phone varchar(50) default NULL,
  address varchar(100) default NULL,
  email varchar(50) default NULL,
  picture blob,
  emp_login varchar(20) NOT NULL default '',
  emp_password varchar(20) NOT NULL default '',
  emp_level int(11) default '0',
  manmonth int(11) default NULL,
  PRIMARY KEY (emp_id)
) TYPE=MyISAM;

#
# Dumping data for table 'emps'
#

INSERT INTO emps VALUES 
(1,2,'John Smith','Director','(364) 134-5455',
'(364) 134-5455','(960) 393-3466','','johns@company.com',
[B]'GIF87a*\0\0÷\0\0\0\0\0\0\0@\0\0\0\0ÿ\0 \0\0 @\0 \0 ÿ\0@\0\0@@\0@
\0@ÿ\0`\0\0`@\0`\0`ÿ\0\0\0@\0\0ÿ\0*\0\0*@\0*\0*ÿ\0À
\0\0À@\0À\0Àÿ\0ÿ\0\0ÿ@\0ÿ\0ÿÿ \0\0 \0@ \0 \0ÿ  \0  @    ÿ @\0 @@ 
@ @ÿ `\0 `@ ` `ÿ \0 @  ÿ *\0 *@ * *ÿ À\0 À@ À Àÿ ÿ\0 ÿ@ ÿ ÿÿ@\0\0@\0@@\0@\0ÿ@ \0@ @@ @ ÿ@@\0@@@@@@@ÿ@`\0@`@@`@`ÿ@\0@@
@@ÿ@*\0@*@@*@*ÿ@
À\0@À@@À@Àÿ@ÿ\0@ÿ@@ÿ@ÿÿ`\0\0`\0@
`\0`\0ÿ` \0` @` ` ÿ`@\0`@@`@`@ÿ``\0``@````ÿ`\0`@``ÿ`
*\0`*@`*`*ÿ`À\0`À@`
À`Àÿ`ÿ\0`ÿ@`ÿ`ÿÿ\0\0\0@\0\0ÿ \0 @  
ÿ@\0@@@@ÿ`\0`@``ÿ\0@ÿ*\0*@**ÿÀ
\0À@ÀÀÿÿ\0ÿ@ÿÿÿ*\0\0*\0@*\0*\0ÿ* \0* @* * ÿ*@\0*@@*@*@ÿ*`\0*`@*`*`ÿ*\0*@**ÿ**\0**@****ÿ*À\0*
À@*À*Àÿ*ÿ\0*ÿ@*ÿ*ÿÿÀ\0\0À\0@À\0À\0ÿÀ \0À @À À ÿÀ@\0À@@À@À@ÿÀ`\0À`@À`À`ÿÀ\0À@ÀÀÿÀ*\0À*@À*À*ÿÀÀ
\0ÀÀ@ÀÀÀÀÿÀÿ\
0Àÿ@ÀÿÀÿÿÿ\0\0ÿ\0@ÿ\0ÿ\0ÿÿ \0ÿ @ÿ ÿ 
ÿÿ@\0ÿ@@ÿ@ÿ@ÿÿ`\0ÿ`@ÿ`ÿ`ÿÿ\0ÿ@ÿÿÿÿ*\0ÿ*@ÿ*ÿ*ÿÿÀ\0ÿ
À@ÿÀÿÀÿÿÿ\0ÿÿ@ÿÿÿÿÿ!ù\0\0\0\0\0,\0\0\0\0*\0\0\0{\0ÿ
H°*Á*\\È°¡Ã#B@±¢E)6Ô± Ç;\n?äÈ
/þ³h2¥AcÈ8
!É?0k¾Ô©ð&Ï
[îLÉR\'KB]\"åYÒ(Ó*Lq>]étãS*\'ijµ9U%
M4KEK¶¬Ù³hÓª%\0;'[/B],
'johns','',0,0);

Now, imagine having hundreds of employees, then that file would be unmanageable when updating it manually via file. Of course, this could be remedied by updating it via a web interface. So, is this how most SQL files look when containing binary data?

borghe · Nov 19, 2004

the advantage to having binary data in the db is that you don't have to worry about it being unique, it is much easier to protect, and in many cases it is much easier to reference.

the disadvantage is that it makes the database huge in most respects (especially if you are talking MP3s) and will take the db a much longer time to search through and return results.

linking is always the best way to go when dealing with large binary files. of course you then have to make sure filenames are unique and have to incoporate a specialized way of making the file secure.

I haven't done anywhere near as much PHP as I have ASP. ASP you can use direct file access on the local filesystem and output it with BinaryWrite() (with appropriate application/type headers) to accomplish this. That way the binary file sits safely away from web access and IIS can serve up the files as needed to authorized users.

I'm sure PHP has similar functions, I've just never used them (yet).

Support NeoGAF

Any MySQL experts here?

heavenly

Member

heavenly

Member

hooo

boooy

heavenly

Member

Cherubae

Member

heavenly

Member

maharg

idspispopd

Phoenix

Member

Blackbird

Member

ourumov

Member

heavenly

Member

borghe

Loves the Greater Toronto Area

Similar threads