Page 1 of 1

creating tables in MySQL.

Posted: Sun Aug 07, 2005 10:01 am
by The-Master
Hi, i am new in the forum as you can see, i am also new to MySQL, i am a C++, Pascal, Delphi, Assembler programmer, and a graphic designer. recently i had an idea to make a text based RPG, so i made this SQL code for the database:

Code: Select all

CREATE TABLE `content` (
  `id` int(11) NOT NULL auto_increment,
  `title` text NOT NULL,
  `body` text NOT NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM ;

CREATE TABLE `nav` (
  `id` int(11) NOT NULL auto_increment,
  `menus` text NOT NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM ;

CREATE TABLE `news` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL default '',
  `author` varchar(255) NOT NULL default '',
  `email` varchar(255) NOT NULL default '',
  `date` date NOT NULL default '0000-00-00',
  `body` text NOT NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM ;

CREATE TABLE `users` (
  `id` int(11) NOT NULL auto_increment,
  `user` varchar(50) NOT NULL default '',
  `pass` varchar(255) NOT NULL default '',
  `email` varchar(255) NOT NULL default '',
  `race` varchar(255) NOT NULL default '', <------- this is the 'RACE'...
  `first` varchar(255) default NULL,   
  `location` varchar(255) default NULL,
  `website` varchar(255) default NULL,
  `level` int(11) NOT NULL default '0',
  `showemail` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM ;
the problem is that i want to make that 'RACE' will get a value from a drop down box, how do i do that?


feyd | tweaked title to make quicker sense

Posted: Sun Aug 07, 2005 10:04 am
by feyd
create a table with all the values that'd be in that type. Change the user table to use the same ID size as this new table. When someone selects a race store the ID in the user table.

Posted: Sun Aug 07, 2005 11:12 am
by The-Master
you mean something like this?:

Code: Select all

CREATE TABLE `races` ( 
  `id` int(11) NOT NULL auto_increment, 
  `race1` varchar(50) NOT NULL default '', 
  `race2` varchar(50) NOT NULL default '', 
  `race3` varchar(50) NOT NULL default '', 
  `race4` varchar(05) NOT NULL default '',   
PRIMARY KEY  (`id`) 
) TYPE=MyISAM ;
if not, can you show an example?...

Posted: Sun Aug 07, 2005 2:49 pm
by feyd
not exactly.. the data should be normalized, so each race get's their own record in the table. Doing a selection against the table will give you all available races (depending on restrictions you add)

Posted: Sun Aug 07, 2005 5:29 pm
by The-Master
so that means i have to change the "varchar" and the "NOT NULL" in the race1, 2, 3, 4...? i can't understand much of what you'r saying i am totaly new to MySQL (2 days only :cry: )...

Posted: Sun Aug 07, 2005 5:54 pm
by feyd
it actually means you get rid of 2, 3, & 4. You can rename 1 to just race, or something.. and add I'd personally add an 'order' field (type int). This will allow you to adjust the placement in the list without having to destroy and reinsert data..

Posted: Mon Aug 08, 2005 6:40 pm
by The-Master
i think i understand now... you mean like this:

Code: Select all

CREATE TABLE `races` ( 
  `id` int(11) NOT NULL auto_increment, 
  `race` int(50) NOT NULL default '', 
PRIMARY KEY  (`id`) 
) TYPE=MyISAM ;
if i am wrong agian i will just try something else then drop-down(radio button or whatever)

Posted: Mon Aug 08, 2005 7:30 pm
by John Cartwright
You got it.

Posted: Wed Aug 10, 2005 6:01 pm
by The-Master
it works good! :)