Getting results for current month!

[runnerjp]

ok so in my db i have my dates as follows  dd/mm/yyyy

 

How would i select the results only happening during the current month!

 

here is how far i have got

 

$getthreads = "Select * from events where active='y' and date= '' ORDER BY DATE (STR_TO_DATE( `date`, '%d/%m/%Y ' ))";

 

 

date= '' is the issue!

[runnerjp]

bump

[rarebit]

I believe there are specific date matching functions, but I use unix time so I can't assist you there.

 

However in other situations i'd use regex:

http://dev.mysql.com/doc/refman/5.0/en/regexp.html#operator_regexp

 

Sorry, just realised that refers to version 5.0, but I can see that it's any diff? ? ?

[mga_ka_php]

$month = 10;

$year = 2009;

$getthreads = "SELECT * FROM events WHERE active='y' AND date LIKE '%/$month/$year'";

 

 

[rarebit]

Ah, yes 'LIKE', I knew there was a quicker function, I looked up MATCH (but no)...

[j0n]

another way.

 

$month = 11;
$query = sprintf("select * from events e where e.active = 'y' and month(e.date) = '%d' order by e.date desc", mysql_real_escape_string($month));

[runnerjp]

I tried bot hof them yet they show nothing sadly...

 

Here is my db setup

  `date` varchar(99) NOT NULL,

'22/11/2009'

 

and the script i ran

 

<?php include 'settings.php'; $month = 11; $year = 2009;
$query = "SELECT * FROM events WHERE active='y' AND date LIKE '%/$month/$year'";
$getthreads2 = mysql_query($query) or die("no events to show"); 

    while ($getthreads3 = mysql_fetch_array($getthreads2)) 
    { 

$getthreads3['event'];
}
?>

[j0n]

`date` varchar(99) NOT NULL

 

If it stores a date, why are you not using a DATE or DATETIME column?  Try changing this and the SQL posted previously should work.