<?php
/*
Methods:
connect
Connectects to a mysql or sphinx database
query
queries the database and saves the result to the class
q
queries the database but does NOT save the result to the class
insertID
gets the insert ID from the query when using an insert
getRow
gets 1 row from the database and returns it as an array
row
gets the current row from a result set
numRows
gets the number of returned rows
affectedRows
gets the number of rows affected by an insert, update, delete
field
gets a field from a result set
getOne
gets one item from a database row
escape
escapes your query
*/
class Mysql{
private $conn = null;
private $sql = null;
private $row = null;
private $server = "", $database;
private $sphinx = false;
public function __construct($host = null, $user = null, $pass = null, $db = null, $port = 3306, $sphinx = false){
if(is_string($host) && is_string($user) && is_string($pass) && is_string($db)){
$this=>connect($host, $user, $pass, $db, $port, $sphinx);
}
}
public function __destruct(){
mysql_close($this->conn);
}
public function connect($host, $user, $pass, $db, $port = 3306, $sphinx = false){
$this->sphinx = (bool)$sphinx;
$this->conn = @mysql_connect("$host:$port", "$user", "$pass");
mysql_select_db($db, $this->conn);
if(!$this->sphinx){
$this->query("set time_zone = 'UTC'");
}
return $this;
}
public function query($query){
if($this->conn === null)
return false;
$this->sql = mysql_query($query, $this->conn);
if(mysql_error()){
echo mysql_error();
}
return $this;
}
public function q($qurey){
if($this->conn === null)
return false;
mysql_query($qurey, $this->conn);
if(mysql_error()){
echo mysql_error();
}
return $this;
}
public function insertID(){
return mysql_insert_id($this->conn);
}
public function getRow($query){
if($this->conn === null)
return false;
$this->sql = mysql_query($query, $this->conn);
return mysql_fetch_assoc($this->sql);
}
public function row(){
$this->row = mysql_fetch_assoc($this->sql);
return $this->row;
}
public function numRows(){
return mysql_num_rows($this->sql);
}
public function affectedRows(){
return mysql_affected_rows($this->conn);
}
public function field($name, $default = ""){
if(isset($this->row[$name]))
return $this->row[$name];
return $default;
}
public function getOne($query, $default = ""){
if($this->conn === null)
return false;
$sql = mysql_query($query, $this->conn);
$row = mysql_fetch_array($sql);
if(isset($row[0]))
return $row[0];
return $default;
}
public function escape($string){
if($this->conn === null)
return false;
return mysql_real_escape_string($string, $this->conn);
}
public function calcFoundRows(){
if($this->sphinx){
$sql = mysql_query("show meta", $this->conn);
$row = mysql_fetch_assoc($sql);
mysql_data_seek($sql, 1);
return $row['Value'];
}else{
$sql = mysql_query("select found_rows() as total", $this->conn);
$row = mysql_fetch_assoc($sql);
return $row['total'];
}
}
}
?>
Usage
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
<?php
$db = new Mysql("localhost", "user", "password", "database");
// Looping through a result set:
$db->query("select * from mytable limit 10");
// You can assign row() to a variable if you want
// while($row = $db->row()){
// $user = $row["user"];
// $email = $row["email"];
// echo "$user:$email\n";
// }
while($db->row()){
$user = $db->field("user");
$email = $db->field("email");
echo "$user:$email\n";
}
// getting one row
$row = $db->getRow("select * from users where userid = 123");
$user = $row["user"];
$email = $row["email"];
echo "$user:$email\n";
// getting one item
// Example 1
$user = $db->getOne("select user from users where userid = 123");
echo $user;
// Example 2
$user = $db->getOne("select user from users where userid = 123", "Admin User");
echo $user; // Displays "Admin User" if userid 123 was not found
// Example 3
$is_user = (bool)$db->getOne("select 1 from users where userid = 123");
if($is_user){
echo "User exists";
}else{
echo "User does not exists";
}
// Insert example
$user = $db->escape($_POST["user"]);
$email = $db->escape($_POST["email"]);
$db->query("insert into mytable (user, email) values ('$user', '$email')");
$insert_id = $db->insertID();
echo "$insert_id\n";
?>
