I want to make a dropdownlist that retrieve the values from the database. The values are the month and the year of the date of user input. The dropdownlist example:-
I have done the query to get the result as above:-
SELECT DISTINCT(DATE_FORMAT(DATE(date),'%m-%Y')) AS date FROM tbl ORDER BY DATE_FORMAT(DATE(date),'%Y'), DATE_FORMAT(DATE(date),'%m') ASC
However, I'm quite confused on how to display all of other columns in tbl after selecting the month in the dropdownlist. Say, in the table tbl, there are description and personInCharge column aside the date column. When selecting the month in the dropdownlist, it suppose to retrieve all data that is related with the selected month.
I've tried using these:-
SELECT *, DISTINCT(DATE_FORMAT(DATE(date),'%m-%Y')) AS date FROM tbl ORDER BY DATE_FORMAT(DATE(date),'%Y'), DATE_FORMAT(DATE(date),'%m') ASC
and
SELECT description, personInCharge, DISTINCT(DATE_FORMAT(DATE(date),'%m-%Y')) AS date FROM tbl ORDER BY DATE_FORMAT(DATE(date),'%Y'), DATE_FORMAT(DATE(date),'%m') ASC
and such but all I get is error. Where am I wrong? How am I to complete this? Is it by using join clause? If so, how?
--UPDATE--
I get to list out the details by using this query:-
SELECT *, (DATE_FORMAT(DATE(date),'%m-%Y'))AS month FROM tbl
But, I still don't know how to integrate with the dropdownlist.
I'm just using these two queries. The first query is for dropdownlist. The second query is to display values from table.
$sql = mysql_query("SELECT DISTINCT(DATE_FORMAT(DATE(date),'%m-%Y'))
AS month
FROM tbl
ORDER BY DATE_FORMAT(DATE(date),'%Y'), DATE_FORMAT(DATE(date),'%m')
ASC");
$query = mysql_query("SELECT *, (DATE_FORMAT(DATE(date),'%m-%Y')) AS month
FROM tbl
WHERE DATE_FORMAT(DATE(date),'%m-%Y')='$date'");
If theres any wrong with my code, lets discuss here.