How to unset BLOB with php/mysql?

Question

Cuz, I did it unintentionally. After reading wikipedia I understand the "binary large object" is for large media files, and I'm not saving a media file.

So how does data get stored this way? What's wrong with this setup to display text as BLOB in phpmyadmin?

the MySql field from phpmyadmin,
Field = 'first_name'
Type = text
Collation = latin1_bin
Null = No
Default = None

The php code, $insertName = "INSERT INTO name(first_name,last_name)VALUES('$firstName','$lastName')";
$dbSuccess_1 = mysql_query($insertName,$connectID) or die ("ERROR_1 - Unable to save to MySQL".error_get_last().mysql_error($connectID));

If you are saying you want to store binary files in your database, that is wrong. You should store these on your file system and in your database store a reference to the file on the system. — Chris
– Chris, Commented Aug 25, 2010 at 19:14

Daniel R · Accepted Answer · 2010-08-25 19:56:48Z

1

If you're asking how to change a BLOB column to TEXT you would use a query similar to this:

ALTER TABLE `name`  
    CHANGE COLUMN `first_name` `first_name` TEXT NULL FIRST
    ,CHANGE COLUMN `last_name` `last_name` TEXT NULL AFTER `first_name`;

You can use PHPMyAdmin to make the change even easier.

answered Aug 25, 2010 at 19:56

Daniel R

1,42911 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Daniel R Over a year ago

IIRC phpMyAdmin(at least older versions) showed both TEXT and BLOB fields as "BLOB". If you created them as text there is probably nothing wrong.

xtian Over a year ago

[I deleted my previous comment to this post, because when I reread my question, I realized this answer is exactly correct.] While phpMyAdmin still shows these original tables as TEXT, when I uploaded to a different server, and created the tables in the same manner, the text displays correctly on a different server. I can only conclude, either some other parameter is altering the TEXT data to BLOB or the table is corrupt in some manner.

Marc B · Accepted Answer · 2010-08-26 00:56:06Z

0

TEXT and BLOB are essentially identical, except that TEXT fields are subject to character set limitations (and character set is taken into account while sorting/grouping the fields), while BLOBs are stored verbatim as a sequence of bytes and will not be transformed.

Relevant docs: http://dev.mysql.com/doc/refman/5.0/en/blob.html

answered Aug 26, 2010 at 0:56

Marc B

362k44 gold badges433 silver badges508 bronze badges

2 Comments

xtian Over a year ago

Are you saying the Collation I've set is causing the BLOB to be set even though I selected TEXT when I created the table?

Marc B Over a year ago

No, that would defeat the purpose of a TEXT field. Possibly it's phpmyadmin treating text as blob for display purposes. Do you have access to the normal mysql console? If you can, get into that and try doing a show create table tablename or describe tablename and see what's reported there.

Collectives™ on Stack Overflow

How to unset BLOB with php/mysql?

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related