• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Retrieving results by month...

#1
[eluser]wakey[/eluser]
Hi,

I've been trying to get this sorted for a while and can't seem to get it to work.

I have records in my mysql database that each have column containing the current timestamp as default. I am attempting to select all records from the current month.

I have a feeling I need to use code such as:

Code:
$month = date('n')

$this->db->where('MONTH(current_time)', $month);
$this->db->where('user_id', $uid);
$query = $this->db->get('hits');

I have tried the following SQL statement in my db which returns the value '4' - SELECT MONTH('2010-04-29 23:11:45')

But when I try this statement on my 'current_time' field (which contains current timestamps) it returns NULL.

Does anyone have any ideas?! Any help appreciated! Smile

#2
[eluser]Ivan A. Zenteno[/eluser]
sorry I not understanding what do you need.

#3
[eluser]WanWizard[/eluser]
As per the MySQL manual, the MONTH() function requires a MySQL date as input, not a timestamp.
Try:
Code:
$this->db->where('MONTH(FROM_UNIXTIME(current_time))', $month);

#4
[eluser]wakey[/eluser]
Thanks for the tip, I thought I was missing something and I now seem to be on the right track.

I've just run the following query on my table directly in phpmyadmin-

Code:
SELECT MONTH(FROM_UNIXTIME(current_time)) FROM hits

and this is returning '1' for each record and running the same query but with MONTHNAME returns 'January' which is strange as all records have a timestamp from April.

Any idea why this is going wrong?

#5
[eluser]WanWizard[/eluser]
What is your definition of a timestamp? How is the database field defined, and what exactly is stored in it?

#6
[eluser]wakey[/eluser]
Hi WanWizard,

This was a very strange one. I got there in the end by using SELECT MONTH(current_time) FROM hits but this only worked after dropping the table and re-creating it exactly as it was. I haven't changed anything except for the field name from current_time to time.

Very strange but thanks for you help Smile


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


Users browsing this thread:
1 Guest(s)


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.