Resource Center : PHP : <flor_db_process: The easy way to do MySQL database scripting with PHP>

flor_db_process: The easy way to do MySQL database scripting with PHP

Coding data access methods in PHP for the MySQL we very often come across the same connect, fetch, query and other methods.

Here is a php script which creates a class for DB access, called flor_db_process, and in a way makes inserts, updates, selects and all that easier, and importantly from re-usable code. This script will be a basic data layer implementation.

An example block of code that uses this class is as below:

<?
include_once "DBinit.php"; //include the class and db connection settings file
$dbP = new flor_db_process(); //instantiate the class
$query = "select * from geekr_cmsSections"; //the query to process
$dbP->fetchQuery($query); //the method call to execute the query and store results
if (!$dbP->hasErr && $dbP->numRows>0)
{
  while ($tmpRw = $dbP->getRowAssoc()) //the method call to access the rows in the resultset
  {
     echo $tmpRw['section_id'];
     echo "<br/>"; 
  }
}
else
  echo "*".$dbP->errStr."*"; //call to print error messages if any.
?>

The above is just a few lines of code to connect to a database, query it, and process the result. The important part here is this creates a data layer type access of data from the db.. and that with little overhead.

Attached package:
The download link above has a package containing all the required files and a test file.

flor_db_process1.class.php has the class implementation
DBinit.php has db settings.

All implementation in above files are compatible with PHP4.0 and PHP5.0, Mysql 4.0 and higher versions.

STEPS to use this class:

Usage:

Detects and uses appropriate mysql or msyqli library methods:
The flor_db_process class, implemented in a file flor_db_process.class.php has both a mysql and mysqli library support and it automatically detects and uses mysqli which is better. If mysqli is not there in the php environment it simply uses equivalent mysql_ methods...with no change required in the calling code.

Also, if your php environment has mysqli support, you can mention a mysql database port number an optional field, default used is mysql default port 3306.

Fetch statements:
For select queries, use the fetchQuery method of the class as below.

$db->fetchQuery($query);
//returns nothing.

The above call will also update the following properties:

$db->numRows = contains integer value of count of rows returned by the query.

$db->resultSet = contains the result object returned by the library. You can also do a $db->resultSet->num_rows to get number of rows.

$db->hasErr = contains a boolean true or false based on whether the database engine returned any error messages.

$db->errStr = contains a string value indicating the error message returned by the mysql engine.

Once, you have executed a select query, to loop through the returned rows, you can use the getRow or getRowAssoc methods. While getRow returns an indexed array of values (which won't have column names), getRowAssoc returns a hash-table like array.

while($tmpRow = $db->getRow())
{
   echo $tmpRow[0]; //print column 1
   echo $tmpRow[1]; //col 2... and so on.
}
while($tmpRow = $db->getRow2())
{
   echo $tmpRow['id']; //returns id field value.
   echo $tmpRow['name']; //returns name field value.
   foreach($tmpRow as $fieldname=>$value)
   {
     echo "$fieldname : $value <br/>";
   }
}

Insert statements:
For insert queries, an insertQuery method can be used, and it will return the new record id if the table to which record was inserted has a primary key with auto increment.

$new_id = $db->insertQuery($query);
//returns new record id if table has auto_increment column... else returns nothing.

Populates the following properties

$db->hasErr

$db->errStr

$db->affRows = contains number of rows affected by the last query.

Update statements:
For update queries, use the updateQuery method.

$db->updateQuery($query)
//returns nothing.

Populates the following properties:

$db->hasErr

$db->errStr

$db->affRows


Closing the connection:

By default PHP closes the database connection when the script ends.. But to close the connection explicitly, use the closeCon method.

$db->closeCon(); //closes the connection.

Yes, but it has the advantage of being easy to code than directly working with mysql or msyqli library methods. Also, it has the advantages of automatically using mysql or mysqli, works well on PHP 4.X and PHP 5.x versions, opens connection automatically when class is instantiated.

MySQL access methods has changes between PHP4.x and PHP5.x, this class will help migrating projects/testing projects on both the environments easier.

Importantly, this provides two core benefits:
1. Uses object oriented programming.
2. Creates a basic data layer in your project.

Advertisement