How to query data within date with php+mysql-PHP Problem-php.cn

How to query data within date with php+mysql

PHPz

Release： 2023-03-23 17:18:01

Original

1469 people have browsed it

PHP and MySQL are one of the most commonly used technologies for web development. In development, it is often necessary to query data within a specific time range. In this article, we will share how to use PHP and MySQL to query data within a date range.

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: order date
amount: Order Amount

Create the table using the following SQL statement:

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 test data using the following SQL statement:

INSERT INTO orders (date, amount) VALUES
('2021-01-01', 100),
('2021-01-02', 50),
('2021-01-03', 200),
('2021-01-04', 150),
('2021-01-05', 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 file called "search.php" and 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['from_date']) && isset($_POST['to_date'])) {
    //将输入的日期格式化为MySQL日期格式
    $from_date = date('Y-m-d', strtotime($_POST['from_date']));
    $to_date = date('Y-m-d', strtotime($_POST['to_date']));

    //查询日期区间内的订单数据
    $sql = "SELECT * FROM orders WHERE date >= '$from_date' 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>
    <h1>Search Orders By Date Range</h1>
    <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. The form will be submitted to the same page using the POST method, followed by 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.

This is how to use PHP and MySQL to query data within a date range. This approach can be applied to any web development task that requires searching data within a time range.

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!