shane85 Posted June 1, 2010 Share Posted June 1, 2010 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 Quote Link to comment Share on other sites More sharing options...
jcbones Posted June 1, 2010 Share Posted June 1, 2010 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'"; Quote Link to comment Share on other sites More sharing options...
shane85 Posted June 1, 2010 Author Share Posted June 1, 2010 nope not a date column, its a timestamp column. It stores the date like 2010-05-25 20:42:08 for example Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted June 1, 2010 Share Posted June 1, 2010 The method that jcbones posted will work with your column. Quote Link to comment Share on other sites More sharing options...
shane85 Posted June 2, 2010 Author Share Posted June 2, 2010 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 Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted June 2, 2010 Share Posted June 2, 2010 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? Quote Link to comment Share on other sites More sharing options...
shane85 Posted June 2, 2010 Author Share Posted June 2, 2010 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??? Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted June 2, 2010 Share Posted June 2, 2010 In his post he suggested creating links that would set $_GET['month']. Quote Link to comment Share on other sites More sharing options...
shane85 Posted June 2, 2010 Author Share Posted June 2, 2010 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 Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted June 2, 2010 Share Posted June 2, 2010 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. Quote Link to comment Share on other sites More sharing options...
shane85 Posted June 3, 2010 Author Share Posted June 3, 2010 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); } ?> Quote Link to comment Share on other sites More sharing options...
jcbones Posted June 3, 2010 Share Posted June 3, 2010 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. Quote Link to comment Share on other sites More sharing options...
shane85 Posted June 3, 2010 Author Share Posted June 3, 2010 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 Quote Link to comment Share on other sites More sharing options...
shane85 Posted June 3, 2010 Author Share Posted June 3, 2010 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 Quote Link to comment Share on other sites More sharing options...
shane85 Posted June 3, 2010 Author Share Posted June 3, 2010 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? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.