Connecting to MySQL with PHP

Setting up a Connection

Before you can get content out of your MySQL database for inclusion in a Web page, you must first know how to establish a connection to MySQL from inside a PHP script. Support for connecting to MySQL is built right into the language. The following PHP function call establishes the connection:

mysql_connect(

Here, address is the IP address or hostname of the computer on which the MySQL server software is running ("localhost" if it's running on the same computer as the Web server software). MySQL server running on the student server does not allow you to establish a connection form other servers. Therefore, address should be "localhost", and the php script should reside in your web directory (public_html or a sub directory in public_html). username and password are the same MySQL user name and password you used to connect to the MySQL server.

Functions in PHP usually return (output) a value when they are called. In addition to doing something useful when they are called, most functions output a value, and that value may be stored in a variable for later use. The mysql_connect function shown above, for example, returns a number that identifies the connection that has been established. Since we intend to make use of the connection, we should hold onto this value. Here's an example of how we might connect to our MySQL server.

$connect = mysql_connect("localhost", "username", "mypasswd");

Since the MySQL server is a completely separate piece of software, we must consider the possibility that the server is unavailable, or inaccessible due to a network outage, or because the username/password combination you provided is not accepted by the server. In such cases, the mysql_connect function doesn't return a connection identifier (since no connection is established). Instead, it returns false. This allows us to react to such failures using an if statement:

@ $connect = mysql_connect("localhost", "username", "mypasswd");
 if (!$connect) {
 echo( "Unable to connect to the database server at this time." );
 exit();
 }

Placing an @ symbol in front of the function name tells the function to fail silently, allowing us to display our own, friendlier error message. if the connection fails and mysql_connect returns false, !$connect will evaluate to true, and cause the statements in the body of our if statement to be executed. Alternatively, if a connection was made, the connection identifier stored in $connect will evaluate to true (any number other than zero is considered "true" in PHP), so !$connect will evaluate to false, and the statements in the if statement will not be executed. exit function causes PHP to stop reading the page at this point.

Another way to display an error message is the following:

$connect = mysql_connect("localhost", "username", "mypasswd") or die (" Unable to connect to the database server at this time. ");

Choosing a Database to use

To select a database, when connecting from PHP, use the function mysql_select_db():

mysql_select_db(database, $connect);

Notice we use the $connect variable that contains the database connection identifier to tell the function which database connection to use. This parameter is optional. When it is omitted, the function will automatically use the link identifier for the last connection opened. This function returns true when it's successful and false if an error occurs.

We can assign the value of the function to a variable:

@ $db = mysql_select_db("database_name", $connect);

If it fails to choose the database, we can display an error message using the following:

if (! $db ) {
echo( "Unable to locate the database_name at this time." );
exit();
}

Another way to display a message is the following:

$db = mysql_select_db(database_name, $connect) or die (" Unable to locate the database_name at this time. ");

Sending SQL Queries with PHP

In PHP, we use the mysql_query function to perform a query.

mysql_query(query, connection_id);

Here query is a string that contains the SQL command we want to execute. As with mysql_select_db, the connection identifier parameter is optional.

What this function returns will depend on the type of query being sent. For most SQL commands, mysql_query returns either true or false to indicate success or failure respectively. Consider the following example:

$sql = "CREATE TABLE Student_info (
         ID INT NOT NULL PRIMARY KEY,
         First_name VARCHA(15),
         Last_name VARCHR(20) NOT NULL,
        Address TEXT
       )";

We can assign the value of the function to a variable:

@ $result = mysql_query($sql, $connect);

We can use the value of the variable to display a message.

if ( $result) {
echo(" Student_info table successfully created!");
} else {
echo("Error creating Student_info table: " . mysql_error() . "");
}

The mysql_error function used here returns a string of text that describes the last error message that was sent by the MySQL server.

For DELETE , INSERT , and UPDATE queries , MySQL also keeps track of the number of table rows (entries) that were affected by the query. Consider the SQL command below,:

$sql = "UPDATE Student_info SET First_name='Sam' WHERE ID =11234;

When we execute this query, we can use the mysql_affected_rows function to view the number of rows that were affected by this update:

if ( $result ) {
echo("Update affected " . mysql_affected_rows() . " rows.");
} else {
echo("Error performing update: " . mysql_error() . "");
}

SELECT queries are treated a little differently, since they can retrieve a lot of data, and PHP must provide ways to handle that information.

Handling SELECT Result Sets

For most SQL queries, the mysql_query function returns either true (success) or false (failure). For SELECT queries this just isn't enough. You'll recall that SELECT queries are used to view stored data in the database. In addition to indicating whether the query succeeded or failed, PHP must also receive the results of the query. As a result, when it processes a SELECT query, mysql_query returns a number that identifies a "result set", which contains a list of all the rows (entries) returned from the query. False is still returned if the query fails for any reason.

@ $result =mysql_query("SELECT Last_name FROM Student_info");
if (!$result) {
echo("Error performing query: " . mysql_error() . "");
exit();
}

Provided no error was encountered in processing the query, the above code will place a result set that contains all the Last names stored in the Student_info table into the variable $result.

The while loop can be used to process the rows in a result set one at a time:

while ( $row = mysql_fetch_array($result) ) {
// process the row...
}

Consider the condition as a statement all by itself:

$row = mysql_fetch_array($result);

The mysql_fetch_array function accepts a result set as a parameter (stored in the $result variable in this case), and returns the next row in the result set as an array. When there are no more rows in the result set, mysql_fetch_array instead returns false.

Now, the above statement assigns a value to the $row variable, but at the same time the whole statement itself takes on that same value. This is what lets us use the statement as a condition in our while loop. Since while loops keep looping until their condition evaluates to false, the loop will occur as many times as there are rows in the result set, with $row taking on the value of the next row each time the loop executes.

Rows of a result set are represented as arrays. If $row is a row in our result set, then $row["Last_name"] is the value in the Last_name column of that row. So here's what our while loop should look like if we want to print all the last names in our database:

while ( $row = mysql_fetch_array($result) ) {
echo("" . $row["Last_name"] . "");
}

To summarize, here's the complete code of a PHP Web page that will connect to our database, fetch all the last names in the database, and display them in HTML paragraphs.

<html>
<head>
<title> List of Last Names </title>
<head>
<body>
<?php
// Connect to the database server
@ $connect = mysql_connect("localhost", "username", "mypasswd");
if (!$connect ){
 echo( "Unable to connect to the database server at this time." );
 exit();
}
// Select the database
@ $db = mysql_select_db("database_name", $connect );
if (! $db ) {
 echo( "Unable to locate the Student_info database at this time." );
 exit();
}
?>
 Here are all the last names in our database: 
<?php

// Request all the Last names
@ $result = mysql_query("SELECT Last_name FROM Student_info");
if (!$result) {
 echo("Error performing query: " . mysql_error() . "");
 exit();
}
// Display each Last name in a paragraph
while ( $row = mysql_fetch_array($result) ) {
 echo("" . $row["Last_name"] . "");
}
?>
</body>
</html>