0

i am outputting database entries with the following code:

<div id="main_area">
<div  id="container">
<div id="php_container" role="main">
<?php
include('connect_it.php');
$sqlget= "SELECT * FROM dbtablename";
$sqldata= mysqli_query ($dbcon,$sqlget) or die('this is an error');
while ($row = mysqli_fetch_array($sqldata, MYSQLI_ASSOC)) {
echo $row[ 'li' ] ;
}
?>
</div>
</div>
</div>

on page load, i'd like the database entries to be ordered by their date (from most recent to least recent) which is stored in a mysql field called 'added' in the format DD/MM/YY.

i'm not sure if it can be done, but as another option, the date is within a span defined by <span id="date">date: 27/11/11</span> so perhaps this span could be used to order the data?

so my question is, how can i make the initial output ordered by date from most recent to least recent?

i have tried:

$sqlget= "SELECT * FROM dbtablename ORDER BY added ASC";

but i got unexpected results.

thank you.

user1063287
  • 10,265
  • 25
  • 122
  • 218
  • 1
    What datatype is your `added` column? It should be [date](http://dev.mysql.com/doc/refman/5.1/en/datetime.html). Also, you'll want to order descending – Phil Nov 30 '12 at 03:49
  • thank you for your reply, the column is varchar(8) , latin1_swedish_ci - can i just change the column's 'type' to 'DATE' in phpMyadmin? i have just done a sql dump in case a backup is needed. thank you. – user1063287 Nov 30 '12 at 03:53

2 Answers2

1

If your added column is a DATE, DATETIME or TIMESTAMP type you can use DATE_FORMAT like so, 

$sqlget= "SELECT *, DATE_FORMAT(added,'%d/%m/%y') as addedFormated FROM dbtablename ORDER BY added ASC";

See, https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

Otherwise if it is a string try (it really should be a DATE type though),

$sqlget= "SELECT *, STR_TO_DATE(added,'%d/%m/%y') as addedFormated FROM dbtablename ORDER BY STR_TO_DATE(added,'%d/%m/%y') ASC";

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

shapeshifter
  • 2,967
  • 2
  • 25
  • 39
  • thank you very much, the second code snippet worked perfectly, i acknowledge all users comments about data type, but for now this method will work, thank you all for your knowledge and suggestions. regards. – user1063287 Nov 30 '12 at 04:28
  • 1
    No problem, If you want to change your column type here is a SO thread with a simple looking script. Adjust the column names to the ones you have. You may also need to use the STR_TO_DATE from above to format the date string so the database can read it. – shapeshifter Nov 30 '12 at 05:35
  • http://stackoverflow.com/questions/7090231/how-to-convert-a-varchar-column-type-to-date-type-without-losing-the-dates – shapeshifter Nov 30 '12 at 05:35
1

A few minor things first (there are more too), <span id="date">...</span> is not valid as the id attribute should be unique, use <span class="date">...</span> instead. Browsers will render it, even render it correctly in most cases, but is not the correct use of an "id" attribute.

Do you need to select all columns in your query? If not, only select the ones you need.

The query you want is:

SELECT * FROM dbtablename ORDER BY added DESC

But this will select all rows and all columns. Now for the tricky part. You say your data is stored in 'dd/mm/yyy' format, so this query will not work as there are about 30 days per month per year. You need to either use a datetime column type, which will store your date data in yyyy-mm-dd 00:00:00 format. As you can see, this will correct all your issues.

Or, as someone else said, use STR_TO_DATE(added,'%d/%m/%y') to extract the data.

Then, on your extracted data, in PHP, use (where $var is your date value):

echo date('d/m/y',strtotime($var));

Hope that helps.

Tigger
  • 8,980
  • 5
  • 36
  • 40