Suzanne — Mon, 18 Aug 2003 16:09:38 +0000

PHPMyAdmin allows you to enter the queries raw. It doesn't obscure anything and helps illustrate things nicely.

m3rajk — Mon, 18 Aug 2003 14:39:04 +0000

nope. i heard about it and looked at the site. it sounds like it'd be obscuring it and i want to learn it. like i said, i had found an example that was inline and that wasn't working. since i realize the books aren't always right wanted to know if the erorr was me or the book. that's why i wasked about it here.

i also know that i'm using a 20 gig drive on hardware that doesn't support drives greater than 8 gigs and seems to work fine as long as partitions are under 8 gigs, so i knew it could have been a quirk with my system, so i wanted to know it wasn't just me.

Suzanne — Thu, 14 Aug 2003 04:35:09 +0000

Try using the search instead. It gives faster and better results than the TOC at mysql.com -- also, there is no better teacher than experience.

Have you looked into using PHPMyAdmin? It's a great way to learn about table creation as it shows you the queries that produce the tables when it does so, and you can, additionally, export the table and see how it's put together.

m3rajk — Thu, 14 Aug 2003 04:03:55 +0000

i have tried the online manual and find the toc that's online makes it hard to find things. the orielly book i have has an examples with unique keys inline, which is why i didn't understand why it's not working and sought the advice of those more experienced.

Mark Hensler — Thu, 14 Aug 2003 00:17:02 +0000

Do you do any research on your own? Obviously your book(s) aren't providing enough help for you to solve your own problems. But I've already linked you to the CREATE TABLE section of the MySQL Documentation in this post.

The documentation gives examples of only setting the primary key inline. The docs do not give examples of setting any other keys inline. By testing this myself, I discoved that setting unique keys inline does work. By testing this myself, I discovered that setting index keys inline does not work.

It took me 2 minutes of my time to connect to my mysql server and learn this. It took me 5 minutes to write up this post.

m3rajk — Wed, 13 Aug 2003 21:38:00 +0000

have you ever declared keys in theline? all the examples i've found in books are like that. i don't get why this doesn't work because of that.

Quote: mysql> CREATE TABLE users(
-> uid int unsigned NOT NULL auto_increment PRIMARY KEY,
-> username varchar(15) NOT NULL UNIQUE KEY,
-> password varchar(15) NOT NULL,
-> email tinytext NOT NULL UNIQUE KEY,
-> gmt_offset tinyint default '-5' NOT NULL,
-> site_access tinyint unsigned default '0' NOT NULL,
-> admin_comment tinytext,
-> approved tinyint unsigned default '12' NOT NULL,
-> last_login_ip tinytext NOT NULL,
-> last_login_date datetime default '0000-00-00 00:00:00' NOT NULL,
-> end_level date default '0000-00-00' NOT NULL,
-> login_duration tinyint default '0' NOT NULL,
-> enroll datetime default '0000-00-00 00:00:00' NOT NULL KEY,
-> gender char(1) default 'F' NOT NULL KEY,
-> last_activity datetime default '0000-00-00 00:00:00' NOT NULL
-> ) TYPE=MyISAM;
ERROR 1064: You have an error in your SQL syntax near 'KEY,
gender char(1) default 'F' NOT NULL KEY,
last_activity datetime default '00' at line 14
mysql>

Mark Hensler — Tue, 12 Aug 2003 02:47:22 +0000

CREATE TABLE users(
  uid int unsigned NOT NULL,
  username varchar(15) NOT NULL,
  password varchar(15) NOT NULL,
  email varchar(255) NOT NULL,
  gmt_offset tinyint default '-5' NOT NULL,
  site_access tinyint unsigned default '0' NOT NULL,
  admin_comment tinytext,
  approved tinyint unsigned default '12' NOT NULL,
  last_login_ip tinytext NOT NULL,
  last_login_date datetime default '0000-00-00 00:00:00' NOT NULL,
  end_level date default '0000-00-00' NOT NULL,
  login_duration tinyint default '0' NOT NULL,
  enroll datetime default '0000-00-00 00:00:00' NOT NULL,
  gender char(1) NOT NULL,
  last_activity datetime default '0000-00-00 00:00:00' NOT NULL,
  PRIMARY KEY (uid),
  UNIQUE KEY username (username),
  UNIQUE KEY email (email)
) TYPE=MyISAM;

CREATE TABLE forums(
  forum_id tinyint unsigned NOT NULL auto_increment PRIMARY KEY,
  title varchar(255) NOT NULL UNIQUE KEY,
  access tinyint unsigned NOT NULL,
  threads tinyint,
  last_post_auth varchar(15) NOT NULL,
  last_post_time time default '00:00:00' NOT NULL
) TYPE=MyISAM;

