datatype to hold 3000 chars

mikeuk · July 20th, 2004, 05:49 AM

What is the most suitable data type for more than 3000 characters.

(I have read that its best to use the BLOB data type but the db MySQL, sets it to binary and I am unable to save any text to it.)

Thanks.

Michael.

Daniel Walker · July 20th, 2004, 07:55 AM

3000 characters? You'd probably want either a BLOB or a TEXT file, since the physical limits of char and varchar are 256 characters. You may want to ask whether you actually need the data in the database, however. You could store the text in each case in a text file on your machine's file system and then just reference that from a field that stored the _path_ to each file.

Why do that? Well, records with BLOB or TEXT data in can get very messy after a while, and take some looking after. Both these types are, for the pruposes of storing text, like having very large varchar feilds - and hence come with the same inherent problems of storing, searching and deleting/inserting large numbers of data records of very different lengths. Eventually the database becomes fragmented, and you need to run OPTIMIZE TABLE to clean things up (this is true even on Linux, which does not suffer fragmentation problems natively, since MySQL uses a linked list of drive locations to store where the data is in its data-directories (http://dev.mysql.com/doc/mysql/en/OP...E.html#IDX1707).

So what are the advantages of storing your text in your database? Well, first and foremost, you only ever have to back up your database, in order to back up all your data. Also, if your fields are _all_ going to be around the 3000 characters in size, then the potential messiness of deleted BLOBs probably isn't a great concern. You'd be running OPTIMIZE TABLES periodically, anyway. String your data in your database is also more flexible, since you can search the data directly, and so on. While using separate files and storing their paths in your database makes a lot of sense when dealing with potential BLOB data types such as images, ithere are less inherent benefits when considering text, since any such approach which puts some of the data for your application outside the actual database means that you are beginning to store data about how you are storing your data, rather than just storing your data.

That's probably more info than you wanted, though....

'Use a TEXT field, Mike.'

Take it easy,
Dan

mikeuk · July 20th, 2004, 08:15 AM

Hi Dan, WOW.

I got all of that. ;)

I think it will definatley be easier to hold all the data into the table as, like you said, backing up and maintaining it will be simpler and everything will then be in one place.

I have assigned the TEXT data type to my variable in MySQL as;

Code:

CREATE TABLE `news` (
...
`news_content` text NOT NULL
FULLTEXT KEY `news_content` (`news_content`)
)

My web form only seems to input about 900 characters approxmately but when I put in a whole loads of text at 1000-3000 characters in phpMyAdmin back end, it takes it without problem and everything displays fine when you come to view it.

Any ideas?

I have the "Beginning PHP 4" book and can not find the answer as to why this may be happening.

Michael.

Daniel Walker · July 20th, 2004, 09:39 AM

How are you sending the data? Internet Explorer has a byte-less-than-2K limit on the length of it's URLs, so if you are using action="get", that'd be a likely reason (PHPMyAdmin uses POST ;).

FWIW, other browsers differ in this respect. I believe the old Netscape had no physical limit other than that set by the OS it was running on (and the fact that it was incredibly slow at doing anything). I don't know what limits the likes of Mozilla/Konqueror/Opera/Safari set.

If you are POSTing them, then I'm somewhat at a loss.

Dan

mikeuk · July 20th, 2004, 09:42 AM

arh... interesting. I didnt know about the browser byte sizes and i've been doing websites for ages!

I am using GET to send my data. I'll change it to POST and see what happens.

Cheers.

Michael.

Daniel Walker · July 20th, 2004, 10:00 AM

There's no limit actually set by the HTTP specifications (either version 1.0 or 1.1), so it's really just a browser-specific thing.

Take it easy,
dan

mikeuk · July 20th, 2004, 10:03 AM

:)
Yeah its worked. The POST method holds much more info than the GET.
Excellent!
Thats was very interesting. Will have to try and find out more info on the differnt byte sizes for some browsers.

Thanks Dan!

Michael.

Daniel Walker · July 20th, 2004, 10:12 AM

Might I observe, that 2K is a pretty big URL!

Dan

mikeuk · July 20th, 2004, 10:18 AM

:D

Michael.