Linux – PHP + Mysql

要如何透過 PHP 來讀取 Mysql (MariaDB) 的資料.

測試環境為 Ubuntu 16.04 x86_64 + Apache2 虛擬機 (IP: 172.16.15.130) 請參考 https://benjr.tw/95861 , web server 為 Nginx + MariaDB Database 請參考 https://benjr.tw/96633 .

RHEL / CentOS 7 Apache + MaraiDB 請參考 https://benjr.tw/323 , Nginx (Web server) 請參考 https://benjr.tw/95761 , Nginx (Web server) + PHP-FPM 請參考 https://benjr.tw/95767

先建立資料庫 user1Data 與 使用者 user1.

root@ubuntu:~# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 62
Server version: 10.0.31-MariaDB-0ubuntu0.16.04.2 Ubuntu 16.04

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database user1Data;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> CREATE USER user1@localhost IDENTIFIED BY '111111';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> grant all privileges on user1Data.* to user1@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> exit;
Bye

使用新建立的使用者 user1 登入後建立 employee 資料表格,並寫入幾行資料.

root@ubuntu:~# mysql -u user1 -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 63
Server version: 10.0.31-MariaDB-0ubuntu0.16.04.2 Ubuntu 16.04

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| user1Data          |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> use user1Data;
Database changed
MariaDB [user1Data]> create table employee (Name char(20),Dept char(20),jobTitle char(20));
Query OK, 0 rows affected (0.04 sec)

MariaDB [user1Data]> DESCRIBE employee;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| Name     | char(20) | YES  |     | NULL    |       |
| Dept     | char(20) | YES  |     | NULL    |       |
| jobTitle | char(20) | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.02 sec)

MariaDB [user1Data]> INSERT INTO employee VALUES ('Ben','Testing','Engineer');
Query OK, 1 row affected (0.00 sec)

MariaDB [user1Data]> INSERT INTO employee VALUES ('Afa','Power','Engineer');
Query OK, 1 row affected (0.01 sec)

MariaDB [user1Data]> SELECT * from employee;
+------+---------+----------+
| Name | Dept    | jobTitle |
+------+---------+----------+
| Ben  | Testing | Engineer |
| Afa  | Power   | Engineer |
+------+---------+----------+
2 rows in set (0.00 sec)

MariaDB [user1Data]> exit;
Bye

PHP 語法

root@ubuntu:~# cd /var/www/html
root@ubuntu:/var/www/html# vi query.php
<?php
    $dbhost = 'localhost';
    $dbuser = 'user1';
    $dbpass = '111111';
    $dbname = 'user1Data';
    $dbc = mysqli_connect($dbhost, $dbuser, $dbpass ,$dbname) or die('Error connecting to MySQL server');
    $query = "SELECT * from user1Data.employee";
    $result = mysqli_query($dbc, $query) or die('MySQL query error');
    echo 'TEST Results' . '<br />';
    while ($row = mysqli_fetch_array($result)) {
    echo $row['Name'] . ' ' . $row['Dept'] . ' ' . $row['jobTitle'] . '<br />';
    }
    echo 'Test Completed';    
mysqli_close($dbc);
?>

主要透過下面幾個與 SQL 函數庫,來獲取資料庫資料.

1. mysqli_connect 使用參數:
   $dbhost – 如果你的 PHP 與 資料庫 在同一台伺服器時,可以直接使用 localhost ,不同時需要使用 IP 或是 Hostname (需注意使用者權限問題).
   $dbuser – 資料庫使用者名稱.
   $dbpass – 資料庫使用者密碼.
   $dbname – 資料庫名稱.
   or die() – or 的用法跟 Linux bash script 的 cmd1 || cmd2 一樣 ,若 cmd1 執行正確且無錯誤,則 cmd2 不執行 ,反之若 cmd1 執行完畢且為錯誤,則開始執行 cmd2.
   會有下列的兩種情況.
   若 mysqli_connect($dbhost, $dbuser, $dbpass ,$dbname) 執行正確且無錯誤就結束.
   若 mysqli_connect($dbhost, $dbuser, $dbpass ,$dbname) 執行失敗就執行 die(‘Error connecting to MySQL server’) (die 等同 exit – http://php.net/manual/en/aliases.php ) – 顯示訊息並跳出程式.
2. mysqli_query 使用參數:
   $dbc – 透過 mysqli_connect 回傳的 $dbc 值.
   $query – SQL 語法 ,我用了查詢 “SELECT * from user1Data.employee” .
3. mysqli_fetch_array 使用參數:
   需要透過 mysqli_query 回傳的 $result 值.
4. mysqli_close 使用參數:
   需要透過 mysqli_connect 回傳的 $dbc 值.

執行結果: