Formatting the Date and yes/no in MySQL [Solved]
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]
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
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.
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
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
This could be written as
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';
}
?>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
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
Another option would be to use a fairly simple preg_replace.
Something like
Untested but should be something like that (previous commands would be needed if you required text such as month names).
Something like
Code: Select all
$var='2007-05-24';
echo preg_replace('/([0-9]+)-([0-9]+)-([0-9])+/','$3-$2-$1',$var);-
influenceuk
- Forum Commoner
- Posts: 42
- Joined: Tue May 08, 2007 7:48 am
- Location: London, UK
Everah | Please use
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 thisCode: 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]- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
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. or to keep it more as you had
(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.
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>";
?>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>";
?>implode is extremely useful when trying to concatenate arrays.
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
to format my dates
Apart from that I usually use something along the lines of
Code: Select all
$date = date("d/m/Y",str_to_time($date_col));-
influenceuk
- Forum Commoner
- Posts: 42
- Joined: Tue May 08, 2007 7:48 am
- Location: London, UK
-
influenceuk
- Forum Commoner
- Posts: 42
- Joined: Tue May 08, 2007 7:48 am
- Location: London, UK
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
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
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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
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.
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`;Thanks Everah for using a mysql function...I was getting worried...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, themysql_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.Code: Select all
SELECT DATE_FORMAT(`date_field`, '%d-%m-%Y') as `new_date_field` FROM `my_table`;
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`;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
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA