php SELECT (count) in mysql/pdo WHERE formatted date

Question

I need to select (count) rows with a specific date formatted by YEAR-MONTH-DAY.

i try

 $date = $_GET['date'];

 // requete qui recupere les evenements
$result = "SELECT COUNT(*) FROM evenement WHERE STR_TO_DATE(start, '%Y-%m-%d') = $date";

 // connexion a la base de donnees
 try {
 $bdd = new PDO('mysql:host=;dbname=', '', '');
 } catch(Exception $e) {
 exit('Impossible de se connecter a la base de donnees.');
 }
 $sth = $bdd->query($result);
 echo json_encode($sth->fetchColumn());

where date variable is a date with that format. i want to count rows and return the number of rows with that date.

i also try

$result = "SELECT COUNT(*) FROM evenement WHERE DATE_FORMAT(start, '%Y-%m-%d') = $date";

and

$result = "SELECT COUNT(*) FROM evenement WHERE DATE_FORMAT(DATE(start), '%Y-%m-%d') = $date";

but nothing works

any ideas?

Thanks

Your code is open to SQL_Injection... Please use prepared statements! — Naruto
– Naruto, Commented Oct 7, 2015 at 14:59
@Fred-ii- $date get value from an array with dates. wich have that format. — dpapatsarouchas
– dpapatsarouchas, Commented Oct 7, 2015 at 15:01

Gordon Linoff · Accepted Answer · 2015-10-07 15:40:07Z

1

You should convert your constant to a date, not the column. So, my first attempt would be:

SELECT COUNT(*)
FROM evenement
WHERE start = DATE('$date');

However, that might not work. You might need to use str_to_date() with the appropriate format.

The reason this method is better is because the optimizer can take advantage of an index on evenement(start).

edited Oct 7, 2015 at 15:40

answered Oct 7, 2015 at 15:07

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

6 Comments

Funk Forty Niner Over a year ago

Doesn't $date not need be quoted?

dpapatsarouchas Over a year ago

@Fred-ii- you are right need quoted. so both answers make my code work. My $date variable need DATE($date). so you suggestion make me think about it and Gordon Linoff 's answer confirmed that.

Funk Forty Niner Over a year ago

@NinjTsax Funny you should say that. I had that very same thought earlier: "both answers given will give the OP their solution" - Now, how bizarre is that? ;-)

Funk Forty Niner Over a year ago

...and this answer should reflect it before accepting it @NinjTsax I think Gordon should have known this. His experience far exceeds mine ;-)

Gordon Linoff Over a year ago

@Fred-ii- . . . Actually, I was thinking it should be a parameter and not built-into the string. My real point was to do the conversion on the parameter and not on the column.

|

dpapatsarouchas · Accepted Answer · 2015-10-07 15:42:10Z

1

The responses and comments all led to this answer

$result = "SELECT COUNT(*) FROM evenement WHERE DATE_FORMAT(DATE(start), '%Y-%m-%d') = DATE('".$date."')"

the problem was that the $date variable need DATE($date) and in SELECT need quotes.

answered Oct 7, 2015 at 15:42

dpapatsarouchas

3011 gold badge4 silver badges16 bronze badges

Comments

Arun Krish · Accepted Answer · 2015-10-07 15:01:41Z

0

Change like this

$result = "SELECT COUNT(*) FROM evenement WHERE DATE_FORMAT(start, '%Y-%m-%d') = '".$date."'";

You need to enclose $date in '' quotes, else it will perform subtraction like(2014-10-01 = 2003)

edited Oct 7, 2015 at 15:01

answered Oct 7, 2015 at 14:59

Arun Krish

2,1531 gold badge12 silver badges15 bronze badges

2 Comments

Funk Forty Niner Over a year ago

and "why" should they "change like this"?

dpapatsarouchas Over a year ago

@Fred-ii- in the table im searching for that date. Dates have different format

Collectives™ on Stack Overflow

php SELECT (count) in mysql/pdo WHERE formatted date

3 Answers 3

6 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related