PHP and MySQL Programming/Database Connectivity

You can only do so much with storing information in files. When you need to store large amounts of data, and perform intensive number crunching on that data, there is nothing better than a good SQL database. In this section, we'll discuss connecting PHP to a MySQL database and perform queries and retrieve data back from the database.

Opening a Connection to a MySQL Database

The first thing that we need to do before we can interact with any database, is to open up a connection to that database server. This is done by using the mysql_connect() function, which returns a database handler, much like a file handler when dealing with files. The database handler is then used to select the active database to use.

Here is the code to setup a connection to the database server, and to select a database to use:

 $db = "database1";
 $link = mysql_connect("localhost", "username", "password");
 mysql_select_db($db, $link);

Or

<?php
  $conn = new mysqli("localhost", "root", "mySecretDBpass", "thegeekstuff");
  
  if ($conn->connect_error) {
    die("ERROR: Unable to connect: " . $conn->connect_error);
  } 

  echo 'Connected to the database.<br>';

  $result = $conn->query("SELECT name FROM employee");

  echo "Number of rows: $result->num_rows";

  $result->close();

  $conn->close();

Creating a Query

Once a connection to a database has been made, you will inevitably want to perform a database query. To create a query on the selected database, we use the mysql_query() function. If you use a SELECT query, then the data returned from that query will be passed to the mysql_query() function, which will in turn return it to a variable which you can specify. In the following example, two queries are made, the first does not return any data, and the second does.

 // A Query without any returned data
 mysql_query ("INSERT INTO `table1` ('val1', 'val2')");
 // A Query with returned data
 $query = mysql_query("SELECT * FROM `table1`");

Retrieving data from a SELECT Query

There are many methods for retrieving data from a SELECT query.

If we take, for example, the following code:

 # --- Connect To Database ---
 $db = "db1";
 $link = mysql_connect("localhost", "user", "pass");
 mysql_select_db($db, $link);
 
 # --- Select Info from Database ---
 $result = mysql_query ("SELECT val1, val2 FROM tbl1");

To now retrieve the data from the $result variable, we can use one of many methods. The recommended method, however, is to sequentially go through each row of the table, storing it into a one-dimensional array. We do this by using the mysql_fetch_row() function, passing it the variable into which the result is stored. Here is a simple example:

 while ($row = mysql_fetch_row($result)){
    foreach ($row as $field) {
       print "$field . ";
    }
    print "";
 }

This will simply output the result in table-like format.

Here is another example of using this method:

 $counter = 0;
 while ($row = mysql_fetch_row($result)){
    $val1[$counter] = $row[0];
    $val2[$counter] = $row[1];
    $counter++;
 }
 $numRows = $counter;

The above example, simply splits the results up into multiple one-dimensional arrays, for easy manipulation.

Closing a Database Connection

It is not always necessary to close a connection when you are finished, but it is advised. It is, however, necessary to close the connection to the database if you want to open up a new connection to a different database.

To close a connection to a database, we use the mysql_close() function, as follows:

 mysql_close();

Error Handling

It is useful when debugging, and even when you just want to make sure that a database does not behave unexpectedly. Once a query has been created via the mysql_query() function, any error messages generated will be stored in the mysql_error() function. Here is a sample code snippet to display a error message. However, when there is no error messages, a blank string is returned.

 print mysql_error();