4WebHelp: PHPLIB: Database Abstraction

What is this?

Database abstraction allows you to write a script that will work with any database, without having to rewrite any of your code. I am using PHPLIB in this tutorial, and it can be downloaded from http://phplib.sourceforge.net/. All the files you need for this tutorial are in the php/ folder, and are named "db_xxxxx.inc", where xxxx is the name of a database.

What is a Class?

Think of a class as a container. This container deals with one or more functions, such as check the spelling of words, display a button on the screen or access a database. However, this container needs information, such as which words to check or which database to connect to, ie the username and the password. These are stored in the class's properties. These functions are known as the methods. Programmers need only learn how to use the class. You put something in, and a predictable outcome comes out. You do not need to understand how it works, and you are able to use the class again and again, without modifying it.

A database abstraction class is one that forms a layer between your program and the databases. It takes care of the different databases and their details. You only have to learn to code for one database, and it will work for all of them.

Let's get started!

I could suggest you read the documentation in the docs/ folder. However, when I was learning PHPLIB I found them really hard to understand, and I suggest that you only use them if you are experienced. PHPLIB may seem tricky to use if you have never used classes before, because you are always referring to the class, but just persevere. It's worth it in the end!

Firstly, I suggest that you rename the db_xxxxx.inc files db_xxxxx.php for security, as we are going to be putting our database details in there, and we don't really want to share them with everyone else, do we? Once you've done that, find the following lines:

/* public: connection parameters */
var $Host = "";
var $Database = "";
var $User = "";
var $Password = "";

Enter you database details as shown below:

/* public: connection parameters */
var $Host = "address.to.connect.to.your.db-usually.localhost";
var $Database = "yourname";
var $User = "username";
var $Password = "***********";

You will soon be able to connect to the database - once we've done some coding!

Firstly, we'll create a table on your database. Run the following SQL query on your database:

CREATE TABLE test (
name varchar(150),
description text,
price varchar(50)
);
# Let's insert some data:
INSERT into test (name, description, price) VALUES ('Banana', 'Funny yellow crescent shaped thing', '0.99/bunch');
INSERT into test (name, description, price) VALUES ('Pineapple', 'Lovely and juicy', '1.69 ea');
INSERT into test (name, description, price) VALUES ('Apples', 'Many different varieties available. Come and see our range', '0.69/pound');
INSERT into test (name, description, price) VALUES ('Oranges', 'Just arrived straight from Israel', '1.42/Kg');

Right, now that's done we'll write some code.
Let's take a greengrocer as an example. We're going to use PHP to show what fruit we have in stock, and print it out on the page.

<html>
<head>
<title>Our fruit</title>
</head>
<body>
This is the fruit we have available at the moment:
<?php
include("db_mysql.php");
$db = New DB_SQL;
$sql = "SELECT * from test";
$db->query($sql);

while($db->next_record()) {
$name = $db->f("name");
$description = $db->f("description");
$price = $db->f("price");

echo("Fruit: ".$name.";   Price: £".$price." \n");
echo($description);
echo("\n\n");
}
?>
</body>
</html>

Looks complicated, doesn't it? Hopefully the following should make things clearer. Let's take one line of code at a time.

<?php
include("db_mysql.php");
$db = New DB_SQL;

These lines include the database abstraction class (in this case for MySQL), and then create an instance of the class which will be referred to using the variable $db.

$sql = "SELECT * from test";
$db->query($sql);

This executes our SQL query on the database.

while($db->next_record()) {
$name = $db->f("name");
$description = $db->f("description");
$price = $db->f("price");

echo("Fruit: ".$name.";   Price: £".$price." \n");
echo($description);
echo("\n\n");
}
?>

This is the actual code that prints the details on the page. Let's take a section at a time:

while($db->next_record()) {
... code goes here ...
}

This tells the script that if there is another row of data returned by the SQL query to re-run the code inside the two {} brackets. This is how it prints out all the data.

$name = $db->f("name");

This assigns to the variable $name the value in the database column 'name'.

echo("Fruit: ".$name.";   Price: £".$price." \n");
echo($description);
echo("\n\n");

This prints the list of fruits onto the page, as you have probably guessed already.

There are actually two ways to do the above, and I have shown you my favourite. It does double the lines of code needed, but I find it is easier to read. The other way is as follows:

while($db->next_record()) {
echo("Fruit: ".$db->f("name").";   Price: £".$db->f("price")." \n");
echo($db->f("description"));
echo("\n\n");
}
?>

So what does this file show when it's been run? You can see the output here.

Conclusion

I hope you have found this tutorial useful. It may seem much harder to learn than just the MySQL functions, but if you ever move to a PostgreSQL server or even (if your site gets really popular) to an Oracle database you will be very glad you have learnt how to use PHPLIB's database abstraction, as you won't have to edit a single line of code!

Latest comments on this tutorial
kishore
thank u for providing nice tutorial now i get information related to data abstraction & while($db->next_record())....
Mark
CREATE TABLE test ( name varchar(150), description text, price varchar(50) ); # Let's insert some data: INSERT into test (name, description, price) VALUES ('Banana', 'Funny yellow crescent shaped thing', '0.99/bunch'); INSERT into test (name, description, price) VALUES ('Pineapple', 'Lovely and juicy', '1.69 ea'); INSERT into test (name, description, price) VALUES ('Apples', 'Many different varieties available. Come and see our range', '0.69/pound'); INSERT into test (name, description, price) VALUES ('Oranges', 'Just arrived straight from Israel', '1.42/Kg'); Is this using MySql 4 or 5?

Add a new comment

Page URL: http://www.4webhelp.net/tutorials/php/phplib_db.php
Back to the "pretty" page!