我有两个MySQL
表:
desc students;
+---------------------------+---------------+
| Field | Type |
+---------------------------+---------------+
| student_id | INT(11) |
| student_firstname | VARCHAR(255) |
| student_lastname | VARCHAR(255) |
+---------------------------+---------------+
desc studentabsence;
+---------------------------+---------------+
| Field | Type |
+---------------------------+---------------+
| student_id | INT(11) |
| student_absence_startdate | date |
| student_absence_enddate | date |
+---------------------------+---------------+
如果我运行以下查询:
SELECT
*
FROM
students s
INNER JOIN studentabsence sa
ON s.student_id = sa.student_id
WHERE s.student_id = '1'
我得到以下输出:
+------------+-------------------+-----------------+------------+---------------------------+-------------------------+
| student_id | student_firstname | student_lastname| student_id | student_absence_startdate | student_absence_enddate |
+------------+-------------------+-----------------+------------+---------------------------+-------------------------+
| 1 | John | Doe | 1 | 2012-08-01 | 2012-08-08 |
+------------+-------------------+-----------------+------------+---------------------------+-------------------------+
现在,我有一个用户可以在其中选择学生的<form>
和一个日期范围。我想知道是否可以将用户选择的两个日期与数据库值进行比较,并且如果匹配,则在学生拥有数据的范围内之间的所有工作日进行回显。
因此,如果用户发布以下表单,请使用上述示例:
student_id = '1'
student_absence_startdate = '2012-08-02'
student_absence_enddate = '2012-08-06'
我想echo
:
2012-08-02
2012-08-03
2012-08-06
任何指向正确方向的指针都受到高度赞赏。
我只是在查询的基础上发布此回复。此外,如果您将数据类型保留为日期,这将对您有很大帮助。
SELECT * FROM students s
INNER JOIN studentabsence sa ON s.student_id = sa.student_id
AND s.student_id = '1'
AND student_absence_startdate >= 2012-08-02
AND student_absence_enddate < 2012-08-06