Page 1 of 1

searching for a specific date and displaying it

Posted: Wed Sep 24, 2008 3:22 am
by zplits
hello everyone. Can anyone help me please. here is the scenario of my problem

1. i have a database which has a field name of date
2. in the date field, i use it to store dates, like 2008-01-01
3. i have a dropdown list box, which displays the year, for example, 2008, 2009, 2010 and so on.
4. what i want to achieve is that, if the user clicks the drop down and selects 2008, and click search; all my database record which contains 2008 will be displayed.

Can anyone help me?

Re: searching for a specific date and displaying it

Posted: Wed Sep 24, 2008 3:51 am
by pcoder
Umm, if you need only the year, then it would be better to store only the year in the database.
Otherwise you have to use sql substringfunction to match with the year.

Re: searching for a specific date and displaying it

Posted: Wed Sep 24, 2008 4:22 am
by zplits
thanks pcoder. I have already set up the database, and the date field has the datatype of date.
So the values in my db now are: 2008-01-01, 2009-03-09, etc. etc.

Is there any other way? ltrim?

Re: searching for a specific date and displaying it

Posted: Wed Sep 24, 2008 6:36 am
by pcoder
You can get the result from:

Code: Select all

 
SELECT SUBSTR('2008-01-01',0,4) FROM DUAL;
 
And trim function is for removing white spaces.

Re: searching for a specific date and displaying it

Posted: Wed Sep 24, 2008 7:15 am
by zplits
Here is my code sir, and it doesn't work

Code: Select all

 
    //$year = $_POST['year'];
    $year = "2008";
$queryExpense = "SELECT * FROM tbl_inventory WHERE date=SUBSTR('$year',0,4)";
    $expenseResult = mysql_query($queryExpense);

Re: searching for a specific date and displaying it

Posted: Wed Sep 24, 2008 11:16 pm
by pcoder
Try this one:

Code: Select all

 
//$year = $_POST['year'];
$year = "2008";
$queryExpense = "SELECT * FROM tbl_inventory WHERE SUBSTR(date,0,4)=$year;
$expenseResult = mysql_query($queryExpense);
 
Hope this helps you. :)