php操作mysql示例备忘录

1. 一般的insert

$query = "INSERT INTO Profile (userName) VALUES ('{$userName}')";  

$this->db->query($query);  

       

$userId = sprintf("%d", $this->db->insert_id);  

$this->db->commit();  

 

2. 数据库中如果有，则更新，没有则插入

$query = "INSERT INTO BookRead (userId, bookId, count) VALUES ($_userId, $_bookId, 1) ON DUPLICATE KEY UPDATE count = count + 1";  

$this->db->query($query);  

$this->db->commit();  

 

3. 更新

$query = "UPDATE Profile Set deviceToken='{$_token}' WHERE userId=$_userId";  

$this->db->query($query);  

$this->db->commit();  

 

4.查询1，操作需要操作的字段

$stmt = $this->db->prepare('SELECT userId, passWord FROM Profile WHERE userName=?');  

$stmt->bind_param("s", $_userName);  

$rs = $stmt->execute();  

$stmt->bind_result($_userId, $_passWord);  

while ($stmt->fetch()) {  

 break;  

}  

$stmt->close();  

 

查询2，返回查询结果数组

$query = "SELECT name, points FROM Profile WHERE 1 ORDER BY points DESC LIMIT $_from, $_to";  

              

if ($result = $this->db->query($query)) {  

          

      

    while ($row = $result->fetch_row()) {  

                      

     $ret = array (  

                "name" => $row[0],  

                "points" => $row[1],  

        );  

    }  

    $result->close();  

          

}