Timestamp

misunderstood · #1 29-Dec-2003, 09:08

Ok JDS its my day for questions

I have a time stamp in my news table. If I recall 1 line it works ok using

PHP Code:


$updatedpage = mysql_result($result2,$i,"pageupdate");
$yeara = substr($updatedpage, 0, 4);
$montha = substr($updatedpage, 4, 2);
$daya = substr($updatedpage, 6, 2);
$houra = substr($updatedpage, 8, 2);
$minutea = substr($updatedpage, 10, 2);
$seconda = substr($updatedpage, 12, 2);
$newupdated=date('l, j F, Y. g:i A', mktime($houra, $minutea, $seconda, $montha, $daya, $yeara));

to translate.

However if I recall all the news items using

PHP Code:


$sql2= "select * from news where newsid ='$newsid' ";
 $result2=mysql_query($sql2,$connection) or die("something is wrong");
$row2=mysql_fetch_array($result2);

It all goes pear shaped.

How can I include the top code to translate my timestamp on all the news items?
I now know I cannot mix my code (thanks to you answering my last posting) and as soon as I look at a manual I get dyslexic

(no offence meant).
I prefer the trial and error (lots of error) and then the ask JDS route.

JdS · #2 29-Dec-2003, 09:49

Describe the `news` table if you can, so we can refer to it when we need to.

Also, you will need to extend...

Code:

SELECT * ...

and actually list all the field names before I can suggest what you can try... e.g.

Code:

SELECT newsid, field1, field2... etc.

misunderstood · #3 29-Dec-2003, 13:05

My news table structure is
`newsid` int(11) NOT NULL auto_increment,
`competid` int(11) NOT NULL default '0',
`headline` varchar(255) NOT NULL default '',
`lead` longtext NOT NULL,
`details` longtext NOT NULL,
`writer` varchar(255) NOT NULL default '',
`email` varchar(255) NOT NULL default '',
`pageupdate` timestamp(14) NOT NULL,

When I recall the news item I want to be able to transform the time stamp. I have been using the code in the earlier poting to transform the timestamp.

To recall I am using

PHP Code:


$sql2= "select * from news, counter where newsid ='$newsid' ";
 $result2=mysql_query($sql2,$connection) or die("something is wrong");
$row2=mysql_fetch_array($result2);

Any help would be grateful as I am confused again

for a change.

JdS · #4 29-Dec-2003, 23:45

I wrote one article a while back about this: http://www.desilva.biz/mysql/formatdate.html

Anyway, looking at your code above, it seems to me that you want your TIMESTAMP formatted like this:

Code:

Wednesday, 3 December, 2003. 1:29 AM

Instead of 'formatting' the date via PHP, I (personally) prefer to do it via MySQL, so here's what you can try too:

PHP Code:


<?php
$sql2  =  "SELECT
            `newsid`,
            `competid`,
            `headline`,
            `lead`,
            `details`,
            `writer`,
            `email`,
            `pageupdate`,
            DATE_FORMAT( `pageupdate`, '%W, %e %M, %Y. %l:%i %p' ) AS `newupdated`
          FROM
            `news`
          WHERE
            `newsid` = $newsid";
$result2  = mysql_query( $sql2, $connection )
              or die( "something is wrong" );
$row2     = mysql_fetch_assoc( $result2 );

// inside $row['newupdated'] is 'Wednesday, 3 December, 2003. 1.29 AM'

// I've left the `pageupdate` field in the query (just above the DATE_FORMAT line),
// just incase you still need to use in your scripts in it's original form...
// otherwise, you can simply delete it.
?>

misunderstood · #5 30-Dec-2003, 06:49

Thanks JDS.
I changed a few things because I wanted to read from 2 table;

PHP Code:


$sql2  =  "SELECT `newsid`,`headline`,`lead`,`details`,`writer`,`email`,`pageupdate` ,DATE_FORMAT( `pageupdate`, '%W, %e %M, %Y. %l:%i %p' ) AS `newupdated`,`visits`, `idnews` FROM `counter`,`news` WHERE news.newsid ='$newsid' and counter.idnews=$newsid"; 
$result2  = mysql_query( $sql2, $connection )or die( "something is wrong" ); 
$row2     = mysql_fetch_array( $result2 );

If you notice on the last line I changed to mysql_fetch_array from mysql_fetch_assoc because it wouldnt work until I did this. Everything seems to work ok but can you tell me if this may cause problems.
Thanks again.

JdS · #6 30-Dec-2003, 08:49

No, it won't cause any problems... just one small comment to add before we're done with this thread:

`newsid`, `counter`.`idnews` and even the variable $newsid are the SAME (or should be). You can just use $newsid where you needed to insert the others, no?

BTW, I assume you've already done this, right at the top of your script?

PHP Code:


$newsid = intval( $_GET['newsid'] );

misunderstood · #7 31-Dec-2003, 19:40

OK

I never had <?

$newsid = intval( $_GET['newsid'] );

?>
but I have now.
The <?

$newsid ?> was called from the page recall but I have included it now. It worked before but hopefully adding the extra code is an extra safeguard. ???
If not, anybody else doing the same will know to add it to get the code to 'operate' correctly.
I dont claim I am an expert but with lots of help I get by

. (thanks JDS)

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Timestamp

Using MySQL date function: DATE_FORMAT()