PHP / MySQL Connection Class

PHP / MySQL Connection Class

The PHP scripting language is a great tool for new people who build websites and learning programming syntax. Most people who use PHP only skim the surface of what the language can do and don’t learn how to use PHP in more of a way to help their code become easier to accomplish backend tasks.

PHP newbie’s usually use it to display output, and find themselves needing to keep it complex and inefficient by pasting code over and over each time they perform similar duties. For example, those who want to connect to a MySQL database to pull or write data will keep adding a block of code to connect to the database each time they try to perform a task. This can become tiresome and really confuse the main function of what they are trying to accomplish.

The power in programming comes from building other functions and classes (OOP, Object Oriented Programming) to do the tedious grunt work for you, so your main body scripts can just focus on the task at hand. If you find yourself doing a lot of copy and paste that should be more automated, reevaluate your code, and keep those types of tasks nested in a function.

For this example, we will build a class script that will handle MySQL queries in an easy and simple way.

The Class Script:
count_queries = 0;
$this->total_query_time = 0;
$this->connect(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD, DB_DATABASE);
if (!$this->db_isconnected) $this->set_error(’0′, ‘mysqlClass Error: MySQL DB Not Connected’);
}

private function connect($db_server, $db_username, $db_password, $db_database) {

$this->database = $db_database;
$this->link = @mysql_connect($db_server, $db_username, $db_password, false);

if ($this->link) {

if (@mysql_select_db($db_database, $this->link)) {
$this->db_isconnected = true;
return true;
} else {
$this->set_error(mysql_errno(),mysql_error(), $zp_real);
return false;
}

} else {

$this->set_error(mysql_errno(),mysql_error());
return false;
}
}

private function set_error($err_num, $err_text) {

echo $err_num . ‘ ‘ . $err_text;
die();
}

public function Execute($sql, $limit = false) {

if ($limit) $sql = $sql . ‘ LIMIT ‘ . $limit;
$time_start = explode(’ ‘, microtime());
$obj = new mysqlClassResult;
$db_resource = @mysql_query($sql, $this->link);
if (!$db_resource) $this->set_error(@mysql_errno($this->link),@mysql_error($this->link));
$obj->resource = $db_resource;
$obj->cursor = 0;
if ($obj->RecordCount() > 0) {

$obj->EOF = false;
$result_array = @mysql_fetch_array($db_resource);
if ($result_array) {

while (list($key, $value) = each($result_array)) {

if (!ereg(’^[0-9]’, $key)) $obj->fields[$key] = $value;
}
$obj->EOF = false;

} else {

$obj->EOF = true;
}

} else {

$obj->EOF = true;
}

$time_end = explode (’ ‘, microtime());
$query_time = $time_end[1]+$time_end[0]-$time_start[1]-$time_start[0];
$this->total_query_time += $query_time;
$this->count_queries++;
return($obj);
}

public function InsertID() {
return @mysql_insert_id($this->link);
}

public function QueryCount() {
return $this->count_queries;
}

public function QueryTime() {
return $this->total_query_time;
}
}

class mysqlClassResult {

public function MoveNext() {

$this->cursor++;
$result_array = @mysql_fetch_array($this->resource);
if (!$result_array) {
$this->EOF = true;
} else {
while (list($key, $value) = each($result_array)) {
if (!ereg(’^[0-9]’, $key)) {
$this->fields[$key] = $value;
}
}
}
}

public function RecordCount() {

return @mysql_num_rows($this->resource);
}
}

?>
Now we need to assign the values to connect to the database. We can use the define() function in an included configuration file. What makes define() work nice in this case, is that defined constants are auto global (they can be accessed in any linked function).

The Configuration Script:
<?php

define(’DB_SERVER’, ‘localhost’);
define(’DB_SERVER_USERNAME’, ”);
define(’DB_SERVER_PASSWORD’, ”);
define(’DB_DATABASE’, ‘clcag’);

?>

Now once we include the configuration and class script files in our main script file, we can now create a new instance of the class object.

$db = &new mysqlClass();

Now if we want to fetch some data from the database, we just use our class object to assign the values to a new variable.

$test = $db->Execute('SELECT * FROM table_name');

Now to iterate through the returned rows, we just reference the fields by field names. By evaluating if the object var EOF is false, we can determine when the last row is returned. After each iteration, we move the pointer to the next row.

while(!$test->EOF){
print_r($test);
$test->MoveNext();
}

We can easily count how many rows were returned by calling a custom function.

echo $test->RecordCount();

We can insert a new row, and then find the last ID inserted in the auto-increment field.

$db->Execute('INSERT INTO table_name (field1, field2, field3) VALUES("a", "b", "c")');

Last Inserted ID: InsertID(); ?>

At the end of our script, we can display the total number of queries and total time in seconds it all took.

QueryCount(); ?> Queries in QueryTime(); ?> second(s)

This code is a customized version of the Query Factory class script originally written by the developers of Zen-Cart

Written by mgraphic on February 28th, 2007 with 2 comments.
Read more articles related to Coding and Other.

Like This Article "PHP / MySQL Connection Class?"
Please consider subscribing to our feed & leaving a comment below.

• [+] Del.icio.us: Bookmark this article

Related articles:

• PHP / MySQL Pagination
  (April 9th, 2007)
• Creating Well-Built Sites
  (February 28th, 2007)