help with news script... I want to display news by months...ie..aug, july, etc

[shane85]

hey guys

so im adding a news script to my site...my db is set up to have the following rows

 

id(int10)

tstamp(timestamp)

subject(text)

message(text)

 

what I would like to do is have it so if I wanted to show news articles from juts august, the link it would be like news.php?month=8 ... how do I achieve this?? is it through a query??? I have it displaying all the news properly and in desc order like I would wnat, but now I just want to narrow it down so if a user wishes to view news from a certain month, they can click on a link like that

 

thanks in advance I appreciate the help

[shane85]

I think I need to format the output of the date to do this correctly? im looking through old posts on the site and that appears to be whta im trying to do....once I get that done, how would I access it though? I want to have links on the side of my page

Jan

Feb

march

etc...

where jan would be news.php?month=1

feb would be news.php?month=2

 

etc...I just dont know how to display it correctly...

[shane85]

anyone?

[obay]

What I would do is convert the timestamp to a datetime, then use that value to do a query such as

 

SELECT * FROM news WHERE MONTH ( converted_timestamp ) = 8

 

where converted_timestamp is a datetime

[shane85]

heres my code so far....

 

<?php

//db conection details ive taken out

// Create function to display the news entries
function print_newsEntry($arrEntry)
{
// convert \n linebreaks to HTML formatted <br> breaks
$arrEntry['message'] = str_replace("\n", '<br>', $arrEntry['message']);
?>

<h3 class="meta_news"><?php echo $arrEntry['subject']; ?></h3>
<span class="date"><strong><?php echo date('M d - Y', $arrEntry['tstamp']); ?></strong></span>
<p><?php echo str_replace("\n", '<br>', $arrEntry['message']); ?></p>
<center><img src="gfx/bcrumb_line.gif" width="567" height="1"></center>
<p></p>

<?php

}

// Get all news entries

mysql_connect(DB_HOST, DB_USER, DB_PASSWORD) or trigger_error(mysql_error());
mysql_select_db(DB_DATABASE) or trigger_error(mysql_error());

$newsEntries = mysql_query('SELECT *, UNIX_TIMESTAMP(tstamp) AS tstamp FROM news ORDER BY tstamp DESC') or trigger_error(mysql_error());

// Display news Entries

while ($newsEntry = mysql_fetch_array($newsEntries))
{
print_newsEntry($newsEntry);
}

?>

 

now ive tried converting the timestamp to a datetime using m (so it will show numeric value 1-12 depending on month) and created a $test query and tried to echo $test but it doesnt work and says $converted_timestmap is suppost to be a row thats not found....what am I doing wrong???

 

$converted_timestamp = date('m', $arrEntry['tstamp']);
$test = mysql_query('SELECT * FROM news WHERE MONTH ( $converted_timestamp ) = 8') or trigger_error(mysql_error());

echo $test;

[obay]

the error there is that your query will become something like:

 

SELECT * FROM news WHERE MONTH ( 8 ) = 8

 

. $converted_timestamp should be a complete mysql date. What you did was you extracted the month using PHP. That was what the

 

MONTH()

 

part of the SQL was for. What you need to do is do the conversion in mysql.. that is, convert the timestamp into a date, name it $converted_timestamp, and insert it into the MONTH() part of your SQL.

[shane85]

hmm kk that make sence....where can I read documentation on how to properly do that?? I know what im trying to do, just dont know exactly what to look for to learn how to do it

[obay]

you can use FROM_UNIXTIME() to convert your timestamp to a date

 

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_from-unixtime

 

then insert the resulting date into your query

[obay]

not sure, but try this:

 

mysql_query('SELECT * FROM news WHERE MONTH ( FROM_UNIXTIME(tstamp) ) = 8')

[shane85]

that last query seems to work this is what I have in my code right now

 

$converted_timestamp = date('m', $arrEntry['tstamp']);
$test = mysql_query('SELECT * FROM news WHERE MONTH ( FROM_UNIXTIME(tstamp) ) = 8') or trigger_error(mysql_error());
//$test = mysql_query('SELECT * FROM news WHERE MONTH ( $converted_timestamp ) = 8') or trigger_error(mysql_error());

echo $test;

 

however im not using $converted_timestamp ... how do I implement that in??? Right now when I echo $test it says

 

Resource id #2

 

for some reason?

[obay]

You don't need $converted_timestamp anymore, just remove it.

 

Basically, you just need this:

$test = mysql_query('SELECT * FROM news WHERE MONTH ( FROM_UNIXTIME(tstamp) ) = 8');

 

but make sure you replace "8" with the month that you want. For example, if you want to display news for june, change the 8 to 6.

 

Now for your other problem, echoing of Resource id.. what do you want to have printed out?

 

Remember that query() function returns a link to your query result, not the result itself, that's why its a resource id. So, for example you want to fetch the news items returned by ur query, you would do something like...

 

$selected_month = 8; //or you can set this to a user-selected value

$items = mysql_query('SELECT * FROM news WHERE MONTH ( FROM_UNIXTIME(tstamp) ) = ' . $selected_month);

while ( $item = @mysql_fetch_array() ) {
     echo "Subject: " . $item['subject'];
     echo "Message: " . $item['message'];
}

[obay]

oops!

 

while ( $item = @mysql_fetch_array() ) {

 

should be

 

while ( $item = @mysql_fetch_array($items) ) {

 

sorry.. my bad!

[shane85]

hey obay...thanks alot for posting that....right now this is what I have for my code

 

<?php

//database connection details removed

// Create function to display the news entries
function print_newsEntry($arrEntry)
{
// convert \n linebreaks to HTML formatted <br> breaks
$arrEntry['message'] = str_replace("\n", '<br>', $arrEntry['message']);
?>

<h3 class="meta_news"><?php echo $arrEntry['subject']; ?></h3>
<span class="date"><strong><?php echo date('M d - Y', $arrEntry['tstamp']); ?></strong></span>
<p><?php echo str_replace("\n", '<br>', $arrEntry['message']); ?></p>
<center><img src="gfx/bcrumb_line.gif" width="567" height="1"></center>
<p></p>

<?php

}

// Get all news entries

mysql_connect(DB_HOST, DB_USER, DB_PASSWORD) or trigger_error(mysql_error());
mysql_select_db(DB_DATABASE) or trigger_error(mysql_error());

// figuring out the timestamp and date

$selected_month = 8; //or you can set this to a user-selected value

$items = mysql_query('SELECT * FROM news WHERE MONTH ( FROM_UNIXTIME(tstamp) ) = ' . $selected_month);

while ( $item = @mysql_fetch_array($items) ) {
     echo "Subject: " . $item['subject'];
     echo "Message: " . $item['message'];
}

?>

 

however it displays just a blank page....what is wrong???

[shane85]

BUMP

sorry guys...still stuck on this