CODEWITHSUNDEEP
phpmysqlsqldatemysqli
WebDesign
WebDesign

Reputation: 29

MYSQLi Date Formatting - Select Query

I am new to all this coding stuff (html, css php and mysqli). Currently , I have a database which has a column with the type: DATE as well. The dates are showing up in the format YYYY-MM-DD and I am querying another column and not the date column.

When I am querying another column for something, I want the result to show the dates in the format dd-mm-yyyy and want only those rows to be displayed that have a date grater than the current date (ie curent date onwards and not old dates).

Table Name: Hurry

Column Name with Dates: SD

Column that I am querying: Hurry

Query: 

     "SELECT * FROM  `Hora` WHERE  `Hurry` LIKE  '%Happy'";

The output code is:

    <table id="table" class="query">
        <tr>
            <th width="50%" colspan = "3"> START </th>
            <th width="50%" colspan = "3"> END </th>
        </tr>
<?php
    while($row = mysqli_fetch_array($results, MYSQLI_ASSOC)){
        echo "<tr>";
                        echo "<td>" . $row['Hurry'] . "</td>";
                        echo "<td>" . $row['4'] . "</td>";
                        echo "<td>" . $row['SD'] . "</td>";
                        echo "<td>" . $row['ST'] . "</td>";
                        echo "<td>" . $row['2'] . "</td>";
                        echo "<td>" . $row['ED'] . "</td>";
                        echo "<td>" . $row['ET'] . "</td>";
        echo "</tr>";
}
    echo "</table>";

In Suumary, I am querying a table for 'Happy' in column 'Hurry' and where it matches, I want to show all matched columns that have a date after the current (today's) date in column 'SD'.

My query works to show me 'happy' from column 'Hurry' but all dates are coming up and they are in the format YYYY-MM-DD which I don't want.

Upvotes: 1

Views: 14986

Answers (3)

EternalHour
EternalHour

Reputation: 8621

To me it looks like several pieces of the puzzle are missing based on the results you want. First of all, you need a condition in your SQL statement to select the correct data you want.

"SELECT * FROM  `Hora` WHERE  `Hurry` LIKE  '%Happy' 
AND DATE_FORMAT(`date`, "%d-%m-%Y") > NOW()";

You could also just select the date as is it and use strtotime() to format it on the PHP side.

Upvotes: 1

ButterflyRay
ButterflyRay

Reputation: 423

Use the php data function to format the date.

Ex:

echo "<td>" .date('d-m-Y', strtotime($row['Hurry'])) . "</td>";

http://php.net/manual/en/function.date.php

Upvotes: 5

John Conde
John Conde

Reputation: 219794

Just use MySQL's DATE_FORMAT() function to format the date however you want:

 SELECT DATE_FORMAT(datecolumn, "%M %d, %Y") AS date ...

The above code outputs the date in the format of Monthname dd, YYYY

Upvotes: 1

Related Questions