数据库访问 mysql连接库--查询

            public
            class
             sqlhelper
{
    
            private
            $host="localhost";
    private$username="root";
    private$password ="root";
    private$db="test";

    publicfunction sqlhelper()
    {
        $conn=mysql_connect($host,$username,$password);
        if(!$conn)
        {
             die("connect fail".mysql_error());
        }
        mysql_select_db($this->db,$this->conn) or die(mysql_error());
        mysql_query("set names utf8");
    }
    publicfunction Execute_dql($sql)
    {
       $res=mysql_query($sql,$this->conn);
       return$res;
    }
    mysql_free_result($res);
    mysql_close($this->conn) or die(mysql_error());
}
    publicfunction Execute_dml($sql)
    {
        $res=mysql_query($sql,$this->conn);
        if(!$res)
        {
            return 0;  //失败        }
        else
        {
            if(mysql_affected_rows($this->conn)>0)
            {
              return 1;
            }
            else
            {
              return 2;
            }
        }    
    }

<?php
 require_once‘SqlHelper.class.php‘;
 if(isset($_REQUEST[‘Enname‘])){
$en_word=$_REQUEST[‘Ename‘];    
}else{
echo"input null";
echo"<a href=‘mainView.php‘>return</a>"
}

$sql="select chword from words where enword=‘".$en_word."‘limit 0,1";

///$sql="select enword from words where chword like ‘%".$en_word."%‘ limit 0,1";
if(mysql_num_rows($res)){
while($row=mysql_fetch_assoc($res)){
}else{
}
mysql_free_result($res);
mysql_close($conn);
}
?>

<?php
header("Content_type:text/html;charset=utf-8")
publicclass SqlHelper
{
    privatestatic$host="localhost";
    privatestatic$username="root";
    privatestatic$password="root";
    privatestatic$db="test";
    private mysqli="";

    public __construct
    {//self::$host$this->mysqli=new MySQLi(self::$host,self::$username,self::$password,self::$db)   //MySQLi中大小无所谓if(!$this->mysqli->conncet_error)
        {
            die("connect error".$this->mysqli->connect_error);
        }
    }

    public Execute_dql($sql)
    {//des select show explain 返回mysqli result$res=$this->mysqli->query($sql) or die("Query error".$this->mysqli->error);
        if($res)
        while($row=$res->fetch-assoic())
        {
            foreach($rowas$key=>$value)
            echo"$value";
        }
        echo"<br/>";
//$res->free();资源的释放应该在调用后释放
//$this->mysqli->close();}

    public Execute_dml($sql)
    {
        $res=$this->mysqli->query($sql) die("Query error".$this->mysqli->error);;
        if($res)
        {
            $counts=$this->mysqli->affeced_rows();
            if($counts>0)
            {//echo"affected rows".$counts;return 0 ;           
            }
            else
            {//echo"no rows affected"return 1;           
            }
        }
        else
        {
            return 2;           //echo"opreate fail";        }
        $this->mysqli->close();
}

    public Execute_Batch_dql($sql)
    {//批量查询$res=$this->mysqli->multi_query($sql);
        if(!$res)
        {
            echo"operate error".$this->mysqli->error;
        }
        else
        {
            do{
                $result=$res->store_result(); //每执行一次取出一个结果集while($row =$result->fetch_row())
                {
                    foreach($rowas$key=>$val)
                    {
                        echo"--$val";
                    }
                    echo"</br>";
                }
                $result->free();
                if(!$res->more_results())
                {
                    break;
                }
            }while($res->next_result();)
        }
        $this->mysqli->close();
}

//批量dmlpublic Execute_Batch_dml($sql)
    {
        $res=$this->mysqli->multi_query($sql);   //批量执行，返回true，false。if(！$res)
        {
            echo"operate error".$this->mysqli->error;
        }
        else
        {
        }
        $this->mysqli->close();
    }
}
?>                                                        

<?php
    $mysqli=mysqli_connect("localhost","root","root","db") or die("conncet error".mysqli_connect_error($mysqli));
    mysqli_query(set names utf8);
    $sql="select * from users";
    $res =mysqli_query($mysqli,$sql);
    if(!$res)
    {
      echo"operate error".mysqli_error($mysqli);
    }
    else
    {
        while(mysqli_fetch_row($res))
        {
            foreach($rowas$key=>$value)
            {
                echo"--$value";
            }
            echo"</br>";
        }
        mysqli_free_result($res);
        mysqli_close($mysqli);
}
?>            

<?php
requrie_once"SqlHelper.class.php";
$sql="update test set name =‘cao‘where id=1;";
$sql.="delete from test where id>1;";
$sql.="insert into test (name,age,score) values(‘piliang‘,10,100)"

$mysqli=new SqlHelper();
$mysqli->Execute_Batch_dml($sql);
?>

  <?php
   require_once("SqlHelper.class.php");
   $mysqli =new SqlHelper();
   $mysqli->autocommit(false);
   $dml1=$mysqli->query("update user set age=age-2  where id=1");
   $dml2=$mysqli->query("update user set age=age+2  where id=2");
   if(!$dml1||!$dml2)
　　{
   　　$mysqli->rollback();
   }
　　else
　　{
   　　$mysqli->commit();
　　}
　　$mysqli->close();
?>

<?php
....
    $id=1;
    $name="cao";
    $age=100;
    $sql="insert into users values(?,?,?)";
    $mysqli=new MySQLi("localhost","root","root","test");
    $mysqli_stmt->prepare($sql);
    $mysqli_stmt->bindparam("isi",$id,$name,$age);  //s代表字符型，i代表数值型$mysqli_stmt->execute() or die("execute error".$mysqli_stmt->error);
    $id=2;
    $name="hua";
    $age=50;
    $mysqli_stmt->bindparam("isi",$id,$name,$age) ;
    $mysqli_stmt->execute() or die("execute error".$mysqli_stmt->error);

  $mysqli_stmt->free();
....
?>

<?php
....
    $sql="select id ,name ,age from users where id>?";
    $mysqli=new MySQLi("localhost","root","root","test");
    if($mysqli->connect_error)
    {
      die($mysqli->connect_error);
    }
    $mysqli_stmt->prepare($sql);
    $id=5;
    $mysqli_stmt->bindparam("i",$id);  //参数需要多次绑定$mysqli_stmt->bindresult($id,$name,$age);//此处是按传址绑定。结果集只需要绑定一次。$mysqli_stmt->execute() or die("execute error".$mysqli_stmt->error);
    while($mysqli_stmt->fetch())
    {
        echo"<br/>--$id--$name--$age";
    }
    $mysqli_stmt->free_result();
    $mysqli_stmt->close();     //关闭预编译指令$mysqli->close();
....
?>

<?php
....
    echo"has rows".$res->num_rows()."and columns".field_count();
 
    echo"<table border=‘1‘><tr>";
    //从表结构表中获取对应列。foreach($field=$res->fetch_field();)
    {
         echo"<th>{$field->name}</th>";
    }
    </tr>
     while($row=$res->fetch_row())
    {
        echo"<tr>";
        foreach($rowas$key=>$value)
        {
            echo"<td>{$value}</td>"
        }
        echo"</tr>";
    }
    echo"</table>";
    $res->result();
....
?>