Working with CURRENT_TIMESTAMP

[eluser]codemonkey[/eluser]
Hello,

My web app uses CURRENT_TIMESTAMP to place a timestamp when something happens, say a new user was created and the timestamp was logged in the admin history table.

1. Is there a good resource on learning how to is this information to display a table to show all users created within the last week, for example, using active records and CI tables?

If no good resources, can you show how to do the example? Last 1 week entries from current date.

2. After doing some searching I found using CURRENT_TIMESTAMP may not of been the best option to use. If so, what should I use and how should I convert my current records to the new format?

I want to learn and use what is best for CI and if it helps me out in the long run, that's a bonus.

Thank you

[eluser]codemonkey[/eluser]
Would something like this work?

Code:

$this->db->where('date_created', 'NOW() - 7 DAY');

ihavenoideawhatimdoing.jpg

[eluser]codemonkey[/eluser]
Why does this return a value

Code:

function get_actions_oneweek()
{

$total_actionsweek = 0;
$this->db->select('id, COUNT(id) AS total');
$actionweekrows = $this->db->get('item_history')->result_array();

foreach ($actionweekrows as $count => $actionweekrow)
{
$total_actionsweek = ($total_actionsweek + $actionweekrow['total']);
}

return $total_actionsweek;
}

while when I specify a time frame, the ID column returns NULL?

Code:

function get_actions_oneweek()
{

$startDate = date("Y-m-d", mktime(0, 0, 0, date('m'), date('d') - 14, date('Y')));
$endDate = date("Y-m-d", mktime(0, 0, 0, date('m'), date('d') - 7, date('Y')));  

$total_actionsweek = 0;
$this->db->select('id, COUNT(id) AS total');
$this->db->where('date_created', "'DATE BETWEEN '" . $startDate . "' AND '" . $endDate . "'");
$actionweekrows = $this->db->get('item_history')->result_array();

foreach ($actionweekrows as $count => $actionweekrow)
{
$total_actionsweek = ($total_actionsweek + $actionweekrow['total']);
}

return $total_actionsweek;
}

I'm still trying to understand how I can query CURRENT_TIMESTAMP...

Thanks

[eluser]CroNiX[/eluser]
for more complex wheres (using functions), you probably need to enter it as a single string.

Code:

$this->db->where('CURDATE() BETWEEN ' . $startDate . ' AND ' . $endDate);

When using functions in selects, you should do something like:

Code:

$this->db->select('id');
$this->db->select('COUNT(id) AS total', FALSE);

use FALSE for 2nd parameter so it won't protect the identifiers which messes up on function names

And as always, run a

Code:

echo $this->db->last_query();

to see what SQL was actually compiled from AR.

[eluser]codemonkey[/eluser]
Thank you for the reply and advise on building my queries. Still didn't return a value.

Code:

function get_actions_oneweek()
{
$this->output->enable_profiler(TRUE);

$startDate = date("Y-m-d", mktime(0, 0, 0, date('m'), date('d') - 14, date('Y')));
$endDate = date("Y-m-d", mktime(0, 0, 0, date('m'), date('d') - 7, date('Y')));  

$total_actionsweek = 0;
$this->db->select('id');
$this->db->select('COUNT(id) AS total');
$this->db->where('CURDATE() BETWEEN ' . $startDate . ' AND ' . $endDate);
$actionweekrows = $this->db->get('item_history')->result_array();

foreach ($actionweekrows as $count => $actionweekrow)
{
$total_actionsweek = ($total_actionsweek + $actionweekrow['total']);
}

return $total_actionsweek;
}

Code:

SELECT `id`, COUNT(id) AS total
FROM (`item_history`)
WHERE CURDATE() BETWEEN 2012-08-29 AND 2012-09-05

Code:

id total
NULL 0

[eluser]CroNiX[/eluser]
Today is 2012-09-12.

So you are saying

Code:

WHERE 2012-09-12 BETWEEN 2012-08-29 AND 2012-09-05

which it isn't, so no result

[eluser]codemonkey[/eluser]
Good point. I thought maybe I should reference the column to check the dates but that didn't work either.

Code:

$this->db->where('date_created BETWEEN ' . $startDate . ' AND ' . $endDate);

Code:

SELECT `id`, COUNT(id) AS total
FROM (`item_history`)
WHERE `date_created` BETWEEN 2012-08-29 AND 2012-09-05

Code:

id total
NULL 0

[eluser]codemonkey[/eluser]
The values are formatted like this in the DB

Code:

2012-06-11 16:11:46

[eluser]codemonkey[/eluser]
Holy smokes, I think I'm getting somewhere. If i do

Code:

function get_actions_oneweek()
{
$this->output->enable_profiler(TRUE);

$startDate = date("Y-m-d", mktime(0, 0, 0, date('m'), date('d') - 14, date('Y')));
$endDate = date("Y-m-d", mktime(0, 0, 0, date('m'), date('d') - 1, date('Y')));  

$total_actionsweek = 0;
$this->db->select('id');
$this->db->select('COUNT(id) AS total');
$this->db->where('date_created >', $endDate);
$actionweekrows = $this->db->get('item_history')->result_array();

foreach ($actionweekrows as $count => $actionweekrow)
{
$total_actionsweek = ($total_actionsweek + $actionweekrow['total']);
}

return $total_actionsweek;
}

It returns the correct number!!!!

If I can get the between to work this will be amazing!

[eluser]CroNiX[/eluser]
I'm guessing that you don't actually have a date in your database that is between those particular dates (2012-08-29 AND 2012-09-05) in the date_created field. Have you verified that you do?

You're returning all dates greater than your end date in the query returning results.

Code:

$this->db->where('date_created >=', $startDate);
$this->db->where('date_created <=', $endDate);

would be the same thing as the between statement.