Formatting the Date and yes/no in MySQL [Solved]

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

influenceuk
Forum Commoner
Posts: 42
Joined: Tue May 08, 2007 7:48 am
Location: London, UK

Formatting the Date and yes/no in MySQL [Solved]

Post by influenceuk »

Hi just wondering how i can format the date in MySQL.
Currently it displays as 2007-05-09, I would like to have it display like - 09-05-2007 (DD-MM-YYYY).

Also a coloum that i am using is currently showing -1 (yes) and 0 (no), how do i go about formatting this to simply display yes or no.

All help will be much appreciated.

Cheers
Last edited by influenceuk on Thu May 10, 2007 10:54 am, edited 1 time in total.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

strftime may be of use although you will probably need to make the php time from yours..
mktime and strtotime may also be of use. I am unsure if there are any MySQL specific commands.

As for writing Yes or No a simple if statement should do it

Code: Select all

<?php
  if ($row['column']=='-1') {
    echo 'Yes';
  } else {
    echo 'No';
  }
?>
This could be written as

Code: Select all

<?php echo ($row['column']==-1 ? 'Yes' : 'No'); ?>
influenceuk
Forum Commoner
Posts: 42
Joined: Tue May 08, 2007 7:48 am
Location: London, UK

Post by influenceuk »

You are a star! I will give these are try and let you know how it goes.

Still am not sure about the date issue. I am sure there is a way to sort it. :)

Once again, thanks for the help
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

Another option would be to use a fairly simple preg_replace.
Something like

Code: Select all

$var='2007-05-24';
echo preg_replace('/([0-9]+)-([0-9]+)-([0-9])+/','$3-$2-$1',$var);
Untested but should be something like that (previous commands would be needed if you required text such as month names).
influenceuk
Forum Commoner
Posts: 42
Joined: Tue May 08, 2007 7:48 am
Location: London, UK

Post by influenceuk »

Everah | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hi,
I currently have this and it displays the coloum from the DB.

echo $myrow["Plays"];

I have tried to edit it so that its like what you advised, with no avail.

Where should i post your code roughly?
This is the code i have so far... (its very basic   )
i have taken the top bit off as i dont think its needed for this

Code: Select all

<?php
$result = mysql_query("SELECT * FROM DATABASE",$db);
echo "<TABLE>";
echo"<TR><TD><B>C1</B><TD><B>C2</B><TD><B>C3</B><TD><B>C4</B></TR>";
while ($myrow = mysql_fetch_array($result))
{
echo "<TR><TD>";
echo $myrow["C1"];
echo "<TD>";
echo $myrow["C2"];
echo "<TD>";
echo $myrow["C3"];
echo "<TD>";
echo $myrow["C4"];
}
echo "</TABLE>";
?>

Everah | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

Please use the syntax tags... Makes thing a lot easier to read...

I prefer to use mysql_fetch_assoc rather than mysql_fetch_array as it produces a the array indexed not by number but by column name. This makes it far easier to see what is going on.

Code: Select all

$result = mysql_query("SELECT * FROM databasetablename",$db);
echo "<table>\n";
echo"<tr><td><b>C1</b><td><b>C2</b><td><b>C3</b><td><b>C4</b></tr>\n";
while ($myrow = mysql_fetch_assoc($result))
{
  $myrow['datecol']=preg_replace('/([0-9]+)-([0-9]+)-([0-9])+/','$3-$2-$1',$myrow['datecol']);
  $myrow['Plays']=($myrow['Plays']==-1 ? 'Yes' : 'No');
  echo "<tr><td>".implode('</td><td>',$myrow)."</td></tr>\n";
}
echo "</table>";
?>
or to keep it more as you had

Code: Select all

