php mysql 資料庫連結與操作

做開發時，經常會與資料庫打交道，記錄一下php與mysql建立連結的方法

   1. <?php   2. class Mysql{   3. private $HOST = "localhost"; // 資料庫地址   4. private $USER = "root"; // 資料庫帳號   5. private $PASS = "lizhihua"; // 資料庫密碼   6. private $DBNAME = "db_iqoomea"; // 資料庫庫名   7. private $CONN;   8. /**   9. * 建構函式：串連資料庫  10. * @return TRUE：串連成功；FALSE：串連失敗。  11. */  12. function Mysql() {  13.    $user = $this->USER;  14.    $pass = $this->PASS;  15.    $host = $this->HOST;  16.    $db = $this->DBNAME;  17.    // 串連資料庫  18.    $conn = mysql_connect($host, $user, $pass);  19.    mysql_select_db($db, $conn);  20.    mysql_query("SET NAMES UTF8");  21.    $this->CONN = $conn;  22.    return true;  23. }  24. /**  25. * 資料庫表查詢  26. *如 select * from table  27. */  28. function getallinfo($table) {  29.    if ( empty($table)) return false;  30.    if ( empty($this->CONN) ) return false;  31.    $conn = $this->CONN;  32.    // 發送SQL語句，獲得結果  33.    $sql = "select * from $table";  34.    $result = mysql_query($sql, $conn);  35.    if ( (!$result) or (empty($result)) ) {  36.     return false;  37.    }  38.    $num = 0;  39.    $data = array();  40.    // 將查詢結果放二維數組中  41.    while ( ($row = mysql_fetch_array($result)) ) {  42.     $data[$num] = $row;  43.     $num++;  44.    }  45.    mysql_free_result($result);  46.    return $data;  47. }  48.  49. //可用於插入、修改、更新、刪除資料,建立資料表  50. function query($strSQL) {  51.    if ( empty($strSQL) ) return false;  52.    if ( empty($this->CONN) ) return false;  53.    $conn = $this->CONN;  54.    $result = mysql_query($strSQL,$conn);  55.    if ( !result ) return false;  56.    return $result;  57. }  58.  59. function select($table,$field,$findname){  60.     if ( empty($table) ) return false;  61.     if ( empty($field) ) return false;  62.     if ( empty($findname) ) return false;  63.     if ( empty($this->CONN) ) return false;  64.     $conn = $this->CONN;  65.     $sql = "select * from $table where $field='$findname'";  66.     $result = mysql_query($sql, $conn);  67.     if ( (!$result) or (empty($result)) ) {  68.         return false;  69.     }  70.     $data = mysql_fetch_array($result);  71.     mysql_free_result($result);  72.     return $data;  73. }  74.  75. //根據查詢條件，返回查詢單條記錄  76. function getinfo($strSQL) {  77.    if ( empty($strSQL) ) return false;  78.    if ( empty($this->CONN) ) return false;  79.    $conn = $this->CONN;  80.    // 發送SQL語句，獲得結果  81.    $result = mysql_query($strSQL, $conn);  82.    if ( (!$result) or (empty($result)) ) {  83.     return false;  84.    }  85.    $data = mysql_fetch_array($result);  86.    mysql_free_result($result);  87.    return $data;  88. }  89.  90. //根據查詢條件，返回多條記錄  91. function getmoreinfo($strSQL){  92.    if ( empty($strSQL) ) return false;  93.    if ( empty($this->CONN) ) return false;  94.    $conn = $this->CONN;  95.    $result = mysql_query($strSQL, $conn);  96.    if ( (!$result) or (empty($result)) ) {  97.     return false;  98.    }  99.    $num = 0; 100.    $data = array(); 101.    // 將查詢結果放二維數組中 102.    while ( ($row = mysql_fetch_array($result)) ) { 103.     $data[$num] = $row; 104.     $num++; 105.    } 106.    mysql_free_result($result); 107.    return $data; 108. } 109. //根據查詢條件，返回多條記錄 110. //function getallinfo($table){ 111. // if ( empty($table) ) return false; 112. // if ( empty($this->CONN) ) return false; 113. // $conn = $this->CONN; 114. // $sql = "select * from $table"; 115. // $result = mysql_query($sql, $conn); 116. // if ( (!$result) or (empty($result)) ) { 117. // return false; 118. // } 119. // $num = 0; 120. // $data = array(); 121. // // 將查詢結果放二維數組中 122. // while ( ($row = mysql_fetch_array($result)) ) { 123. // $data[$num] = $row; 124. // $num++; 125. // } 126. // mysql_free_result($result); 127. // return $data; 128. //} 129. 130. //返回影響（插入，查詢，更新，刪除）到的行數 131. function getrowsnum($strSQL){ 132.    if ( empty($strSQL) ) return false; 133.    if ( empty($this->CONN) ) return false; 134.    $conn = $this->CONN; 135.    $result = mysql_query($strSQL,$conn); 136.    $num = mysql_num_rows($result); 137.    mysql_free_result($result); 138.    return $num; 139. } 140. 141. function getallrowsnum($table){ 142.    if ( empty($table) ) return false; 143.    if ( empty($this->CONN) ) return false; 144.    $conn = $this->CONN; 145.    $sql = "select * from $table"; 146.    $result = mysql_query($sql,$conn); 147.    $num = mysql_num_rows($result); 148.    mysql_free_result($result); 149.    return $num; 150. } 151. 152. /* 可用於修改資料 153. * $tableName 表名 154. * $setPorpertyName 修改的欄位名 155. * $setValue 修改的值 156. * $id 指定id 157. * 158. */ 159. function update($strSQL) { 160.    if ( empty($strSQL) ) return false; 161.    if ( empty($this->CONN) ) return false; 162.    $conn = $this->CONN; 163.    // 發送SQL語句，更新資料庫 164.    $result = mysql_query($strSQL, $conn); 165.    if(!$result)return false; 166.    return $result; 167. } 168. 169. //刪除指定表的某個id的記錄 170. function delete($tableName,$id) { 171.    if ( empty($tableName) ) return false; 172.     if ( empty($this->CONN) ) return false; 173.     $conn = $this->CONN; 174.     $strSQL="delete from $tableName where id='$id'"; 175.     $result = mysql_query($strSQL, $conn); 176.     return true; 177. } 178. 179. //返回一個數組，數組包含該表所以欄位 180. function getfields($table){ 181.     if ( empty($table) ) return false; 182.     if ( empty($this->CONN) ) return false; 183.     $conn = $this->CONN; 184.     $sql = "show full fields from $table"; 185.     $showresult = mysql_query($sql,$conn); 186.     $showdata = array(); 187.     while($showrow = mysql_fetch_array($showresult)){ 188.         $showdata[] = $showrow['Field']; 189.     } 190.     mysql_free_result($showresult); 191.     return $showdata; 192. } 193. //返回一個表的欄位的數目 194. function getfieldsnum($table){ 195.     if ( empty($table)) return false; 196.     if ( empty($this->CONN) ) return false; 197.     $conn = $this->CONN; 198.     $sql = "select * from $table"; 199.     $showresult = mysql_query($sql,$conn); 200.     $getcount = mysql_num_fields($showresult); 201.     mysql_free_result($showresult); 202.     return $getcount; 203. } 204. 205. } 206. ?>

使用舉例

1.    1. <?php   2. header("content-Type: text/html; charset=utf-8");   3. session_start();   4. include "../include/conn.inc";   5. $str=microtime();   6. $arr=explode(" ",$str);   7. $start = $arr[0] * 1000;   8. $consql = new Mysql();   9. mysql_query('SET NAMES utf8;');  10. $valuetype = 2;  11.  12. $musicname = $_GET['musicname'];  13.  14. //$sqlsec = "select * from SecondDay_Info where CityName='$musicname'";  15.  16. $Song_Info = array();  17. $Singer_Info = array();  18. $Album_Info = array();  19. $SecondDay_Info = array();  20. $strSQL = "select * from liricstable where singer_name='$musicname'";  21. if($valuetype == 2){  22.     $Song_Info = $consql->getmoreinfo($strSQL);  23.     print_r($str1);  24.     foreach($Song_Info as $rows){  25.         $str = $rows['song_name'] . "----" . $rows['singer_name'] . "----" . $rows['album_name'] . "----" . $rows['liric_path'] . "----" . $rows[album_cover_path] . "\n";  26.         print_r($str);  27.     }  28. }  29. ?>

代碼邏輯簡單，看看注釋就知道什麼意思了。