在PHP+MySQL中
除了需要熟悉PHP和MySQL個別的語法以外
我認為關於GET與POST的運用也很重要
因此接續上篇新增資料庫與資料表後要學習的是SELECT & DELETE

資料庫活用=>把在資料庫裡的東西都列出來
會用到mysqli_num_rows()與mysqli_fetch_assoc()
在stackoverlow中有人整理了幾個很詳細的用法：https://stackoverflow.com/questions/18577774/differences-in-mysqli-fetch-functions

SELECT

  1. 新增一個list.php

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    <?php 
    $conn=require_once('config.php');

        $sql="SELECT * FROM tb3";
        $result = mysqli_query($conn,$sql);
        
        if (mysqli_num_rows($result) > 0) {
                // output data of each row
            while($row = mysqli_fetch_assoc($result)) {
                
            echo "col1: " . $row["tb3_col1"]. " - col2: " . $row["tb3_col2"] . "<br>";
            }
            
    mysqli_close($conn); //最後記得把和資料庫的連線關起來
        }else{
                echo "Null";
            }

        ?>

    且另外在db-tb3裡新增一些資料

    最後傳回這些

或是我們可以用表格的形式來展現

php標籤是可以混著html使用的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<table border="1">
        <thead>
            <tr>
                <td>col1</td>
                <td>col2</td>
            </tr>
        </thead>
        <tbody>
        <?php
            $conn=require_once('config.php');
            $sql="SELECT * FROM tb3";
            $result = mysqli_query($conn,$sql);
            if (mysqli_num_rows($result) > 0) {
            while($row = mysqli_fetch_assoc($result)) {
            ?>
                <tr>
                    <td><?php echo $row['tb3_col1']?></td>
                    <td><?php echo $row['tb3_col2']?></td>
                </tr>
            <?php
            }
                mysqli_close($conn);
            }else{?>
                <tr>
                    <td colspan="2"><?php echo "null" ?></td>
                    
                </tr>
            <?php
                 }
            ?>
            </tbody>
</table>

另外如果要同時放兩個(無表格版與表格版)
記得前面先別關mysqli

DELETE

在table裡面新增一個欄位叫做delete，該欄位中回傳id(unique)，並且透過表單的方式進行刪除。 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
<?php 
    $conn=require_once('config.php');
    
        $sql="SELECT * FROM tb3";
        $result = mysqli_query($conn,$sql);
        
        if (mysqli_num_rows($result) > 0) {
                // output data of each row
            while($row = mysqli_fetch_assoc($result)) {
                
            echo "col1: " . $row["tb3_col1"]. " - col2: " . $row["tb3_col2"] . "<br>";
            }
            
    //mysqli_close($conn); //最後記得把和資料庫的連線關起來
        }else{
                echo "Null";
            }

        ?>
<table border="1">
        <thead>
            <tr>
                <td>col1</td>
                <td>col2</td>
                <td>delete</td>
            </tr>
        </thead>
        <tbody>
        <?php
            $sql="SELECT * FROM tb3";
            $result = mysqli_query($conn,$sql);
            if (mysqli_num_rows($result) > 0) {
            while($row = mysqli_fetch_assoc($result)) {
            ?>
                <tr>
                    <td><?php echo $row['tb3_col1']?></td>
                    <td><?php echo $row['tb3_col2']?></td>
                    <td><form method="post">
                        <input type="hidden" name="id" value="<?php echo $row['tb3_col1']?>">
                        <input type="submit" value="delete" name="delete_btn">
                        </form></td>
                </tr>
            <?php
            }
                
            }else{?>
                <tr>
                    <td colspan="3"><?php echo "null"; ?></td>
                    
                </tr>
            <?php
                 }
            if(isset($_POST['delete_btn'])){
                $sql2="DELETE FROM tb3 WHERE tb3.tb3_col1='".$_POST["id"]."'";
                if(mysqli_query($conn,$sql2)){
                    echo "delete!";
                    header("Refresh:0");
                }else{
                    echo mysqli_errno($conn);
                }
                
        }
            mysqli_close($conn);
            ?>
            </tbody>
</table>

UPDATE也是同樣的思路，這些都可以寫得更模組化一點