So you're interested in learning how to incorporate MySQL into your Web Applications, eh? Well good, you should be. If you've been using PHP for a little while (I'm assuming you know the PHP Basics) then you've probably realized the language is quite useless without some form of a database to store data in. There are many different forms of databases which PHP supports, so your options aren't very limited. MSSQL, Firebird SQL, PostgreSQL, Oracle, they're all other options that are open to you as well.
Why MySQL though? It has various advantages over the others. For one, it's Open Source. Second, it has been ported to many different Operating Systems (means it can be run on almost anything). However, what probably attracts most people is this: It's fast and free. The lack of price on MySQL is what makes it so common, that's why Oracle is really only found on Corporation servers. This tutorial isn't aimed at those people though, this is aimed for the enthusiast =P
Enough jabber already, you should understand why you're choosing MySQL by now anyways. However, I want to mention that for you to use MySQL you must have it either installed on your server or you must have a host that supports it. PHP must also be enabled to support it. If you've got that, then great, lets start.
I plan to discuss the following PHP Functions in this tutorial, all are for interacting with MySQL:
mysql_connect()
mysql_select_db()
mysql_query()
mysql_num_rows()
mysql_fetch_array()
resource mysql_connect ( [string server [, string username [, string password [, bool new_link [, int client_flags]]]]] )
The mysql_connect() function is where it all begins. To use MySQL you need to form a connection between your script and the database. The function usually only needs three parameters (host, username, password). Here's an example:
<?php
$dbConn = mysql_connect ('localhost', 'root', 'mysqlPassword');
?>
As seen above, the three parameters are "localhost"(host), "root"(username), "mysqlPassword"(password). Of course these would change depending on your settings. Chances are though you'll always use "localhost" for the host parameter. The only time you should have to use another host address is if your Web Host has MySQL on a different server. You can always ask them for the details if you think they may have MySQL setup like that.
bool mysql_select_db ( string database_name [, resource link_identifier] )
So you've connected your script to MySQL...now what? Well, everything in MySQL is kept in Databases. You might have unlimited databases, 10, 50, 1, it doesn't matter. What does is that you have at least one database setup to store whatever information you wish to put in. Information is further broken into Tables within databases, but that'll be discussed more with the mysql_query() function.
mysql_select_db() only requires one parameter, but it's good practise to include the second. These two parameters are (database name, db_conn_variable). Using the above example to build on:
<?php
$dbConn = mysql_connect ('localhost', 'root', 'mysqlPassword');
$dbSelect = mysql_select_db ('testDB', $dbConn);
?>
You'll notice I used the variable which the connection is assigned to. This just sets up an identifier, so if you wish to have multiple connections with multiple databases (wasteful, but possible), you can do it like so. The database in example is called "testDB". This'll have to be changed to whatever the database really is. You make that decision =P
resource mysql_query ( string query [, resource link_identifier] )
MySQL uses the SQL Language for input and manipulation. To pass SQL to the database you need to use the mysql_query() function. Chances are you'll only use one parameter with this, and that's the Query operation. I won't be going into detail about the SQL Language, but, you can reference http://www.w3schools.com/sql/default.asp if need be. Nearly anything you would pass directly to the MySQL Server can be passed through the mysql_query() function. Obviously a few things are unneeded, such as "USE ___database_name___;", as you've already done that with mysql_select_db().
<?php
$dbConn = mysql_connect ('localhost', 'root', 'mysqlPassword');
$dbSelect = mysql_select_db ('testDB', $dbConn);
$dbQuery = mysql_query ("SELECT * FROM testTable WHERE id > 40 AND tab_owner = 'theOwner' ORDER BY id DESC");
?>
int mysql_num_rows ( resource result )
One very common thing that people want to do (whether for debugging purposes or to actually display the data) is to find out how many rows come back from a query. This is an amazingly simple process, PHP has a built-in feature to do it for you. That being the mysql_num_rows() function. It takes one parameter only, and that being the variable that holds the query you wish to process. In our current example that variable would be "$dbQuery". If I had multiple queries going on then I'd use the variable of the query I want to number of rows returned about. Extremely simple.
<?php
$dbConn = mysql_connect ('localhost', 'root', 'mysqlPassword');
$dbSelect = mysql_select_db ('testDB', $dbConn);
$dbQuery = mysql_query ("SELECT * FROM testTable WHERE id > 40 AND tab_owner = 'theOwner' ORDER BY id DESC");
$numRows = mysql_num_rows ($dbQuery);
echo $numRows;
?>
array mysql_fetch_array ( resource result [, int result_type] )
I wasn't actually going to include this function in this tutorial, but I figured you'd want to know how to output the rows you query =P However, to compromise, I won't discuss the PHP aspects (as you'll see, the while() loop). I'm assuming you know how that works, so it'll go without saying.
mysql_fetch_array() is used to assign to an array the data of a selected row. mysql_fetch_array() has an advantage over mysql_fetch_row(), that being, it can store the data as an Associative Array. This means you can refer to the array items by the column name, not by a number. Lets get the example up, so I can better explain.
<?php
$dbConn = mysql_connect ('localhost', 'root', 'mysqlPassword');
$dbSelect = mysql_select_db ('testDB', $dbConn);
$dbQuery = mysql_query ("SELECT * FROM testTable WHERE id > 40 AND tab_owner = 'theOwner' ORDER BY id DESC");
$numRows = mysql_num_rows ($dbQuery);
echo $numRows;
{
echo $myRow [''id''];
echo $myRow [''myOtherColumn''];
echo $myRow [''tab_owner''];
}
?>
1. Connec to MySQL
2. Get our database
3. Query the database for the material we want
4. Assign to a variable the amount of rows returned
5. Display the number of rows
6. Begin a loop that'll go through all the rows returned from the query. Assign array to variable
7. Display data from array. Refer to columns through their names, not number
8. Close off the while() loop
So you've now seen a very simple and straight-forward example evolve to encompass various functions. You've already been equipped with the very basic information needed to go forward and create web applications backed by a MySQL Database. You'll want to further your knowledge, if you go into more complex situations. But, you'll still need the knowledge shown to you through this tutorial. Now, go and apply it =P The best way to learn is by using it in a real environment.