CODEWITHSUNDEEP
phpmysql
Sachin Kanungo
Sachin Kanungo

Reputation: 1064

php - Output date in d-m-Y format from MySQL timestamp column

I have a database table which process timestamp when someone signup for my service. The timestamp works correctly and inserts whole time in database but i need only Date,Month and Year format in it. Please have a look at my code:

    if((!isset($_SESSION['USER'])) || (!isset($_SESSION['UID'])) || (!isset($_SESSION['UTYPE'])) || (($_SESSION['UTYPE']!='D') && ($_SESSION['UTYPE']!='F') && ($_SESSION['UTYPE']!='T')))
{
    header( 'Location: login.html' );
}
else
{
    include 'connection.php';
    include 'constants.php';

    $file_name = "Avanti-Website-Leads-".date('dmy-His');

    echo "<html><head>";
    ?>
    <script src="javascript/jquery-1.9.1.js"></script>
    <script type="text/javascript">
    function export2excel(file_name)
    {
        var link = document.createElement('a');
        link.download = file_name+".xls";
        link.href = 'data:application/vnd.ms-excel,' + encodeURIComponent($('body').html());
        link.click();
    }
    </script>

    <?php

    echo "</head><body onload=\"export2excel('".$file_name."')\" >";

    $dbh = new PDO("mysql:host=".$dbhost.";dbname=".$dbname, $dbuser, $dbpass);
    unset($dbhost, $dbuser, $dbpass, $dbname);

    if(($_SESSION['UTYPE']=='D')||($_SESSION['UTYPE']=='F'))
    {
        $query = "SELECT * FROM `bookavanti` where uid = ".$_SESSION['UID']." order by `bookavanti`.`lastupdate` DESC";
    }
    else
    {
        $query = "SELECT * FROM bookavanti order by lastupdate";
    }

    $sql = $dbh->prepare($query);
    $sql->execute();
    $affected_rows = $sql->rowCount();
    if($affected_rows>0)
    {
        echo "<table style='font-size:12px;text-align:center;'>";
        echo "<tr><th>Booking Time</th><th>Booking Type</th><th>Client Name</th><th>Email Address</th><th>Contact No.</th><th>Address</th><th>Pin Code</th><th>City</th><th>State</th></tr>";
        for ($i=0; $i < $affected_rows; $i++)
        {
            $row = $sql->fetch(PDO::FETCH_ASSOC);
            $x = explode('-',$row['lastupdate'],0);
            echo "<tr><td>".date("m.d.y",$x)."</td><td>".$row['type']."</td><td>".$row['name']."</td><td>".$row['email']."</td><td>".$row['contact']."</td><td>".$row['address']."</td><td>".$row['pin']."</td><td>".$row['city']."</td><td>".$row['state']."</td></tr>";
        }
        echo "</table>";
    }
    echo "</body></html>";

I believe the lines

$x = explode('-',$row['lastupdate'],0);
        echo "<tr><td>".date("m.d.y",$x)."</td>

have problem. What should i do to output timestamp input to d m Y format.

Upvotes: 1

Views: 2238

Answers (3)

Sherif
Sherif

Reputation: 11944

Just so you know date expects an integer (as a Unix timestamp from the epoch) for its second argument, which is optional and by default is the current timestamp.

timestamp

The optional timestamp parameter is an integer Unix timestamp that defaults to the current local time if a timestamp is not given. In other words, it defaults to the value of time().

Second, $x = explode('-',$row['lastupdate'],0) is basically the same exact thing as saying $x = array($row['lastupdate']), which doesn't make a lot of sense if you're handing $x to date().

What you probably meant to do was something more along the lines of this...

list($year, $month, $day) = explode('-', $row['lastupdate']);
$date = "$month.$day.$year";

Which just splits the formatted date string stored in MySQL by hyphen and reassembles the parts in the desired order. If the formatted date string contains anything other than just the date you can discard that bit as well by just splitting the string by space first, before splitting it by hyphen, like so...

// Say your $row['lastupdate'] string is '2015-07-18 13:29:00', for example
list($tmpDate) = explode(' ', $row['lastupdate'], 2);
list($year, $month, $day) = explode('-', $tmpDate);
$date = "$month.$day.$year";

Now you get the desired 07.18.2015.

Now, of course, you could also convert the formatted date string into a Unix timestamp first with strtotime(), and then try converting the Unix timestamp back into a formatted string using date() (probably also something you were trying to do), but you have to be aware of things like Timezones, which may or may not become problematic if you aren't carefully and fully understand the implications of what strtotime does and how MySQL deals with timezones, for example (assuming you're also using MySQL somewhere else).

Upvotes: 2

manjeet
manjeet

Reputation: 1517

you can use date_format mysql function like this

select date_format(now(), '%m-%d-%Y');

Upvotes: 0

Unni Babu
Unni Babu

Reputation: 1814

Use php date() and strtotime() 

$newDate = date("d-m-Y", strtotime($row['lastupdate']));  // new date contains d-m-y format of lastupdate

Upvotes: 3

Related Questions