Solution for a MySQL query

Question

I need a solution for my project..Actually, I got trapped in a problem..Let me show you the picture of my problem. Senerio of my problem

If you can see, there are some columns of 12 months.So I need to run a MySQL query or a program which can show me the selected columns which contain the text "Not Paid".

For example.

If I want to check the members between January to March or June to November etc. who are "Not Paid"..so what is the solution for that...??

I know that we can use the "between" clause but I am not getting the idea..Because I want to use two drops down menu where the user can select from which month to which month they want to see.

Please help me to get rid out of this problem..I am a new beginner in PHP.

Answer 1

If I were you I would represent the months as dates, even if strings (e.g., '2017-06') and then map the month to the semantic representation string (e.g., 'June'). So one way is to store the date in your MySQL database as a varchar.

In terms of modeling the data in your database, I would create a table with three columns 1) the user_id; 2) the due_date; 3) the payment_status.

.----------------------------.
| 0 | 2017-06    | paid      |
| 1 | 2017-07    | not paid  |
| 2 | 2017-08    | paid      |
| 3 | 2017-09    | paid      |
'----------------------------'

Now you can write a query to retrieve the members between January to March who has "Not Paid". (note: this is just one way, there are many ways to query this)

SELECT 
 DISTINCT(user_id) 
FROM payments_table 
WHERE due_date >= '2017-03' 
 AND due_date < '2017-06' 
 AND payment_status = 'not paid';