PHP extracting mysql databse date

User 126492 Photo


Ambassador
1,506 posts

Hi all.

Are there anyone who is clever at php used to extract data from a mysql database and place it into a webpage.

Actually I am ok extracting from a database but I have run into a problem and cannot figure out how to solve it.

The date stored in the database is in the format yyy-mm-dd I want to convert it to the UK format of dd-mm-yyy.
Below is the php I am using to extract the info but I have tried a couple of but the conversion doesn't work.


<?php
// ------------------------ database connection
// VARS for connection
$vDBHost = "localhost";
$vDBUser = "*********";
$vDBPass = "*********";
$vDBName = "**********";
// Open Db Connection
function OpenDB()
{
global $vDBHost;
global $vDBUser;
global $vDBPass;
global $vDBName;
// Connect to server
$con = mysql_connect("$vDBHost","$vDBUser","$vDBPass");
if(!$con)
{
echo mysql_errno() . ": " . mysql_error() . "<br>\n";
echo "Could not connect to database server!<br>\n";
exit;
}
if(!mysql_select_db($vDBName, $con))
{
echo mysql_errno() . ": " . mysql_error() . "<br>\n";
echo "Could not connect to database!<br>\n";
exit;
}
return $con;
}

// Connect to DB
$dbcon = OpenDB();

// -------------------------- fetch data
echo "<table width=\"850\" align=\"center\">";
echo "<tr><th>Date</th><th></th><th>Name</th><th>Scooter</th><th>Class</th><th>Event</th><th>Speed</th></tr>";
// select all the records
$result = mysql_query("SELECT * FROM speeds WHERE Reg='Y' AND speed > 0.0 ORDER BY speed DESC",$dbcon);
// for each record found - while loop
while ($arow = mysql_fetch_array($result)) {
// load to vairables
$field1 = $arow["created_at"];
$field2 = $arow["Country"];
$field3 = $arow["Name"];
$field4 = $arow["Scooter"];
$field5 = $arow["Class"];
$field6 = $arow["Event"];
$field7 = $arow["Speed"];
$field8 = $arow["Reg"];

// -------------------------- display data
// output rows
echo "<tr><td>$field1</td><td>$field2</td><td>$field3</td><td>$field4</td><td>$field5</td><td>$field6</td><td align='right'>$field7</td>";
} // end while loop
echo "</table>";
?>


I think there is a function I need to add to above but I can't figure it out. Click the link to see the page the info is on and you will see a couple of the date entries formatted in US format and not UK format as I would like. Doesn't matter about the rest of the dates, I can edit those in the database later

http://www.100mphscooter.com/speeds2.html

Thanks in advance to anyone that can help.

Jim
---------------------------
User 10077 Photo


Senior Advisor
1,042 posts

There are probably much more efficient ways to do this, but here's a function that you can include that will allow you to have multiple date conversions. I've left room for more types of conversions.

/* ---------------------------------------------
MySQL DATE CONVERSION
$datetoconvert - date in MySQL format yyyy-mm-dd
$datestyle - style in which you want the date returned

example:
convertmysqldate('2017-01-17', 'dayfirstslash')
returns 17-01-2017
--------------------------------------------- */

function convertmysqldate($datetoconvert, $datestyle){
$thedate = $datetoconvert;
$return_fulldate = "";

$month = substr($thedate,5,2);
$day = substr($thedate,8,2);
$year = substr($thedate,0,4);
$monthname = monthnames($month);

if($datestyle = 'monthfirstslash'){
$return_fulldate = $month.'/'.$day.'/'.$year;

}elseif($datestyle = 'monthfirsthyphen'){
$return_fulldate = $month.'-'.$day.'-'.$year;

}elseif($datestyle = 'dayfirstslash'){
$return_fulldate = $day.'/'.$month.'/'.$year;

}elseif($datestyle = 'dayfirsthyphen'){
$return_fulldate = $day.'-'.$month.'-'.$year;

}elseif($datestyle = 'fulldate'){
$return_fulldate = $monthname.' '.$day.', '.$year;

}elseif($datestyle = ''){
$return_fulldate = '';

}elseif($datestyle = ''){
$return_fulldate = '';

}elseif($datestyle = ''){
$return_fulldate = '';

}else{
$return_fulldate = '';

}

return $return_fulldate;
}

