With the development and availability of open source and powerful tools like PHP and mySQL, combining the power of HTML and dynamic data has never been easier. Now with just a simple learning curve, anyone can get started in creating a truly dynamic web site, one that interacts with its users in every possible way.

This tutorial will help you get started in working with mySQL and PHP. It will assume that you have no prior experience in using mySQL with PHP before. After reading this tutorial, you should get an idea on what a database is, how to use it, and how to access it. You can then go ahead and start reading on the many more advanced topics regarding database systems.

Introduction to mySQL
MySQL has become a very popular database for usage in personal and commercial web sites. It is fast, easy, supported by a wide scale of web based scripting languages, and best of all its free to use. Before we start showing off code, there is one rule to remember when working with mySQL and PHP. That rule is very simple; a basic query consists of several parts in PHP:

Almost all mySQL queries when used with PHP follow the above rule. There are some exceptions like when editing or deleting data.

What is a Query?
A query is simply a command to issue to the database to perform a certain task. The most basic of queries are those to fetch data, edit data, and delete data. Of course there are many more, but for the sake of this tutorial we will be only covering these basics among several others.

How a Database is Divided
You could think of a database as a notebook and the different tables in it as different sections of the notebook.

Think about, for example, your math notebook for school. You probably divided it into different sections; Lectures, Sections, Homework, Labs, Handouts. But whenever you use the notebook, regardless of the section your working on, your using it for a common purpose and that's math! A database can be thought of the same way. It organizes different tables holding different data but revolving around the same purpose.

Let's provide a real life example though of a database instead of all those school related analogies. The LimitLess Studios web site uses a database on a mySQL server to serve dynamic content. Our database is divided into several tables:

In-Depth Look At Tables
If sections of a notebook are like tables in a database, then the individual elements for each section are like the fields for each table. Confused? Let's break it up a second.

Fields, or Attributes if you want to use the literal term, of a table are the different elements that define how the data will be stored in a table. For example, take a look at any page in your notebook and look at some common elements between them. You might have a page number, a date, a lecture number, and finally the actual notes for the lecture. Even though the elements are the same, no two pages will contain the same value for the same element. For example, you can't have two pages numbered 2 in your notebook, but rather page 1 and page 2.

The same analogy can be applied to tables. Consider our Users table mentioned before. The fields that make it up can be:

On each page in your notebook, these common elements have values. Each page can be considered a row in a database. A row is simply a record of data in the database with values for each field being different.

Again look at your notebook, each page has a content for each element, whether it be the date or page number. Yet this content is different but they share the same basic layout. You can't have a page without a page number for example.

Okay enough with the introductions. Let's get down to business and show some code to create a database and one table so we can show examples on some basic queries. Usually, depending on your host, you will not have privilege to create a database unless they do it for you or through some sort of Control Panel they have provided.

Which ever the way, create a database called examples. On the next page, we will provide examples on how to create the table through code or using phpMyAdmin if you have it available to you. phpMyAdmin greatly simplifies the task of creating tables in a database - So if you have it, it would be better to use it!

Okay let's see some code. Use the below code to create a mySQL table using PHP. An explanation will follow but pay close attention to the comments and you should get an idea of what is going on.

What have we done??? You can pretty much guess. Let's divide the code into sections and explain what is going on in each section so that things are easier to understand.

Basically what we are doing here is opening a link to mySQL on the first line. This function, mysql_connect takes three arguments; the address of the mySQL server; this will usually be 'localhost', the User Name, and the Password for the mySQL server.

After this, we selected the database we previously created so we can issue queries to it. Selecting the database is only required once during an entire script's execution, unless the mySQL connection is closed.

Here we have done all the major work. We created a new variable called $sql to house our SQL string. SQL is the language used to construct queries to a mySQL server. The SQL is pretty easy to understand by just looking at it but let's take a look at more in depth to avoid confusion.

CREATE TABLE is the command to, you guessed it, create a new table. We gave the table a name of table0 and created three fields for it, ID, Name, and Age.

ID serves as an index for each record, just like a page number does for a notebook. In the line that reads :

INT means the field will house integers, NOT NULL means the field always requires some sort of value and can not be empty, AUTO_INCREMENT means that the the value for each record will always increment automatically with each record inserted, starting with 1, PRIMARY KEY means that this field is the table's primary key. It will be sorted in accordance to it.

The same idea can be applied to the other two remaining fields, Name and Age, except that they have less properties.

