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

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

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

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Attachments:

blog_002.php 9K

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!

Dave Edis - Senior Developer
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

The first CMS Builder reference book is now available on-line!







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!

Dave Edis - Senior Developer
interactivetools.com