PHP と MySQL を使用して検索システムをセットアップする

# This login command assumes that the
# password is empty and the user is "root"
mysql -u root -p

CREATE database fruit_db;

Query OK, 1 row affected (0.001 sec)

CREATE TABLE fruit
(id INT NOT NULL AUTO_INCREMENT,
 name VARCHAR(20) NOT NULL,
 color VARCHAR(20) NOT NULL,
 PRIMARY KEY (id))
 ENGINE = InnoDB;

Query OK, 0 rows affected (0.028 sec)

DESC fruit;

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
| color | varchar(20) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

INSERT INTO fruit (id, name, color) VALUES (NULL, 'Banana', 'Yellow'), (NULL, 'Pineapple', 'Green')

Query OK, 2 rows affected (0.330 sec)
Records: 2  Duplicates: 0  Warnings: 0

SELECT * FROM fruit;

+----+-----------+--------+
| id | name      | color  |
+----+-----------+--------+
|  1 | Banana    | Yellow |
|  2 | Pineapple | Green  |
+----+-----------+--------+

<main>
	<form action="searchdb.php" method="post">
		<input
			type="text"
			placeholder="Enter your search term"
			name="search"
			required>
		<button type="submit" name="submit">Search</button>
	</form>
</main>

* {
	margin: 0;
	padding: 0;
	box-sizing: border-box;
}

body {
	display: grid;
	align-items: center;
	place-items: center;
	height: 100vh;
}

main {
	width: 60%;
	border: 2px solid #1560bd;
	padding: 2em;
	display: flex;
	justify-content: center;
}

input,
button {
	padding: 0.2em;
}

<?php
    if (isset($_POST['submit'])) {
        // Connect to the database
        $connection_string = new mysqli("localhost", "root", "", "fruit_db");
        
        // Escape the search string and trim
        // all whitespace
        $searchString = mysqli_real_escape_string($connection_string, trim(htmlentities($_POST['search'])));

        // If there is a connection error, notify
        // the user, and Kill the script.
        if ($connection_string->connect_error) {
            echo "Failed to connect to Database";
            exit();
        }

        // Check for empty strings and non-alphanumeric
        // characters.
        // Also, check if the string length is less than
        // three. If any of the checks returns "true",
        // return "Invalid search string", and
        // kill the script.
        if ($searchString === "" || !ctype_alnum($searchString) || $searchString < 3) {
            echo "Invalid search string";
            exit();
        }

        // We are using a prepared statement with the
        // search functionality to prevent SQL injection.
        // So, we need to prepend and append the search
        // string with percent signs
        $searchString = "%$searchString%";

        // The prepared statement
        $sql = "SELECT * FROM fruit WHERE name LIKE ?";

        // Prepare, bind, and execute the query
        $prepared_stmt = $connection_string->prepare($sql);
        $prepared_stmt->bind_param('s', $searchString);
        $prepared_stmt->execute();

        // Fetch the result
        $result = $prepared_stmt->get_result();

        if ($result->num_rows === 0) {
            // No match found
            echo "No match found";
            // Kill the script
            exit();

        } else {
            // Process the result(s)
            while ($row = $result->fetch_assoc()) {
                echo "<b>Fruit Name</b>: ". $row['name'] . "<br />";
                echo "<b>Fruit Color</b>: ". $row['color'] . "<br />";

            } // end of while loop
        } // end of if($result->num_rows)

    } else { // The user accessed the script directly

        // Tell them nicely and kill the script.
        echo "That is not allowed!";
        exit();
    }
?>