A simple and practical php operation for mysql databases

This article mainly introduces a simple and practical php database class for mysql operations. it not only contains common php operations for mysql databases, but also contains the filtering function for dangerous characters, it is very practical. if you need it, you can refer to the following example to describe a simple and practical php operation mysql database class. Share it with you for your reference. The details are as follows:

The code is as follows:

/*
This database connection class automatically loads the SQL anti-injection function to filter sensitive SQL query keywords, in addition, the show table status field can be determined and all table names of the database can be obtained using the show table class. */
@ 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 .' '; $ 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 ('SQL statement executed'. $ SQL. 'the following error occurs:'. 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) // obtain the maximum value of the $ filed field in the $ 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 column type of auto_increment is bigint, the value returned by mysql_insert_id () is 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 check whether a field exists. 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); // add a value to determine whether a field exists. $ 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 = "updat' $ tbname' set $ SQL where $ where "; Else return true; } Else { $ SQL = "replace into '$ tbname '('". implode ('','', array_keys ($ array )). "') values ('". 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 to show table. However, 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 ') // modify it in. { 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 () // obtain the names of all tables { $ 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), used for the content table, //// // update: 'content' mediumtext not null is added by default, and the field) { If (in_array ($ tbname, $ this-> get_tables () return false; //// // when the table name already exists, false is returned. If ($ this-> query ("create table '{$ tbname }'( 'Tentid' mediumint (8) not null, 'Content' mediumtext not null, Key ('tentid ') ) Engine = myisam default charset = utf8 ", true) return true; // success, true is returned. Return false; // return false if a failure occurs. } Function create_table ($ tbname) // Create a Member model empty table (initially only the field userid int (20) for the member table) { 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 (10), chr (13 )), 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', 'onafterupdat', 'onbeactivforeate ', 'onbeforecopy', 'weight', 'weight', 'onbeforeeditfocal ', 'onbeforepaste', 'onbeforeprint ', 'onbeforeunload', 'onbeforeupdate', 'onblur', 'onbounce ', 'oncellchang', 'onchange', 'onclick', 'ontextmenu ', 'oncontrolselect', 'oncopy', 'oncut', 'ondataavaable', 'ondatasetchanged ', 'ondatasetcomplete ', 'ondblclick', 'onactivate', 'ondrag', 'ondragdrop', 'ondragend', 'ondragenter', 'ondragleave ', 'ondragover', 'ondragstart', 'ondrop ', 'onerror', 'onerrorupdat', 'onfilterupdate', 'onfinish ', 'onuse', 'onfocusin', 'onfocusout', 'onhelp', 'onkeylow', 'onkeypres ', 'onkeyup', 'onlayoutcomplete', 'onload', 'onlosecapture ', 'onmousedown', 'onmouseenter', 'onmouseleave ', 'onmousemove', 'onmoveout ', 'onmouseo tutorial version', 'onmouseup', 'onmousewheel ', 'onmove', 'onmoveend', 'onmovestart', 'onpaste ', 'propertychang', 'onreadystatechang ', 'onreset', 'onresized', 'onresizeend', 'onresizestart ', 'onrowexit', 'weight', 'onwsinserted', 'onscroll', 'onselect', 'onselectionchang ', '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 ('/webpage effect: [^ "'] */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 PHP programming.