MYSQL Date Format

[gfoisy]

Ok.. I am a complete novice in MySQL and PHP, so please excuse my ignorance.

I have a simple MYSQL database with a single table that has a field called "date", that is formatted as Date type. YYYY-MM-DD

 

I have generated a php page for my website using a PHP code generator, to display a MYSQL query of the data entered.

 

It works perfectly...... HOWEVER, I don't like the date displaying on my PHP webpage in the YYYY-MM-DD format, as it is difficult to read I think for users.

 

Here's the lines of code of my PHP page that I think are involved:

 

function sql_select()

{

  global $conn;

  $sql = "SELECT * FROM (SELECT `gigs`.`Date`, `gigs`.`Time`, `gigs`.`Description` FROM gigs WHERE (`gigs`.`Date` >=CURDATE()) ORDER BY `gigs`.`Date` ASC) subq";

  $res = mysql_query($sql, $conn) or die(mysql_error());

  return $res;

}

 

function sql_getrecordcount()

{

  global $conn;

  $sql = "SELECT COUNT(*) FROM (SELECT `gigs`.`Date`, `gigs`.`Time`, `gigs`.`Description` FROM gigs WHERE (`gigs`.`Date` >=CURDATE()) ORDER BY `gigs`.`Date` ASC) subq";

  $res = mysql_query($sql, $conn) or die(mysql_error());

  $row = mysql_fetch_assoc($res);

  reset($row);

  return current($row);

} ?>

 

 

 

Can someone please help me and tell me exactly how I would have to change this above code to format the display of the "date" field in a MM-DD-YYYY format?  Remember, I am clueless on MySQL and PHP for the most part, and I have researched this and tried several suggestions I've found, but I'm missing something everytime.  I've found that it should be done in the MySQL query first off as the best option, then I hear not to do that and to code it in PHP and convert it.... etc...  The above code simply pulls three fields, with the date Ascending and where the date records are >=CURDATE.  That's it.

 

HELP.  PLEASE.

[Mchl]

Use these two functions: strtotime and date

 

$formattedDate = date("your format",strtotime($row['Date']));

 

Read manual entry for date for details on formats

[gfoisy]

So where exactly do I place this line of code in my php page?  I know I'd have to customize the "your format" part.

 

 

$formattedDate = date("your format",strtotime($row['Date']));

 

[gfoisy]

I tried it like this and it didn't work:

 

function sql_select()

{

  global $conn;

  $sql = "SELECT * FROM (SELECT `gigs`.`Date`, `gigs`.`Time`, `gigs`.`Description` FROM gigs WHERE (`gigs`.`Date` >=CURDATE()) ORDER BY `gigs`.`Date` ASC) subq";

  $formattedDate = date("D M Y",strtotime($row['Date']));

  $res = mysql_query($sql, $conn) or die(mysql_error());

  return $res;

}

 

function sql_getrecordcount()

{

  global $conn;

  $sql = "SELECT COUNT(*) FROM (SELECT `gigs`.`Date`, `gigs`.`Time`, `gigs`.`Description` FROM gigs WHERE (`gigs`.`Date` >=CURDATE()) ORDER BY `gigs`.`Date` ASC) subq";

  $formattedDate = date("D M Y",strtotime($row['Date']));

  $res = mysql_query($sql, $conn) or die(mysql_error());

  $row = mysql_fetch_assoc($res);

  reset($row);

  return current($row);

 

[Mchl]

The first function returns a mysql resource, so the modification would be outside of it.

 

The second function would be

 

function sql_getrecordcount()
{
  global $conn;
  $sql = "SELECT COUNT(*) FROM (SELECT `gigs`.`Date`, `gigs`.`Time`, `gigs`.`Description` FROM gigs WHERE (`gigs`.`Date` >=CURDATE()) ORDER BY `gigs`.`Date` ASC) subq";
  $res = mysql_query($sql, $conn) or die(mysql_error());
  $row = mysql_fetch_assoc($res);
  $row['Date'] = date("D M Y",strtotime($row['Date']));
  reset($row);
  return current($row);
}

 

You could also use MySQL's DATE_FORMAT() function to modify your queries instead.

 

Like this:

 

$sql = "SELECT COUNT(*) FROM (SELECT DATE_FORMAT(`gigs`.`Date`,'%d-%m-%Y') AS `Date`, `gigs`.`Time`, `gigs`.`Description` FROM gigs WHERE (`gigs`.`Date` >=CURDATE()) ORDER BY `gigs`.`Date` ASC) subq";

[gfoisy]

Hi Mchl,

 

This makes perfect sense!  And it's exactly the type of help I needed.  Unfortunately when I try either of the two methods separately, neither makes the format change from the YYYY-MM-DD format.  The page displays without error, but it doesn't change the format.

 

The test page is www.theroasthouse.com/calendar2.php

 

I left the first section of code alone as you mentioned, and only modified the second section exactly as you entered.

 

Thoughts?

[Mchl]

Probably the date is taken from unmodified code. You can modify query from the first function just as the one in second.

[gfoisy]

Modified first function only with date_format approach.

The tried first and second function with date_format approach.

 

This time both attempts show the page but with no data displaying.  Just what appears to be an empty table.  There are 4 valid records that normally display.

 

??

[Mchl]

Please paste your modified code

[gfoisy]

Here it is:

 

function sql_select()

{

  global $conn;

  $sql = "SELECT COUNT(*) FROM (SELECT DATE_FORMAT(`gigs`.`Date`,'%d-%m-%Y') AS `Date`, `gigs`.`Time`, `gigs`.`Description` FROM gigs WHERE (`gigs`.`Date` >=CURDATE()) ORDER BY `gigs`.`Date` ASC) subq";

  $res = mysql_query($sql, $conn) or die(mysql_error());

  return $res;

}

 

function sql_getrecordcount()

{

  global $conn;

  $sql = "SELECT COUNT(*) FROM (SELECT DATE_FORMAT(`gigs`.`Date`,'%d-%m-%Y') AS `Date`, `gigs`.`Time`, `gigs`.`Description` FROM gigs WHERE (`gigs`.`Date` >=CURDATE()) ORDER BY `gigs`.`Date` ASC) subq";

  $res = mysql_query($sql, $conn) or die(mysql_error());

  $row = mysql_fetch_assoc($res);

  reset($row);

  return current($row);

 

[gfoisy]

Do you want the entire php page code?

[Mchl]

Note that you originally had

SELECT * FROM

in the first function

not

SELECT COUNT(*) FROM

 

it should stay this way