Anyone know why I'm getting a MySQL error?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
mattcooper
Forum Contributor
Posts: 210
Joined: Thu Mar 17, 2005 5:51 am
Location: London, UK

Anyone know why I'm getting a MySQL error?

Post 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]
User avatar
cj5
Forum Commoner
Posts: 60
Joined: Tue Jan 17, 2006 3:38 pm
Location: Long Island, NY, USA

RTFM

Post 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)?
foobar
Forum Regular
Posts: 613
Joined: Wed Sep 28, 2005 10:08 am

Re: RTFM

Post 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.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

mysql manual wrote:BLOB and TEXT columns cannot have DEFAULT values
Read more from here.
User avatar
mattcooper
Forum Contributor
Posts: 210
Joined: Thu Mar 17, 2005 5:51 am
Location: London, UK

Post 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!!

:)
Post Reply