problem with TEXT field

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
thallish
Forum Commoner
Posts: 60
Joined: Wed Mar 02, 2005 11:38 am
Location: Aalborg, Denmark

problem with TEXT field

Post by thallish »

hi

I got a problem with setting up a table that includes a text field i phpMyAdmin. The main idea is that the text field is gonna contain text of variable length in my database. (I'm using it for news segments and stuff like that)

well here is my code:

Code: Select all

CREATE TABLE `segment` (
`id` INT NOT NULL AUTO_INCREMENT ,
`headline` VARCHAR( 40 ) NOT NULL ,
`description` VARCHAR( 200 ) NOT NULL ,
`content` TEXT( 1000 ) NOT NULL ,
`author` VARCHAR( 10 ) NOT NULL ,
`viewed` INT NOT NULL ,
`type` VARCHAR( 10 ) NOT NULL ,
`area` VARCHAR( 10 ) NOT NULL ,
`date` DATETIME NOT NULL ,
PRIMARY KEY ( `id` ) ,
INDEX ( `headline` , `type` , `area` )
)
and here is the MySql error:

Code: Select all

#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '(1000) NOT NULL, `author` VARCHAR(10) NOT NULL, `viewed` INT NO
Questions:

1. Why?

2. Is it the best way?

3. Is using BLOB better?

hope somebody can help me

regards

thallish
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Re: problem with TEXT field

Post by scorphus »

thallish wrote:1. Why?
Because of a mystype or wrong syntax, TEXT type is not declared with a length. So change it to:

Code: Select all

CREATE TABLE `segment` (
	`id` INT NOT NULL AUTO_INCREMENT ,
	`headline` VARCHAR( 40 ) NOT NULL ,
	`description` VARCHAR( 200 ) NOT NULL ,
	`content` TEXT NOT NULL ,
	`author` VARCHAR( 10 ) NOT NULL ,
	`viewed` INT NOT NULL ,
	`type` VARCHAR( 10 ) NOT NULL ,
	`area` VARCHAR( 10 ) NOT NULL ,
	`date` DATETIME NOT NULL ,
	PRIMARY KEY ( `id` ) ,
	INDEX ( `headline` , `type` , `area` )
);
thallish wrote:2. Is it the best way?
I'm not sure but it seems to be...
thallish wrote:3. Is using BLOB better?
...since `content` will be holding character strings, TEXT is the correct type option.

Read the manual: 11.4.3. The BLOB and TEXT Types

Regards,
Scorphus.
thallish
Forum Commoner
Posts: 60
Joined: Wed Mar 02, 2005 11:38 am
Location: Aalborg, Denmark

Post by thallish »

hey and thx for your answer. It works now

I must have been doing something wrong because the first
time I tried to create a table with a TEXT field without a length I also got an error saying something like I should specify one, but something else must have been wrong then.. Oh well it's working now :wink:

Just another question after I've read the manual. Is BLOB used for images?

regards
thallish
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

blob is used for binary data.. but we'll recommend not using it for images as they are accessed far too often, each image from the database will require a seperate page request and database query. You'll add a bunch of time to load any image from the server.
Post Reply