Connecting to mySQL Databases

In this tutorial we look at one of the most powerful features of any application programming languages - using databases. PHP and mySQL have become a ubiquitous combination with nearly all web applications preferring this setup. Sure, there are other languages but the fact that both PHP and mySQL are open source swings the popularity towards them.

To start, we will concentrate on connecting to a mySQL database using PHP.

The syntax to connect to a mySQL database is simple as is shown below:

mysql_connect("hostname","username","password");

The hostname is the name of the server where the db (database) resides. Usually the applications run on the same system where the db is, so the hostname is usually localhost.

mysql_connect("localhost", "username", "password");

In case you are using variables for the parameters, then the construct to get a mysql connection would be:

mysql_connect($hostname, $user, $password);

where $hostname, $user and $password represent the hostname, user id and password respectively.

The last argument is not mandatory if the database is set up so that it does not need a password (not a great idea).

Now that we have connected to the database server, we need to select the database with which we want to work. This is done using the following syntax:

mysql_select_db($database)

where $database represents the database name in the server.

The next step is to execute queries on the database. Note the following snippet of code for the same:

$query = "select name, age from students";
   $students = mysql_query ($query);
while ($row = mysql_fetch_row($students)){
   echo ("Student Name: ".$row[1]."<br />");
   echo ("Age: ".$row[2]."<br />");
   }

The second line executes the query on the database and the result is stored in $students. This result holds multiple rows which are separated into individual rows using mysql_fetch_row.

As a good programming practice and based on object oriented principles, it is better to have common functions to connect to databases and execute queries on them. This will help to later change the user id,db name or password at one go instead of changing them at various places.