fetch current date record of dd/mm/yyyy format in php mysql

[jackgoddy123]

Hello experts,

 

   I am stuck with an query. Actually i want to fetch current date records from my database and the below code probably fetches all my current date records. But it fetches in d/m/y (26/01/14) format but i need in dd/mm/yyyy (26/01/2014) .

Below is the code which gives output as d/m/y format:

<?php

ini_set( "display_errors", 0);

$con=mysqli_connect("localhost","root","","test");

// Check connection

if (mysqli_connect_errno())

  {

  echo "Failed to connect to MySQL: " . mysqli_connect_error();

  }

$today = date('d-m-y');

$result = mysqli_query($con,"SELECT * FROM Persons WHERE DATE(startdate) = '$today'");


echo "<table border='1'>

<tr>

<th>id</th>

<th>name</th>

<th>Startdate</th>

<th>Details</th>


</tr>";


while($row = mysqli_fetch_array($result))

  {

  echo "<tr>";

echo "<td>" . $row['id'] . "</td>";

  echo "<td>" . $row['name'] . "</td>";

  echo "<td>" . $row['startdate'] . "</td>";

   echo "<td>" . $row['details'] . "</td>";

  echo "</tr>";

  }

echo "</table>";


mysqli_close($con);

?>

I tried with the above code and also tried to change the date format i.e :

 $today = date('d-m-y'); 

to $today = date('dd-mm-yyyy');

 

I am not understanding what i am missing. Just need some help in this. Any help is appreciated.

[Psycho]

If the date in the database is in the format mm-dd-yy, then it is not being stored as a date type. You need to change the field to a date type then store them in the format yyyy-mm-dd. When storing as a date you can then use all of the MySQL date functions to do whatever you need to do with those values. In this case your query would be very simple

SELECT * FROM Persons WHERE DATE(startdate) = NOW()

No PHP code will be needed to calculate today's date - MySQL will do that for you. Then you can do one of two things.

 

1) You can retrieve the value in the format yyyy-mm-dd and then use PHP to translate it into the format you want:

 

while($row = mysqli_fetch_array($result))
{
    $startdate = date('d-m-Y', strtotime($row['startdate']));
    // . . .
}

 

2) You can have MySQL format the date automatically in the query. You can only do this if you are actually storing them as a date type in the correct format

[coed]SELECT *, DATE_FORMAT(startdate, '%d/%m/%Y') AS formattedDate FROM Persons WHERE DATE(startdate) = NOW()[/code]

 

But, all of that is unnecessary. As long as you are correctly retrieving the records for the current day, you already know what the date is.

 

 

As to why this was not working for you. The PHP date() function has specific parameters for formatting the date. A 'y' (lowercase) is the two-digit year, whereas the 'Y' is the four digit year. Don't just guess what to put into a function - check the manual. It is pretty easy to follow: http://us3.php.net/manual/en/function.date.php

Edited January 26, 2014 by Psycho

[jackgoddy123]

If the date in the database is in the format mm-dd-yy, then it is not being stored as a date type. You need to change the field to a date type then store them in the format yyyy-mm-dd. When storing as a date you can then use all of the MySQL date functions to do whatever you need to do with those values. In this case your query would be very simple

SELECT * FROM Persons WHERE DATE(startdate) = NOW()

No PHP code will be needed to calculate today's date - MySQL will do that for you. Then you can do one of two things.

 

1) You can retrieve the value in the format yyyy-mm-dd and then use PHP to translate it into the format you want:

while($row = mysqli_fetch_array($result))
{
    $startdate = date('d-m-Y', strtotime($row['startdate']));
    // . . .
}

2) You can have MySQL format the date automatically in the query. You can only do this if you are actually storing them as a date type in the correct format

[coed]SELECT *, DATE_FORMAT(startdate, '%d/%m/%Y') AS formattedDate FROM Persons WHERE DATE(startdate) = NOW()[/code]

 

But, all of that is unnecessary. As long as you are correctly retrieving the records for the current day, you already know what the date is.

 

 

As to why this was not working for you. The PHP date() function has specific parameters for formatting the date. A 'y' (lowercase) is the two-digit year, whereas the 'Y' is the four digit year. Don't just guess what to put into a function - check the manual. It is pretty easy to follow: http://us3.php.net/manual/en/function.date.php

 

 

Thanx for you reply. 

I have even google and your reply is perfectly fine. But when i run the above query it results no output. I knw that for "Y" i can get "2014" but when i use it in query its not giving any output. Inside my database i have records with my current date. But unfortunately it fetches no output.

[Psycho]

What is the field TYPE for the 'startdate' in the database? As I previously pointed out, you stated the value in the DB is in the format d/m/y, which would mean you are not storing the data correctly. Most likely you are storing it as a string. Int hat case you cannot use the DATE functions within MySQL. Store the data correctly and the query will work.