Restricting $query results to field value passed at end of URL
6 posts by 2 authors in: Forums > CMS Builder
Last Post: October 31, 2013 (RSS)
By gkornbluth - October 23, 2013 - edited: October 23, 2013
Hi All,
As I mentioned in post 2232090, I’m using the following on a blog viewer to create an “archive” list of unique months and years with posts
$query = "SELECT DATE_FORMAT(date, '%M %Y') as dateAndYear, YEAR(date) as year, MONTH(date) as month FROM cms_blog GROUP BY dateAndYear ORDER BY date";
$result = mysql_query($query) or die("MySQL Error: ". htmlspecialchars(mysql_error()) . "\n");
while ($record = mysql_fetch_assoc($result)):
?>
<a href="blog2.php?date_year=<?php echo $record['year'] ?>&date_month=<?php echo $record['month'] ?>&orderBy=<?php echo $orderBy ?>"><?php echo $record['dateAndYear']; ?> </a><br/>
<?php endwhile ?>
I ’m restricting the article records that are shown on the page to those where a field called blog_chapter equals the request at the end of the URL IE: http://www.mysite.com/blog.php?blog_chapter=Chapter-One
My problem is that I have no idea how to place that same limitation on the $query that creates the unique "archive" list on the page.
Hope someone can help.
Thanks,
Jerry Kornbluth
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
By Dave - October 30, 2013
Hi Jerry,
Can you provide some more details? For example, what format of URL would you like to use and what records do you want it to pull up from the database?
Let me know and I'll try to help. Thanks!
interactivetools.com
Hi Dave,
Thanks for looking at this.
I’m not explaining it very well.
If you go to http://www.terrerybovich.com/blog.php you'll see that I’ve hidden the post records from October 2013 and they don’t appear in the blog itself.
However, they appear in the Archive list and I’d like any hidden records not to.
Hope that makes more sense.
I’ve attached the code for blog.php
Jerry Kornbluth
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
By Dave - October 31, 2013
Hi Jerry,
Ok, so let me see if I understand correctly. You want to display a list of available months under "Archives" but not if that month doesn't contain any visible articles?
If so, I'd try adding the code in red to your query:
FROM cms_blog WHERE `hidden` = 0 GROUP BY dateAndYear
Hope that helps! Let me know if it works for you. Thanks!
interactivetools.com
Hi Dave,
Thank you, it certainly does work.
I had no idea how to add a where statement to the existing code and all of my attempts resulted in errors.
Really appreciate the help.
Jerry Kornbluth
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
By Dave - October 31, 2013
Hi Jerry,
Ok, glad to hear it's working. Yes, SQL is it's own language with a very strict grammar, it can be tricky to know what goes where!
Cheers!
interactivetools.com