+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Working with CURRENT_TIMESTAMP (/showthread.php?tid=54495)
Pages:
1
2
Working with CURRENT_TIMESTAMP - El Forum - 09-12-2012
[eluser]yacman[/eluser]
OP, can you determine that date field in your database is a DATETIME field and not a VARCHAR field.
Working with CURRENT_TIMESTAMP - El Forum - 09-12-2012
[eluser]codemonkey[/eluser]
Quote: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?
Yep I did and do.
I'll try formatting it like you show.
Quote:OP, can you determine that date field in your database is a DATETIME field and not a VARCHAR field.
The data type is timestamp, default is CURRENT_TIMESTAMP
Working with CURRENT_TIMESTAMP - El Forum - 09-12-2012
[eluser]yacman[/eluser]
OP I recommend that you start with the sql, then build an active record query around it, or
just use the sql and do a $this->db->query();
e.g. say this returns your count correctly from the sql command line.
Code:
SELECT count(id) as total
FROM item_history
where date_created >= '2012-09-01' and date_created <= '2012-09-30';Cronix has provided you with the correct active record statement to produce this:
Code:
$this->db->select('COUNT(id) as total',false);
$this->db->where('date_created >=', $startDate);
$this->db->where('date_created <=', $endDate);
$query = $this->db->get('item_history');
$total = 0;
//This is a grouped result, so there will only be one row returned.
if (!empty($query) && $query->num_rows() > 0) {
//Reference the result sets first rows column named 'total'.
$total = $query->row()->total;
$query->free_result();
}
echo "Total Rows {$total}";If you just have a sql query you know works, then use it:
Code:
$sql = "SELECT count(id) as total
FROM item_history
where date_created >= '{$startDate}' and date_created <= '{$endDate}'";
$query = $this->db->query($sql);
...Working with CURRENT_TIMESTAMP - El Forum - 09-13-2012
[eluser]boltsabre[/eluser]
Correct me if I'm wrong (really late here), but isn't
[quote]The data type is timestamp, default is CURRENT_TIMESTAMP[quote]
mean that any time you update a row for a user their "date_created" field will be updated to a new timestamp... this field/column should really be a "date_time", and create a new column called "last_update" with a timestamp datatype....
Working with CURRENT_TIMESTAMP - El Forum - 09-13-2012
[eluser]codemonkey[/eluser]
Yacman, I see what you are saying. I didn't think of it like that. Using the profiler helps with this too since I can copy the query in mysql and see what that returns. So I have the tools to play and test.
I really was just unsure on how to use the information in the column. How to format the query the correct way.
Quote:Correct me if I’m wrong (really late here), but isn’t
The data type is timestamp, default is CURRENT_TIMESTAMP
mean that any time you update a row for a user their “date_created” field will be updated to a new timestamp… this field/column should really be a “date_time”, and create a new column called “last_update” with a timestamp datatype….
Yes and no, I think it would depend on how the data is being inserted. For this table, each new entry is a new row with a new time stamp.