Simple MySQL Tutorial

[Sly]

Posted this on HF earlier. (http://www.hackforums.net/showthread.php?tid=1285020)

-----------------------------------------------------------------------

Any code that contains [ ]s denotes an optional field.

This tutorial assumes that you already have experience with PHP. If you are unsure that you will understand this tutorial, then you probably won't. Visit http://php.net to become familiar with PHP. You can also visit http://w3schools.com to learn PHP step-by-step, and view realtime code that you can edit and play around with.



Connecting to your database
Some people like to use the following for their code to connect to their database.

PHP Code:

<?php
    
    $user   = 'someuser';
    $pass   = 'somepass';
    $server = 'localhost';
    $db     = 'somedb';
    
    mysql_connect($server, $user, $pass);

?>

I like to make my database connection static, because I'm not going to change the username or password a lot, and can easily edit it to the right password without the use of a variable taking up space. Example:

PHP Code:

<?php

    mysql_connect('localhost', 'someuser', 'somepass');
    
?>

Selecting Database
Once you're connected to your database, you have to select your database. I had to help someone the other day with a null fetch_array return (covered later in this tutorial), and the problem ended up being that he wasn't selecting his database. You have to select your database before you can retrieve any results, or send any queries that need to access the database. You do this by the following:

PHP Code:

<?php

    mysql_select_db('somedb');
    
?>

So, now your code should look something like this:

PHP Code:

<?php

    mysql_connect('localhost', 'someuser', 'somepass');
    mysql_select_db('somedb');

?>

If this does not return any errors from PHP, then you have successfully connected to and selected your database.



Queries
The next step is to make your query for the database. There are a few different queries to cover.

The first query we're going to cover is the SELECT query. This is the query you want to use when you are trying to grab information from your database.

For this example, our database has a table called "users", with the columns being "id", "username", "password", "email".

If you want to grab only certain columns from your table, you can use the following:

PHP Code:

<?php

    $result = mysql_query("SELECT `id`,`username`,`email` FROM `users` ORDER BY `id` ASC");

?>

Now, let's break this code down.

1. SELECT - This starts the grabbing of information from the database you selected after you connected.
   
2. `id`,`username`,`email` - This denotes that you only want to grab "id", "username", and "email" from the database table.
   
3. FROM `users` - This is the table that you want to grab the information from. Using this with SELECT * will return all columns from the table (id, username, password, and email).
   
4. ORDER BY `id` ASC - This will order the results from the table by the "id" column, and order them ascending (1, 2, 3, 4, 5). Another way to use this is to ORDER BY `id` DESC. Descending will do the opposite of ascending, and order them "5, 4, 3, 2, 1".
   

For our example, we're going to use the SELECT command, but we want all information to be returned, from all columns.
You would do this with the following:

PHP Code:

<?php

    $result = mysql_query("SELECT * FROM `users` ORDER BY `id` ASC");

?>

Now, let's break this code down.

1. SELECT - This starts the grabbing of information from the database you selected after you connected.
   
2. * - This means that you want to grab ALL columns from the table.
   
3. FROM `users` - This is the table that you want to grab the information from. Using this with SELECT * will return all columns from the table (id, username, password, and email).
   
4. ORDER BY `id` ASC - This will order the results from the table by the "id" column, and order them ascending (1, 2, 3, 4, 5). Another way to use this is to ORDER BY `id` DESC. Descending will do the opposite of ascending, and order them "5, 4, 3, 2, 1".
   

So, at this point.. your code should look like this:

PHP Code:

<?php

    mysql_connect('localhost', 'someuser', 'somepass');
    mysql_select_db('somedb');
    
    $result = mysql_query("SELECT * FROM `users` ORDER BY `id` ASC");

?>

So, now say that you want to read all of the users that were returned by the database. Let's say we have 3 users. The following is an example of what our database information would be.

ID, Username, Password, E-Mail
1, Test, lol123, test@domain.com
2, Test2, lol321, test2@domain.com
3, Test3, lol534, test3@domain.com

Now, this data will be returned and assigned to the $result variable, as we used $result = (our query).

A very popular mistake at this point, is people try to jump directly into mysql_fetch_array(). Example:

PHP Code:

<?php

    $result = mysql_fetch_array($result);

?>

Now, even though this code IS correct, it only grabs 1 row of the entire result. That means you'll only get the first user from the database. The best way to grab the multirow results from $result is the following:

PHP Code:

<?php

    while ($data = mysql_fetch_array($result)) {
        echo 'ID: '.$data['id'].' Username: '.$data['username'].' Password: '.$data['password'].' E-Mail: '.$data['email'];
    }

?>

When you use this while(){} loop, $data is assigned to the row that is fetched from $result. You could use mysql_fetch_row($result, 1), mysql_fetch_row($result, 2), etc., but who wants to go through all of that when you can get so simple?


All together, so far, your code should look like this:

PHP Code:

<?php

    mysql_connect('localhost', 'someuser', 'somepass');
    mysql_select_db('somedb');
    
    $result = mysql_query("SELECT * FROM `users` ORDER BY `id` ASC");
    
    while ($data = mysql_fetch_array($result)) {
        echo 'ID: '.$data['id'].', Username: '.$data['username'].', Password: '.$data['password'].', E-Mail: '.$data['email'];
    }

?>

The output of this code should be:

ID: 1, Username: Test, Password: lol123, E-Mail: test@domain.com
ID: 2, Username: Test2, Password: lol321, E-Mail: test2@domain.com
ID: 3, Username: Test3, Password: lol534, E-Mail: test3@domain.com

Now, that is the VERY basics of MySQL. Some of the other queries that you can use with mysql_query() are UPDATE and INSERT. These look like the following:

PHP Code:

<?php

    mysql_query("UPDATE `users` WHERE `id`='1' SET `username`='Test4',`password`='lol5',`email`='test4@domain.com'");

?>

This will update the user "Test" (id: 1) to the username "Test4", with a password of "lol5", instead of "lol321", and change the email to "test4@domain.com".

We can break this code down like this:

1. UPDATE - Tells the database that you want to update an existing entry in a table.
   
2. `users` - Tells the database what table you want to update the entry in.
   
3. WHERE `id`='1' - This tells the database what entry you want to edit. This is where the "id" column we had set up earlier comes in handy. We want to edit ID number 1, which would be the "Test" account.
   
4. SET `username`='Test4',`password`='lol5',`email`='test4@domain.com' - Tells the database what you want to update in the entry that you have selected.
   

The following will insert a completely new user into our database.

PHP Code:

<?php

    mysql_query("INSERT INTO `users`(`id`,`username`,`password`,`email`) VALUES('4','Test5','lol123','test5@domain.com')");

?>

Given that no errors are returned by PHP, you have successfully inserted a new user (Test5).

Simply put:

1. INSERT INTO - Tells the database that you're wanting to insert a new entry into a table.
   
2. `users`(`id`,`username`,`password`,`email`) - This tells the database what entries you are wanting to complete for the entry, in the order you will use in VALUES().
   
3. VALUES('4','Test5','lol123','test5@domain.com') - This tells the database the data that you are wanting to use for the new entry. It follows the order of what you used in the `table`(`column`[,`column`[,`column]]) command.
   

So, by the end of these processes, our database the we began with:

ID, Username, Password, E-Mail
1, Test, lol123, test@domain.com
2, Test2, lol321, test2@domain.com
3, Test3, lol534, test3@domain.com=

Now looks like:

ID, Username, Password, E-Mail
1, Test4, lol5, test4@domain.com
2, Test2, lol321, test2@domain.com
3, Test3, lol534, test3@domain.com
4, Test5, lol123, test5@domain.com

Closing your MySQL connection
Once you're done executing your code, you can close the MySQL connection to save bandwidth.

PHP Code:

<?php

    mysql_connect('localhost', 'someuser', 'somepass');
    mysql_select_db('somedb');
    
    //  Insert your code here..
    
    mysql_close();

?>

I don't personally use this, because my database logs would be huge with the opening and closing of the link after every execution.
If you want to, that's your choice. It's not required when using the database.



Getting information from the database with an HTML form
Let's say we have a form, and we want to use the information from that form to grab information from the database.

For this example, I will use a simple login form.

PHP Code:

<?php

    mysql_connect('localhost', 'someuser', 'somepass');
    mysql_select_db('somedb');

    $page = strtolower($_REQUEST['page']);
    if ($page == "login") {    
        if (!empty($_POST['username'])) {
            $result = mysql_query("SELECT * FROM `users` WHERE `username`='".mysql_escape_string($_POST['username'])."' LIMIT 1");
            $result = mysql_fetch_array($result);
            
            if (!empty($_POST['password']) && $_POST['password'] === $result['password']) {
                echo 'You have logged in. Your email address is: '.$result['email'];
            }
        }
    }

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
        <title>MySQL Test</title>
    </head>
    <body>
        <form action="./?page=login" name="login" method="post">
            <input type="text" name="username" /><br />
            <input type="password" name="password" /><br />
            <input type="submit" value="Login" />
        </form>
    </body>
</html> 


Now, we definitely want to break this code down. (We will only be working with the PHP code, not HTML code.) I'm also assuming that you do know basic PHP, so I'm not going through the PHP functions.

1. mysql_escape_string($_POST['username']) - This escapes all characters that could possibly effect the database. Example: SQL Injections using 's or "s.
   
2. mysql_query("SELECT.......LIMIT 1") - This tells the database that you only want it to return 1 result for your query. This can also be used for INSERT, and UPDATE queries.
   

The rest of the code should be self explanitory, as we have already covered it above.

The output of this code, using the account "Test5" (refer back to our last database output), and assuming you use the correct password, should be:

You have logged in. Your email address is: test5@domain.com

End of Tutorial
This is the end of this tutorial. For more on MySQL, please refer to the PHP functions manual, provided by http://php.net.

Hopefully this tutorial will help some of you guys out that are still learning PHP.

Note: If anyone has anything to add to what's already here, post it in this thread.

[Gaijin]

This is the best first post any user had on SF.
The tutorial is simple to follow and understand, you cover the basics and you cover them good, great work mate.
+rep ya later, can't do it now.

Keep up the quality.
btw: Welcome to the Forums.

[KoBE]

Nice tutorial. Keep up the HQ posts!

[begginer]

Thanks for tutorial buddy!

[Sly]

Thanks for the feedback, and the welcoming. Posted a couple more tutorials. One of which I couldn't get on HF, due to it's 15,000 character limitation on posts.