Flash CS4 Videos Finally Out!

edoplaza · 2009-10-31 14:32:30

edoplaza
Member
Offline

From: Caracas, Venezuela
Registered: 2009-01-01
Posts: 97

Topic: Change the date format Mysql - php

Hello,

I'd like to change the date format from my DB (yyyy-mm-dd) to dd-mm-yyyy using php.

Here is the url:

http://ww w.germanmarcano.com/eventos/indexEnglish.php

Here is the code:

<?php require_once('../Connections/connAdmin.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$maxRows_RsEventos = 10;
$pageNum_RsEventos = 0;
if (isset($_GET['pageNum_RsEventos'])) {
  $pageNum_RsEventos = $_GET['pageNum_RsEventos'];
}
$startRow_RsEventos = $pageNum_RsEventos * $maxRows_RsEventos;

mysql_select_db($database_connAdmin, $connAdmin);
$query_RsEventos = "SELECT * FROM eventos ORDER BY fecha DESC";
$query_limit_RsEventos = sprintf("%s LIMIT %d, %d", $query_RsEventos, $startRow_RsEventos, $maxRows_RsEventos);
$RsEventos = mysql_query($query_limit_RsEventos, $connAdmin) or die(mysql_error());
$row_RsEventos = mysql_fetch_assoc($RsEventos);

if (isset($_GET['totalRows_RsEventos'])) {
  $totalRows_RsEventos = $_GET['totalRows_RsEventos'];
} else {
  $all_RsEventos = mysql_query($query_RsEventos);
  $totalRows_RsEventos = mysql_num_rows($all_RsEventos);
}
$totalPages_RsEventos = ceil($totalRows_RsEventos/$maxRows_RsEventos)-1;
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"><!-- InstanceBegin template="/Templates/MainTemplateE.dwt.php" codeOutsideHTMLIsLocked="false" -->
<head>

<link type="text/css" rel="stylesheet" href="/floatbox/floatbox.css" />

<script type="text/javascript" src="/floatbox/floatbox.js"></script>
<script type="text/javascript">
var gaJsHost = (("https:" == document.location.protocol) ? "https://ssl." : "http://www.");
document.write(unescape("%3Cscript src='" + gaJsHost + "google-analytics.com/ga.js' type='text/javascript'%3E%3C/script%3E"));
</script>
<script type="text/javascript">
try {
var pageTracker = _gat._getTracker("UA-11331216-1");
pageTracker._trackPageview();
} catch(err) {}</script>


<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<!-- InstanceBeginEditable name="doctitle" -->
<title>Events</title>
<!-- InstanceEndEditable -->
<link href="../css/main.css" rel="stylesheet" type="text/css" />
<!-- InstanceBeginEditable name="head" --><!-- InstanceEndEditable -->
</head>

<body>

<div id="banner">
    <div id="bannercentered">
         <div id="mainNav">
        
        <ul class="mainMenu1">
            
            <li><a href="../indexEnglish.php">Home</a></li>
          <li><a href="../biografia/indexEnglish.php">Biography</a></li>
         <li><a href="../juicioscriticos/indexEnglish.php">Critics</a></li>
        </ul>
        <ul class="mainMenu1">
            
            
            <li><a href="../repertorio/indexEnglish.php">Repertory</a></li>
          <li><a href="../grabaciones/indexEnglish.php">CDs</a></li>
          <li><a href="../grabacionesvivo/indexEnglish.php">Live Recordings</a></li>
        </ul>
        
        <ul class="mainMenu1">
            
            
            <li><a href="../videos/indexEnglish.php">Videos</a></li>
             <li><a href="../publicaciones/indexEnglish.php">Publications</a></li>
          <li><a href="../galeria/indexEnglish.php">Gallery</a></li>
          
         
        </ul>
        
         <ul class="mainMenu1">
            
            
       
          <li><a href="indexEnglish.php">Events</a></li>
          <li><a href="../contacto/indexEnglish.php">Contact</a></li>
          
         
        </ul>
      </div>
    </div>
</div>

<div id="content"><!-- InstanceBeginEditable name="contenido" -->
  <div id="contentcentered">
   
   <h1>Events</h1>
   
   <?php do { ?>
     <div class="eventosind">
       
       
       <p class="tituloeventos"><?php echo $row_RsEventos['titulo']; ?></p>
        
         
      
          
        <table class="tablaeventosinicio">
                   <colgroup>
                     <col class="coleventos1" />
                     <col class="coleventos2" />
                     </colgroup>
                   <tr>
                     <td class="negrita">Date:</td>
                        <td><?php echo $row_RsEventos['fecha']; ?></td>
                        </tr>
                   <tr>
                     <td class="negrita">Place:</td>
                        <td><?php echo $row_RsEventos['lugar']; ?></td>
                        </tr>
                   <tr>
                     <td class="negrita">Time:</td>
                        <td><?php echo $row_RsEventos['hora']; ?></td>
                        </tr>
                 </table>
                 <p class="pequena"><?php echo $row_RsEventos['comentarios']; ?></p>
     </div>
     <?php } while ($row_RsEventos = mysql_fetch_assoc($RsEventos)); ?></div>
<!-- InstanceEndEditable --></div>

<div id="footer">
    <div id="footercentered">
    <div id="copyright">
    <a href="../indexEnglish.php">home</a> | <a href="../administracion/loginprincipal.php">administration</a> |  <a href="../contacto/indexEnglish.php">contact</a> &nbsp;&nbsp;&nbsp;Copyright © Germán Marcano 2009<br />
    </div>
  </div>
</div>
</body>
<!-- InstanceEnd --></html>
<?php
mysql_free_result($RsEventos);
?>

Thanks

Eduardo

Last edited by edoplaza (2009-10-31 14:33:17)

Wickham · 2009-10-31 16:34:38

Wickham
Advanced Member
Offline

Gender: Male
From: Salisbury UK
Registered: 2008-12-20
Posts: 1,231

Re: Change the date format Mysql - php

All the date codes are here:-
http://php.net/manual/en/function.date.php

Use d-m-Y for dd-mm-yyyy

Wickham · 2009-11-01 02:13:38

Wickham
Advanced Member
Offline

Gender: Male
From: Salisbury UK
Registered: 2008-12-20
Posts: 1,231

Re: Change the date format Mysql - php

EDIT - go to my edit at the bottom of the post.

No one else has answered so I will offer a little more information, but my PHP and MySQL knowledge is not much; I was hoping falkencreative would answer the question properly.

Your page display has this code
<td class="negrita">Date:</td>
<td><?php echo $row_RsEventos['fecha']; ?></td>

which just repeats what is in the database field 'fecha', so perhaps one solution is to change the format in the database, not in the PHP code which displays it on the page.

If you look at this example:-
http://www.wickham43.net/formphptomysql.php

You will see a form for submission of data which has a date already inserted with a date format <? echo date("d M Y"); ?> and the database stores this as text (Field: Date; Type: text) and the display of the data in the table at the bottom of the page just shows the text of the date that the database stored. You can see from the dates in the display that the format changes half way down because I changed the date format in the top submission form.

My Date is stored as text but you probably have the database storing date in a date format. You don't show how you get the dates into the database; either change the date format in the form you use to input into the database or go into the database and change the date format there. MySQL date is always stored in the YYYY-MM-DD format and it's complicated to reformat it so it may be easier to change the database Type to Text and then the PHP for the page should output the same text as the database.

Editing the Type for the Field in MySQL is relatively simple and you would then have to edit the previous database entries manually but future date entries as text would be repeated as text by the PHP code.

I expect that there is a way to take the date format from the database and change it to another format with PHP for the table display but falkencreative will have to answer that.

EDIT: I've been experimenting and this code worked for me:-

date('d-m-Y', strtotime($row['fecha'])) but I was testing with a different database and a different form

so where you have
<td><?php echo $row_RsEventos['fecha']; ?></td>

in your PHP file I think you need

Try it.

Last edited by Wickham (2009-11-01 11:29:11)

edoplaza · 2009-11-01 19:26:48

edoplaza
Member
Offline

From: Caracas, Venezuela
Registered: 2009-01-01
Posts: 97

Re: Change the date format Mysql - php

Hi Wickham,

Thanks for your response. I haven't had the time to work today, but tomorrow I'll try what you suggest. I'll write a very simple code with just one record to isolate the problem and test different approaches. Also I bought a book with lots of information. I'm sure this is a common issue, it shouldn't be that hard.

Edited:

Ok, I uploaded a very simple page with one record:

http://www.germanmarcano.com/datetest.php

Code:

<?php require_once('Connections/connAdmin.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

mysql_select_db($database_connAdmin, $connAdmin);
$query_Rsprueba = "SELECT * FROM eventos";
$Rsprueba = mysql_query($query_Rsprueba, $connAdmin) or die(mysql_error());
$row_Rsprueba = mysql_fetch_assoc($Rsprueba);
$totalRows_Rsprueba = mysql_num_rows($Rsprueba);
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<?php echo $row_Rsprueba['fecha']; ?>
</body>
</html>
<?php
mysql_free_result($Rsprueba);
?>

I will try to work on this file and see what happens

Regards,

Eduardo

Last edited by edoplaza (2009-11-01 22:02:34)

edoplaza · 2009-11-05 08:16:21

edoplaza
Member
Offline

From: Caracas, Venezuela
Registered: 2009-01-01
Posts: 97

Re: Change the date format Mysql - php

Finally, I found a solution:

I was trying to use the DATE_FORMAT function, but it didn't work (still don't know why)

I then changed this:

echo $row_RsEvents['column_date'];

Into this:

$formatted_date= $row_RsEvents['column_date'];
echo date("d-m-Y",strtotime($formatted_date));

and it works perfectly

Cheers,

Eduardo

Edit:

Sorry Wickham, I didn't see your last edit. I am glad we both came to the same solution! (but yours is better than mine )

Last edited by edoplaza (2009-11-05 08:20:45)

Flash CS4 Videos Finally Out!

Change the date format Mysql - php

Posts [ 5 ] - Views [ 376 ]

1 Topic by edoplaza 2009-10-31 14:32:30

Topic: Change the date format Mysql - php

2 Reply by Wickham 2009-10-31 16:34:38

Re: Change the date format Mysql - php

3 Reply by Wickham 2009-11-01 02:13:38

Re: Change the date format Mysql - php

4 Reply by edoplaza 2009-11-01 19:26:48

Re: Change the date format Mysql - php

5 Reply by edoplaza 2009-11-05 08:16:21

Re: Change the date format Mysql - php