Date format in PHP MYSQL

Question

Well I have these two statements. IGNORE THE SECURITY RISK (SQL INJECTION) A user inputs a date in this format 2016-7-9. How can one get and modify the date since I've tried with

$sql =  "SELECT DISTINCT msisdn FROM customer WHERE time_paid BETWEEN ADDDATE('$time1',INTERVAL 0 HOUR) AND ADDDATE('$time2',INTERVAL '23:59' HOUR_MINUTE)";

Where $time1 and $time2 are in this format so the select statement does not work. I could be wrong but I think it expects in this format 2016-01-12?

This works:

$sql =  "SELECT DISTINCT msisdn FROM customer WHERE time_paid BETWEEN 2015-01-10 12:23:34 AND 2017-12-12 12:12:12";

Anyone how can I use this format 2016-3-3 in my query?

I hate when people say "I'm not that far along..." or "This site will not be public..." or "It's only for school, so security doesn't matter...". If teachers and professors are not talking about security from day one, they're doing it wrong. Challenge them. They're teaching sloppy and dangerous coding practices which students will have to unlearn later. I also hate it when folks say, "I'll add security later..." or "Security isn't important now..." or "Ignore the security risk...". If you don't have time to do it right the first time, when will you find the time to add it later? — Jay Blanchard
– Jay Blanchard, Commented Jul 8, 2016 at 15:31
How about str_pad or some other magic sauce you php people use — Drew
– Drew, Commented Jul 8, 2016 at 15:33
@JayBlanchard +1000000 Nobody ever finds the time to go back and fix the security flaws left exposed by lazy coding until it has been exploited and the data is defaced or destroyed. It is SO SIMPLE to code with parameters it is laziness or negligence to do it any other way. — Sean Lange
– Sean Lange, Commented Jul 8, 2016 at 16:14

Muhammad Sumon Molla Selim · Accepted Answer · 2016-07-08 15:45:59Z

1

Format your MySQL time_paid DATETIME column value to match with your constraint value this way:

DATE_FORMAT(time_paid, '%Y-%c-%e') // 2016-3-3

where,

%Y - Four digits year e.g., 2000, 2001,…etc.

%c - Month in numeric e.g., 1, 2, 3…12

%e - Day of the month without leading zero e.g., 1,2,…31

So, your SQL would be:

$sql =  "SELECT DISTINCT msisdn FROM customer WHERE DATE_FORMAT(time_paid, '%Y-%c-%e') BETWEEN ADDDATE('$time1',INTERVAL 0 HOUR) AND ADDDATE('$time2',INTERVAL '23:59' HOUR_MINUTE)";

edited Jul 8, 2016 at 15:45

answered Jul 8, 2016 at 15:41

Muhammad Sumon Molla Selim

4,1291 gold badge26 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Date format in PHP MYSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related