Help with SQL output

sql output based on part of a string

[bysonary]

hello

I have a problem, I want to be able to display the results from a mysql database, say for example the field in the database called date contained the following few records

Thursday 20 January 2007
Thursday 20 Feburary 2007
Wednesday 12 March 2006
Thursday 20 March 2007
Friday 21 March 2007
Thursday 20 April 2007

this field is a varchat field in the mysql database, how can i perform a query that will return each record based on the MONTH part of the string, for example if i choose March from the list it will return

Wednesday 12 March 2006
Thursday 20 March 2007
Friday 21 March 2007

and the other results will be ignored, I thought about using strstr() from php but then realized that might not be what I need, can anyone help out here?

Cheers

ChrisW

[henry0]

It would be easier if instead of varchar you used the "date" field
however here is a solution:
<?
error_reporting(E_ALL);

$date="monday june 12 2005";
$test=strtotime($date);
$month=date("M",$test);
echo"$month";
?>
it could be "m" for: 06
or M for: jun

then you may run a few "if" or a switch

[bysonary]

can this be used to check the field contains a certain string though?

I mean if i choose June from a list all of the records for june regardless of year or anything will be displayed

e.g

select all from posts where date_field contains June

the field will usually contain a longer string like

Saturday 2nd June 2007

I just wanted to show the records for june from my database

[Habtom]

$month = "June";
SELECT * FROM posts WHERE date_field LIKE '%$month%';

May be this should give you the result. But like what is suggested before, it is good if you store your dates in date format or timestamp, will be easy to manipulate or do queries at later stages.

Habtom

[bysonary]

I have a field that stores the unix timestamp if that might help my situation?

[jatar_k]

Habtom's solution should work

to use the timestamp you could do a range for a certain month in a certain year and use BETWEEN in your select query but not if you want every june record from every year.