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
- Create database.php as following
- Call query or execute method as following
| 1 | $CONFIG['db']['server'] = 'localhost'; |
| 2 | $CONFIG['db']['database'] = 'database'; |
| 3 | $CONFIG['db']['username'] = 'username'; |
| 4 | $CONFIG['db']['password'] = 'password'; |
| 5 | $CONFIG['db']['prefix'] = 'prefix_'; |
| 6 | |
| 7 | $CONFIG['mail']['report'] = 'webmaster@host.com'; |
| 8 | $CONFIG['mail']['sender'] = 'webmaster@host.com'; |
| 9 | |
| 10 | require_once('database.php'); |
| 11 | |
| 12 | $db = new database(); |
| 13 | $id = 'abcdef'; |
| 14 | $data = $db->query(sprintf("select * from %s where id = %s", $db->table('task'), $db->quote($id) )); |
| 15 | print_r($data); |
| 16 | $db->execute(sprintf("delete from %s where id = %s", $db->table('task'), $db->quote($id) )); |
| 17 | $db->clean(); |
$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();
| 1 | <?php |
| 2 | |
| 3 | class database { |
| 4 | |
| 5 | private $cfg; |
| 6 | private $link; |
| 7 | |
| 8 | public function __construct() { |
| 9 | global $CONFIG; |
| 10 | $this->cfg = $CONFIG['db']; |
| 11 | $this->link = mysql_connect($this->cfg['server'], $this->cfg['username'], $this->cfg['password']); |
| 12 | if (!$this->link) { |
| 13 | $this->sendError(mysql_error()); |
| 14 | } |
| 15 | if ($this->link) { |
| 16 | if (!mysql_select_db($this->cfg['database'], $this->link)) { |
| 17 | $this->sendError(mysql_error()); |
| 18 | $this->clean(); |
| 19 | } |
| 20 | } |
| 21 | } |
| 22 | |
| 23 | public function clean() { |
| 24 | mysql_close($this->link); |
| 25 | $this->link = null; |
| 26 | } |
| 27 | |
| 28 | public function quote($src) { |
| 29 | return "'" . mysql_real_escape_string($src) . "'"; |
| 30 | } |
| 31 | |
| 32 | public function table($src) { |
| 33 | return $this->cfg['prefix'] . $src; |
| 34 | } |
| 35 | |
| 36 | public function query($sql) { |
| 37 | $data = array(); |
| 38 | if (!$this->link) return $data; |
| 39 | $result = mysql_query($sql, $this->link); |
| 40 | if ($result) { |
| 41 | while ($row = mysql_fetch_assoc($result)) { |
| 42 | $data[] = $row; |
| 43 | } |
| 44 | } else { |
| 45 | $this->sendError(mysql_error()); |
| 46 | } |
| 47 | return $data; |
| 48 | } |
| 49 | |
| 50 | public function execute($sql) { |
| 51 | if (!$this->link) return false; |
| 52 | $result = mysql_query($sql, $this->link); |
| 53 | if (!$result) { |
| 54 | $msg = mysql_error(); |
| 55 | $this->sendError($msg); |
| 56 | return $msg; |
| 57 | } else { |
| 58 | return true; |
| 59 | } |
| 60 | } |
| 61 | |
| 62 | public function sendError($message) { |
| 63 | global $CONFIG; |
| 64 | $from = $CONFIG['mail']['sender']; |
| 65 | $to = $CONFIG['mail']['report']; |
| 66 | $header = "From: $from\r\nReply-To: $from\r\n"; |
| 67 | $subject = "Database error!"; |
| 68 | mail($to, $subject, $message, $header); |
| 69 | } |
| 70 | |
| 71 | } |
| 72 | |
| 73 | ?> |
<?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);
}
}
?>
No comments:
Post a Comment