Order text of database result

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

stijn22
Forum Commoner
Posts: 43
Joined: Sat Aug 13, 2011 10:14 am

Order text of database result

Post 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 :)
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Order text of database result

Post 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.
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: Order text of database result

Post 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...
stijn22
Forum Commoner
Posts: 43
Joined: Sat Aug 13, 2011 10:14 am

Re: Order text of database result

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Order text of database result

Post 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)
There are 10 types of people in this world, those who understand binary and those who don't
stijn22
Forum Commoner
Posts: 43
Joined: Sat Aug 13, 2011 10:14 am

Re: Order text of database result

Post by stijn22 »

Ok, I get it. Can you show me how that's done? :roll:
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Order text of database result

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
stijn22
Forum Commoner
Posts: 43
Joined: Sat Aug 13, 2011 10:14 am

Re: Order text of database result

Post 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?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Order text of database result

Post 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');
stijn22
Forum Commoner
Posts: 43
Joined: Sat Aug 13, 2011 10:14 am

Re: Order text of database result

Post by stijn22 »

My dates are in this format: 2011-10-04 or YYYY-MM-DD.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Order text of database result

Post 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?
stijn22
Forum Commoner
Posts: 43
Joined: Sat Aug 13, 2011 10:14 am

Re: Order text of database result

Post 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.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Order text of database result

Post 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/>";
}
stijn22
Forum Commoner
Posts: 43
Joined: Sat Aug 13, 2011 10:14 am

Re: Order text of database result

Post 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/>";
							}
              ?>
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Order text of database result

Post by Celauran »

date_create_from_format was introduced in PHP 5.3.0. What version are you running?
Post Reply