I am seriously having problems learning this stuff, and I don't understand things such as how the tables fit together, or how making a million tables instead of just one is more efficient. The SELECT function is totally beyond me right now, as well.
Could somone suggest some exercises to do to learn the code more easily? Maybe a site with good tutorials? I have a reference right in front of me, but it isn't cutting it.
Well you're trying to understand the theory and relatively good at the practice of it and as many have learned you either learn the theory first and then the practice or learn the practice and then the theory. so that means just take it at face value that a million tables is more efficient then one big one... (unless someone would like to explain, for I'm more practically sided and not learned in the theory concerning mysql and the such)...Putting that aside.
The select statement is actually very simple, lets show you two such queries you'll be continously using in a cms type enviroment.
Select query -
SELECT `item` FROM `table`;
This query would select the column called item
SELECT `item` FROM `table` WHERE `value`=1
This query would select a single item from the column which
has the row where `value` is equal to one
SELECT `item`,`item2` FROM `table` WHERE `value`=1
This query would select two peices of data one from colum
'item' and the other from column 'item2' and it would select
it from the row where the column 'value' has a value of 1
Insert Query -
INSERT INTO `field`(`id`,`column`,`column2`) VALUES('1','data1','data2')
All this query does it insert a new row into the field called
'field' and it inserts 1,data1,abd data2 into id,column, column2 respectively.
Take time and read the available documentation over, and over, and over again. Getting your head around database manipulation isn't something you can do overnight, especially starting from scratch. My advice is to learn 'databases' more widely first before even thinking about the specifics of queries (SELECT/INSERT...). Learn what a database actually is, how and why data is stored in various ways, from a very wide angle approach. Learn the subject of "databases" rather than "MySQL". I know this really isn't much practical help to you at all but I'm not so much a fan of the "use this code, it'll work" approach.
Perhaps a good book or two on the subject (whether printed or digital) might help to understand the subject. They often provide a wider textual description then throw in a little bit of code to relate theory with practice. For learning, things like books and articles might provide easier understanding than the specific documentation because the latter can often appear to be just a jumble of babble if you're not clear on the terminology.
I've read a bunch of those books and by the looks of it, I haven't really grasped it myself. Books online, tutorials, friends helping me out, you guys. I've gotten better but I am no where near where I want to be.
Salathe, do you have any digital paperwork on the particular subject? I'm always interested in reading about those subjects.
Different things work better for different people, or sometimes not at all! I learned about databases from the age of 14 in school and continued formal learning until 19, combined with online resources (by far my greatest source) and the occasional book. I'm still learning new things even now, 10 years later. Perhaps this long-term approach is somewhat lacking in today's world.
As for any ebooks on the subject, I have none at hand (I rarely use ebooks at all, so never go to look for them) but I would be very surprised if there aren't good ones available easily. Hopefully some other member(s) will have used that particular resource type in the past.
Auto increment adds one to the value on every row inserted, I always stick 'NULL' in there. For instance, you have these rows
1 - Lazy
2 - Foo
3 - Bar
All you have to tell the database to assign is the name, it will take care of the ID itself.
If you delete 3 and insert another row, it will look like this
1 - Lazy
2 - Foo
4 - Monkey
It will always add one, not from the row count or the last value, it keeps a value elsewhere. All your primary keys should be auto_increment unless you have a good reason otherwise (I've yet to find one)
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).
// 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),
// execute the query
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.
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.
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.
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".