<?php
$result = mysql_query("SELECT * FROM databasetablename",$db);
echo "<table>\n";
echo"<tr><td><b>C1</b><td><b>C2</b><td><b>C3</b><td><b>C4</b></tr>\n";
while ($myrow = mysql_fetch_assoc($result))
{
  $myrow['datecol']=preg_replace('/([0-9]+)-([0-9]+)-([0-9])+/','$3-$2-$1',$myrow['datecol']);
  $myrow['Plays']=($myrow['Plays']==-1 ? 'Yes' : 'No');
  echo "<tr><td>{$myrow['col1name']}</td><td>{$myrow['col2name']}</td><td>{$myrow['Plays']}</td><td><{$myrow['datecol']}/td></tr>\n";
}
echo "</table>";
?>
(I am assuming the column you want is called 'datecol' in the database and is the 4th column and Plays in third) . Adjust column names/positions as necessary.
implode is extremely useful when trying to concatenate arrays.
User avatar
phpdevuk
Forum Contributor
Posts: 220
Joined: Mon Jul 04, 2005 5:31 am
Location: UK
Contact:

Post by phpdevuk »

I found this helpful with mysql dates http://dev.mysql.com/doc/refman/5.0/en/ ... tr-to-date

Apart from that I usually use something along the lines of

Code: Select all

$date = date("d/m/Y",str_to_time($date_col));
to format my dates
influenceuk
Forum Commoner
Posts: 42
Joined: Tue May 08, 2007 7:48 am
Location: London, UK

Post by influenceuk »

Cheers you guys, i will give this a try 2nite when i get home.

I did read that MySQL site thing last nite, but was a lil unsure on what to do.

Sorry bout the code bit, i wondered how u did that, then i realised there is a button up there!
influenceuk
Forum Commoner
Posts: 42
Joined: Tue May 08, 2007 7:48 am
Location: London, UK

Post by influenceuk »

CoderGoblin you rock! it worked, the date and the yes/no bit!!

Just need to work out how to format the table now and make it look nice! but i guess thats just standard PHP and HTML now, isn't it?

thanks for all your help
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

Please to be of help. CSS may also be of use. You may want to look at w3schools.com if you need information on both HTML and CSS. I often use it to double check things.
influenceuk
Forum Commoner
Posts: 42
Joined: Tue May 08, 2007 7:48 am
Location: London, UK

Post by influenceuk »

I'm not too bad with HTML, CSS is another story lol I been playing with CSS and seem to be getting the hang of it.

Me thinks the downloading to more eBooks is in order lol :)

Cheers for the help
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

MySQL Date and Time functions
DATE_FORMAT()
I would do this at the data level, since the DB would process this faster than having to run it through PHP. And it is selected in the format you want already at that point. Also, the

Code: Select all

SELECT DATE_FORMAT(`date_field`, '%d-%m-%Y') as `new_date_field` FROM `my_table`;
mysql_fetch_array() function does return results in one of three formats: Numerically indexed, Associatively indexed and indexed by both. Read the PHP manual page on mysql_fetch_array() for more information.
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

Everah wrote:MySQL Date and Time functions
DATE_FORMAT()
I would do this at the data level, since the DB would process this faster than having to run it through PHP. And it is selected in the format you want already at that point. Also, the

Code: Select all

SELECT DATE_FORMAT(`date_field`, '%d-%m-%Y') as `new_date_field` FROM `my_table`;
mysql_fetch_array() function does return results in one of three formats: Numerically indexed, Associatively indexed and indexed by both. Read the PHP manual page on mysql_fetch_array() for more information.
Thanks Everah for using a mysql function...I was getting worried...

I recommend...you use the sql statement to format the date and not php...Also...For the yes/no question try something like this.

Code: Select all

SELECT IF(`myfield` = 0,'No','Yes' ) as `new_date_field` FROM `my_table`;
More information on control flow functions in mysql:

http://dev.mysql.com/doc/refman/4.1/en/ ... tions.html
influenceuk
Forum Commoner
Posts: 42
Joined: Tue May 08, 2007 7:48 am
Location: London, UK

Post by influenceuk »

hi guys, haveing a few issues with the MySQL side of this.

how am i to enter the code you have provided?
I read on the MySQL site and other sites you need to connect via SSH, well to be honest i have tried todo this but not suceeded :(

Any suggestions? is there any way to do this using PHPmyadmin?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

What interface are you using for mysql (phpMyAdmin, SQLYog, Navicat)? You can do it with a SQL query through PHP or using an admin tool like those I just asked about.
Post Reply