creating tables in MySQL.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
The-Master
Forum Commoner
Posts: 45
Joined: Sun Aug 07, 2005 9:51 am
Location: Israel

creating tables in MySQL.

Post 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
Last edited by The-Master on Sun Aug 07, 2005 11:09 am, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
The-Master
Forum Commoner
Posts: 45
Joined: Sun Aug 07, 2005 9:51 am
Location: Israel

Post 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?...
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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)
The-Master
Forum Commoner
Posts: 45
Joined: Sun Aug 07, 2005 9:51 am
Location: Israel

Post 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: )...
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
The-Master
Forum Commoner
Posts: 45
Joined: Sun Aug 07, 2005 9:51 am
Location: Israel

Post 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)
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

You got it.
The-Master
Forum Commoner
Posts: 45
Joined: Sun Aug 07, 2005 9:51 am
Location: Israel

Post by The-Master »

it works good! :)
Post Reply