I have a field in the database called date where the content is: 29/07/2014
Now, I'm trying to return how many fields have today's date and I actually know that there's 1.
This is my query:
$result = mysqli_query($con,"SELECT * FROM mytable WHERE `date` = date('d/m/Y') ");
$totalToday=mysqli_num_rows($result);
Why is $totalToday return 0 when it should be returning 1 ? Syntax ?
Your issue lies in your query:
$result = mysqli_query($con,"SELECT * FROM mytable WHERE `date` = date('d/m/Y') ");
Your query string literally means "Take every column from mytable where the date field is equal to the evaluation of the MySQL date function with the input 'd/m/Y'."
In order to get the date function from PHP evaluated into your query string, you need to evaluate it separately:
$result = mysqli_query($con,"SELECT * FROM mytable WHERE `date` = '" . date('d/m/Y') . "'");
The MySQL function for formatting dates is DATE_FORMAT. The first argument is the time to format, the second argument is a format string, and the formatted elements are prefixed with @.
$result = mysqli_query($con,"SELECT * FROM mytable WHERE `date` = date_format(NOW(), '@d/@m/@Y') ");
$resultis not an array, it's amysqli_resultobject.date('d/m/Y')<< that is a PHP date function. It is recommended to change your table so that thedatecolumn is a real MySQL DATE type, rather than a string. However, you can get the date formatted that way in MySQL withDATE_FORMAT(CURDATE(), '%d/%m/%Y')OOP, use$result->num_rows. Not that it solves the problem, though.