php mysql 数据库链接与操作

aliounian

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. <?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. ?>