Page 1 of 1

Anyone know why I'm getting a MySQL error?

Posted: Tue Jan 17, 2006 3:37 pm
by mattcooper
Sami | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]


Why on earth why won't phpMyAdmin create a table with this:

Code: Select all

CREATE TABLE `content` (

`id` INT( 2 ) NOT NULL AUTO_INCREMENT ,
`pageid` VARCHAR( 15 ) NOT NULL ,
`title` VARCHAR( 25 ) NOT NULL ,
`metatags` VARCHAR( 255 ) NOT NULL ,
`heading` VARCHAR( 40 ) NOT NULL ,
`textblock1` MEDIUMBLOB( 500 ) NOT NULL ,
`textblock2` MEDIUMBLOB( 500 ) NOT NULL ,
`image1` VARCHAR( 40 ) NOT NULL ,
`image2` VARCHAR( 40 ) NOT NULL ,
`footer` VARCHAR( 100 ) NOT NULL ,
INDEX ( `pageid` ) 
)
Any ideas?


Sami | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]

RTFM

Posted: Tue Jan 17, 2006 3:43 pm
by cj5
SQL-query :

CREATE TABLE `content` (
`id` INT( 2 ) NOT NULL AUTO_INCREMENT ,
`pageid` VARCHAR( 15 ) NOT NULL ,
`title` VARCHAR( 25 ) NOT NULL ,
`metatags` VARCHAR( 255 ) NOT NULL ,
`heading` VARCHAR( 40 ) NOT NULL ,
`textblock1` MEDIUMBLOB( 500 ) NOT NULL ,
`textblock2` MEDIUMBLOB( 500 ) NOT NULL ,
`image1` VARCHAR( 40 ) NOT NULL ,
`image2` VARCHAR( 40 ) NOT NULL ,
`footer` VARCHAR( 100 ) NOT NULL ,
INDEX ( `pageid` )
)

MySQL said:
#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 '( 500 ) NOT NULL ,
`textblock2` MEDIUMBLOB( 500 ) NOT NU


Did you check to see that your version of MySQL (not phpMyAdmin) supports MEDIUMBLOB?
If you are only using 500 characters for a text block why not just use TEXT (it supports up to 65,000 something characters)?

Re: RTFM

Posted: Tue Jan 17, 2006 3:54 pm
by foobar
cj5 wrote: If you are only using 500 characters for a text block why not just use TEXT (it supports up to 65,000 something characters)?
AFAIK, BLOB types have the same lengths as corresponding TEXT types (length + 3 bytes), but are binary safe. He might want his textblocks to be binary safe.

Posted: Tue Jan 17, 2006 6:27 pm
by raghavan20
mysql manual wrote:BLOB and TEXT columns cannot have DEFAULT values
Read more from here.

Posted: Wed Jan 18, 2006 2:53 am
by mattcooper
Lovely guys, thank you. Removed the value for the textblock fields and used the id field as the primary key, with pageid as index. This now works - thanks for bringing me along with SQL a bit more, it's the next stage on my journey!!

:)