MySQL Basics Tutorial

Time to create our own database. We'll use phpmyadmin ( assuming you're using XAMPP and got it running, if not check out this tutorial ).

1. To access phpmyadmin, go to localhost/phpmyadmin.


2. Create a database ('test_database') as shown in the picture below.
   


3. Create a table ('products', 5 fields) as shown in the picture below.
   


4. Create the fields for the table as shown in the picture below.
   
   


5. Make sure the ID field is unique by making it primary key and make it auto-increment as shown in the picture below.
   

Now it's time to use php and mysql to connect to our database. First we need to connect to the mysql server, we can use the php function mysql_connect to do this. This functions requires you to supply the server name ( e.g. localhost ), the database user ( e.g. root ) and the password if any.

mysql_connect("localhost", "root", "");

now we need to select the database ( the one we just created ). We can use the php function mysql_select_db to do this.

mysql_select_db("test_database");

And that's it, we established a connection with the mysql database. Now we can start interacting with our database, using mysql queries.

There are 3 types of mysql queries to interact with our database. Each can be used (executed) with the PHP function mysq_query.

$query = mysql_query("query");

The query to be executed is put between brackets as argument for the function. The 3 types of queries are: insert queries, update queries and select queries.

Insert Query

mysql_query("INSERT INTO table_name VALUES(field1, field2, field3)VALUES('value1', 'value2', 'value3')");

The insert query is used to put data inside of a table in the database ( create entries to the mysql table ). Using the table 'products' we made, we could for example run the following query to add a product called 'Samsung Galaxy S II' with description 'A great smartphone.', category 'Mobile Phones' and price '599.95'. The ID is automaticly generated as we set that field to be the primary key and auto-increment ( which means it will start at 1, and for each new entry made to the table it will increase by 1 ).

mysql_query("INSERT INTO products(name, description, category, price)VALUES('Samsung Galaxy S II', 'A great smartphone', 'Mobile Phones', '599.95') ");

Update Query

mysql_query("UPDATE table_name SET field1 = 'value1', field2 = 'value2', field3 = 'value3' WHERE clause");

(The 'WHERE clause' means the condition for the entries to update, for example: 'WHERE id = 2', but is not necessary: if there's no 'WHERE'-part, all entries wil be updated/affected.)

The update query is used to update one (or multiple) specific entries from a table in the database. Using the table 'products' we made and the product we just added with our MySql INSERT query, we could for example run the following query to update the price of the product 'Samsung Galaxy S II'.

mysql_query("UPDATE products SET price = '549.95' WHERE name = 'Samsung Galaxy S II' ");

We now updated all entries with the name 'Samsung Galaxy S II' and updated the price for those entries ( products ) to '549.95'. However it's more precise to do it by ID, as the ID of a product/entry in the mysql table is always unique. So we could also use the ID of the product ( for example 1 ) to make sure we update that specific product only:

mysql_query("UPDATE products SET price = '549.95' WHERE id = 1");

Select Query

mysql_query("SELECT field1, field2, field3 FROM table_name WHERE clause");

(The 'WHERE clause' means the condition for the entries to select (specifiements), for example: 'WHERE id = 2', but is not necessary: if there's no 'WHERE'-part, all entries wil be selected.)

The select query is used to select entries from a table in the database ( or combine multiple tables ). Using the table 'products' we made and the product we just added to this table, we can select that specific product using this query:

mysql_query("SELECT * FROM products WHERE id = 1");

or if we select it by its name:

mysql_query("SELECT * FROM products WHERE name = 'Samsung Galaxy S II'");

We used the * sign to select ALL fields (id,name,price,description). We could, though, also select just a few fields or only the price for example:

mysql_query("SELECT price FROM products WHERE name = 'Samsung Galaxy S II'");

To actually catch the results of this query, we can use the PHP function mysql_fetch_assoc, which catches all results that were selected by the query given as argument to the function as an array. In this case we just selected one entry ( the product 'Samsung Galaxy S II' ) so we could catch it in one variable:

$product = mysql_fetch_assoc(mysql_query("SELECT * FROM products WHERE id = 1"));

Now $product will be an array containing the result found, which would be this array in this case:

array(
"id" => 1,
"name" => "Samsung Galaxy S II",
"description" => "A great smartphone.",
"price" => "549.95"
);

So we could get the price of the product from $product['price'] for example. Or in general:

$query = mysql_query("select query");
$result = mysq_fetch_assoc($query);
$field_value = $result['field_name'];

However this only works when just ONE entry is selected: what if we want to select multiple entries? Then we'll have to use the php while loop: which will use the mysql_fetch_assoc function to catch each entry as an array into a variable. For example:

<?php

$query = mysql_query("SELECT * FROM products"); //select ALL products

//handle each entry found, having 1 entry as array in $product per time the while loop is executed

while($product = mysql_fetch_assoc($query)) {

$products[] = $product;

}

?>

Using the above code we made $products be an array of ALL products found by the query $query. What the while loop basicly does is: find the first result and catch it as array in $product, then execute the while loop's code, then find the next result, catch it as array in $product, then execute the while loop's code again, etc., etc..