Search


print PDF Ireland
Interface with mysql

Introduction

Php allows a connection very simple with many databases. when a database isn't directly supported by php, it's possible to use an ODBC driver, standard driver to communicate with the databases.

The communication with the databases is possible with SQL requests, a langage of 4ème generation which are known by many SGBD

In the examples below, the system of databases management used is MySQL, a free and quick SGBD working under linux. As the majority of web servers (such as the amazing apache server) work under linux, in this fact MySQL is the most used with Php.

The variables declaration

The first step consists to declare variables which will be able to the connection to the databases (there are the functions parameters of database connection)

$user: the user name
$passwd: the password
$host: the host( the computer which hosts the database)
$bdd: the database name


Reminder: the variables aren't visible by our visitors because the script (having the .php extension) is consistenly interpreted by the web server.

This variables are in fact parameters of the function allowing the connection to the database. So it's possible to give directly the values of these variables in each function allowing the connection, but quickly this becomes boring when there are a changement of password for example. the more easy is to store all this values in a alone file. Thus when there are a changement of password, all the files using variables are update.

The basics functions

Php gives a large choice of functions allowing to use the databases. However, amoung all these functions, four functions are fundamental:

The function of connection to the server, the function of database choice, the function of requests, the function of deconnection.

With the MySQL SGBD, this fucntions are the followings: mysql_connect, mysql_select_db, mysql_query, mysql_close.

Errors management

Some of this functions return a value allowing to know the connection status, thus it's possible to stop the script to avoid the errors in cascading. Two methods allow to make this operation:

The result store of the execution of the function in a variable. By example:
$connect = mysql_connect($host,$user,$passwd);

The utilisation of the die() function in case of execution error. If the function returns the 0 values (e.g if there is an error) the function die () returns an error message. For example:
mysql_connect($host,$user,$passwd) or die("error connection on the server $host");


Results processing

When we make a selection request thanks to the function mysql_query, it is important to store the result of the request (the records) in a variable which calls usually $result.

However, this variable contents all the records and isn't exploitable such as. Thus we use the function mysql_fetch_row() which cuts the result lines in column (for exemple Name, Address, ...) and associates them to an array variable in the order where they arrive.

Thus, imagine a table called "link" containing the name and the URL of web site. It is possible to recover all the records and display them in an array.

<html>
<head>
<title>Links</title>
<head>
<body>

<table border="1" cellpadding="0" cellspacing="0">

<tr>
<th>Name of the site</th>
<th>URL</th>
</tr>

<?php
/* Parameters Declaration of connection */
$host = the-machine;
/* Generally the machine is localhost */
/* E.g the machine on which the script is hosted */
$user = your-login;
$bdd = Database-name;
$passwd = Password;

/* Server connection */
mysql_connect($host, $user,$passwd) or die("connection error on the server!");
mysql_select_db($bdd) or die("connection error on the database");

/* Creation and send of the request */
$query = "SELECT name,url FROM sites ORDER BY name";
$result = mysql_query($query);

/* Results fetching */
while($row = mysql_fetch_row($result)){
$Name = $row[0];
$Url = $row[1];
echo "<tr>

<td><a href="$Url">$Name</a></td>

<td>$Url</td>

</tr>
";
}

/* Deconnection of the database */
mysql_close();
?>

</tr>
</table>
</body>
</html>


In this example above-mentioned, the requests return the fields name and URL. The function mysql_fetch_row() analyze each result line of the request and store the columns in the array "row[]". Thus, the name field will store in row1 and URL in row1. In another hand, we include usually mysql_fetch_row() in a while loop for all the result line are processed. When it has no more lines to process, the while loop finishes and the execute the next instructions.

Detect a empty result

It can be usefull, before insert datas into a table, to detect the attendance of a record into a table, to avoid to store doubles. it can do by making a SQL request with an SELECT order and an WHERE clause allowing to check the attendance or not of records corresponding to the request. The no attendance of result is defined by a null return of the part of the mysql_fecth_row() function. Here is an example display the result of a request with a result not empty or displaying a error message if the result is empty (the HTML code in which the PHP code must be implemanted has been voluntarily forgoten.

<?php

/* Parameters declaration of connection */
$host = the-machine;
/* In general, the-machine is localhost */
/* E.g the machine on which the script is hosted */

$user = <xxx>Your-Login</xxx>;
$bdd = <xxx>Database-Name</xxx>;
$passwd = <xxx>Your-Password</xxx>;

/* Connection to the server */
mysql_connect($host, $user,$passwd) or die("connection error to the server");
mysql_select_db($bdd) or die("connection error to the database");

/* Creation and send of the request*/
$query = "SELECT nom,url FROM sites ORDER BY nom";
$result = mysql_query($query);

Results recuperation
if (!mysql_fetch_row($result)) {
echo "Any records corresponding";
}
else {
while($row = mysql_fetch_row($result)){
$Name = $row[0];
$Url = $row[1];
echo "<tr>

<td><a href="$Url">$Name</a></td>

<td>$Url</td>

</tr>
";
}
}

Database deconnection
mysql_close();
?>