php timetamps vs mysql timestamps

Maryanne

Records from $mysql_table:

id | animal | my_timeStamp
1 | deer | 2017-10-03 11:12:28
2 | dog | 2017-10-08 12:19:08
3 | bird | 2017-10-11 10:32:56
4 | fish | 2017-10-15 11:07:34

$result = mysqli_fetch_assoc(mysqli_query($conn, "select * from mysql_table"));

I want to display animals from LESS than 2 days ago, but the following does not work because "curdate()" is a mySQL and not a PHP function:

if($result['my_timeStamp'] > curdate() -2) { //do something }

What can I change in that line of code to make it work?

(Note: for "learning purposes" I don't want to change my mySQL 'select' statement -- I just want to learn what to do on the PHP "result" side of things 🙂

Maryanne

update:
I got this line of code to work, but is there a less "wordy" line of code that would work more efficiently?
if ($result['my_timeStamp'] < date("Y-m-d",strtotime('-2 days'))) { // do something }

pbismad

The only datetime math that php can do is with strtotime(), mktime(), the datetime class, or integer unix timestamps. All of these require extra statements to get the values on both sides of the comparison into the same comparable units.

benanamen

I don't want to change my mySQL 'select' statement

So you don't want to do it correctly? You don't do it in Php. You ask the database for the information you want. That's what it's job is.

Maryanne

benanamen;11064339 wrote:
So you don't want to do it correctly? You don't do it in Php. You ask the database for the information you want. That's what it's job is.

Well, like I mentioned in my post, I wanted to know the answer for "learning purposes." But you do make a good point... how would you do it? Currently, my mySQL table has a timestamp column whereby any time something gets inserted, mySQL automatically timestamps it. And, alas, the timestamp column looks like my example, e.g.
id | animal | my_timeStamp
1 | deer | 2017-10-03 11:12:28
What would you do? Would you have like a varchar column and have the mySQLi insert statement say something like "insert into mytable ('animal','time_column') values ('tiger','what-kind-of-PHP-time-goes-here?')"

I'm not sure what sort of php time function format to insert into mysql.

benanamen

Leave the auto timestamp alone. Just insert the animal. To get the animals inserted within the last two days you would do this:

SELECT
	animal 
FROM
	tablename 
WHERE
	my_timeStamp >= DATE_SUB( CURDATE( ), INTERVAL 2 DAY )