Web development often involves receiving a specific year and month from a user and retrieving data for that month from a database.
For example, a user may select a year and month (e.g., 2023-01) through an HTML form, and based on that information, you may want to extract data for that month (e.g., 2023-01-02, 2023-01-28, etc.) from a MySQL database.

In this article, we will explain how to process year-month data submitted using input type=”month” in an HTML form and retrieve data for a specific month in MySQL.

Contents
  1. Example SQL Query
  2. Important Notes
  3. Summary

Example SQL Query

Based on the year-month data (e.g., 2023-01) submitted via POST using input type=”month” from an HTML form, the following SQL query can be used to extract data for a specific month in MySQL. In this example, the posted year and month are used as a variable, and data from the first day to the last day of that month is selected.

Here, we assume that the table name in the database is your_table, the column storing dates is date_column, and the posted year-month value is stored in the variable posted_year_month.

SELECT *
FROM your_table
WHERE date_column >= STR_TO_DATE(CONCAT(posted_year_month, '-01'), '%Y-%m-%d')
  AND date_column < LAST_DAY(STR_TO_DATE(CONCAT(posted_year_month, '-01'), '%Y-%m-%d')) + INTERVAL 1 DAY;

　
This query performs the following operations:

  1. STR_TO_DATE(CONCAT(posted_year_month, '-01'), '%Y-%m-%d')
    Appends -01 to the posted year-month string and converts it into a date type representing the first day of the month (e.g., 2023-01-01).
  2. LAST_DAY(STR_TO_DATE(CONCAT(posted_year_month, '-01'), '%Y-%m-%d'))
    Retrieves the last day of the month.
  3. + INTERVAL 1 DAY
    Gets the day after the last day (the first day of the next month). This ensures that the end of the range is set to 00:00:00 on the first day of the next month.
  4. In the WHERE clause, records are selected where the value of date_column is greater than or equal to the first day of the selected month and less than the first day of the next month.

　
This query demonstrates a basic method for selecting data for a given month based on the posted year-month value. In an actual application, the value of posted_year_month must be retrieved from the POST data submitted by the form. Additionally, to avoid security risks such as SQL injection, it is important to perform proper data validation and escaping.

Important Notes

  • Security Measures
    When using user input in SQL queries, there is a risk of SQL injection. Always perform proper data validation and escaping, or use prepared statements to ensure security.
  • Data Format Consistency
    Data submitted from input type="month" is in the "YYYY-MM" format. To maintain consistency with the date column in the database, conversion to the correct format is required.

Summary

This method is a basic technique for retrieving data from a MySQL database based on the year and month selected by a user through an HTML form. Even beginner engineers can easily understand and implement it. However, interactions with databases require careful attention to security. Always follow secure coding practices to maintain the safety of your application.

We hope this article helps with your development. We also hope it deepens your understanding of SQL queries and enables you to perform more efficient database operations.

　
※Please use this information at your own risk.

MySQL yyyymm