做开发时,经常会与数据库打交道,记录一下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. <?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. ?>
代码逻辑简单,看看注释就知道什么意思了。