How to query data within date with php+mysql-Mysql Tutorial-php.cn

How to query data within date with php+mysql

WBOY

Release： 2023-05-30 20:13:16

forward

971 people have browsed it

The first step is to create a database table and insert some data. Let's create a table named "orders", including the following fields:

id: self-increment ID
date: place an order Date
#amount: Order amount

Use the following SQL statement to create the table:

CREATE TABLE orders (
id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
date DATE,
amount DECIMAL(10,2)
);

Copy after login

Next, we can insert some To test the data, use the following SQL statement:

INSERT INTO orders (date, amount) VALUES
(&#39;2021-01-01&#39;, 100),
(&#39;2021-01-02&#39;, 50),
(&#39;2021-01-03&#39;, 200),
(&#39;2021-01-04&#39;, 150),
(&#39;2021-01-05&#39;, 75);

Copy after login

Now we have a table containing order data.

The next step is to write PHP code to query the data within the date range. We will create a new file named "search.php" and then use the following code:

<?php
//连接数据库，以下为参数示例
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
$conn = new mysqli($servername, $username, $password, $dbname);

//检查是否连接成功
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

//检查搜索日期的输入
if (isset($_POST[&#39;from_date&#39;]) && isset($_POST[&#39;to_date&#39;])) {
    //将输入的日期格式化为MySQL日期格式
    $from_date = date(&#39;Y-m-d&#39;, strtotime($_POST[&#39;from_date&#39;]));
    $to_date = date(&#39;Y-m-d&#39;, strtotime($_POST[&#39;to_date&#39;]));

    //查询日期区间内的订单数据
    $sql = "SELECT * FROM orders WHERE date >= &#39;$from_date&#39; AND date <= &#39;$to_date&#39;";
    $result = $conn->query($sql);

    //输出查询结果
    if ($result->num_rows > 0) {
        echo "<table><tr><th>ID</th><th>Date</th><th>Amount</th></tr>";
        while($row = $result->fetch_assoc()) {
            echo "<tr><td>".$row["id"]."</td><td>".$row["date"]."</td><td>".$row["amount"]."</td></tr>";
        }
        echo "</table>";
    } else {
        echo "0 results";
    }
}

//关闭连接
$conn->close();
?>

Copy after login

This code connects to the database and then checks the search date range entered by the user. If a date range is entered, the script will format the entered date into MySQL date format, then query the order data and output the results in a table. If there are no results, "0 results" will be output.

Now, we need to create an HTML form that accepts user input. Here is the complete code for the "search.php" file:

<!DOCTYPE html>
<html>
<head>
    <title>Search Orders By Date Range</title>
</head>
<body>
    <h2>Search Orders By Date Range</h2>
    <form method="POST" action="search.php">
        From: <input type="text" name="from_date" placeholder="YYYY-MM-DD">
        To: <input type="text" name="to_date" placeholder="YYYY-MM-DD">
        <input type="submit" value="Search">
    </form>
    <?php include &#39;search.php&#39; ?>
</body>
</html>

Copy after login

This HTML file creates a form that allows the user to enter a starting and end date. We will submit the form to the same page using the POST method and include the PHP code we just created above.

Now, we can open this file in a browser and use the form to enter a date range to search for order data. The results will be displayed in a table.

The above is the detailed content of How to query data within date with php+mysql. For more information, please follow other related articles on the PHP Chinese website!