Page 1 of 2

Order text of database result

Posted: Mon Oct 03, 2011 4:09 pm
by stijn22
Hi,

This is my current script for echo-ing the results of a database:

Code: Select all

<?php
					 			require_once("connect.php"); //Voor de connectie met de database.

								$select = "SELECT * FROM agenda ORDER BY datum DESC LIMIT 2"; 
								$query = mysql_query($select)or die(mysql_error()); 

								while($list = mysql_fetch_object($query)){ 

														echo "<b>Datum: </b>$list->datum<br/>"; 
														echo "<b>Locatie: </b>$list->locatie<br/>"; 
														echo "<b>Plaats: </b>$list->plaats<br/>"; 
														echo "<img src=\"images/line-leftbar.jpg\"><br/><br/>";
														}
						  ?>
However, the column "datum" (dutch for date) is varchar. If I want to be able to easily order the results, it's usefull if I change this to the type date. This had the input type of YYYY-MM-DD, for example 2012-08-31.

And here's my problem. Dutch poeple read the date in a other format, namely DD-MM-YYYY. And that's why I need a code that changes the result from YYYY-MM-DD to DD-MM-YYYY. And then echoes the correct format that is readable for the Dutch people :)

Re: Order text of database result

Posted: Mon Oct 03, 2011 4:23 pm
by Celauran
Might I suggest making the field an unsigned int and storing the date and time in UNIX time format? You could then use the date() function to display the date in whichever format you wish, or even leave it up to users' preference.

Re: Order text of database result

Posted: Mon Oct 03, 2011 5:53 pm
by twinedev
Go ahead and keep the field as a date, then do the following:

SELECT *, DATE_FORMAT(`datum`,"%d-%m-%Y") AS fDatum FROM `agenda` ORDER BY `datum` DESC LIMIT 2

Now in your results, instead of using

Code: Select all

echo "<b>Datum: </b>$list->datum<br/>"; 
use

Code: Select all

echo "<b>Datum: </b>$list->fDatum<br/>"; 
( I place a f in front of it to indicate Formatted)

See http://dev.mysql.com/doc/refman/5.5/en/ ... ate-format for more formatting options...

Re: Order text of database result

Posted: Tue Oct 04, 2011 4:26 am
by stijn22
When I change it into this:

Code: Select all

<?php
					 			require_once("connect.php"); //Voor de connectie met de database.

								$select = "SELECT *, DATE_FORMAT(`datum`,"%d-%m-%Y") AS fDatum FROM `agenda` ORDER BY `datum` DESC LIMIT 2"; 
								$query = mysql_query($select)or die(mysql_error()); 

								while($list = mysql_fetch_object($query)){ 

														echo "<b>Datum: </b>$list->fDatum<br/>"; 
														echo "<b>Locatie: </b>$list->locatie<br/>"; 
														echo "<b>Plaats: </b>$list->plaats<br/>"; 
														echo "<img src=\"images/line-leftbar.jpg\"><br/><br/>";
														}
						  ?>
I get this error: Parse error: syntax error, unexpected '%' in /home/*******/public_html/index.php on line 51.

Re: Order text of database result

Posted: Tue Oct 04, 2011 5:05 am
by VladSun
I vote for not using the DB engine as a "text formatter" if the formatted text is not used somehow in the query itself - use PHP date format functions.

Putting the date formatting into the DB violites the MVC principles IMHO (i.e. parts of the View layer are moved into the Model layer)

Re: Order text of database result

Posted: Tue Oct 04, 2011 3:01 pm
by stijn22
Ok, I get it. Can you show me how that's done? :roll:

Re: Order text of database result

Posted: Tue Oct 04, 2011 3:16 pm
by VladSun
You could use Celauran's suggestion, though I don't like putting dates into timestamp format - many of the Date&Time DB functions won't work with it (and "typecasting" would not use indexes if any).

http://www.php.net/manual/en/datetime.c ... format.php
Example #1 is what you are looking for, IMHO.

Re: Order text of database result

Posted: Wed Oct 05, 2011 4:43 am
by stijn22
Well, I made this:

Code: Select all

