phpMyDB – Data base class for MySql

phpMyDB is not only a class handler for MySql written in PHP, it’s also has a query cache built in, a great debugger and is damn easy to use!

I’ve wrote this class just few weeks a go since I need it for the new version of Open Classifieds (coming soon I promise, many changes).

This class uses fileCache class to be able (if you want to) to cache the query result in a really simple way (transparent to the programmer).

First Download

Usage with examples:

Constructor:
DB Constructor – connects to the server and selects a database

$ocdb = new phpMyDB(DB_USER, DB_PASS, DB_NAME,DB_HOST,DB_CHARSET);

In case of error will print using the private function print_error

Debugger:
Logs all the connection or action inside the class, you can set it on whenever you want.

$ocdb->setDebug(true);
$ocdb->returnDebug();

We set it on, and at the end of our script(or whenever you want) we should returnDebug, by default in “HTML”, but can return as an “array”.
Also you can add new “logs” with $ocdb->addLog(“any string”);

Activate cache:
Uses fileCache class. As parameter we have:
1: true or false to activate or deactivate the cache, by default false
2: Time in seconds by default 3600 (10 seconds expires in the example)
3: Path for the cache, by default cache/

$ocdb->setCache(true,10);

Normal query:
Just performs a normal query, but keeps the query counter and logs the action if debugger it’s on

$result =$ocdb->query($query);
if (mysql_num_rows($result)){
     while ($row=mysql_fetch_assoc($result)){
          echo 	$row['title'];
     }
}

getRows
Returns in rows the target query. If cache is activated automatically cache the result as an array.
Parameters:
1: Query to return values
2: Returning as “assoc” (default), “row” , “object” ,”value” (returns first field form the query)
3: Cache type “cache” or “APP” by default “cache”
Example: Cached query, returning the row as an object (cache must be activated):

$result=$ocdb->getRows($query,"object");
if ($result){//more than 1 result
	foreach ( $result as $row ){
           echo $row->title;
       }
}

getValue
Get value from first value of the query and stores it in the cache.

$query="SELECT count(*) total FROM oc_postshits";
$total_records = $ocdb->getValue($query,"APP");

In the second parameter we can use “cache” (default).

Insert:
Insert into,parameters:
1: table + fields
2: values

$ocdb->insert("categories (name,friendlyName)","'name','fname'");

To get the last id use $ocdb->getLastID();

Update:
Updates table, parameters:
1: table
2: sets
3: where

$ocdb->update("posts","isAvailable=0,isConfirmed=0","idPost=1 and password='asdsadsad'");

Delete:
Delete from, parameters:
1: table
2: where

$ocdb->delete("categories","idCategory=2");

Close db:
Closes DB connection and unsets variables.

$ocdb->closeDB();

Full example:

include "phpMyDB.php";

define('DB_HOST','localhost');
define('DB_USER','user');
define('DB_PASS','some pass');
define('DB_NAME','openclassifieds');
define('DB_CHARSET','utf8');

$ocdb = new phpMyDB(DB_USER, DB_PASS, DB_NAME,DB_HOST,DB_CHARSET);

$ocdb->setDebug(true);
$ocdb->setCache(true,10);

$ocdb->insert("postshits (idPost,ip)","97,'10.0.0.0'");
echo 'test insert see id:' . $ocdb->getLastID();

$query="SELECT count(*) total FROM oc_posts";
$total_records = $ocdb->getValue($query,"APP");
echo 'Total records: '.$total_records;

$query="SELECT p.title from oc_posts p Limit 3";
$ocdb->setCache(false);//cache deactivated
$result=$ocdb->getRows($query,"object");
$ocdb->setCache(true);//after the query is done activated once more

if ($result){//more than 1 result
	foreach ( $result as $row ){
		echo $row['title'];
	}
}//if count
else echo "nothing found";

$ocdb->closeDB();
$ocdb->returnDebug();

echo $ocdb->getQueryCounter().' queries generated and '.$ocdb->getQueryCounter("cache").' queries cached';