PHP Database Operation class (Implementation of table additions and deletions, fetching rows, querying multiple data, etc.)

Source: Internet
Author: User
Tags php database rtrim
PHP database Operation class, to realize the table additions and deletions, to get the number of rows, query multiple data records, left connection query, create data table structure. Feature-rich, convenient to move the value, the need for friends, you can refer to the next.

PHP Database class complete code with example below.

1. Code

     Links = mysql_connect (Db_host, Db_user, db_pwd); if (DEBUG) {mysql_select_db (db_name) or Die (' ERROR: '. mysql_error ());} else{mysql_select_db (db_name);} $char _sql = "SET NAMES". CHARSET. "'"; $this->query ($char _sql);} /*** ****************** Operation *******************//*** Additions and deletions change operation method * Input SQL statement * Returns a Boolean or result set $row*/function query ($sql) {if (DEBUG) { $render = mysql_query ($sql) or Die (' Query ERROR: '. Mysql_error (). "
SQL statement error: ". $sql); return $render;} Else{return mysql_query ($sql);}} /*** calculated row Count method * Input * result array */function count ($table, $condition = ' 1 ') {$sql = "SELECT count (*) from '". Db_prefix. $table. " ' Where $condition '; $result = $this->select ($sql); return $result [0][' Count (*) '];} /*** Original SQL Statement Check operation method * Input SQL statement * result array */function Select ($sql) {$row = $this->query ($sql); $results = Array (); while ($arr = $t His->fetch ($row)) {$results [] = $arr;} $this->free_result ($row); return $results;} /*** Check if a field has a value * @param enter the table name. field, value * @return ID or false**/function check_exists ($table, $val) {$render = false; $tab = Explod E ('. ', $table), if ($tab [' 1 '] && $tab [' 1 ']!= ' id ') {$fields = $tab [' 1 ']; $table = "{$tab [0]}.id,{$fields}";} else{$fields = ' id '; $table = $tab [0]. ". ID ";} $condition = "' $fields ' = ' {$val} '"; $detail = $this->read ($table, $condition), if ($detail [$fields]) {$render = $detail [' ID '];} return $render;} /*** querying multiple Data methods * Enter table name. field, field, query condition, number of bars * If the condition is an array, enter advanced search mode * return result array */function readall ($table, $condition = ' 1 ', $limit = '){$tab = explode ('. ', $table); $table = $tab [' 0 '];if ($tab [' 1 ']) {$fields = $tab [' 1 ']; $fields _array =explode (', ', $fields); $fields = '; foreach ($fields _array as $one) {$fields. = "' $one ',";} $fields = RTrim ($fields, ', ');} else{$fields = ' * ';} if (Is_array ($condition)) {$condition = $this->parse_condition ($condition);} $sql = "Select $fields from". Db_prefix. $table. " ' Where $condition ', if ($limit) $sql. = "Limit $limit"; return $this->select ($sql);} /*** querying a single data method * Enter the table name. field, field, query condition * Returns an array of results */function read ($table, $condition = ' 1 ') {$render = $this->readall ($table, $ condition,1); return $render [0];} /*** Modify Data method * Enter table name, insert data array (' field ' = = ' value '), Condition * Return boolean value */function update ($table, $data, $condition) {$set = "; foreach ($ Data as $key = = $val) {$set. = "' $key ' = '". $val. "',";} $set = RTrim ($set, ', '), if (Is_array ($condition)) {$condition = $this->parse_condition ($condition);} $sql = "Update". Db_prefix. $table. " ' Set $set where $condition '; return $this->query ($sql);} /*** Insert Data method * Enter table name, data array (' field ' = = ' value ') * returnBack to Boolean */function insert ($table, $data) {$fields = array (); $values = Array (); foreach ($data as $key + = $val) {if (Is_array ($ val) {$_values = array (); $_fields = Array (); foreach ($val as $k = = $v) {$_fields[]= "' $k '"; $_values[]= "' {$v} '";} $fields = $_fields; $values [] = ' ('. Implode (', ', $_values) ') ';} else{$fields [] = "' $key '"; $values [] = "' {$val} ';}} $fields = Implode (', ', $fields), $values = Implode (', ', $values); $sql = "INSERT INTO". Db_prefix. $table. " ' ($fields) VALUES ($values) "; return $this->query ($sql);} /*** Delete Data method * Enter table name, condition * Returns bool*/function Delete ($table, $condition) {if (empty ($condition)) {die (' condition cannot be null ');} if (Is_array ($condition)) {$condition = $this->parse_condition ($condition);} $sql = "Delete from". Db_prefix. $table. " ' Where $condition '; return $this->query ($sql);} /*** function for parsing conditions * @param condition Array * $arr [] = "' id ' ==0"; $arr [] = "' id ' ==5"; $arr [' id '] = "5"; $arr [' or '] [] = "' id '!=2"; $arr [' or '] [] = "' id '!=1"; $arr [' or ' [] = "' id '!=2"; $arr [' Groups '][]= ' id '; $arr [' Orders '] [' ID ']= ' ASC '; $arr [' Orders '] [' TD ']= ' DESC '; * * @return str**/function parse_condition ($condition) {$and = ' 1 '; $or = ' 0 '; $groups = Array (); $orders = Array (); foreach ($co Ndition as $key = = $val) {if (Is_numeric ($key)) {$and. = "and $val";} ElseIf (Strtolower ($key) = = ' or ') {//handles the OR condition if (Is_array ($val)) {foreach ($val as $k = = $v) {if (Is_numeric ($k)) {$or. = "or {$v} ";} ElseIf (Is_array ($v)) {$v = implode (', ', $v); $or. = "or ' $k ' in ($v)";} else{$or. = "or" $k = ' {$v} ';}}} else{$or. = "or $val '";}} ElseIf (Strtolower ($key) = = ' groups ') {//Process group Byforeach ($val as $k = = $v) {$groups [] = $v;}} ElseIf (Strtolower ($key) = = ' orders ') {//Process order Byforeach ($val as $k = = $v) {$orders [] = $k. '. $v;}} Else{if (Is_array ($val)) {$val = implode (', ', $val); $and. = "and ' $key ' in ($val)";} else{$and. = "and ' $key ' = ' {$val} ';}}} if ($and! = ' 1 ' && $or! = ' 0 ') $where = $and. ' or '. $or; ElseIf ($and! = ' 1 ') $where = $and; ElseIf ($or! = ' 0 ') $where = $or; ($groups) $where. = "GROUP BY". Implode (', ', $groups), if ($orders) $where. = "ORDER By". Implode (', ', $orders); $where = Str_rEplace (' 1 and ', ', Str_replace (' 0 or ', ', $where)); return $where;} /*** Lock Table method * Input table name, lock type, R or W write lock to be placed in front of Read lock * return bool*/function Lock ($table, $type = ' R ') {if ($type = = ' R ') {$type = ' read ';} else{$type = ' WRITE ';} $sql = "Lock table". Db_prefix. $table. " ' $type '; return $this->query ($sql);} /*** Unlock Table Method * * returns Bool*/function unlock () {$sql = "unlock tables"; return $this->query ($sql);} /*** The result set into the array method * Returns an array, the pointer moves down */function fetch ($row) {return mysql_fetch_array ($row, MYSQL_ASSOC);} /*** calculation result set row number method * Input $row* returns the number of rows */function num_rows ($row) {return mysql_num_rows ($row);} /*** computed result set column number method * Input $row* returns the number of columns */function num_fields ($row) {return mysql_num_fields ($row);} /*** Release result set Memory * Returns a Boolean value */function Free_result ($row) {return mysql_free_result ($row);} /*** View the field name of the specified table * Enter table name * Return all fields an array group */function List_fields ($table) {$fields = Mysql_list_fields (db_name, Db_prefix. $table, $ this->links); $columns = Mysql_num_fields ($fields); for ($i = 0; $i < $columns; $i + +) {$row [$i] = Mysql_field_name ($fi ELDs, $i);} return $row;} /*** View database version sideMethod */function version () {return mysql_get_server_info ();} /*** insert when inserting id*/function insert_id () {return mysql_insert_id ();} /*** Paging Method */function page ($table, $condition = ' 1 ', $pagesize =20, $id = ' page ') {$page = $_get[$id];if (! $page) $page = 0; ElseIf (!is_numeric ($page)) Die (' page error ');//Find result set $p = $page * $pagesize; $limit = $p. ",". $pagesize; $results = $this ReadAll ($table, $condition, $limit);//Get result set rows $num = $this->count ($table, $condition);//define last page $maxpageif ($num% $ pagesize) {$maxpage = (int) ($num/$pagesize + 1),} else$maxpage = $num/$pagesize, if (STATICS) {//Get URL information from the server if ($_get[$id ] = = = null) {$_server["Request_uri"] = Str_replace (' index.php ', ' ", $_server[" Request_uri "]); $_session[$id] = str_ Replace ('. html ', ' ', $_server["Request_uri"], $count), $_session[$id] = $count? $_session[$id]: $_session[$id]. ' Index '; if (!sizeof ($_get)) $_session[$id].= "-htm";} $STR = "First", if ($page) $str. = "Previous", if ($page -3>=0) $str. = "". ($page-2). ""; if ($page -2>=0) $str. = "". ($page-1). ""; if ($page -1>=0) $str. = "". $page." "If ($page < $maxpage) $str. = ($page + 1)." "If ($page +1 < $maxpage) $str. =" ". ($page +2). ""; if ($page +2 < $maxpage) $str. = "". ($page +3). ""; if ($page +3 < $maxpage) $str. = "". ($page +4). ""; if ($page +1 < $maxpage) $str. = "Next", if (! $maxpage) $maxpage =1; $str. = "Last". ($page + 1). " /". $maxpage." Total ";} else{//get URL information from server if ($_get[$id] = = = null) {$_session[$id] = $_server["Request_uri"];if (!sizeof ($_get)) $_session[$ id].= "? P=1";} $STR = "First", if ($page) $str. = "Previous", if ($page -3>=0) $str. = "". ($page-2). ""; if ($page -2>=0) $str. = "". ($page-1). ""; if ($page -1>=0) $str. = "". $page. " "If ($page < $maxpage) $str. = ($page + 1)." "If ($page +1 < $maxpage) $str. =" ". ($page +2). ""; if ($page +2 < $maxpage) $str. = "". ($page +3). ""; if ($page +3 < $maxpage) $str. = "". ($page +4). ""; if ($page +1 < $maxpage) $str. = "Next", if (! $maxpage) $maxpage =1; $str. = "Last". ($page + 1). " /". $maxpage." Total ";} Return Array ($results, $str);} /*** left connection multiple table query * @param * @return **/function leftjoin ($left, $righT, $on, $condition, $limit =1) {$left = explode ('. ', $left); $right = Explode ('. ', $right); $left [' 0 '] = "'". Db_prefix. $left [' 0 ']. " $right [' 0 '] = "'". Db_prefix. $right [' 0 ']. " The IF ($left [' 1 '] | | $right [' 1 ']) {$fields = ', if (!empty ($left [' 1 '])) {$_field = explode (', ', $left [' 1 ']); foreach ($_ field as $one) {$fields. = $left [' 0 ']. ". $one." `,";}} if (!empty ($right [' 1 '])) {$_field = explode (', ', $right [' 1 ']), foreach ($_field as $one) {$fields. = $right [' 0 ']. ". ". $one." `,";}} $fields = RTrim ($fields, ', ');} else{$fields = ' * ';} $on = Str_replace (' \2 ', $right [0],str_replace (' \1 ', $left [0], $on)); $condition = Str_replace (' \2 ', $right [0],str_ Replace (' \1 ', $left [0], $condition)), $sql = "SELECT {$fields} from {$left [0]} left JOIN {$right [0]} in ({$on}) WHERE ({$c Ondition}) LIMIT {$limit} "; $query = $this->query ($sql); $field _num = Mysql_num_fields ($query); while ($arr = Mysql_ Fetch_array ($query, Mysql_num)) {$_arr = array (); for ($i =0; $i < $field _num; $i + +) {$table = Str_replace (Db_prefix, ", Mysql_field_table ($query, $i); $field = Mysql_field_name ($query, $i); $_arr[$table. '. '. $field] = $arr [$i];} $array []=$_arr;} $array = $limit ==1? $arrat [0]: $array; return $array;} /*** used to create a table structure * @param table name, structure array (field, format, comment) Table Comment Index Array (field, field) Full-text Search (field, field) * @return Print **/function createtable ($ TableName, $data, $comment = ', $key = ', $fulltext = ') {$_key= '; $_type = '; $_fulltext = '; $tablename = db_prefix.$ tablename; $sql = "CREATE TABLE IF not EXISTS ' $tablename ' (' id ' int () unsigned not NULL auto_increment COMMENT ' id ',"; fo Reach ($data as $one) {switch ($one [1]) {case ': $_type = ' varchar (255) '; break;case ' tinyint ': $_type = ' tinyint (1) '; Case ' time ': $_type = ' int '; Break;default:if (Strpos ($one [1], '. ')! ==false) {$_type = explode ('. ', $one [1]); $_type = $_type[0]. ' ('. $_type[1]. ') ';} Else{$_type = $one [1];} break;} $sql. = "' {$one [0]} ' $_type not NULL COMMENT ' {$one [2]} ',";} if (!empty ($key)) {foreach ($key as $one) {$_key.= "key ' $one ' (' $one '),";}} if (!empty ($fulltext)) {foreach ($fulltext as $one) {$_key.= "fulltext ' $one ' (' $one '),";}} $sql.= $_key.$_fulltext. " PRIMARY KEY (' id ')) engine=myisam DEFAULT charset= ". CHARSET. " COMMENT ' $comment '; "; if (!$_get[sure]) {if (empty ($_get)) {$url = '? sure=1 ';} else{$url = $_server["Request_uri"]. ' &sure=1 ';} echo "is about to perform the following table-building operations:
$sql determine
";} ElseIf ($_get[sure] && $this->query ($sql)) {echo "Done";} Else{echo "Operation failed:
>
$sql
";} Exit;}};? >

Invocation Example:

 
      CreateTable (' article ', $data, ' Article table ');//additions and Deletions $data[' title ']= ' t '; $data [' keyword ']= ' k '; $Db->insert (' article ', $ Data), $num = $Db->read (' article.id ', ' 1 ORDER by id DESC '), $data [' created '] = mktime () + $num [' id ']; $Db->update (' Article ', $data, ' ' id ' =2 '); $Db->delete (' article ', "' id ' = 3"); >
  • Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.