1

Hello today I coded a system where each user login was recorded and inserted into a database using PDO for MySQL, and I logged each of these logins with a date using the below PHP code and instered each value into my MySQL database table called user_logins. I logged the logins with the date string below...

date("D dS F H:i:s")

That outputs "Sat 12th September 20:59:33" (for example), but what I am trying to do is get all of the values from user_logins table but only get them if they have happened today. So basically I want to turn the system I current have (displaying all recent logins) to only showing todays logins that have happened today?

Please don't sned me to another question, because them questions use a different date() method to me, and most questions I have already checked and are unsuccessful.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Liam Hardy
  • 155
  • 4
  • 13
  • What datatype are you using for the last login field? – markdwhite Sep 13 '15 at 15:03
  • A varchar, using the date() in php as shown in the question. – Liam Hardy Sep 13 '15 at 15:04
  • 1
    Use the correct datatype. It's not a string: it's a date. Date data types allow for easy comparisons with other dates in queries, eg: "select * from user_logins where last_login > date_sub( now(), date_interval('24 hours'))". (From memory, possible typos) – markdwhite Sep 13 '15 at 15:12

2 Answers2

1

You have to change field type for the date, and make it datetime, using 2015-09-13 12:12:12 format.

Then you will have today's activity data by adding to WHERE clause

WHERE date(date_field) = CURDATE()

While to display your data you have to format it either in SELECT using DATE_FORMAT() or in PHP, by using strtotime() AND date() frunctions.

This is how databases work, and sooner you learn it, the more time you'll save.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
-1

You can split the date field into sub strings and then compare those day and month field. Try this to see how to split a string in mysql.

Community
  • 1
  • 1
Joey
  • 1,233
  • 3
  • 11
  • 18