CREATE TABLE threads(
  thread_id bigint unsigned NOT NULL auto_increment PRIMARY KEY,
  curr_fid tinyint unsigned NOT NULL,
  access tinyint unsigned NOT NULL,
  title tinytext NOT NULL UNIQUE KEY,
  thread_auth varchar(15) NOT NULL,
  last_post_auth varchar(15) NOT NULL,
  amt_posts smallint unsigned NOT NULL,
  orig_fid tinyint unsigned NOT NULL
) TYPE=MyISAM;

'Fields do not need to be a primary key to be searchable. They only need to be indexed, and any of the three index types (primary, unique, index) will do.

m3rajk — Tue, 12 Aug 2003 01:51:31 +0000

the online documentation and the book i have are what i was looking through. although i can't seem to find as much online. i just keep finding the TOC unable to direct me where to go to find what i'm looking for

m3rajk — Tue, 12 Aug 2003 01:49:31 +0000

current tables that will offend (reccommendations on which feilds to turn into unique ...

main user table
CREATE TABLE users(
	uid int unsigned NOT NULL auto_increment PRIMARY KEY,
	username varchar(15) NOT NULL PRIMARY KEY,
	password varchar(15) NOT NULL,
	email tinytext NOT NULL PRIMARY KEY,
	gmt_offset tinyint default '-5' NOT NULL,
	site_access tinyint unsigned default '0' NOT NULL,
	admin_comment tinytext,
	approved tinyint unsigned default '12' NOT NULL,
	last_login_ip tinytext NOT NULL,
	last_login_date datetime default '0000-00-00 00:00:00' NOT NULL,
	end_level date default '0000-00-00' NOT NULL,
	login_duration tinyint default '0' NOT NULL,
	enroll datetime default '0000-00-00 00:00:00' NOT NULL,
	gender char(1) default 'F' NOT NULL,
	last_activity datetime default '0000-00-00 00:00:00' NOT NULL
) TYPE=MyISAM;

'note: most searches done via username or uid. however, for password retrieval, lookup is done via email

and the others, actually 3, and wont be used or made for a little bit...

main forum table
CREATE TABLE forums(
	forum_id tinyint unsigned NOT NULL auto_increment PRIMARY KEY,
	title tinytext NOT NULL PRIMARY KEY,
	access tinyint unsigned NOT NULL,
	threads tinyint,
	last_post_auth varchar(15) NOT NULL,
	last_post_time time default '00:00:00' NOT NULL
) TYPE=MyISAM;


threads table
CREATE TABLE threads(
	thread_id bigint unsigned NOT NULL auto_increment PRIMARY KEY,
	curr_fid tinyint unsigned NOT NULL,
	access tinyint unsigned NOT NULL,
	title tinytext NOT NULL PRIMARY KEY,
	thread_auth varchar(15) NOT NULL,
	last_post_auth varchar(15) NOT NULL,
	amt_posts smallint unsigned NOT NULL,
	orig_fid tinyint unsigned NOT NULL
) TYPE=MyISAM;

Mark Hensler — Tue, 12 Aug 2003 01:44:46 +0000

Table with a primary key containing 3 combined fields (highly recommended against).

CREATE TABLE users(
uid int unsigned NOT NULL,
username varchar(15) NOT NULL,
password varchar(15) NOT NULL,
email varchar(255) NOT NULL,
gmt_offset tinyint default '-5' NOT NULL,
site_access tinyint unsigned default '0' NOT NULL,
admin_comment tinytext,
approved tinyint unsigned default '12' NOT NULL,
last_login_ip tinytext NOT NULL,
last_login_date datetime default '0000-00-00 00:00:00' NOT NULL,
end_level date default '0000-00-00' NOT NULL,
login_duration tinyint default '0' NOT NULL,
`join` datetime default '0000-00-00 00:00:00' NOT NULL,
gender char(1) NOT NULL,
last_activity datetime default '0000-00-00 00:00:00' NOT NULL,
PRIMARY KEY (uid, username, email)
) TYPE=MyISAM;

'
Table with a primary key, and 2 seperate unique keys (recommended).

CREATE TABLE users(
uid int unsigned NOT NULL,
username varchar(15) NOT NULL,
password varchar(15) NOT NULL,
email varchar(255) NOT NULL,
gmt_offset tinyint default '-5' NOT NULL,
site_access tinyint unsigned default '0' NOT NULL,
admin_comment tinytext,
approved tinyint unsigned default '12' NOT NULL,
last_login_ip tinytext NOT NULL,
last_login_date datetime default '0000-00-00 00:00:00' NOT NULL,
end_level date default '0000-00-00' NOT NULL,
login_duration tinyint default '0' NOT NULL,
`join` datetime default '0000-00-00 00:00:00' NOT NULL,
gender char(1) NOT NULL,
last_activity datetime default '0000-00-00 00:00:00' NOT NULL,
PRIMARY KEY (uid),
UNIQUE KEY username (username),
UNIQUE KEY email (email)
) TYPE=MyISAM;

'Do you know how to use the MySQL Documentation? These are simple syntax errors that are easily identified while studying the examples in the CREATE TABLE documentation.