runnerjp Posted November 13, 2009 Share Posted November 13, 2009 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! Quote Link to comment Share on other sites More sharing options...
runnerjp Posted November 14, 2009 Author Share Posted November 14, 2009 bump Quote Link to comment Share on other sites More sharing options...
rarebit Posted November 14, 2009 Share Posted November 14, 2009 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? ? ? Quote Link to comment Share on other sites More sharing options...
mga_ka_php Posted November 14, 2009 Share Posted November 14, 2009 $month = 10; $year = 2009; $getthreads = "SELECT * FROM events WHERE active='y' AND date LIKE '%/$month/$year'"; Quote Link to comment Share on other sites More sharing options...
rarebit Posted November 14, 2009 Share Posted November 14, 2009 Ah, yes 'LIKE', I knew there was a quicker function, I looked up MATCH (but no)... Quote Link to comment Share on other sites More sharing options...
j0n Posted November 14, 2009 Share Posted November 14, 2009 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)); Quote Link to comment Share on other sites More sharing options...
runnerjp Posted November 15, 2009 Author Share Posted November 15, 2009 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']; } ?> Quote Link to comment Share on other sites More sharing options...
j0n Posted November 19, 2009 Share Posted November 19, 2009 `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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.