<?php
					 			require_once("connect.php"); //Voor de connectie met de database.

								$select = "SELECT * FROM agenda ORDER BY datum DESC LIMIT 2"; 
								$query = mysql_query($select)or die(mysql_error()); 
								
								while($list = mysql_fetch_object($query)){ 
								
														$date = date_create_from_format('Y-M-D', 'datum');

														echo "<b>Datum: </b>date_format($date, 'd-m-Y');<br/>"; 
														echo "<b>Locatie: </b>$list->locatie<br/>"; 
														echo "<b>Plaats: </b>$list->plaats<br/>"; 
														echo "<img src=\"images/line-leftbar.jpg\"><br/><br/>";
														}
						  ?>
I know this isn't working, because I get a fatal error on: $date = date_create_from_format('Y-M-D', 'datum');
I think I cannot put 'datum' there. I've tried this:

Code: Select all

<?php
					 			require_once("connect.php"); //Voor de connectie met de database.

								$select = "SELECT * FROM agenda ORDER BY datum DESC LIMIT 2"; 
								$query = mysql_query($select)or die(mysql_error()); 
								
								while($list = mysql_fetch_object($query)){ 
								
														$datum = $list->datum								
														$date = date_create_from_format('Y-M-D', '$datum');

														echo "<b>Datum: </b>date_format($date, 'd-m-Y');<br/>"; 
														echo "<b>Locatie: </b>$list->locatie<br/>"; 
														echo "<b>Plaats: </b>$list->plaats<br/>"; 
														echo "<img src=\"images/line-leftbar.jpg\"><br/><br/>";
														}
						  ?>
But that gives the error unexpected variable. What should I do?

Re: Order text of database result

Posted: Wed Oct 05, 2011 8:31 am
by Celauran
When using date_create_from_format, you need to specify the current format. The fatal error is probably a result of a mismatch there. Check how the dates are stored in your database.

Example:

Code: Select all

$foo = '2011-10-04 23:11:03';
$date = DateTime::createFromFormat('Y-m-d H:i:s', $foo);
echo $date->format('d/m/Y');

Re: Order text of database result

Posted: Wed Oct 05, 2011 10:49 am
by stijn22
My dates are in this format: 2011-10-04 or YYYY-MM-DD.

Re: Order text of database result

Posted: Wed Oct 05, 2011 11:19 am
by Celauran
stijn22 wrote:My dates are in this format: 2011-10-04 or YYYY-MM-DD.
Which is 'Y-m-d', not 'Y-M-D'. You have no time specified?

Re: Order text of database result

Posted: Wed Oct 05, 2011 1:54 pm
by stijn22
Nope, it's just only date. In the format Y-m-d as you said, and it as to be formatted to d-m-Y.

Re: Order text of database result

Posted: Wed Oct 05, 2011 1:56 pm
by Celauran
See my example above, then. Of course, you'll want to use $list['datum'] instead of $foo.

EDIT:

Code: Select all

$select = "SELECT * FROM agenda ORDER BY datum DESC LIMIT 2";
$query = mysql_query($select) or die(mysql_error());

while ($list = mysql_fetch_object($query))
{
    $date = date_create_from_format('Y-m-d', $list->datum);

    echo "<b>Datum: </b>date_format($date, 'd-m-Y');<br/>";
    echo "<b>Locatie: </b>$list->locatie<br/>";
    echo "<b>Plaats: </b>$list->plaats<br/>";
    echo "<img src=\"images/line-leftbar.jpg\"><br/><br/>";
}

Re: Order text of database result

Posted: Wed Oct 05, 2011 4:06 pm
by stijn22
I get a fatal error when I try it out:
Fatal error: Call to undefined function date_create_from_format() in /home/********/public_html/index.php on line 59

Does it say that it doesn't know the function of date formatting?

This is the current code I am using:

Code: Select all

<?php
							
							require_once("connect.php"); //Voor de connectie met de database.
							
							$select = "SELECT * FROM agenda ORDER BY datum DESC LIMIT 2";
							$query = mysql_query($select) or die(mysql_error());

							while ($list = mysql_fetch_object($query))
							
							{
    					$date = date_create_from_format('Y-m-d', $list->datum);

    					echo "<b>Datum: </b>date_format($date, 'd-m-Y');<br/>";
    					echo "<b>Locatie: </b>$list->locatie<br/>";
    					echo "<b>Plaats: </b>$list->plaats<br/>";
    					echo "<img src=\"images/line-leftbar.jpg\"><br/><br/>";
							}
              ?>

Re: Order text of database result

Posted: Wed Oct 05, 2011 4:27 pm
by Celauran
date_create_from_format was introduced in PHP 5.3.0. What version are you running?