Page 1 of 2

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

Posted: Wed May 09, 2007 2:38 am
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

Posted: Wed May 09, 2007 5:31 am
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'); ?>

Posted: Wed May 09, 2007 6:14 am
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

Posted: Wed May 09, 2007 6:22 am
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).

Posted: Wed May 09, 2007 6:26 am
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]

Posted: Wed May 09, 2007 6:45 am
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.

Posted: Wed May 09, 2007 7:03 am
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

Posted: Wed May 09, 2007 7:11 am
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!

Posted: Wed May 09, 2007 7:28 am
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

Posted: Wed May 09, 2007 7:34 am
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.

Posted: Wed May 09, 2007 7:51 am
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

Posted: Wed May 09, 2007 12:27 pm
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.

Posted: Thu May 10, 2007 6:33 am
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

Posted: Thu May 10, 2007 10:13 am
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?

Posted: Thu May 10, 2007 10:28 am
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.