After defining the query we simply issue it to the mySQL database by way of the function mysql_query. This function accepts one argument, and that is the SQL query to issue. We already created the query in a variable called $sql that is why we passed it as an argument.

Finally, we closed our connection to mySQL by way of the function mysql_close.

Inserting Data
Now that we have an actual table in our database, let's learn how to insert data into it. Inserting data into a mySQL database via PHP is a very easy task. Let's see some code first than explain how:



			<?php



// Open Connection To mySQL.

// Replace 'username' and 'password' with appropiate login values for your server.



$mysqlLink = mysql_connect( 'localhost' , 'username' , 'password' );



// Select mySQL Database.



mysql_select_db( 'examples' , $mysqlLink );



// Create Query To Create Table.



$sql = 'INSERT INTO `table0` (`ID`, `Name`, `Age`) VALUES (NULL, \'Mike\', \'19\');';



// Issue Query.



mysql_query( $sql );



// Close mySQL Connection.



mysql_close( $mysqlLink );



?>

The code is basically the same as before, the only important difference is the query we defined in the variable $sql. Let's break it down further to explain the query in detail :

$sql = 'INSERT INTO `table0` (`ID`, `Name`, `Age`) VALUES (NULL, \'Mike\', \'19\');';

The SQL command to insert data into a table. Between the ` marks is the table name we wish to insert data into.

$sql = 'INSERT INTO `table0` (`ID`, `Name`, `Age`) VALUES (NULL, \'Mike\', \'19\');';

The affected fields we wish to insert data into. Remember that a table is made up of fields and each field will require some sort of value.

$sql = 'INSERT INTO `table0` (`ID`, `Name`, `Age`) VALUES (NULL, \'Mike\', \'19\');';

The values for each field. They must be in the same order as the fields. So in the example, NULL will be inserted into ID, Mike will be inserted into Name, and 19 will be inserted into Age. You might be wondering why there are backslashes in the values. Each value to insert must be between single quotation marks but because this is a PHP string started with single quotation marks, any other one's must be escaped so PHP does not produce a parse error.

Here is an interesting note. We are adding a NULL value to the field ID even though in our definition when we created the table, we said we always want this field to expect some sort of value. This is the beauty of the AUTO_INCREMENT option for this field. We do not need to provide a value for this field as mySQL will always do it for us automatically.

Editing Data
Let's learn how to edit existing data in a table. Again let's see the code first :



			<?php



// Open Connection To mySQL.

// Replace 'username' and 'password' with appropiate login values for your server.



$mysqlLink = mysql_connect( 'localhost' , 'username' , 'password' );



// Select mySQL Database.



mysql_select_db( 'examples' , $mysqlLink );



// Create Query To Create Table.



$sql = 'UPDATE `table0` SET `Name` = \'Sam\', `Age` = \'21\' WHERE `ID` = 1;';



// Issue Query.



mysql_query( $sql );



// Close mySQL Connection.



mysql_close( $mysqlLink );



?>

Again the same basic structure, except for the SQL query. Let's further examine it :

$sql = 'UPDATE `table0` SET `Name` = \'Sam\', `Age` = \'21\' WHERE `ID` = 1;';

This is the SQL command to edit an existing value on a specific table in our database.

$sql = 'UPDATE `table0` SET `Name` = \'Sam\', `Age` = \'21\' WHERE `ID` = 1;';

We use the keyword SET to define all fields we want to edit along with their new values. Multiple fields are separated by a comma. Also take notice that we put the new values in single quotation marks, taking care to escape them to avoid any PHP parse errors.

$sql = 'UPDATE `table0` SET `Name` = \'Sam\', `Age` = \'21\' WHERE `ID` = 1;';

This is perhaps the most important part of the query. We have to tell mySQL which record in our database we want to update. In this case we told it to update the record where the field ID has a value of 1.

Remember before we said that ID is our unique identifier for each record in our table. No two records will ever have the same value for ID. What happens if we change our query to be like this:

$sql = 'UPDATE `table0` SET `Name` = \'Sam\', `Age` = \'21\' WHERE `Name` = \'Alex\';';

Name is just an ordinary field. We can have multiple users with the name Alex so this query will update all records that have a Name of Alex, which is not what we want in this case.

Deleting Data
Deleting data is quite easy and here is the code to do it:



			<?php



// Open Connection To mySQL.

// Replace 'username' and 'password' with appropiate login values for your server.



$mysqlLink = mysql_connect( 'localhost' , 'username' , 'password' );



// Select mySQL Database.



mysql_select_db( 'examples' , $mysqlLink );



// Create Query To Create Table.



$sql = 'DELETE FROM `table0` WHERE `ID` = 1;';



// Issue Query.



mysql_query( $sql );



// Close mySQL Connection.



mysql_close( $mysqlLink );



?>

The DELETE command in SQL deletes an entire row from the table, not individual fields like most novice people would think. This command is used to delete an entire row when it is no longer needed, such as when a user deletes his account. His user information is no longer needed, so it is removed from the database.

The code above is pretty simple to understand compared to other queries. Can you figure out how it works? It is pretty simple by just looking at it.

Selecting Data
The best for last! Selecting data from a database is a multi step process in PHP. We usually select data from a database to use somewhere in our script; to display it, use it in some sort of calculation, or compare it to other data.

Selecting data can be done in one of two ways. Firstly, we can prompt to select specific rows based on some sort of condition *OR* we can prompt to select all rows in the table. The latter is easier, so let's see some code on how to do it:



			<?php



// Open Connection To mySQL.

// Replace 'username' and 'password' with appropiate login values for your server.



$mysqlLink = mysql_connect( 'localhost' , 'username' , 'password' );



// Select mySQL Database.



mysql_select_db( 'examples' , $mysqlLink );



// Create Query To Create Table.



$sql = 'SELECT * FROM 'table0`';



