Textpattern CMS

#1 2007-10-08 01:45:56

hafnius

Archived Plugin Author

From: DK

Registered: 2004-09-02

Posts: 47

problem creating table in db

Hi all

i am meddling a bit with a plugin that need its own table so i write this at the begining of my plugin code:

 $q = "SHOW TABLES LIKE '".PFX."txp_dict2'";
	$tcheck = getRows($q);
	 if (!$tcheck) {
	$qc = "CREATE TABLE `".PFX."txp_dict2` (
	`id` int(8) NOT NULL auto_increment,
	`date` datetime NOT NULL default '0000-00-00 00:00:00',
	`word_title` varchar(100) NOT NULL default '',
	`word_desc` text NOT NULL,
	PRIMARY KEY  (`id`)
	KEY `word_title` (`word_title`(5),*
	FULLTEXT KEY `word_desc` (`word_desc`)*
	) TYPE=MyISAM; ";
	safe_query($qc);
		 }

which trows an error saying i have an error in my sql-syntax. If i remove the two lines with the * at the end of them it flies and the table is created fine. So the problem is with the indexes. What i want to accomplice is this:

 CREATE TABLE `txp_dict2` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  `word_title` varchar(100) NOT NULL default '',
  `word_desc` text NOT NULL,
  PRIMARY KEY  (`word_id`),
  KEY `word_title` (`word_title`(5)),
  FULLTEXT KEY `word_desc` (`word_desc`)
) ENGINE=MyISAM;

which is a dump from PhPmyadmin, but where in my syntax am i doing something wrong?

Kind Regards
/Klaus b

---

– Nobody puts Baby in the corner !
Johnny Castle, Dirty Dancing

#2 2007-10-08 03:14:44

rsilletti

Moderator

From: Spokane WA

Registered: 2004-04-28

Posts: 707

Re: problem creating table in db

Are you missing a comma on the PRIMARY KEY line in your initial example?

#3 2007-10-08 12:20:34

hafnius

Archived Plugin Author

From: DK

Registered: 2004-09-02

Posts: 47

Re: problem creating table in db

Yeah that i was indeed – also the index size of 5 should be on the “word_title” field and not the “word_desc” field. This is what worked:

 $q = "SHOW TABLES LIKE '".PFX."txp_dict5'";
			$tcheck = getRows($q);
			 if (!$tcheck) {
			$qc = "CREATE TABLE `".PFX."txp_dict5` (
			`id` int(8) NOT NULL auto_increment,
			`date` datetime NOT NULL default '0000-00-00 00:00:00',
			`word_title` varchar(100) NOT NULL default '',
			`word_desc` text NOT NULL,
			PRIMARY KEY  (`id`),
			KEY `word_title` (`word_title`(5)),
			FULLTEXT KEY `word_desc` (`word_desc`)
			) TYPE=MyISAM; ";
			safe_query($qc);
				 }

Thanks :-)

---

– Nobody puts Baby in the corner !
Johnny Castle, Dirty Dancing