sort by date

[bigrossco]

I have the code below but would like to modify it so it will sort the output by the date

 

Thanks

 

r

 

<?php
    $host = "$lang_dbhost";
    $user = "$lang_dbuser";
    $pass = "$lang_dbpass";
    $db = "$lang_dbase";

    // open connection
    $connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");

    // select database
    mysql_select_db($db) or die ("Unable to select database!");



    $query = "SELECT id, DATE_FORMAT(date,'%d/%m/%Y'), time, tech, description
FROM calander";

    // execute query
    $result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());

?>




<form method="post">
  <?php
if (mysql_num_rows($result) > 0) {
echo"<table border = 1>";

while($row = mysql_fetch_row($result)){
echo"<tr>";
echo"<td><b>Date: </b>".$row[1]." <b>Time: </b>".$row[2]." <b>Staff: </b>".$row[3]." <a href=\"./update-appoint.php?id=".$row[0]."\">$lang_update</a>  <a href=\"./delete-appoint.php?id=".$row[0]."\">$lang_delete</a> </b></td>";
echo"</tr>";
        echo "<td>" . $row[4]."</td>";
//echo"</table>";
  }
  }
else {

echo"<b>Their is currently no appointments</b>";
}

?>

[obsidian]

Basically, since you're formatting the date, you're better off selecting it twice (once with formatting and once without). That way, you can ORDER BY the unformatted date:

<?php
$sql = mysql_query("SELECT date, DATE_FORMAT(date, '%d/%m/%Y') AS formatted, time, tech, description 
FROM calendar 
WHERE date = DATE() ORDER BY date");
?>

 

Notice also that if your datatype of the column is a DATE field, it's already in the %Y-%m-%d format, so you can lose the second DATE_FORMAT() call for a simple DATE() call instead.

 

Good luck

[bigrossco]

i get the message Error in query: . Query was empty when i do your code

[trq]

Date is a reserved word in sql.

 

<?php
$sql = mysql_query("SELECT `date`, DATE_FORMAT(`date`, '%d/%m/%Y') AS formatted, time, tech, description 
FROM calendar 
WHERE `date` = DATE() ORDER BY `date`");
?>

[bigrossco]

still get the same message

[AndyB]

Is your table name calander or calendar .. there's some confusion in this thread about that, and ..

 

why not have a more useful error message? For example:

 

$result = mysql_query($query) or die("Error ". mysql_error(). " with query ". $query);

[tauchai83]

you try to add Group By in the sql statement and see what happen.

[bigrossco]

as in first post its calander

 

i tried  adding $result = mysql_query($query) or die("Error ". mysql_error(). " with query ". $query); the message i get is Error Query was empty with query