PHP Database Class to access Mysql

This is a simple class that evolved out of the php code I wrote so far.


It has the following functions :

1. dbms() - The constructor to do the initialisation like connecting to the database etc.
2. query($query) - The method to take a sql string and perform the query.
3. close() - Close the database connection
4. backup() - Create a backup of the database as an sql file and then zip it.
5. restore() - Restore database from a zip file which was created using the previous function backup()
6. get_last_insert_id() - Get the id from the last INSERT statement using the LAST_INSERT_ID() function of mysql database.
7. indb($table , $field , $value) - Checks whether a value of $value already exists in the $field column of a table $table. e.g. checking whether a username or an email already exists or not.

Code :

<?php
class dbms {
 #DB Configuration parameters
 var $host = 'localhost';
 var $username = 'root';
 var $password = 'secret';
 var $db = 'projects_bookmark';
 #Connection object to be used in this object
 var $connection;
 #Constructor
 function dbms() {
  #Attempt to connect to database
  $this->connection = mysql_connect($this->host, $this->username, $this->password);
  #Check connection validity
  if (!$this->connection) {
   die ("Could not connect to the database: 
". mysql_error($this->connection));
  }
  #If connection was successful then select the database
  $db_select = mysql_select_db( $this->db , $this->connection );
  #Check database selection validity
  if (!$db_select) {
   die ("Could not select the database: 
". mysql_error($this->connection));
  }
 }
 #Function to perform a query
 function query($query) {
  $result = mysql_query( $query , $this->connection);
  if (!$result) {
   die ("Could not query the database: 
 $query ". mysql_error());
  }
  return $result;
 }
 #Method to close
 function close() {
  mysql_close($this->connection);
 }
 #Function to backup database to a zip file
 function backup() {
  $suffix = time();
  #Execute the command to create backup sql file
  exec("mysqldump --user={$this->username} --password={$this->password} --quick --add-drop-table --add-locks --extended-insert --lock-tables --all {$this->db} > backups/backup.sql");
  
  #Now zip that file
  $zip = new ZipArchive();
  $filename = "backups/backup-$suffix.zip";
  if ($zip->open($filename, ZIPARCHIVE::CREATE) !== TRUE) {
   exit("cannot open <$filename>n");
  }
  $zip->addFile("backups/backup.sql" , "backup.sql");
  $zip->close();
  #Now delete the .sql file without any warning
  @unlink("backups/backup.sql");
  #Return the path to the zip backup file
  return "backups/backup-$suffix.zip";
 }
 #Function to restore from a file
 function restore($path) {
  $f = fopen('restore/temp.sql' , 'w+');
  if(!$f) {
   echo "Error While Restoring Database";
   return;
  }
  $zip = new ZipArchive();
  if ($zip->open($path) === TRUE) {
   #Get the backup content
   $sql = $zip->getFromName('backup.sql');
   #Close the Zip File
   $zip->close();
   #Prepare the sql file
   fwrite($f , $sql);
   fclose($f);
   
   #Now restore from the .sql file
   $command = "mysql --user={$this->username} --password={$this->password} --database={$this->db} < restore/temp.sql";
   exec($command);
   
   #Delete temporary files without any warning
   @unlink('restore/temp.sql');
  } 
  else {
   echo 'Failed';
  }
 }
 #Function to get the last insert ID
 function get_last_insert_id() {
  $results = $this->query("SELECT LAST_INSERT_ID()");
  $r = mysql_fetch_array($results);
  return $r[0];
 }
 #Function to check whether a value exists in some column of some table
 function indb($table , $field , $value) {
  $db = new dbms();
  $results = $db->query("SELECT $field FROM $table WHERE $field = '$value' ");
  if(mysql_num_rows($results))
   return true;
  else 
   return false;
 }
}
?>








Simple!

Last Updated On : 1st May 2009

Subscribe to get updates delivered to your inbox

1 Comment + Add Comment

  • 1 use pdo
    2 dont use die/exit in db level
    it kills error handling and testability

Leave a comment