SORT BY DATE sqlite doesn't work like normal sql? can i use php function to sort?
Hi guys, so I am using sqlite database which has a table called "calender".
This table contains the following columns: id -INTEGER titel -VARCHAR beschrijving -VARCHAR datum -DATETIME inhoud -TEXT album -VARCHAR
Anyway: I am storing my dates like this: "03-01-2015" So: d - m - Y.
However, when I run my sqlQuery to select them, I cannot use the ORDER BY datum. It doesn't order them! sql function STR_TO_DATE ('datum', '% d,% m,% Y') It seems that it does not exist in sqlite, nor when it returns a "no such function" error. (I don't even store dates as a string, but as actual DATETIMES, so this is not a problem)
Anyway, I am displaying data from my table like this:
while ($x = $resultGetAllCalender->fetchObject()){
$html .="
<div class='col-md-4'>
<div class='col-sm-10 kalenderTopic'>
<div class='datum'><strong>Datum: <span>$x->datum</span></div>
<div class='datum'>Activiteit: <span>$x->titel</span></div>
<div class='inhoud'>$x->beschrijving</div>
<div class='readMore'><a href='index.php?page=kalenderDetail&id=$x->id'>Meer informatie</a>
</div>
</div>
I end up echo out my $ html later.
I thought so. Maybe I can sort the result first with $ resultGetAllCalender-> FetchObject () (using a php function like usort ()). Before running the while loop to add all my data to my $ html variable.
However, I'm confused that I can't seem to find the result of my sample. Any hints are greatly appreciated, I will continue to crush my brain in the meantime!
Edit: my method containing the request: (I don't use ORDER BY anymore, as it doesn't work)
public function getAllKalender(){
$sql = "SELECT * FROM kalender";
$statement = $this->makeStatement($sql);
return $statement;
}
source to share
You can simply create a custom sort string in the SQL query with substr()
:
substr(datum,7)||substr(datum,4,2)||substr(datum,1,2)
What this means is grabbing parts of a field datum
and combining them into order, which makes it sortable:Ymd
Live example
Take a look at this fiddle for a live example: http://sqlfiddle.com/#!7/de165/1
Request example
SELECT * FROM calender ORDER BY substr(datum,7)||substr(datum,4,2)||substr(datum,1,2) ASC;
source to share