Basic MySQL DB operations.

Lets have a look at some very basic MySQL PHP functions using which we can connect to a MySQL DB and retrieve some data from a sample database. Lets say we have a table called “albums” in our database “test”. This table stores music album information in 4 columns:

1. id - This is a unique album id which is basically a primary key and auto-incremented for each album
2. name - This is the name of the album.
3. music_dir - This stores the name of the music director for the album
4. year - when the album was released.

The PHP code we write to access this table should basically, connect to the mysql database, execute a SQL query on the table, get the results of the query and print the results.

Here is a sample code for the same with explanations at each step..

<?php

//This function will establish a connection to the MySQL server.
$conn = mysql_connect(”localhost”, “root”, “”) or die(”Unable to connect to MySQL Server”);

//We will now use the above $conn and select the database in which the “users” table resides.
mysql_select_db(”test”, $conn) or die(”Unable to select database”);

//Once the DB is selected, we can now fire the SQL query and get the results.
$result=mysql_query(”SELECT * FROM albums;”);

//Did we get any result set from the above query?
if(!$result) {
die( “Failed to get results from the albums table.”);
}

//Fetch the number of rows in the result set $result
$rows=mysql_numrows($result);

$i=0;

//Loop through all the entries in the result..
while($i<$rows) {

//Get the values for particular column in the albums table.
$name=mysql_result($query, $i, ‘name’);

$musicdir=mysql_result($query, $i, ‘music_dir’);

$year=mysql_result($query, $i, “year”);

//Print the values here..
echo “
$name\n
$musicdir\n
$year”;

$i++;

}

?>

In a future post we will look at how we can make this code more modular by defining PHP functions and using SSI.