next up previous contents
Next: Join 結合查詢 Up: Clause 紀錄詢問條件 Previous: Sorting 排序   Contents   DYWANG_HOME

PHP 排序

  1. 先在資料庫 dywphp 的 tblphp 資料表新增兩筆紀錄。
    [root@kvm8 ~]# mycli -S /var/lib/mysql/mysql.sock -uroot -p123qwe 
    mariadb 10.3.11-MariaDB
    mycli 1.20.1
    Chat: https://gitter.im/dbcli/mycli
    Mail: https://groups.google.com/forum/#!forum/mycli-users
    Home: http://mycli.net
    Thanks to the contributor - jbruno
    mariadb root@localhost:(none)> use dywphp;                                      
    You are now connected to database "dywphp" as user "root"
    Time: 0.001s
    mariadb root@localhost:dywphp> insert into tblphp (title,author,submission_date)
                                    values("c++","dywang","2020-01-11");            
    Query OK, 1 row affected
    Time: 0.010s
    mariadb root@localhost:dywphp> insert into tblphp (title,author,submission_date)
                                    values("c++","peter","2020-01-12");             
    Query OK, 1 row affected
    Time: 0.003s
    mariadb root@localhost:dywphp> quit;                                            
    Goodbye!
    
  2. 撰寫 sorting.php,查詢變數 $sql 加入 ORDER BY author DESC 條件。
    [root@kvm8 ~]# vim /var/www/html/sorting.php 
    [root@kvm8 ~]# cat /var/www/html/sorting.php 
    <?php
    $dbhost = 'localhost:3306';
    $dbuser = 'root';
    $dbpass = '123qwe';
    $conn = mysqli_connect($dbhost, $dbuser, $dbpass) 
    	or die(mysqli_connect_error().PHP_EOL);
    mysqli_select_db( $conn, 'dywphp' )
    	or die('Error: '.mysqli_error($conn).PHP_EOL);
    $sql = "select id, title, author,submission_date from tblphp order by author desc";
    $retval = mysqli_query( $conn, $sql )
    	or die('Error: '.mysqli_error($conn).PHP_EOL);
    while($row = mysqli_fetch_array($retval, MYSQLI_ASSOC)) {
        echo "Dywphp ID :{$row['id']}  <br> ".
             "Title: {$row['title']} <br> ".
             "Author: {$row['author']} <br> ".
             "Submission Date : {$row['submission_date']} <br> ".
             "--------------------------------<br>";
    }
    mysqli_free_result($retval);
    echo "Fetched data successfully\n";
    mysqli_close($conn); 
    ?>
    
  3. curl 連上網址 http://kvm8.deyu.wang/sorting.php,管線處理將 html 換行標籤 <br> 改成 shell 的換行符號 \n,網頁上以 author 降序列出資料表 tblphp 所有紀錄。
    Dywphp ID :4  
    Title: c++ 
    Author: peter 
    Submission Date : 2020-01-12 
    --------------------------------
    Dywphp ID :2  
    Title: python 
    Author: dywang 
    Submission Date : 2020-01-29 
    --------------------------------
    Dywphp ID :3  
    Title: c++ 
    Author: dywang 
    Submission Date : 2020-01-11 
    --------------------------------
    Fetched data successfully
    



De-Yu Wang 2020-04-07