Kelvin’s Blog

Databases are central to many web applications. One reason why PHP is a great web programming language is its extensive database support.

The MySQL database examples in this blog use PHP PDO database access layer, which is only available since PHP 5. With PDO, you use the same PHP functions no matter what database engine you’re talking to, this it very similar to JDBC if you use Java before. Although the syntax of the SQL may differ from database to database, the PHP code remains similar. This will be helpful if the developer or the management decided to change database after development, it makes the web application integration with other database easier.

Please take note, PDO offers data access abstraction, not total database abstraction, it will certainly improve, maybe PDO2. Other PHP libraries, such as PEAR DB, ADODb, and MDB2 attempt to solve the total database abstraction problem, they hide different databases implementation details such as update statement, date handling and column types behind a layer of code. If you want both data access abstraction and database abstraction together, why not try Zend Framework hidden gems called Zend_Db, but remember if you need to use PDO connecting to MySQL database with Zend_Db, you still have to install pdo_mysql PHP extension.

For now, I will not talk about Zend_Db, I will focus on PDO. I will leave Zend Framework for the next posts. Below is a simple connection to MySQL database table called animal, and execute the select statement to list all the animals from the table:

<?php
 
// mysql hostname
$hostname = 'localhost';
 
// mysql username
$username = 'username';
 
// mysql password
$password = 'password';
 
try {
 
	$dbh= new PDO("mysql:host=$hostname;dbname=animal", $username, $password);
 
	// echo a message saying we have connected
	echo 'Connected to database';
 
	// the SQL SELECT statement
	$sql = "SELECT * FROM animals";
 
	foreach ($dbh->query($sql) as $row) {
		print $row['animal_type'] .' - '. $row['animal_name'] . '';
	}
 
	// close the database connection
	$dbh = null;
 
} catch( PDOException $e) {
	echo $e->getMessage();
}
 
?>

Bookmark It

Hide Sites