Mysql查询匹配的月份和年份

Question

I have form with 4 fields namely(start month, start year, end month, end year) and MySQL table structure is like id, customer id, amount, month, year

Now, I need to display the rows with matching condition as between the start month and year and end month and year.

I tried this query

select id,customer id,concat(month,'-',year) as d1 from payroll where
empid='$_POST[emp_id]' and (STR_TO_DATE(d1,'%m-%Y') between
STR_TO_DATE('$_POST[fmonth]-$_POST[fyear]','%m-%Y') and
STR_TO_DATE('$_POST[tmonth]-$_POST[tyear]','%m-%Y'))

Please advise....

Answer 1

Assuming the columns year and month and the 4 form fields are integers, this would work:

SELECT id
     , customer id
     , CONCAT(month, '-', year) AS d1 
FROM payroll 
WHERE empid = '$_POST[emp_id]' 
  AND (year, month) >= ( '$_POST[fyear]', '$_POST[fmonth]' ) 
  AND (year, month) <= ( '$_POST[tmonth]', '$_POST[tyear]' )

---

You should probably take care of those $_POST[] before sending them to the database for security reasons (SQL injection).

A compound index on (empid, year, month) would help performance.