help with showing posts only from month X

[shane85]

hey guys

I made a post about this a week or so ago but have been so busy havent had time to experiment and try to get to the bottom of this.

 

Basically I have a news section on my site, which pulls all the info from a mysql db and displays the subject of the msg, the msg, and the timestamp of when it was posted.

 

Now, I have a side table on the side of my site with months, Jan, Feb, Mar, Apr, etc. What I would like to do is so when someone clicks on one of those links, it just shows posts from that month. Someone recomended I do the following

$sql = "SELECT * FROM `news` WHERE tstamp >= '2010-".$month."-01' AND tstamp <= '2010-".$month."-30';

 

however, where do I define $month? Wouldnt it be better for may, for example, if rather then $month I just put 05 ? any other thoughts on this??

 

Thanks

[jcbones]

Is tstamp a date column?

 

if so, you should have links made like.

<a href="?month=02">Feb</a>

 

Then you can:

$month = (int)$_GET['month'];
$sql = "SELECT * FROM `news` WHERE month(tstamp) = '$month'";

[shane85]

nope not a date column, its a timestamp column. It stores the date like

 

2010-05-25 20:42:08

 

for example

[PFMaBiSmAd]

The method that jcbones posted will work with your column.

[shane85]

trying to do some experimenting here finally...

 

sql('SELECT * FROM news WHERE month(tstamp) ='$month' ORDER BY tstamp DESC') or trigger_error(mysql_error());

 

gives me the following error

 

Parse error: syntax error, unexpected T_VARIABLE in news2.php on line 69

[PFMaBiSmAd]

You should always use over-all (initial and final) double-quotes when forming a query, because A) It allows you to put single-quotes inside the query without producing php syntax errors, and B) allows you to put php variables inside the query and have them replaced with their value at runtime.

 

Edit: And in fact, why did you switch from the double-quotes and forming the query in a variable that you used in the first post in this thread?

[shane85]

hmm ok good to know ty

 

im curious how jcbones code is going to work

 

$month = (int)$_GET['month'];
$sql = "SELECT * FROM `news` WHERE month(tstamp) = '$month'";

 

because on the news page currently, im showing all results from the db. Where does $month get defined???

[PFMaBiSmAd]

In his post he suggested creating links that would set $_GET['month'].

[shane85]

I used that code....but it doesnt work...does php know $month is the 2nd line in the timestamp or does that have to somehow be defined??? sorry for newbie question

[PFMaBiSmAd]

does php know $month is the 2nd line in the timestamp or does that have to somehow be defined???

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_month

 

it doesnt work...

What exactly doesn't work (your code could contain dozens of errors, sight unseen and unknown to us, that could produce the symptom "it doesn't work") and what exactly did it do v.s. what you expected (for all we know you don't have any matching data)? Without your code and a statement of what you saw in front of you when you tried it on your server with your data, it is simply impossible to help you.

[shane85]

duh

sorry....should have explained myself a bit better

 

it doesnt display any results at all.

 

most of the code im using is as follows

 

<?php

// 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"><a href="article.php?id=<?php echo $arrEntry['id']; ?>"><?php echo $arrEntry['subject']; ?></a></h3>
<span class="date"><strong><?php echo date('M, d - Y', $arrEntry['tstamp']); ?></strong></span>
<p>
<?php $arrEntry['message'] = substr($arrEntry['message'], 0, 350); // Display only 350 Characters
echo str_replace("\n", '<br>', $arrEntry['message']); ?>...</p>
<p><a href="article.php?id=<?php echo $arrEntry['id']; ?>">Click Here</a> to read more.</p>

<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());

$month = (int)$_GET['month'];

$newsEntries = mysql_query('SELECT *, UNIX_TIMESTAMP(tstamp) AS tstamp FROM news WHERE month(tstamp) = "$month" ORDER BY tstamp DESC') or trigger_error(mysql_error()); 
// my old query I use to display all results
// $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);
}

?>

[jcbones]

You must send the page the $month variable.

 

This would be in a $_GET array.  You set a $_GET array by passing it in the URL.

 

<a href="page.php?month=02">Feb</a>

 

PHP will then GET the variable $month from the URL, and pass it to the page.

$month = $_GET['month'];

 

You can force the variable to an integer by assigning it:

$month = (int) $_GET['month'];

 

From your original post:

Now, I have a side table on the side of my site with months, Jan, Feb, Mar, Apr, etc.

 

You would need to make the links I've described above, for each of these months.

[shane85]

hmmm ok thank you I will try that. I do have them like that, however, could it be its not working correctly because its not hardcoded in this file, rather linked to an include??

 

Also, im assuming the year thing would work the same? If I wanted to do the year, I would just do

 

<a href="page.php?year=2010&month=02">Feb</a>

 

??

 

Thanks again

[shane85]

I added the following links on the page just for testing purposes

 

<a href="news2.php?month=01">Jan</a>
<a href="news2.php?month=02">Feb</a>
<a href="news2.php?month=03">Mar</a>
<a href="news2.php?month=04">Apr</a>
<a href="news2.php?month=05">May</a>
<a href="news2.php?month=06">Jun</a>
<a href="news2.php?month=07">Jul</a>
<a href="news2.php?month=08">Aug</a>
<a href="news2.php?month=09">Sep</a>
<a href="news2.php?month=10">Oct</a>
<a href="news2.php?month=11">Nov</a>
<a href="news2.php?month=12">Dec</a>

 

but when I click on any of them nothing shows up....and in my db there are mosts from may, sept, a fwe others as well

[shane85]

hmm ok im making progress...I think the reason its displaying nothing is:

its the main page, where im trying to show ALL news postings. Then on the side, I have the links for months, if they wish to view by month. Queston is, how do I make it show all when news2.php loads, but then when they click on say may, june, july, execute a different query?