A simple and practical PHP operation MySQL database class _php skills

This article illustrates a simple and practical PHP operation MySQL database class. Share to everyone for your reference. Specifically as follows:

Copy Code code as follows:

/*
This section of the database connection class, he will automatically load SQL anti-injection function, filtering some sensitive SQL query keywords, but also can increase the Judgment Field Show table status of nature and Show table class to get all the database table names. */
@ini_set (' Mysql.trace_mode ', ' off ');
Class MySQL
{
Public $dblink;
Public $pconnect;
Private $search = Array ('/union (s* (/*.**/) s*) +select/i ', '/load_file (s* (/*.**/) s*) + (/I ', '/into (s* (/*.**/)? s*) + Outfile/i ');
Private $replace = Array (' Union select ', ' Load_file (', ' into outfile ');
Private $rs;

function __construct ($hostname, $username, $userpwd, $database, $pconnect =false, $charset = ' UTF8 ')
{
Define (' Allowed_htmltags ', ' $this->pconnect= $pconnect;
$this->dblink= $pconnect mysql_pconnect ($hostname, $username, $userpwd): mysql_connect ($hostname, $username, $ USERPWD);
(! $this->dblink| |! Is_resource ($this->dblink)) && fatal_error ("Connect to the Database unsuccessfully!");
@mysql_unbuffered_query ("Set names {$charset}");
if ($this->version () > ' 5.0.1 ')
{
@mysql_unbuffered_query ("Set sql_mode = '");
}
@mysql_select_db ($database) or fatal_error ("Can not select table!");
return $this->dblink;
}

function query ($sql, $unbuffered =false)
{
echo $sql. ' <br> ';
$this->rs= $unbuffered mysql_unbuffered_query ($sql, $this->dblink): mysql_query ($sql, $this->dblink);
(! $this->rs| |! Is_resource ($this->rs)) && fatal_error ("Execute the query unsuccessfully! Error: ". Mysql_error ());
if (! $this->rs) fatal_error (' Execute SQL statement '. $sql. ') The following error occurred: '. mysql_error ());
return $this->rs;
}

function Fetch_one ($sql)
{
$this->rs= $this->query ($sql);
return dircms_strips Tutorial Lashes ($this->filter_pass (mysql_fetch_array ($this->rs,mysql_assoc)));
}

function Get_maxfield ($filed = ' id ', $table)//Get maximum value of $filed field in $table table
{
$r = $this->fetch_one ("Select {$table}.{ $filed} from ' {$table} ', order by ' {$table} '. ' {$filed} ' desc limit 0,1 ');
return $r [$filed];
}

function Fetch_all ($sql)
{
$this->rs= $this->query ($sql);
$result =array ();
while ($rows =mysql_fetch_array ($this->rs,mysql_assoc))
{
$result []= $rows;
}

Mysql_free_result ($this->rs);
Return Dircms_stripslashes ($this->filter_pass ($result));
}

function Fetch_all_withkey ($sql, $key = ' id ')
{
$this->rs= $this->query ($sql);
$result =array ();
while ($rows =mysql_fetch_array ($this->rs,mysql_assoc))
{
$result [$rows [$key]]= $rows;
}

Mysql_free_result ($this->rs);
Return Dircms_stripslashes ($this->filter_pass ($result));
}

function last_insert_id ()
{
if (($insertid =mysql_insert_id ($this->dblink)) >0) return $insertid;
else//If the type of the Auto_increment column is bigint, the value returned by mysql_insert_id () will be incorrect.
{
$result = $this->fetch_one (' Select last_insert_id () as Insertid ');
return $result [' Insertid '];
}
}

function Insert ($tbname, $varray, $replace =false)
{
$varray = $this->escape ($varray);
$TB _fields= $this->get_fields ($tbname); Upgrade to increase the presence of the Judgment field

foreach ($varray as $key => $value)
{
if (In_array ($key, $TB _fields))
{
$fileds []= '. $key. `';
$values []=is_string ($value)? " $value. ': $value;
}
}

if ($fileds)
{
$fileds =implode (', ', $fileds);
$fileds =str_replace (",", $fileds);
$values =implode (', ', $values);
$sql = $replace? " Replace into {$tbname} ({$fileds}) VALUES ({$values}) ': INSERT INTO {$tbname} ({$fileds}) VALUES ({$values}) ";
$this->query ($sql, true);
return $this->last_insert_id ();
}
else return false;
}

function Update ($tbname, $array, $where = ")
{
$array = $this->escape ($array);
if ($where)
{
$TB _fields= $this->get_fields ($tbname); Increase the presence of a judgment field

$sql = ';
foreach ($array as $k => $v)
{
if (In_array ($k, $TB _fields))
{
$k =str_replace (",", $k);
$sql. = ", ' $k ' = ' $v '";
}
}
$sql = substr ($sql, 1);

if ($sql) $sql = "Update" $tbname ' set $sql where $where ';
else return true;
}
Else
{
$sql = "replace into ' $tbname ' ('". Implode (', ', Array_keys ($array)). " ". Implode (" ', ' ", $array)."
}
return $this->query ($sql, true);
}

function Mysql_delete ($tbname, $idarray, $filedname = ' id ')
{
$idwhere =is_array ($idarray) implode (', ', $idarray): Intval ($idarray);
$where =is_array ($idarray)? " {$tbname}. {$filedname} in ({$idwhere}) ': ' {$tbname}. {$filedname}={$idwhere} ";

return $this->query ("delete from {$tbname} where {$where}", true);
}

function Get_fields ($table)
{
$fields =array ();
$result = $this->fetch_all ("Show columns from ' {$table} '");
foreach ($result as $val)
{
$fields []= $val [' field '];
}
return $fields;
}

function Get_table_status ($database)
{
$status =array ();
$r = $this->fetch_all ("Show Table status from". $database. " `"); Show table status is similar in nature to show table, but it can provide a large amount of information for each table.
foreach ($r as $v)
{
$status []= $v;
}
return $status;
}

function Get_one_table_status ($table)
{
return $this->fetch_one ("Show table status like ' $table '");
}

function Create_fields ($tbname, $fieldname, $size =0, $type = ' varchar ')//2010-5-14 correction
{
if ($size)
{
$size =strtoupper ($type) = = ' varchar ' $size: 8;
$this->query ("ALTER TABLE ' {$tbname} ' add ' $fieldname ' {$type} ' ({$size}) not NULL", true);
}
else $this->query ("ALTER TABLE ' {$tbname} ' add ' $fieldname ' mediumtext not NULL", true);
return true;
}

function get_tables ()//Get all table names
{
$tables =array ();
$r = $this->fetch_all ("Show Tables");
foreach ($r as $v)
{
foreach ($v as $v _)
{
$tables []= $v _;
}
}
return $tables;
}

function create_model_table ($tbname)//Create a Content model table (start: Initially only the field ContentID Int (20), for the content table,/////////////////////// update:2010-5-20 default to add ' content ' mediumtext not null, field
{
if (In_array ($tbname, $this->get_tables ()) return false; Returns false when the table name already exists
if ($this->query ("CREATE table ' {$tbname} '" (
' ContentID ' Mediumint (8) NOT NULL,
' content ' Mediumtext not NULL,
Key (' ContentID ')
) engine = MyISAM default Charset=utf8 ", true") return true; Returns true if successful
return false; Failure returns false
}

function create_table ($tbname)//Create a Membership Model empty table (initial only field UserID Int (20), for member table, 2010-4-26)
{
if (In_array ($tbname, $this->get_tables ()) return false;
if ($this->query ("CREATE table ' {$tbname} '" (
' UserID ' Mediumint (8) NOT NULL,
Key (' UserID ')
) engine = MyISAM default Charset=utf8 ", true") return true;
return false;
}

function Escape ($STR)//filter Dangerous characters
{
if (!is_array ($STR)) return Str_replace (Array (' n ', ' R '), Array (Chr (), CHR), mysql_real_escape_string (preg_ Replace ($this->search, $this->replace, $str), $this->dblink));
foreach ($str as $key => $val) $str [$key] = $this->escape ($val);
return $str;
}

 function Filter_pass ($string, $allowedtags = ', $disabledattributes = Array (' onabort ', ' onactivate ', ' Onafterprint ', ' onafterupdate ', ' onbeforeactivate ', ' onbeforecopy ', ' onbeforecut ', ' onbeforedeactivate ', ' Onbeforeeditfocus ', ' onbeforepaste ', ' onbeforeprint ', ' onbeforeunload ', ' onbeforeupdate ', ' onblur ', ' onbounce ', ' Oncellchange ', ' onchange ', ' onclick ', ' oncontextmenu ', ' oncontrolselect ', ' oncopy ', ' oncut ', ' ondataavaible ', ' ondatasetchanged ', ' ondatasetcomplete ', ' ondblclick ', ' ondeactivate ', ' Ondrag ', ' ondragdrop ', ' ondragend ', ' OnDragEnter ', ' ondragleave ', ' ondragover ', ' ondragstart ', ' ondrop ', ' onerror ', ' onerrorupdate ', ' onfilterupdate ', ' OnFinish ', ' onfocus ', ' onfocusin ', ' onfocusout ', ' onhelp ', ' onkeydown ', ' onkeypress ', ' onkeyup ', ' onlayoutcomplete ', ' OnLoad ', ' onlosecapture ', ' onmousedown ', ' onmouseenter ', ' onmouseleave ', ' onmousemove ', ' onmoveout ', ' onmouseo tutorial ver ', ' OnMouseUp ', ' onmousewheel ', ' onmove ', ' onmoveend ', ' onmovestart ', ' onpaste ', ' onpropertychange ', ' onReadyStateChange ', ' onreset ', ' onresize ', ' onresizeend ', ' onresizestart ', ' onrowexit ', ' onrowsdelete ', ' Onrowsinserted ', ' onscroll ', ' onselect ', ' onselectionchange ', ' onselectstart ', ' onstart ', ' onstop ', ' onsubmit ', ' OnUnload '))
{
if (Is_array ($string))
{
foreach ($string as $key => $val) $string [$key] = $this->filter_pass ($val, allowed_htmltags);
}
Else
{
$string = Preg_replace (' s. Implode (' | ', $disabledattributes). *? ([S>])/', ', Preg_replace ('/< (. *?) >/ie ', ' < '. Preg_replace Array ('/web effects: [^ "']*/i ', '/(". Implode (' | ', $disabledattributes). ") []*=[]*["'][^" ']*[', ']/i ', '/s+/'), Array (', ', '), Stripslashes (')). ' > ' ", Strip_tags ($string, $allowedtags)));
}
return $string;
}

function drop_table ($tbname)
{
return $this->query ("drop table if exists ' {$tbname} '", true);
}

Function version ()
{
Return Mysql_get_server_info ($this->dblink);
}
}

I hope this article will help you with your PHP program design.