透過PHP連接MySQL資料庫實作留言板功能

PHP與HTML搭配可以逕而存取資料庫內容以及進行改寫動作,例如你想要實作一個留言區,當訪客想針對你的內容進行留言時,將他的訊息以及留言寫入資料庫中。

環境:

安裝XAMPP(https://www.apachefriends.org/download.html)

安裝JetBrains PhpStorm(https://www.jetbrains.com/phpstorm/)

安裝JetBrains DataGrip(https://www.jetbrains.com/datagrip/)

(注:這邊是使用JetBrains PhpStorm作為編輯器使用編輯PHP檔案)

(注:這邊是使用JetBrains DataGrip作為MySQL的操作)

資料庫連接流程:

  1. 透過XAMPP啟動MySQL連結
  2. 透過XAMPP開啟終端機
  3. 輸入 mysql -h 127.0.0.1 -uroot -p
  4. 並且輸入密碼 (一開始SQL是沒有設定密碼的)

如果遇到無法透過終端機連接並得到此ERROR 1045 (28000): Access denied for user ‘frede’@’localhost’ (using password: NO)

請於終端機輸入
Login using mysql client=>
mysql -h 127.0.0.1 -uroot -p
Enter password: ****(密碼空白)
如果連接成功則會看到下列訊息

[email protected] c:\xampp
# mysql -h 127.0.0.1 -uroot -p
Enter password: ****
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 40
Server version: 10.4.22-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, 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 |
| mysql              |
| performance_schema |
| phpmyadmin         |
| publications       |
| test               |
+--------------------+
6 rows in set (0.001 sec)
XAMPP上的Shell
透過終端機登陸MySQL

連接MySQL資料庫透過DataGrip

DataGrip是一款數據庫客戶端工具,DataGrip,是JetBrains公司也就是出品Intellij IDEA的公司。 DataGrip是一款數據庫管理客戶端工具,方便連接到數據庫服務器,執行sql、創建表、創建索引以及導出數據等。https://www.jetbrains.com/datagrip/download。安裝過程也很簡單,雙擊安裝,下一步,中間會讓你選擇主題,本人選擇的是經典的Darcula,安裝完成後,啟動,界面如下

為什麼選擇DataGrip ?DataGrip可輕鬆處理數據庫 解決專業 SQL 開發者的特定需求量身定做的全新數據庫 IDE。

進到DataGrip輸入下列訊息即可登錄XAMPP上所啟動的MariaDB。

目前DataGrip支援的資料庫

連接上資料庫後,可以透過你熟悉的SQL指令或者是透過介面功能來完成,非常容易上手。這邊我創一個表格,裡面內含四個欄位分別是姓名、信箱、圖標、留言內容。

連接PHP連接資料庫

因為PHP內建mysqli功能,所以可以直接進行讀寫。

下兩個程式碼即可以對在地端的資料庫進行連接。連接時需要提供下列訊息給予此函數。

$hn : 先設定資料庫資訊,主機通常都用本機。
$db : 以root管理者帳號進入資料庫。
$pw : root的資料庫密碼。
$un : 登入後要使用的資料庫(名稱)。

<?php
 require_once 'login.php';
 $conn = new mysqli($hn, $un, $pw, $db);
 if ($conn->connect_error) die("Fatal Error");
?>
<?php
    $hn = 'localhost';
    $db = 'register';
    $un = 'root';
    $pw = 'root';
    $con = new mysqli($hn, $un, $pw, $db);
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <link rel="stylesheet" href="styles.css" />
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta http-equiv="X-UA-Compatible" content="ie=edge" />
    <title>Frederic Static Template</title>
</head>
<body>

<?php
    $hn = 'localhost';
    $db = 'register';
    $un = 'root';
    $pw = 'root';
    $conn = new mysqli($hn, $un, $pw, $db);
    if ($conn->connect_error) die("Fatal Error");
    if (isset($_POST['name']) &&
        isset($_POST['email']) &&
        isset($_POST['avatar']) &&
        isset($_POST['comment']))
    {
        $Name = get_post($conn, 'name');
        $Email = get_post($conn, 'email');
        $Avatar = get_post($conn, 'avatar');
        $Comment = get_post($conn, 'comment');
        $query2 = "INSERT INTO commentlist VALUES" . "('$Name', '$Email', '$Avatar', '$Comment')";
        $result = $conn->query($query2);
        if (!$result) echo "INSERT failed<br><br>";
    }


    echo <<<_end
    <form action="register.php" method="post">
      <lable> UserName </lable><input type="text" id="name" name="name">
      <lable> Email </lable><input type="text" id="email" name="email">
      <lable> Avatar </lable><input type="text" id="avatar" name="avatar">
      <lable> Comment  </lable><input type="text" id="comment" name="comment">
      <input type="submit" id="btn" value="Register">
    </form>
_end;
$query = "SELECT * FROM commentlist";
$result = $conn->query($query);
if (!$result) die ("Database access failed");
$rows = $result->num_rows;
for ($j = 0 ; $j < $rows ; ++$j)
{
    $row = $result->fetch_array(MYSQLI_NUM);
    $r0 = htmlspecialchars($row[0]);
    $r1 = htmlspecialchars($row[1]);
    $r2 = htmlspecialchars($row[2]);
    $r3 = htmlspecialchars($row[3]);
    echo <<<_END
 <pre>
 UserName $r0
 Email $r1
 Avatar $r2
 Comment $r3
 </pre>
_END;
}
$result->close();
$conn->close();
function get_post($conn, $var)
{
    return $conn->real_escape_string($_POST[$var]);
}
    ?>
<h1><span>General Information about Amsterdam</span></h1>
<p>
    Amsterdam, capital of the Netherlands! These days the city has a
    population of just over 790.000 inhabitants and is the largest city in the
    country. Amsterdam is located in the province ‘Noord-Holland’, situated in
    the west. It is one of the most popular destinations in Europe, receiving
    more than 4.5 million tourists annually.
</p>
<h1>
    List of tourist attractions in Amsterdam
</h1>
<ul>
    <li><a href="https://keukenhof.nl/nl/">Keukenhof</a></li>
    <p>
        Keukenhof is located in the province of South Holland, south of Haarlem
        and southwest of Amsterdam in the area called the "Dune and Bulb Region"
        (Duin- en Bollenstreek). It is accessible by bus from Haarlem and Leiden
        train stations as well as Schiphol. Though its grounds are open
        year-round for private affairs and festivals, Keukenhof is only open to
        the general public for a world-renowned 8 week tulip display from
        mid-March to mid-May,[5] with peak viewing arriving near mid-April,
        depending on growing season weather, which varies annually. In 2019, 1.5
        million people visited Keukenhof,[6] equivalent to 26,000 visitors per
        day.[7] By comparison, the Rijksmuseum receives an average of 8,000
        visitors per day, the Efteling receives 14,000.
    </p>
    <li><a href="https://www.iamsterdam.com/en/see-and-do/things-to-do/attractions-and-sights/places-of-interest/magere-brug-skinny-bridge">Magere Brug</a></li>
    <p>
        One of Amsterdam’s many bridges is the beautiful Magere Brug, known to English speakers at the Skinny Bridge. This wooden drawbridge was once so narrow that it was hard for two pedestrians to pass each other. To cope with increasing traffic on the Amstel, a wider bridge replaced the narrow original in 1871.
    </p>
    <img
        src="https://upload.wikimedia.org/wikipedia/commons/thumb/a/aa/Amsterdam_Magere_Brug_3.jpg/1280px-Amsterdam_Magere_Brug_3.jpg"
        alt="NL"
        width="1680"
        height="690"
    />
</ul>
</body>
</html>

結果

每當使用者增加新的評論此頁面會重載,並將新的評論顯示於下方。

參考文獻

MySQL :: MySQL Documentation

mysqli_connect()

mysqli_select_db()

INSERT

SELECT

mysqli_result

mysqli_real_escape_string()

更多相關PHP連結