PHP and MySQL: Introduction
MySQL is a popular database choice for PHP applications. This page focuses on PHP-based MySQL functions that access a MySQL database. We begin by listing the commonly used MySQL functions and then provide two examples to get started.
MySQL APIs
We begin with signature and description of some of the common MySQL APIs.
/* Establish/Close connection */ $dbs = mysql_connect($db_host, $user, $password, $db_name); $result = mysql_close($db); /* List all databases available and select one from the list */ $rid = mysql_list_dbs(); $result = mysql_select_db($database_name); /* Run a query and parse the output */ $rid = mysql_query($query); $num_rows = mysql_num_rows($rid); $row = mysql_fetch_row($rid); /* Retrieve error number and error string */ $errno = mysql_errno(); $error = mysql_error();
Let us describe these functions.
mysql_connect() function allows us to establish a connection to a database server. This function takes three arguments: address of the host which runs the database server, username, and password for the database.
Once we are done accessing the database, we can use mysql_close() to close the connection by passing the $db identifier returned from the mysql_connect() call. Beyond this, any database operation result in an error.
A database server can have many databases. We can use mysql_list_dbs() to find names of all the databases present on a database server. Upon success, this function returns a resource id ($rid). We can pass this resource id to (yet) another function, mysql_fetch_row() and retrieve all the names. Upon failure, mysql_list_dbs() returns FALSE.
We can use mysql_select_db() function to select a specific database. After this step, all query-related APIs will work on the selected database. This function returns a bool (boolean) with TRUE upon success and FALSE upon failure.
Function mysql_query() helps us run SQL queries. This function returns a resource id ($rid). We can pass this resource id to mysql_fetch_row() function to retrieve individual table rows. Further, we can also use mysql_num_rows() function to get the number of rows present in the query output.
Lastly, when an error occurs, mysql_errno() and mysql_error() functions return the error number and the error string respectively.
Getting Started
To get started, we provide (below) two simple PHP examples that connect to a local MySQL database server and run some SQL queries.
ExampleA: Connect to a database server
We use mysql_connect() to connect to a database running on the local machine ("localhost"). We pass credentials as "user1001" username and "password1001" password. The example then uses mysql_close() to close the connection to database server. If the connect step (mysql_connect() call) fails, then the example uses mysql_errno() and mysql_error() functions to print the error information.
<!-- Filename: mysqldb_connect.php -->
<?php
$db = mysql_connect("localhost", "user1001","password1001");
if (!$db) {
$errno = mysql_errno();
$error = mysql_error();
echo "mysql_connect() failed with error: $error ($errno)";
} else {
echo "mysql_connect() succeeded";
}
mysql_close();
?>
Please note that for above program to work, we need to have a MySQL database server running on the localhost. Also, the server should have a MySQL user with username as "user1001" and password as "password1001". To run the above example (and all examples on this page), please replace the values for your own database server.
To run the above program, let us first store this example in a file (we call it "mysqldb_connect.php") and keep it at a location that is accessible to the web server (in Fedora/Red Hat Linux, the default location for web server is "/var/www/html/"). Next, we need to ensure that the web server itself is running. If we were to load this file from a browser (let us say, "http://localhost/mysqldb_connect.php"), then the browser would display the following:
Figure: Basic MySQL Connect
However, the above mysql_connect() call could fail due to several reasons.
The first reason for error could be that the mysqld (MySQL daemon) may not be running; if this is the case, then we would get an error of type, "Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'"; the corresponding error number would be 2002. On Linux (Fedora/Red Hat), the command "service mysqld status" should tell us if the mysqld is running or not. If it is not running, then we can start the service using "service mysqld start"; most probably, we need to have root access for running the above two commands.
The second reason for error could be passing an incorrect hostname; if we were to (incorrectly) pass "localhost232" instead of "localhost", then MySQL would throw an error saying "Unknown MySQL server host 'localhost232'"; the corresponding error number would be 2005.
The third reason for error could be passing incorrect user credentials. If we were to pass incorrect combination of username (let us say, "dafd") and password, then the error would be "Access denied for user 'dafd'@'localhost' (using password: YES)"; the corresponding error number would be 1045.
ExampleB: Connect to a database server and run SQL queries
Our second PHP example connects to a database server, lists available databases, connect to a database named "test", and finally lists all the tables available in the "test" database. For reasons of brevity, the code also has a small function, show_error() that prints both the error number and error name, if we run into a failure.
Once connected to the database server, the example selects a database on the server (a database server can have a large number of databases) using mysql_select_db() function.
As a database server can contain several databases, a database can itself contain several tables. Simply put, a table holds one or more rows of data records; each data record is essentially a tuple of data elements, where each tuple is identified as a column. PHP allows us to easily list all the tables present in the database using a simple "SHOW TABLES" SQL query. We can mysql_query() function to run this query (and as we will see, other SQL queries as well!); we can pass this resource id to mysql_fetch_row() function to retrieve individual table rows.
Here is the example:
<!-- Filename: mysqldb.php -->
<?php
/* Print Error */
function show_error($failure_step) {
$errno = mysql_errno();
$error = mysql_error();
echo "$failure_step failed with error: $error ($errno)";
}
/* Connect to the database server */
if (!mysql_connect("localhost", "","")) {
show_error("mysql_connect");
return;
}
/* List available databases */
$dbs = mysql_list_dbs();
if (!$dbs) {
show_error("mysql_list_dbs");
return;
} else {
echo "Available databases are: \n";
while ($row = mysql_fetch_row($dbs)) {
echo $row[0], "<br>";
}
}
/* Connect to the database named "test" */
if (!mysql_select_db("test")) {
show_error("mysql_select_db");
return;
}
/* Next, print all the tables in the database "test" */
$query = "SHOW TABLES FROM test";
$result = mysql_query($query);
if (!$result) {
show_error("mysql_query");
return;
} else {
echo "List of all tables available with database test: \n";
while ($row = mysql_fetch_row($result)) {
echo $row[0], "<br>";
}
}
/* Once done, end the connection */
mysql_close();
?>
If we were to view this file (we call it "mysqldb.php") using a browser (URL: "http://localhost/mysqldb.php"), then the browser output would display the below-provided text. The output shows that the database server has three databases; two of these databases ("information_schema" and "test") are available by default with all MySQL servers! The output also shows that "test" database has three tables; please note that these tables are not available by default and were added manually.
Available databases are: information_schema social_network_db test List of all tables available with test: favorite_info friends_address my_friends