Easy MySQL Database Handler Class

Introduction:
Web development requires a lot of repeated functionality in every page you build. The way you handle these functions defines your web application. A well built application is a key to giving you, the developer, the proper tool to build powerful components and pages without the messy hassle and headaches of going through repeated code structures and lets you focus on the primary function you are working with.

Here is a suggestive method that you can use on your development structures with can handle MySQL queries with ease.

The Class Script:
You can download the attached file mySqlClass.inc.php (make sure the filename ends with the php extention).

To connect to the database, we need to define the connection login credentials with the proper username, password, database name and host. Create a file (something like configure.php) and write the following defined example replacing the generic value data with the correct values that fit your database connection. Save this file in a location where you would save other global includes for all of your application pages.

PHP Code:

<?php
  //# Define database connection
  define('DB_SERVER', 'localhost');
  define('DB_SERVER_USERNAME', 'user_name_here');
  define('DB_SERVER_PASSWORD', 'password_here');
  define('DB_DATABASE', 'table_name_here');

Now to make the connection, we first include the configure.php file that defines our connection credentials. Then we include the actual class script file and start a new instance and call the connection method.

PHP Code:

<?php
//###  Start MySQL connection
  require_once('configure.php');
  require_once('mySqlClass.inc.php');
  $db = new mySqlClass();
  $db->Connect(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD, DB_DATABASE);

Usage:
We can use the query method as if it were a standard mysql_query() function to pass a new SQL query. We assign this to a variable to capture the results.

PHP Code:

$result = $db->SqlQuery("
  SELECT *
  FROM members
"); 


All of the current results assigned to the new capture variable are stored as an associative array with the database table column names (or those specified within the query) are the keys. We can either print the value of the entire row array or a specific field column.

PHP Code:

// Print entire row:
print_r($result->field);
// Print just a specified field column
echo $result->field['id']; 


In order to iterate through all of the returned rows, we can loop it through a construct until all of the rows have been returned.

PHP Code:

while ($result->NextRow())
{
  print_r($result->field);
} 


Overview:
I hope this article helps you to achieve more manageable methods of coding and working with a database. This is intended to provide a learning tool for those who are interested in learning more about PHP and application development. The provided code is only for suggestion, and those who would like to offer suggestions and improvements are encouraged to do so.

Thanks. Personally, I'd like to see more usage of MySQLi -- it's kind of like programming for PHP 4 now.

A class which releases MySQLi results when there's nothing useful there (e.g. when I do a multi_query and all queries are updates, I still have to clear out all the results) would be nice.

For the informed out there, I'm just not interested in the abstraction class that PHP 5 offers -- I've never had a need to code for anything other than MySQL and wouldn't have a clue if my SQL commands were compatible with an of the supported entities.

Great information. Can this be a sticky thread?

Hi.

I just tested and there is an error in class on line 33:

if (!$result_resource) $this->set_error(@mysql_errno($this->link), @mysql_error($this->link));

it must be

if (!$result_resource) $this->_error(@mysql_errno($this->link), @mysql_error($this->link));

Nice job.

Originally Posted by voancea

Hi.

I just tested and there is an error in class on line 33:



it must be



Nice job.

Thanks! I see it on Line 45 though, but a mistake none-the-less!

Thanks, mgraphic. You did a marvelous job on this post. I never liked databases in the past because I thought they were so confusing, but this post will certainly change my views.

- Steve