More on PHP

PHP and MySQL

MySQL is the most common open-source database system. It is very popular across the globe and has very good integration with PHP. Data is stored in tables within a particular database. Tables are in turn made up of rows and columns.  SQL on the other hand is a query language used to interact between an application and the database. Each type of database has its particular SQL language, so it can be a bit tricky to work with different database servers. For the sake of this blog, I will only speak about MySQL.

The first thing to do when interfacing between PHP and MySQL is to open a connection. This can be done using the mysql_connect() function which takes the servername, the username and the password as parameters, among others. Most of the times, this connection is stored in a string variable, as shown below:

<?php
      $con = mysql_connect("localhost","peter","abc123");
      if (!$con)
      {
            die('Could not connect: ' . mysql_error());
      }
     

      // some code

      mysql_close($con);
?>

The last line before the closing tag shows the connection being closed. This is an important step because malicious users can make use of open connections. Also, open connections will increase the load on the server. The code in the if statement is executed if the connection fail. The mysql_error() shows the exact reason for the failure. This is very important to troubleshoot the issue.

All the queries work this same from PHP as if they were executed in MySQL Workbench, so I will not go into INSERT and UPDATE statements.

Selecting Data

Although the SQL command for selecting data is still the same, the results returned need to be manipulated to be used for the developer's needs. The code below is to select all the rows in the table and display them one after the other.

mysql_select_db("my_db", $con);

$result = mysql_query("SELECT * FROM Persons");

while($row = mysql_fetch_array($result))
  {
  echo $row['FirstName'] . " " . $row['LastName'];
  echo "<br />";
  }

The first line above shows the code used to select a Database from the server. This is important because a database server might have more than one database. The result variable holds the data returned by the mysql_query () function. The function mysql_fetch_array() returns the next row in the recordset. The while loop is used to loop through all the records in the recordset. Each row is an array of columns and the value of each row can either be accessed by the field name or by the index. $row['0'] and $row['FirstName'] will return the same value.

Show Results in HTML

The results can then be formatted by also echoing the HTML tags. This can be seen in the example below:

echo "<table border='1'>
<tr>
<th>Firstname</th>
<th>Lastname</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['FirstName'] . "</td>";
  echo "<td>" . $row['LastName'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

This will create an HTML table dynamically with all the records in the table.

PHP and ODBC

ODBC is an API which allows PHP to connect to a data source.  With such a connection, PHP can connect to any database or computer on the network. In this example I will setup a connection with a MS Access Database. To create an ODBC connection to a MS Access Database, these steps have to be followed:

1. Open the Administrative Tools icon in your Control Panel.
2. Double-click on the Data Sources (ODBC) icon inside.
3. Choose the System DSN tab.
4. Click on Add in the System DSN tab.
5. Select the Microsoft Access Driver. Click Finish.
6. In the next screen, click Select to locate the database.
7. Give the database a Data Source Name (DSN).
8. Click OK.

This process required access to the location of the database. Next, we have to setup a connection to the ODBC. the odbc_connect() function is used to accomplish this task. This function takes 4 parameters: data source name, username, password and optional cursor type.  The code below shows this:

$conn=odbc_connect('northwind','','');
$sql="SELECT * FROM customers";
$rs=odbc_exec($conn,$sql);

Retrieving Records

The function odbc_fetch_row() function can be used to return records from the result-set. This function takes the OSBC result identifier and an optional row number. To read fields from a record, the odbc_result() function is used and this function takes the ODBC result identifier and a field number or name.

<?php
$conn=odbc_connect('northwind','','');
if (!$conn)
  {exit("Connection Failed: " . $conn);}
$sql="SELECT * FROM customers";
$rs=odbc_exec($conn,$sql);
if (!$rs)
  {exit("Error in SQL");}
echo "<table><tr>";
echo "<th>Companyname</th>";
echo "<th>Contactname</th></tr>";
while (odbc_fetch_row($rs))
  {
  $compname=odbc_result($rs,"CompanyName");
  $conname=odbc_result($rs,"ContactName");
  echo "<tr><td>$compname</td>";
  echo "<td>$conname</td></tr>";
  }
odbc_close($conn);
echo "</table>";
?>

The php code above shows the whole process using a MS Access Database and ODBC instead of MySQL. Last but not least, the connection will be closed. As we can see, the code is very similar to the mysql_fetch_array() function used before.