next up previous contents
Next: Delete 刪除紀錄 Up: Query 紀錄詢問 Previous: Where 條件   Contents

Update 變更紀錄

  1. 先列出資料表中的紀錄。
    [root@kvm8 html]# mysql -uroot -p123qwe \
    -e "use dyw; select * from dyw_tbl;"
    +--------+-----------+------------+-----------------+
    | dyw_id | dyw_title | dyw_author | submission_date |
    +--------+-----------+------------+-----------------+
    |      1 | PHP       | david      | 2014-12-24      |
    |      3 | MySQL     | dywang     | 2014-12-12      |
    |      4 | Linux     | linda      | 2014-12-24      |
    +--------+-----------+------------+-----------------+
    
  2. 變更紀錄 dyw_id=1dyw_title 為 python。
    [root@kvm8 html]# mysql -uroot -p123qwe \
    -e "use dyw; update dyw_tbl set dyw_title='python' where dyw_id=1;"
    
  3. 再列出資料表中的紀錄,紀錄 dyw_id=1dyw_title 已更新為 python。
    [root@kvm8 html]# mysql -uroot -p123qwe \
    -e "use dyw; select * from dyw_tbl;"
    +--------+-----------+------------+-----------------+
    | dyw_id | dyw_title | dyw_author | submission_date |
    +--------+-----------+------------+-----------------+
    |      1 | python    | david      | 2014-12-24      |
    |      3 | MySQL     | dywang     | 2014-12-12      |
    |      4 | Linux     | linda      | 2014-12-24      |
    +--------+-----------+------------+-----------------+
    
  4. 一次更新一筆紀錄的 2 個欄位。
    [root@kvm8 html]# mysql -uroot -p123qwe \
    -e "use dyw; update dyw_tbl set dyw_title='c++',dyw_author='peter' \
    where dyw_id=1;"
    
  5. 再列出資料表中的紀錄,紀錄 dyw_id=1dyw_titledyw_author 2 個欄位都已更新。
    [root@kvm8 html]# mysql -uroot -p123qwe \
    -e "use dyw; select * from dyw_tbl;"
    +--------+-----------+------------+-----------------+
    | dyw_id | dyw_title | dyw_author | submission_date |
    +--------+-----------+------------+-----------------+
    |      1 | c++       | peter      | 2014-12-24      |
    |      3 | MySQL     | dywang     | 2014-12-12      |
    |      4 | Linux     | linda      | 2014-12-24      |
    +--------+-----------+------------+-----------------+
    
  6. 只要更改原 php 程式的查詢變數 $sql,改為 UPDATE 命令即可。
    [root@kvm8 html]# vim updaterecord.php 
    [root@kvm8 html]# cat updaterecord.php 
    <?php
    $dbhost = 'localhost:3306';
    $dbuser = 'root';
    $dbpass = '123qwe';
    $conn = mysql_connect($dbhost, $dbuser, $dbpass);
    if(! $conn )
    {
      die('Could not connect: ' . mysql_error());
    }
    $sql = 'UPDATE dywphp_tbl 
            SET dywphp_title="python"
            WHERE dywphp_id=2';
    
    mysql_select_db('dywphp');
    $retval = mysql_query( $sql, $conn );
    if(! $retval )
    {
      die('Could not get data: ' . mysql_error());
    }
    while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
    {
        echo "Dywphp ID :{$row['dywphp_id']}  <br> ".
             "Title: {$row['dywphp_title']} <br> ".
             "Author: {$row['dywphp_author']} <br> ".
             "Submission Date : {$row['submission_date']} <br> ".
             "--------------------------------<br>";
    } 
    mysql_free_result($retval);
    echo "Fetched data successfully\n";
    mysql_close($conn);
    ?>
    
  7. 開啟瀏覽器,連上網址 http://kvm8.deyu.wang/updaterecord.php,網頁上回傳 'Fetched data sucessfully'。
  8. 再開啟瀏覽器,連上網址 http://kvm8.deyu.wang/selectrecord.php,網頁上列出紀錄 dywphp_id=2 的 title 已變更為 python。
    Image updaterecord



2018-06-19