Having trouble learning MySQL database codes...

Aaron · 01-03-2008, 07:18 PM

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.

TlcAndres · 01-03-2008, 07:49 PM

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 -

Quote:

SELECT `item` FROM `table`;

This query would select the column called item

Quote:

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

Quote:

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 -

Quote:

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.

ReSpawN · 01-03-2008, 08:25 PM

The INSERT Query can be formatted as;

PHP Code:


			
// One of the more simply - one way - ... ways.
$resQuery = 'INSERT INTO `field` SET `column1` = `1` WHERE `id` = 1';

It's a lot easies if you have to do tons of inserts and updates. You can simply sum them up underneath each other like so;

PHP Code:


			
$resQuery = 'INSERT INTO `field` SET    `column1` = `1`, 
                                        `column1` = `1`, 
                                        `column1` = `1`, 
                                        `column1` = `1`, 
                                        `column1` = `1`
                                        WHERE `id` = "1"';

Wildhoney · 01-03-2008, 11:02 PM

I always go for the following formatting approach:

sql Code:

INSERT INTO
    myTable
SET
    myColumn = 1, 
    myColumn = 1,
    myColumn = 1,
    myColumn = 1,
    myColumn = 1
WHERE
    myColumn = 1

ReSpawN · 01-03-2008, 11:05 PM

Quote:

Originally Posted by Wildhoney

I always go for the following formatting approach:

sql Code:

INSERT INTO
    myTable
SET
    myColumn = 1, 
    myColumn = 1,
    myColumn = 1,
    myColumn = 1,
    myColumn = 1
WHERE
    myColumn = 1

I like it? It comes close to my original scripting style ... BSD/Alaman I believe it was called...
Good input mate.

Salathe · 01-04-2008, 12:01 AM

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.

ReSpawN · 01-04-2008, 12:03 AM

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.

Salathe · 01-04-2008, 12:17 AM

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.

SOCK · 01-04-2008, 03:01 AM

Just to add to the already useful comments, I find the best resource period is the MySQL Manual online. I can't recommend the MySQL Tutorial enough.

Their 'developer zone' section is incredibly useful overall.

In addition, download and follow along with the provided example databases.

trs21219 · 01-04-2008, 05:46 AM

this may help you a little MySQL Cheat Sheet - Cheat Sheets - ILoveJackDaniels.com
he made it in pdf and png for your viewing pleasure :)

Rendair · 01-04-2008, 01:04 PM

This website is also good for learning basics of pretty much most web based codes out there at the moment.

Tizag.com

Aaron · 01-04-2008, 10:26 PM

Okay okay... Do any of you use WAMP? What do I use to toy with the databases? phpmyadmin or sqlite?

SOCK · 01-05-2008, 02:13 AM

Quote:

Originally Posted by Aaron

Okay okay... Do any of you use WAMP? What do I use to toy with the databases? phpmyadmin or sqlite?

Yes, the Win XP Pro workstation I have at work is running Apache/MySQL/PHP and Perl. If I had a preference in all cases I'd use Linux, however.

Run through the MySQL Tutorial as I mentioned above. Get used to using the 'mysql' monitor command line application. It's what I use 90% of the time.

phpMyAdmin is good in a pinch for a visual method to manage databases and tables.

SQLite is a completely different RDBMS, similar to MySQL but smaller, lighter and uses flat files (or memory). Has nothing to do with MySQL. I've used it in two or three cases, I like working with it.

ReSpawN · 01-04-2008, 11:04 PM

Some good links there tbh. I will get a bigger image of them once I can get into it, but there is no time for that. Anyways, to answer Aarons question;

I use WAMP in combination with PHP 5.2.5, phpDesigner 2008, phpMyAdmin and NaviCAT.

danielneri · 01-05-2008, 01:04 AM

Like Rendair said, Tizag is an amazing site to use. I still use it nowadays for reference purposes.

Very explanatory and easy to understand.

Aaron · 01-05-2008, 06:01 AM

aggh I don't understand this one bit >.<

PHP Code:


			

mysql_query("CREATE TABLE example(
id INT NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(id),
 name VARCHAR(30), 
 age INT)")
 or die(mysql_error());

Is this really really really messy or something? The syntax is whats really blowing me out of the water... Does it work like this?:

PHP Code:


			
mysql_query("CREATE TABLE example(
            id INT NOT NULL AUTO-INCREMENT, PRIMARY KEY(id),
            name VARCHAR(30),
            age INT)
            ")

where id, name, and age are the columns, and id is a number that can't be left blank and goes up every time?

With auto-increment... Does it go up by the row number or does it just add one?

so like...

PHP Code:


			
number AUTO-INCREMENT NOT NULL

the value inserted is 1 in all instances below this point unless otherwise stated:

the first row has no input, will the value cause an error because it is not null, or will it automatically assign "0" like an array, or "1" like you would expect?

we are on the third row. Will the value auto-increment to three because it is the third row, or will it go to two because it is auto-incrementing from one?

Village Idiot · 01-05-2008, 06:32 AM

Auto increment adds one to the value on every row inserted, I always stick 'NULL' in there. For instance, you have these rows

ID Name
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
ID Name
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)

Aaron · 01-05-2008, 06:59 AM

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.

So... How would I have a value start at one, and go up every time a new user registers?

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.

SOCK · 01-05-2008, 05:19 PM

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. :)

Village Idiot · 01-05-2008, 07:03 PM

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.

Your primary key should only be a number which exists for the sole purpose of being a constant and unique ID number. What if you want to change names?

Quote:

Originally Posted by Aaron

So... How would I have a value start at one, and go up every time a new user registers?

Set the primary key to auto_incriment, every time you insert a row the ID will go up one. Here is a table I used in a recent project.

[code]
CREATE TABLE `users` (
`id` int(9) NOT NULL auto_increment,
`username` varchar(255) NOT NULL,
`mail` varchar(255) NOT NULL,
`pass` varchar(255) NOT NULL,
`rank` int(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
[/quote]

Create that table in your database and insert a few rows.

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.

Just dummy values, its considered geeky (partially in jest). I just use them because I don't want to spend any time thinking over meaningless values.