MySQL Database Integration Tutorial Author - Scrowler (http://forums.biorust.com/member.php?userid=66)

Now that you’ve learned the basics of PHP, you'll want to start back-ending your website with database connectivity. The most common PHP-connective database server is called mySQL and, thankfully, it is as easy to use as PHP itself! In this tutorial you will learn how to connect to a database, extract data and print it on a page, order data, insert data into a database, edit data and delete data.

Connecting and selecting the database
PHP has its own function to connect to mySQL databases which, ironically enough, is called mysql_connect(). The arguments contained within its brackets are defined as host, user, password. I remember this by the acronym: HUP (because it sounds cool). The host is “localhost” by default. Most web servers have this host added if not defaulted, so we need not worry about this argument too much. The second is the username that has permissions to access the database you are connecting to...  but at the moment we are just connecting to the server, so this username is a username that has permissions to just connect to the mySQL server. Fairly obviously, the password is the password for the username; it is not encrypted in any way.

For those who don’t understand this, here’s an example of a PHP script connecting to a database server:

<? mysql_connect(“localhost” , “joeblog” , “opensesame”); ?>

Now we need to select the individual database, and for this we use a function called mysql_select_db() whose sole argument is the database name as it appears on the server (this is case sensitive!).

Here’s some example PHP code showing us how to select a database:

<? mysql_select_db(“joes_database”); ?>

Now that we are successfully connected to a database, we now can start managing our data! The 4 main mySQL commands are SELECT, INSERT, UPDATE, and DELETE. I think it’s self explanatory what these commands do…

Selecting Data
All of the mySQL commands use the PHP function mysql_query() to execute them. Selecting is probably one of the most commonly used mySQL commands, used to retrieve (and often display) data from your chosen database.  To enable us to do this we will have to venture a little bit into mySQL language, but don't worry - its not too hard!

The basic format of mySQL SELECT command is as follows:
SELECT columnname FROM tablename

You can select all of the columns by using a * instead of the column name (this is commonly called a wildcard). You cannot just echo a mySQL command like this, however, because if you do it will output something along the lines of 'Array #1'. We need to play with the function mysql_fetch_array() to actually use the data. Usually this function is used in a while() PHP statement, which cycles through each row in a mySQL table and loads the contents of the selected column.

You can add ORDER BY commands into the end of SELECT commands to make them order the data that mySQL will select. The format is thus:
ORDER BY ColumnName ASC/DESC

Of course you don’t write ASC/DESC, you just choose one - ASC means ascending, DESC means descending. Now, imagine we have a database called joeblog containing a table of data called JoeBlog. Here’s an example of how we cycle through the 4 rows of data that are in the table JoeBlog and column Joe:

<? Mysql_connect(“localhost”,”joeblog”,”opensesame”) or die(mysql_error()); Mysql_select_db(“joes_database”); $query = mysql_query(“SELECT * FROM JoeBlog ORDER BY Joe ASC”) or die(“There was an error!”); while( $row = mysql_fetch_array ( $query ) ) { echo $row[‘Joe’].’ this is one of the columns.<br>’; } echo ‘Finished!’; ?>

In the variable named “row” the name in the square brackets and apostrophes is the name of the column we are printing data from. It is necessary to use all the variables, because it makes the whole process a lot smoother, and tidier - we don’t want a line of code with 3 or 4 functions inside each other!  

And that's it - we’ve just successfully printed 4 lines of data from a database! I didn’t introduce our friend mysql_error() before, but it tells us of the nature of a mySQL bug if one occurs.  Its a nice little debug feature included as standard in PHP.

Inserting data
Inserting data is much more simple than selecting it, because you don’t need to use as many variables.  In fact, you just need to use one line of code (pretty much) and for this we use the mySQL command:

INSERT INTO TableName(Column1,Column2,Column3) VALUES(‘data1’,’data2’,’data3’)

This may look advanced but its actually extremely simple!   TableName obviously represents the name of the table you want to fill with the new data. Column1, Column2 and Column3 are just there as examples, and you can  you replace them with the name of the columns in your table at leisure.  Remember that if you don’t include a column, mySQL just leaves that cell blank on the new entry.   The values part of the above command includes the data. “data1” goes into “Column1”, “data2” into “Column2”, etc.

So let’s put this into PHP. We are just going to insert one string of data into our one column, bringing us up to a total of 5 rows.

<? mysql_connect(“localhost”,”joeblog”,”opensesame”) or die(mysql_error()); mysql_select_db(“joes_database”); $ourdata = “the data we are going to put into the database”; mysql_query(“INSERT INTO JoeBlog (Joe) VALUES(‘$ourdata’)”) or die(mysql_error()); echo ‘If mysql_error() didn’t spit anything at you and you are reading this message then the insert worked fine!’; ?>

Now we get on to the easy commands...

Deleting and Editing data
There really isn’t much explaining to do on the subject of these two - they are just the same as INSERT, except with a different command:

UPDATE TableName SET ColumnName = Value WHERE Row = id

E.g. UPDATE JoeBlog SET Joe = ‘the data we are going to put into the database but its now edited’ WHERE id = 5

Our table doesn’t have an ID field but we’re going to pretend it does (you can, in fact, use any field you like). ID fields just give each row a unique ID number, which are quite often automatically incremented.  They sometimes make database structures a little easier for humans to comprehend, and ensures that the data in only ONE column is updated by the UPDATE command.

I’ll tie the example in with the DELETE command’s example. Delete has the format:

DELETE FROM TableName WHERE id = Value

E.g. DELETE FROM JoeBlog WHERE id = 5

The two examples above effectively edit the values we entered into our database earlier, and then delete it. Here's the combined PHP code for these operations:

<? mysql_connect(“localhost”,”joeblog”,”opensesame”) or die(mysql_error()); mysql_select_db(“joes_database”); mysql_query(“UPDATE JoeBlog SET Joe = ‘the data we are going to put into the database but its now edited’ WHERE id = 5”) or die(mysql_error()); echo ‘If you are reading this then the data has been successfully edited. Remember that our id field is imaginary at the moment but when you make your own table you will include the id field.’; mysql_query(“DELETE FROM JoeBlog WHERE id = 5”) or die(mysql_error)); echo ‘If you are reading this then the data has been successfully deleted.’; ?>

Well, that's it for my introduction to mySQL/PHP.  This tutorial was not aimed to inform you on how to set up mySQL tables (that may come at a later stage), but simply explain how to use them.  Given the ease of setting up databases, though, a tutorial on the absolute basics would be like teaching your granny to suck eggs.

If you have any questions regarding this tutorial (written exclusively for BioRUST) then please contact me on the forums by clicking on my name below and following the links to either Private Message or Email (you may have to register).  Bye for now!