// Issue Query.



$result = mysql_query( $sql );



// Loop through results and print them.



while( $row = mysql_fetch_array( $result ) ) {

    echo $row[ 'ID' ] . $row[ 'Name' ] . $row[ 'Age' ];

}



// Close mySQL Connection.



mysql_close( $mysqlLink );



?>

That was a lot of some new code but the basic concept is the same. Let's break down the code into segments and explain what exactly is going on to avoid confusion.

$sql = 'SELECT * FROM `table0`';

This is the SQL command to select data from a table as a whole. It contains the fields we want to select as well as the table we want to select them from. We said before we want to select ALL fields from the table. The * is the wildcard symbol that we need.

$sql = 'SELECT * FROM `table0`';

This is the table we want to select data from. Because we did not provide a specific row or set of rows to select, mySQL by default will select all rows in the database.

$result = mysql_query( $sql );

This is new. We have never before saved the result of a mysql_query function call to a variable so why now? PHP returns a resource of the selected data in the table. To access this data we have to save it in a variable as if it were a normal value.

Now let's take a look on how through PHP we can actually use the data selected by mySQL. For this example we will simply just print the data.



			<?php



while( $row = mysql_fetch_array( $result ) ) {

    echo $row[ 'ID' ] . $row[ 'Name' ] . $row[ 'Age' ];

}



?>

Couple of things to notice here. First there is a new function used called mysql_fetch_array. How does this function work? Well, mysql_fetch_array takes a query result resource as an argument. In our case it is the selected data from the database. It returns an associative array of field values in each row selected.

mysql_fetch_array however returns ONE ROW AT A TIME, that is why we started a while loop. So that we can loop through each row selected.

The associative array has the field names selected as keys and their values as the array value itself. That is why when we printed each row we did this:

echo $row[ 'ID' ] . $row[ 'Name' ] . $row[ 'Age' ];

Notice that each key in the array $row is the name of the fields in our table!

Other Examples of Data Selection
We can select data from a table using more complicated queries, but the basic idea to use them in code is the same as explained before. Here are several more examples of selecting data:

$sql = 'SELECT * FROM `table0` LIMIT 0, 30 ';

Select all fields and all records from a table, but only display 30 records starting with the first record.

$sql = 'SELECT * FROM `table0` LIMIT 10, 30 ';

Select all fields and all records from a table, but skip the first 10 records, and display the first 30 records after them.

$sql = 'SELECT `ID` , `Name` FROM `table0`';

Select fields ID and Name and all records from a table.

$sql = 'SELECT `ID` , `Name` FROM `table0` Where `ID` > 10';

Select fields ID and Name from a table, where the field ID has a value bigger than 10.

Conclusion
In this tutorial we covered the basics of using mySQL from PHP as well as covered the most basic and most frequently used SQL queries to insert, edit, delete, and select data.


	Basic mySQL Queries And PHP Tutorial Author - Limitless (http://www.llstudios.net)