function monthnames($month){
$returnmonth = '';

if($month == 1){$returnmonth = 'January';
}elseif($month == 2){$returnmonth = 'February';
}elseif($month == 3){$returnmonth = 'March';
}elseif($month == 4){$returnmonth = 'April';
}elseif($month == 5){$returnmonth = 'May';
}elseif($month == 6){$returnmonth = 'June';
}elseif($month == 7){$returnmonth = 'July';
}elseif($month == 8){$returnmonth = 'August';
}elseif($month == 9){$returnmonth = 'September';
}elseif($month == 10){$returnmonth = 'October';
}elseif($month == 11){$returnmonth = 'November';
}else{$returnmonth = 'December';}


return $returnmonth;
}
How-To Instructions for Coffeecup Users (tutorials, examples, and downloads)
http://usconsumernet.net/help-for-coffe … -users.php

Personal & Specialized Help for Coffeecup Users
Email me at cchelp@uscni.org or call 865-687-7698
User 126492 Photo


Ambassador
1,506 posts

Brian

I was looking for a simpler way tha you have offered.

I think there is just a 3 line piece of code I could insert to convert the US format to the UK format.

Thanks for offering though.
Jim
---------------------------
User 379556 Photo


Registered User
933 posts

This isn't a direct answer to the problem posed, but may help a little.

The mentioned three-line piece of code may not help because
(a) The first post said 'The date stored in the database is in the format yyy-mm-dd. I want to convert it to the UK format of dd-mm-yyy'.
(b) The latest post says 'I think there is just a 3 line piece of code I could insert to convert the US format to the UK format'.
(c) I believe that the US format is month-day-year rather than the year-month-day mentioned in the first post.

There is diversity of date presentation among nations (USA mm-dd-yyyy; China, Korea etc. yyyy-mm-dd; most other countries dd-mm-yyyy). In such circumstances I might be tempted not to convert the date at all, but simply to clarify what order is being shown on the web page by use of an appropriate heading to the date column.

Frank
User 126492 Photo


Ambassador
1,506 posts

Frank

The date has to be in the UK format.

There is a way to do it but I just cannot find it.
Jim
---------------------------
User 379556 Photo


Registered User
933 posts

I understand. I think it will still be necessary for you to know for that search whether the conversion is to be from year-month-day format or from the US format.

You have probably already considered the last three suggestions here.

Frank
User 10077 Photo


Senior Advisor
1,042 posts

Frank Cook wrote:
I understand. I think it will still be necessary for you to know for that search whether the conversion is to be from year-month-day format or from the US format.

Frank, If it is stored in standard MySQL format, it is yyyy-mm-dd.

Jim, If I'm not mistaken, $field1 is your field with the date in it. Here's a 4-line version:

$month = substr($field1,5,2);
$day = substr($field1,8,2);
$year = substr($field1,0,4);
$ukdate = $day.'-'.$month.'-'.$year;


and a 1-line version.

$ukdate = substr($field1,8,2).'-'.substr($field1,5,2).'-'.substr($field1,0,4);
How-To Instructions for Coffeecup Users (tutorials, examples, and downloads)
http://usconsumernet.net/help-for-coffe … -users.php

Personal & Specialized Help for Coffeecup Users
Email me at cchelp@uscni.org or call 865-687-7698
User 126492 Photo


Ambassador
1,506 posts

Thanks Brian

That looks the sort of thing am looking for.

Where abouts in my code would I put either of the 2 versions?
Jim
---------------------------
User 78051 Photo


Registered User
199 posts

wouldnt it just be easier to use an echo statement that gets the value from the database and converts it - like this:
this is if youre using mysqli:
<?php echo(date("d M Y",strtotime($RecordsetName->getColumnVal("ColumnName")))); ?>

this is if youre using the older mysql:
<?php echo(date('d M y',strtotime($row_RecordsetName['ColumnName']))); ?>


This woud return the date as dd Mmm yyyy (eg 16 May 2017). If you wanted to put / in between, just edit the echo statement "d / M / Y",strtotime

for more formatting check out http://php.net/manual/en/function.date.php
User 126492 Photo


Ambassador
1,506 posts

I have decided to add a new column to the database and instead of setting it as a Date field I will set it as text, there will only be one person entering the info plus the field will not be used as a search.

It's far easier than going through all this.
Jim
---------------------------

Have something to add? We’d love to hear it!
You must have an account to participate. Please Sign In Here, then join the conversation.