PDO数据库操作类——更新数据的实现

使用PDO数据库抽象层往数据表中更新、插入或者删除数据,都是通过PDO的exec()方法,如果你熟悉Mysql数据库的sql语句,那么理解起来就更轻松了,你可以把它当作mysql的query()来使用,而且exec()执行完毕还会返回影响的数据行总数。

例如存在表mo2g_test

id name
1 PDO
2 mPHP

我们要更新mo2g_test中id为1的数据,把他的name值更改为mysql,那么mysql的使用方法如下

1

2

3

$strSql = "update mo2g_test set name = 'mysql' where id = 1";

mysql_query($strSql);

$reslut = mysql_affected_rows();//返回影响了多少行数据

PDO的exec()更简单

1

2

$strSql "update mo2g_test set name = 'mysql' where id = 1";

$reslut $pdo->exec($strSql);//返回影响了多少行数据

其实更新数据的sql都有固定的格式:

1)更新一个属性

1

update 数据表 set 属性名 = '更新内容' where 更新条件

2)更新多个属性

1

update 数据表 set 属性名1 = '更新内容',属性名2 = '更新内容',属性名n = '更新内容' where 更新条件

所以,我根据这一特征封装了PDO类的update方法

1

function update($table,$arrData,$condition) {}

1)$table为数据表的名称

2)$arrData为要更新的数据

3)$condition为更新条件

这里我们使用封装好的update演示一下如何更新上述的数据:

1

2

3

4

$talbe 'mo2g_test';

$arrData array('name'=>'mysql');

$condition 'id = 1';

$pdo->update($table,$arrData,$condition);

update函数的实现代码如下

1

2

3

4

5

6

7

8

9

10

11

12

13

14

public function update($table,$arrData,$condition) {

    $flag = 1;

    foreach($arrData as $key => $value) {

        if($flag) {

            $data "$key = '$value'";

            $flag = 0;

        else $data .= ",$key = '$value'";

    }

    $strSql "update $table set $data where $condition";

    if($this->result = $this->db->exec($strSql)) {

        return true;

    }

    return false;

}

 

<?php
 2          $data = array(array('id' => 1, 'val' => 2), array('id' => 2, 'val' => 3));
 3          $update_sql = 'UPDATE tbl_test SET val = CASE id';
 4          $params = array();
 5          $params[":ids"] = $ids;
 6          $in_arr = array();
 7 
 8          foreach($data as $key => $item) {
 9                  $update_sql .= "WHEN :id_" . $key . "THEN :val_" . $key . " ";
10                  $params[":id_" . $key] = $item['id'];
11                  $params[":val_" . $key] = $item['val'];
12                  $params[":ids_" . $key] = $item['id'];
13                  array_push($in_arr, ":id_" . $key);
14          }
15          $update_sql .= "END WHERE id IN (" . implode(',' $in_arr) . ")";
16          TEST::execute($update_sql, $params);//此处会调用bindParam绑定参数
?>
$pdo->select('*','mo2g_test','id=1');//获取id为1的数据
$arrData = $pdo->fetch();//获取找到的数据
print_r($arrData);//输出找到的数据
 
$pdo->select('*','mo2g_test','id>1','id');//根据id从大到小进行排序
$pdo->select('*','mo2g_test','id>1','id asc');//根据id从小到大进行排序
$pdo->select('*','mo2g_test','id>1',array('field'=>'id','order'=>'asc'));//根据id从小到大进行排序
$pdo->select('*','mo2g_test','id>1','id','limit 1');//根据id从大到小进行排序,并只获取1条数据
$arrData = $pdo->fetch_all();//获取找到的所有数据
print_r($arrData);//输出找到的数据
 
//推荐使用的查询方式
public function query($strSql) {
    $this->result = $this->db->query($strSql);
    return $this;
}
 
/*
$select:要获取的属性
$table:数据表名称
$condition:获取条件
$order:数据排序
$limit:获取几条数据
*/
public function select($select,$table,$condition = '',$order = array(), $limit = '') {
    if( is_array($order) && isset($order['order']) && ( $order['order'] == 'desc' || $order['order'] == 'asc' ) && isset($order['field']) ) {
        $order = "order by $order[field] $order[order]";
    } elseif( is_string($order) ) {
        $order = "order by {$order}";
    } else $order = '';
    $this->sql = $strSql = $condition == '' ?
        "select $select 
        from $table 
        $order 
        $limit"    
        :  
        "select $select 
        from $table 
        where $condition 
        $order 
        $limit";
    if($this->result = $this->db->query($strSql)) {
        return $this;
    }
    return $this;
}
//获取所有查询到的数据
public function fetch_all() {
    $arrData = array();
    if( $this->result ) {
        while($row = $this->result->fetch(PDO::FETCH_ASSOC) ) {
            $arrData[] = $row;
        }
    }
    return $arrData;
}
//获取一条查询到的数据
public function fetch() {
    $arrData = array();
    if( $this->result ) {
        $arrData = $this->result->fetch(PDO::FETCH_ASSOC);
    }
    return $arrData;
}
public function delete($table,$condition) {
    $strSql = "delete from $table where $condition";
    if($this->result = $this->db->exec($strSql)){
        return $this;
    }
    return false;
}

 

使用PHP的PDO扩展进行批量更新操作

<?php
     $data = array(array('id' => 1, 'val' => 2), array('id' => 2, 'val' => 3));
     $update_sql = 'UPDATE tbl_test SET val = CASE id';
     $params = array();
     $params[":ids"] = $ids;
     $in_arr = array();

     foreach($data as $key => $item) {
             $update_sql .= "WHEN :id_" . $key . "THEN :val_" . $key . " ";
             $params[":id_" . $key] = $item['id'];
             $params[":val_" . $key] = $item['val'];
             $params[":ids_" . $key] = $item['id'];
             array_push($in_arr, ":id_" . $key);
     }
     $update_sql .= "END WHERE id IN (" . implode(',' $in_arr) . ")";
     TEST::execute($update_sql, $params);//此处会调用bindParam绑定参数
?>

 

发布了430 篇原创文章 · 获赞 415 · 访问量 925万+
展开阅读全文

PHP PDO:如何显示受影响的UPDATE行以及带有过程的SELECT结果?

01-30

<div class="post-text" itemprop="text"> <p>I have a procedure with a mix of <code>SELECT</code> commands and <code>UPDATE</code> commands. Using PHP and PDO, for each query, I want to see either the results or the affected rows; however, this doesn't work for <code>UPDATE</code>, <code>INSERT</code>, or <code>DELETE</code> queries (unless they are the only query in the procedure). I am able to retrieve the results from the <code>SELECT</code> commands using <code>$stmt->nextRowset();</code> however, I cannot retrieve the total number of records that have been updated, deleted, or inserted. How can I retrieve the affected rows?</p> <p>Here is my PHP code:</p> <pre><code>$stmt = $connection->query($query); do { // updates the counter for each procedure query $j++; // gets the total rows from each rowset $totalRows = $stmt->rowCount(); $data = getColumnData($connection, $stmt); // checks if any data was returned if (!$data) { $rowText = $totalRows == 1 ? "row was" : "rows were"; $queryData .= $totalRows != 0 ? "<p><label>Procedure Query {$j}</label></p><p class='success'>A total of {$totalRows} {$rowText} affected!</p>" :"<p><label>Procedure Query {$j}</label></p><p><i class='fa fa-exclamation-triangle'></i> No results were returned! Check the query outside the procedure.</p>"; // displays the rows affected for other queries } else { $queryData .= "<p><label>Procedure Query {$j}</label></p><p class='success'>A total of {$totalRows} rows were returned!</p>{$data}"; } } while ($stmt->nextRowset()); </code></pre> <p>Here is a sample procedure that I quickly wrote up to demonstrate what the procedure looks like:</p> <pre><code>DELIMITER $$ CREATE PROCEDURE get_orders(IN vDate Date) BEGIN SELECT * FROM `order`; UPDATE `order` SET `order_date` = vDate; SELECT * FROM `order`; END$$ DELIMITER ; CALL get_orders(Curdate() - 1); </code></pre> <p>This is the output but as you can see there is a missing result for the <code>UPDATE</code> between queries one and two: <a href="https://i.stack.imgur.com/58vof.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/58vof.png" alt="enter image description here"></a></p> </div> 问答

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 编程工作室 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览