Thursday 22 December 2011

Access mysql database

Access mysql database
This task use php to execute query on mysql database. If error occurs, error message is sent to specified email.
Access mysql database using utility class
  1. Create database.php as following
  2. Call query or execute method as following
call methods
$CONFIG['db']['server'] = 'localhost';
$CONFIG['db']['database'] = 'database';
$CONFIG['db']['username'] = 'username';
$CONFIG['db']['password'] = 'password';
$CONFIG['db']['prefix'] = 'prefix_';

$CONFIG['mail']['report'] = 'webmaster@host.com';
$CONFIG['mail']['sender'] = 'webmaster@host.com';

require_once('database.php');

$db = new database();
$id = 'abcdef';
$data = $db->query(sprintf("select * from %s where id = %s", $db->table('task'), $db->quote($id) ));
print_r($data);
$db->execute(sprintf("delete from %s where id = %s", $db->table('task'), $db->quote($id) ));
$db->clean();
    
database.php
<?php

class database {

    private $cfg;
    private $link;

    public function __construct() {
        global $CONFIG;
        $this->cfg = $CONFIG['db'];
        $this->link = mysql_connect($this->cfg['server'], $this->cfg['username'], $this->cfg['password']);
        if (!$this->link) {
            $this->sendError(mysql_error());
        }
        if ($this->link) {
            if (!mysql_select_db($this->cfg['database'], $this->link)) {
                $this->sendError(mysql_error());
                $this->clean();
            }
        }
    }

    public function clean() {
        mysql_close($this->link);
        $this->link = null;
    }

    public function quote($src) {
        return "'" . mysql_real_escape_string($src) . "'";
    }

    public function table($src) {
        return $this->cfg['prefix'] . $src;
    }

    public function query($sql) {
        $data = array();
        if (!$this->link) return $data;
        $result = mysql_query($sql, $this->link);
        if ($result) {
            while ($row = mysql_fetch_assoc($result)) {
                $data[] = $row;
            }
        } else {
            $this->sendError(mysql_error());
        }
        return $data;
    }

    public function execute($sql) {
        if (!$this->link) return false;
        $result = mysql_query($sql, $this->link);
        if (!$result) {
            $msg = mysql_error();
            $this->sendError($msg);
            return $msg;
        } else {
            return true;
        }
    }

    public function sendError($message) {
        global $CONFIG;
        $from = $CONFIG['mail']['sender'];
        $to = $CONFIG['mail']['report'];
        $header = "From: $from\r\nReply-To: $from\r\n";
        $subject = "Database error!";
        mail($to, $subject, $message, $header);
    }

}

?>
    
PHP

  Protected by Copyscape Online Copyright Protection

No comments:

Post a Comment