[SOLVED] delimiting timestamp queries to show only date, no time

[greencoin]

whohoo! It's been a blast here... have learned a lot and with the guidance of phpfreaks members, have actually made something decent on my website!

 

My next question has me swamped - I can't find anything on google or even searching the forums that comes remotely close to being understandable by me. I have a MySQL timestamp(current timestamp) I'm using to date every entry. HOWEVER, I can't figure out how to get rid of the time portion and only display the date.

 

Any help is much appreciated O Ye Mighty PHP Gods...!! ~Rich

[chocopi]

Can you post your code, please ?

 

If your using DATETIME in MySQL then you can just explode the space and then you have your date and time separately

 

Eg:

 

<?php
$var = "01/01/2001 12:12:12";
list($date,$time) = explode(' ',$var);
echo $date; // should be 01/01/2001
?>

 

Hope that helps

 

~ Chocopi

[greencoin]

well, I really don't have any code to show. It's just a basic query that displays the rows using;

 

<td>" . $row['time'] . "</td>

 

thanks ~Rich

[chocopi]

is it stored in the database at a DATETIME though so it looks like 00/00/0000 00:00:00 ???

 

If it is then

 

you can use my code by

 

<?php

$var = $row['time'];
list($date,$time) = explode(' ',$var);
echo $date;

?>

 

if not please tell me what it is stored as

 

Hope it helps

 

~ Chocopi

[greencoin]

I'm sorry, I thought you saw in my original post - it's saved as a timestamp (current_timestamp). ~Rich

[chocopi]

Oh sorry i didnt notice, sorry 

 

But my code should still work so try the changes from my previous post 

 

And hopefully it will be fine

 

~ Chocopi

[greencoin]

OK - doesn't work..still displays the timestamp completely. here's the code to show it's applied;

 

mysql_select_db("GCsales") or die(mysql_error()); 
$data = mysql_query("SELECT * FROM GC_Tracker") 
or die(mysql_error()); 
Print "<table width=860 border=0 cellpadding=1 cellspacing=1 bgcolor=cccccc class=table_results>"; 
Print "<tr bgcolor=ccffcc>"; 
Print "<td width=35>Order</td>";
Print "<td width=150>Customer</td>";
Print "<td width=75>Phone</td>";
Print "<td width=50>Area</td>";
Print "<td width=75>City</td>";
Print "<td width=70>Amount</td>";
Print "<td width=100>1st Product</td>";
Print "<td>Created On</td></tr>";
while($info = mysql_fetch_array( $data )) 
{ 

Print "<tr bgcolor=ffffff>"; 
Print "<td>".$info['cid'] . "</td> ";
Print "<td>".$info['customer'] . "</td> "; 
Print "<td>".$info['phone'] . " </td>";
Print "<td>".$info['area'] . " </td>";
Print "<td>".$info['city'] . "</td> "; 
Print "<td>".$info['amount'] . " </td>";
Print "<td>".$info['prod1'] . " </td>";
$var = $info['time'];                                  // here it is
list($date,$time) = explode(' ',$var);               //here it is
Print "<td>".$var. " </td></tr>";                    //here it is
Print "</table>";
Print "<br>\n";
Print "<input type=Button name=printit value=print onclick=javascript:window.print();>\n"; 
?> 

[chocopi]

instead of using this

 

Print "<td>".$var. " </td></tr>"; //here it is

 

use

 

Print "<td>".$date." </td></tr>"; //here it is

 

as we have split $var into $date and $time and you want $date to be used

 

That should sort it

 

~ Chocopi

[Caesar]

If stored as a timestamp...you don't need to do a whole lot. In fact, works much nicer when it is a timestamp...

 

 

<?php

  $formatstamp = date("M d, Y",$info[time]);

  echo $formatstamp;

?>

 

That's it.

[greencoin]

OK, I've put your code in Caesar and it keeps spitting out 1969 Dec, 31 even though I've rearranged the "Y, M, d" fields. Here's my use of code;

....
Print "<td>".$info['prod1'] . " </td>";
$formatstamp = date("Y, M, d",$info[time]); 
Print "<td>".$formatstamp. " </td></tr>"; 
} 
Print "</table>";
Print "<br>\n";
.....

 

 

If stored as a timestamp...you don't need to do a whole lot. In fact, works much nicer when it is a timestamp...

 

 

<?php

  $formatstamp = date("M d, Y",$info[time]);

  echo $formatstamp;

?>

 

That's it.

 

Chocopi, your correction works like a champ! Thanks guys. ~Rich

[Caesar]

Hmmmglad you got it. Although if that didn't work..means your "timestamp" isn't really a timestamp. :-) It must be formatted or stored as an actual DATE.

[chocopi]

Im glad it worked

 

But you should try and use Caesar's as its neater and generally better

 

~ Chocopi

[levidyllan]

i sort it out within the SELECT command:

$query = "SELECT something, DATE_FORMAT(yourDate, ' %W %D %M %y') AS date FROM someTable";

 

and the only the date is printed with " $row['date'] ", which out puts like "Saturday 23rd May 07"

 

hope this helps

[greencoin]

MySQL says it's a timestamp with the default being current_timestamp.. I am using godaddy hosting MySQL 4.1. makes a difference?

 

and yes Chocopi I'd love to use Caesar's. I'm all for concision (yeah yeah, I know my code ain't great but as a n00b I gotta get things done before I make em perfect)

 

 

Hmmmglad you got it. Although if that didn't work..means your "timestamp" isn't really a timestamp. :-) It must be formatted or stored as an actual DATE.

[levidyllan]

not sure mine is v5 and its says timestamp with default as CURRENT_TIMESTAMP,  and it all seems to work for me

[levidyllan]

you could try:

 

 mysql_select_db("GCsales") or die(mysql_error()); 
$data = mysql_query("SELECT cid, customer, phone, area, DATE_FORMAT(time, ' %W %D %M %y') AS mydate, city,amount ,prod1 FROM GC_Tracker") 
or die(mysql_error()); 
Print "<table width=860 border=0 cellpadding=1 cellspacing=1 bgcolor=cccccc class=table_results>"; 
Print "<tr bgcolor=ccffcc>"; 
Print "<td width=35>Order</td>";
Print "<td width=150>Customer</td>";
Print "<td width=75>Phone</td>";
Print "<td width=50>Area</td>";
Print "<td width=75>City</td>";
Print "<td width=70>Amount</td>";
Print "<td width=100>1st Product</td>";
Print "<td>Created On</td></tr>";
while($info = mysql_fetch_array( $data )) 
{ 

Print "<tr bgcolor=ffffff>"; 
Print "<td>".$info['cid'] . "</td> ";
Print "<td>".$info['customer'] . "</td> "; 
Print "<td>".$info['phone'] . " </td>";
Print "<td>".$info['area'] . " </td>";
Print "<td>".$info['city'] . "</td> "; 
Print "<td>".$info['amount'] . " </td>";
Print "<td>".$info['prod1'] . " </td>";
Print "<td>".$info['mydate']. " </td></tr>";            //here it is
Print "</table>";
Print "<br>\n";
Print "<input type=Button name=printit value=print onclick=javascript:window.print();>\n"; 
?>

 

Although you would have to place the order of the fields in the SELECT to match your db, as I have no idea so just improvised :-)