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:
- id - This is a unique album id which is basically a primary key and auto-incremented for each album
- name - This is the name of the album.
- music_dir - This stores the name of the music director for the album
- 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.
