现在的位置: 首页 > 综合 > 正文

php mysql 数据库链接与操作

2013年08月07日 ⁄ 综合 ⁄ 共 5583字 ⁄ 字号 评论关闭

做开发时,经常会与数据库打交道,记录一下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. ?>

代码逻辑简单,看看注释就知道什么意思了。

抱歉!评论已关闭.