PHP Classes

How to Use a Simple Database Connection Class - PHP MySQL Database package blog

Recommend this page to a friend!
  All package blogs All package blogs   PHP MySQL Database PHP MySQL Database   Blog PHP MySQL Database package blog   RSS 1.0 feed RSS 2.0 feed   Blog How to Use a Simple D...  
  Post a comment Post a comment   See comments See comments (0)   Trackbacks (0)  

Author:

Viewers: 1,263

Last month viewers: 190

Package: PHP MySQL Database

Most PHP application sites need to access a database to store and retrieve application data.

There are many classes to connect to a database. The best and the ones that are simple and easy to understand, so everybody can use them with a good sense of what is going on in the class.

Read this short tutorial article to learn how you can use the PHP MySQL Database class to perform all database query functions with the least complexity.




Loaded Article

In this article you will learn:

What is the PHP MySQL Database Class

How to Configure the Database Access to Use This Class

How to Query a Database

How to Setup a Database Connection

How to Query a Database

Sample Data to Test the Class

How to Download the Update Site Package or Install it With PHP Composer


What is the PHP MySQL Database Class

Based on KISS principle (Keep it simple and stupid/short) the class is simple, short and easy to understand. Unlike complex classes where often developers do not understand working of class and end up just using it without understanding. 

his class however is simple enough for users to understand what they are doing.

How to Configure the Database Access to Use This Class

The database configuration, which includes database username, password etc is kept in separate file for better security.

Edit config.ini.php to set your database configuration values. For better security, keep this file out of Web document root directory, which in some cases may be public/www . If you change the location of the configuration file, update the path of the file in the database.class.php . By default all files are kept in same directory.

$this->db = parse_ini_file("config.ini.php");

How to Query a Database

You only need two functions (query() and fetch()) to execute queries on database and fetch result set respectively.

How to Setup a Database Connection

Make sure you have edited configuration file with your database details first, then create a new instance of database class.

$db = new MySQLDb;

How to Query a Database

Once new instance of the class is successfully created, it will establish a connection to database. You are now ready to run a SQL query through query method.

$query = "select * from guestbook";
$result = $db->query($query);

To fetch the result set into an array, use fetch() method after running query:

$rows = $db->fetch();
var_dump($rows);

All rows would be returned into $rows array which can be used with any loop to echo.

To get count of returned rows, use $result object's property:

$result->num_rows

Do a var_dump() to see details of returned result object, in case of successful insert, update and delete query $result object returns true, this can be evaluated with simple if/else conditional.

echo ($result) ? "query successful" : "query was not successful";

Select Query

$db = new MySQLDb;
$query = "select * from guestbook";
$result = $db->query($query);
$rows = $db->fetch();
var_dump($rows);

Insert Query

$query = "insert into guestbook (user, message, DATE) values ('user', 'a message', now())";
$result = $db->query($query);
echo ($result) ? "<br> Inserted" : "<br> Insert unsuccessful";

Update Query

$query = "update guestbook set user = 'user1' where id = 3";
$result = $db->query($query);
echo ($result) ? "<br> Updated" : "<br> Update unsuccessful";

Delete Query

$query = "delete from guestbook where id = 2";
$result = $db->query($query);
echo ($result) ? "<br> Deleted" : "<br> Delete unsuccessful";

Sample Data to Test the Class

At the end, SQL for a table is available, to test class just create table populate it with some content and try running different queries mentioned above in this blog post.
CREATE TABLE `guestbook` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user` varchar(20) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `message` varchar(400) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `date` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

insert into guestbook (user, message, DATE) values ('me', 'anything', now());
insert into guestbook (user, message, DATE) values ('user', 'a message', now());
insert into guestbook (user, message, DATE) values ('user1', 'new message', now());

How to Download the Update Site Package or Install it With PHP Composer

The PHP Database Class package is available for you to download as a ZIP archive by going to the download page or install it using the PHP Composer Tool by going to the installation instructions page.




You need to be a registered user or login to post a comment

1,611,040 PHP developers registered to the PHP Classes site.
Be One of Us!

Login Immediately with your account on:



Comments:

No comments were submitted yet.



  Post a comment Post a comment   See comments See comments (0)   Trackbacks (0)  
  All package blogs All package blogs   PHP MySQL Database PHP MySQL Database   Blog PHP MySQL Database package blog   RSS 1.0 feed RSS 2.0 feed   Blog How to Use a Simple D...