Closed Thread Icon

Topic awaiting preservation: Displaying the date (Page 1 of 1) Pages that link to <a href="https://ozoneasylum.com/backlink?for=26767" title="Pages that link to Topic awaiting preservation: Displaying the date (Page 1 of 1)" rel="nofollow" >Topic awaiting preservation: Displaying the date <span class="small">(Page 1 of 1)</span>\

 
jive
Paranoid (IV) Inmate

From: Greenville, SC, USA
Insane since: Jan 2002

posted posted 10-01-2005 22:16

Hello all,

Ran into a snag. I could swear that my code is correct but I'm getting a mysql error saying that the query is empty. Heres my code:

code:
<?php //news Display page
include ('config.php');



$newslist = mysql_query("SELECT id, DATA_FORMAT(newsdate,'%e %b %y') AS newsdate, newstitle, newstext FROM news"); 

if (@mysql_query($newslist)) {
	echo ('<p>So far so good, your news annoucement has been deleted!</p>');
}
else {
	echo('<p>uh oh, what up with that?</p>' .
			mysql_error() . '<p>');
}



while ($news = mysql_fetch_array($newslist)) {
	$newsid = $news['id'];
	$newsdate = $news['newsdate'];
	$newstitle = $news['newstitle'];
	$newstext = $news['newstext'];

echo ('<h5>' .$newstitle. '</h5>' .$newsdate .
	  '<p>'  .$newstext. '</p>' 	
);

} 

?>



Essentially I just want to be able to display the date like this:

9 Sept 05

Any clues?

DL-44
Maniac (V) Inmate

From: under the bed
Insane since: Feb 2000

posted posted 10-01-2005 23:23

Not familiar with MySQL's date formatting capabilities - I always use PHP to format the date...

FWIW

jive
Paranoid (IV) Inmate

From: Greenville, SC, USA
Insane since: Jan 2002

posted posted 10-02-2005 02:28

How would you do it using php?

Ensellitis
Bipolar (III) Inmate

From: Kansas City, MO , USA
Insane since: Feb 2002

posted posted 10-02-2005 03:14

echo date("j M y");



(Edited by Ensellitis on 10-02-2005 03:15)

jive
Paranoid (IV) Inmate

From: Greenville, SC, USA
Insane since: Jan 2002

posted posted 10-02-2005 03:48

ok, I used the following:



code:
include ('config.php');

$newslist = mysql_query('SELECT * FROM news'); 

if (@mysql_query($newslist)) {

echo ('<p>So far so good, your news annoucement has been deleted!</p>');

}

else {

echo('<p>uh oh, what up with that?</p>' .

mysql_error() . '<p>');

}

while ($news = mysql_fetch_array($newslist)) {

$newsid = $news['id'];

$newsdate = $news['newsdate'];

$newstitle = $news['newstitle'];

$newstext = $news['newstext'];

echo ('<h5>' .$newstitle. '</h5>' . date($newsdate,'j M y') . 

     '<p>' .$newstext. '</p>');

}





but my output ended up being:2005-09-27 00:00:00



instead of 9 sept 05. I'm also getting a mysql error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Resource id #4' at line 1



I don't see any syntax errors.

Skaarjj
Maniac (V) Mad Scientist

From: :morF
Insane since: May 2000

posted posted 10-02-2005 13:21

That's because, as far as I can tell, you're feeding a MySQL date into the date function, which is not how it works. It reads in Unix Timestamps and turns them into regularly formatted dates. if you want ot do it like that when you insert the news item in the first place you'll need to do

code:
"INSERT INTO news (newsdate) VALUES(".date().")"



Otherwise you'll need to do (and I'm damn sure there's an easier way to do this, but I can't find it, so this is the way I'm suggesting)

code:
$datetime_array = explode(" ",$news['newsdate']);
$date_array = explode("-",$datetime_array[0]);
$newsdate = mktime(0,0,0,$date_array[2],$date_array[1],$date_array[0]);
unset($datetime_array);
unset($date_array);




Justice 4 Pat Richard

jive
Paranoid (IV) Inmate

From: Greenville, SC, USA
Insane since: Jan 2002

posted posted 10-02-2005 15:10

actually, did this:


code:
include ('config.php'); 
$newslist = mysql_query("SELECT id, DATE_FORMAT(newsdate,'%e %b %y') AS newsdateformatted, newstitle, newstext from news"); 

if (@mysql_query($newslist)) { 
echo ('<p>So far so good, your news annoucement has been deleted!</p>'); 
} 
else { 
echo('<p>uh oh, what up with that?</p>' . 
mysql_error() . '<p>'); 
} 

while ($news = mysql_fetch_array($newslist)) { 
$newsid = $news['id']; 
$newsdate = $news['newsdateformatted']; 
$newstitle = $news['newstitle']; 
$newstext = $news['newstext']; 
echo ('<h5>' .$newstitle. '</h5>' . $newsdate  . 
     '<p>' .$newstext. '</p>'); 
}



And it worked like a charm.

bitdamaged
Maniac (V) Mad Scientist

From: 100101010011 <-- right about here
Insane since: Mar 2000

posted posted 10-02-2005 21:45

yar you had DATA_FORMAT instead of DATE_FORMAT in your first post.



.:[ Never resist a perfect moment ]:.

zavaboy
Bipolar (III) Inmate

From: f(x)
Insane since: Jun 2004

posted posted 10-02-2005 22:05

I usualy just use UNIX_TIMESTAMP then use PHP's date() funtion to make it into any format I want, also allows it to be more flexible.

jive
Paranoid (IV) Inmate

From: Greenville, SC, USA
Insane since: Jan 2002

posted posted 10-03-2005 17:13

show me the UNIX_TIMESTAMP way of doing things. sounds interesting.

DL-44
Maniac (V) Inmate

From: under the bed
Insane since: Feb 2000

posted posted 10-03-2005 18:06

http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

about half way down the page.

« BackwardsOnwards »

Show Forum Drop Down Menu