Quote:
Originally Posted by Aaron
aggh I don't understand this one bit >.<
|
If you're trying to learn SQL by simply using statements in a PHP script, I'm inclined to agree with
danielneri and suggest using phpMyAdmin until you understand the syntax.
At the very least, when using a PHP script to perform any CREATE TABLE statements, use IF NOT EXISTS in the statement so you won't overwrite the table each time the script loads (unless this is your intention).
PHP Code:
// the CREATE TABLE statement
$sql= <<< END
CREATE TABLE IF NOT EXISTS `example` (
`ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`age` TINYINT(3) NULL,
PRIMARY KEY (ID),
UNIQUE (name),
INDEX (age)
) ENGINE=MyISAM
END;
// execute the query
$result= mysql_query($sql)
OR die('QUERY ERROR:<br />' .$sql. '<br />' .mysql_error());
Note the
heredoc syntax, it gives you the most flexibility in creating SQL statements within PHP. You can edit that to your heart's content without fussing with quotes, spacing, extra lines and whatnot.
Also note the table design. I prefer to have each column defined on a separate line, then define each index afterwards. A method I learned from an SQL guru long ago and still adhere to. Another habit is to surround each column with `backticks` - strictly not necessary but I find it neatly defines column names and distinguishes them from other data or key definitions (this isn't meant to be advocation of using
reserved words in your table and column definitions).
Also note the column definitions. You need to think ahead when designing the table and figure that the user will only be allowed to have let's say 30 characters in their name, that the age will realistically be between 13 and 65, so there is no need to have extra overhead in using INT (a data type that goes up to
4294967295 (if unsigned) - I don't know anyone that old). Another consideration is whether or not to allow a column to have a NULL value (the absence of value, in other words, as opposed to '0', which is a value). If the user's age is unknown, you want to represent that with NULL, not '0'. Otherwise this can really screw up any averaging or other calculations you want to perform.
MySQL Manual :
Data Types
Quote:
Originally Posted by Aaron
If you don't want a user to have the same username as another user you set that as the primary key. There is a fine reason for no auto-increment.
|
Yes and no. When creating a table for users, you want to consider that another table will be using the `user_id` value to relate to it. Having a CHAR / VARCHAR type column is not normalized and is very inefficient. You're storing redundant data rather than a numeric key.
IMHO, the best way is to have an auto_increment INT type column as PRIMARY KEY, and create a UNIQUE index on the user name column. You may find alot of dissent from this opinion online, and it is likely that those opinions are correct, but not coming from a MySQL user.
Quote:
Originally Posted by Aaron
So... How would I have a value start at one, and go up every time a new user registers?
|
Use an auto_increment INT type PRIMARY KEY field as you've already shown.
Quote:
Originally Posted by Aaron
edit: and why does everyone have this and that as "foo" and "bar" on the explainations? My dad says that all the time and it freaks me out.
|
You're dad is likely saying "fubar", not "foo bar". Was he in the military?
"fubar" - acronym for F'ed Up Beyond Recognition (polite version)
"foo bar" - two words often used in programming examples, much like "Alice" and "Bob" are used in crypto examples. I've always assumed "foo bar" was derived from "fubar".
Wikipedia: "foobar" - looks like I was right. :)