Page 1 of 1

problem with TEXT field

Posted: Sat Apr 16, 2005 11:48 am
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

Re: problem with TEXT field

Posted: Sat Apr 16, 2005 12:39 pm
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.

Posted: Sat Apr 16, 2005 3:20 pm
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

Posted: Sat Apr 16, 2005 3:27 pm
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.