Programming:PHP/mysql
From Wikibooks, the open-content textbooks collection
Return to PHP.
Contents |
[edit] MySQL
MySQL is the most popular database used with PHP. PHP with MySQL is a powerful combination showing the real power of Server-Side scripting. PHP has a wide range of MySQL functions available with the help of a separate module. In PHP5, this module has been removed and must be downloaded separately.
MySQL allows users to create tables, where data can be stored much more efficiently than the way data is stored in arrays.
In order to use MySQL or databases in general effectively, you need to understand SQL, or Structured Query Language.
[edit] How to - Step By Step
[edit] Connecting to the MySQL server
PHP has the function mysql_connect to connect to a MySQL server which handles all of the low level socket handling. We will supply 3 arguments; the first is the name of your MySQL server, the second a MySQL username and third, a MySQL password. In this example, it is assumed your server is localhost. If you are running a web server on one system, and MySQL on another system, you can replace localhost with the IP address or domain name of the system which MySQL resides on (ensure all firewalls are configured to open the appropriate ports). mysql_connect returns a link_identifier that we can now use for communicating with the database. We will store this link in a variable called $link.
<?php
$link = mysql_connect ("localhost", "your_user_name", "your_password");
?>
[edit] Selecting a Database
The next part is selecting which database you would like to work on. PHP has the function mysql_select_db which will allow you to select the database you would like to work on. The first argument, the database name, is required. The second argument is called the link_identifier, which is optional and is what we generated by the mysql_connect command. If it is not specified PHP will use the last link opened by mysql_connect, however for completeness we'll use it here to highlight exactly what is happening.
<?php
mysql_select_db ("your_database_name", $link);
?>
[edit] Running a Query
We have connected to the mysql server and then selected the database we want to use, now we can run an SQL query over the database to select information, do an insert, update or delete. To do this we use mysql_query. This takes two arguments: the first is an SQL query string which is required and the second is our link_identifier which is optional. Like mysql_select_db, if the link_identifier is not included it will use the last link opened. However it is included here to show exactly how mysql_query knows what it is communicating with. If we are doing a select sql statement mysql_query generates a resource or the Boolean false to say our query failed, and if we are doing a delete, insert or update it generates a Boolean, true or false, to say if that was successful or not.
The basic code for running a query is the php function "mysql_query(query, [database])". The "query" argument is a MySQL query. The optional database argument is a database connection, which will (and generally does) default to the last connection opened (here, the connection represented by $link). For example, to return the query "SELECT * FROM customers ORDER BY customer_id ASC", you could write
<?php
mysql_query("SELECT * FROM customers ORDER BY customer_id ASC");
?>
However, this straightforward method will quickly become ungainly due to the length of MySQL queries and the common need to repeat the query when handling the return. All (or almost all) queries are therefore made in two steps. First, the query is assigned a variable (conventionally, this variable is named "$query" or "$sql_query" for purposes of uniformity and easy recognition), which allows the program to call simply "mysql_query($sql_query)".
$sql_query = "SELECT * FROM customers ORDER BY customer_id ASC";
Secondly, to handle the information returned from the query, practical considerations require that the information returned also be assigned to a variable. Again by convention rather than necessity (i.e. you could name it anything you wanted), this information is often assigned to "$result", and the function is called by the assignment of the variable.
$result = mysql_query ($sql_query, $link);
It is important to understand that this code calls the function mysql_query, in addition to assigning the return to a variable "$result". [NOTE: The queries that ask for information -- SELECT, SHOW, DESCRIBE, and EXPLAIN -- return what is called a resource. Other types of queries, which manipulate the database, return TRUE if the operation is successful and FALSE if not, or if the user does not have permission to access the table referenced.]
To catch an error, for debugging purposes, we can write:
<?php
$result = mysql_query ($sql_query, $link)
or die (mysql_error () . " The query was:" . $sql_query);
?>
If the function mysql_query returns false, PHP will terminate the script and print an error report from MySQL (such as "you have an error in your SQL syntax") and the query.
Thus, our final code would be, assuming that there were a database connection named $link:
<?php
$sql_query = "SELECT * FROM customers ORDER BY customer_id ASC";
$result = mysql_query ($sql_query, $link)
or die (mysql_error () . " The query was:" . $sql_query);
?>
[edit] Putting it all together
In the previous sections we looked at 3 commands, but not at how to use them in conjunction with each other. So let's take a look at selecting information for a table in our mysql database called MyTable, which is stored in a mysql database called MyDB.
<?php
//Connect to the mysql server and get back our link_identifier
$link = mysql_connect ("your_database_host", "your_user_name", "your_password");
//Now we select which database we would like to use
mysql_select_db ("MyDB", $link);
//Our SQL Query
$sql_query = "Select * From MyTable";
//Run our sql query
$result = mysql_query ($sql_query, $link);
//Close Database Connection
mysql_close ($link);
?>
[edit] Getting Select Query Information
Well that doesn’t help, because what are we to do with $result? Well when we do a select query we select out information from a database we get back what is known as a resource, and that is what is stored in $result, our resource identifier. A resource is a special type of PHP variable, but lets look at how to access information in this resource.
We can use a function called mysql_fetch_assoc it takes one parameter, our resource identifier $result, and generates an associative array corresponding to the fetched row. Each column in the table corresponds to an index with the same name. We can now extract out information and print it like so:
<?php
//Connect to the mysql server and get back our link_identifier
$link = mysql_connect("localhost", "your_user_name", "your_password")
or die('Could not connect: ' . mysql_error());
//Now we select which database we would like to use
mysql_select_db("MyDB") or die('could not select database');
//Our SQL Query
$sql_query = "Select * From MyTable";
//Run our sql query
$result = mysql_query($sql_query)or die('query failed'. mysql_error());
//iterate through result
while($row = mysql_fetch_assoc($result))
{
//Prints out information of that row
print_r($row);
echo $row['foo'];
//Prints only the column foo.
}
// Free resultset (optional)
mysql_free_result($result);
//Close the MySQL Link
mysql_close($link);
?>
