CODEWITHSUNDEEP
phpmysql
CMArg
CMArg

Reputation: 1567

Retrieving date data from select query

I'm selecting from a database and echoing the results in a table. Everything worked ok until I change one column datatype from varchar to date. My first query (when column Date_PP datatype was varchar) was:

$queryitem = "SELECT Date_PP, Com, Doc, Place, p1, p2, p3, p4, p5, p6, p7, p8 
              FROM MyTable 
              WHERE Com>=12 
              ORDER BY Date_PP ";

That worked ok, but Date_PP was a string, not real date data. After changing the db column from varchar to date, I modified the query to:

$queryitem = "SELECT Date_PP, Com, Doc, Place, p1, p2, p3, p4, p5, p6, p7, p8, 
              DATE_FORMAT(Date_PP, '%Y-%m-%d') AS Date_PP 
              FROM MyTable 
              WHERE Com>=12
              ORDER BY Date_PP ";

The complete piece of code is:

$queryitem = "SELECT Date_PP, Com, Doc, Place, p1, p2, p3, p4, p5, p6, p7, p8, 
              DATE_FORMAT(Date_PP, '%Y-%m-%d') AS Date_PP
              FROM MyTable
              WHERE Com>=12
              ORDER BY Date_PP ";
if ($result = $link->query($queryitem))  {
    if ($success = $result->num_rows > 0) {
        $ac=0;
        while ($row = $result->fetch_row()) {
            echo "<tr>";
            echo "<td > ".$row[1]." ".$row[2]."</td>";
            echo "<td > ".$row[3]."</td>";
            if ($row[0]=="" or isnull($row[0])) {
                echo "<td > not yet </td>";
            }
            else {
                echo "<td> ".$row[0]." </td>";
            }
            for ($i = 4; $i <= 11; $i++) {
                echo "<td >".$row[$i]."</td>";
            }
            echo "</tr>";
        }
    }
}

But now I can't echo the results. Code stops when echoing $row[0].My guess is that date data is no longer "placed" in $row[0], but can't get this to work. Anyone can spot the problem?

Upvotes: 1

Views: 51

Answers (1)

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324640

You changed the column's type. If the column's contents weren't compatible with the new type, then it is removed, perhaps made null or some other appropriate "zero-value". You've nuked your data. Hope you had a backup!

For the record, date columns behave like string values in the format YYYY-MM-DD. You give it that, it stores it in an internal format, and returns that format back. You don't need to do anything there.

Upvotes: 1

Related Questions