14 Replies Latest reply: Jul 22, 2012 4:57 PM by MurraySummers RSS

    How to display posts by category

    Macjie123

      I completed David Powers 'Building your first dynamic website' tutorial series (http://www.adobe.com/devnet/dreamweaver/articles/first_dynamic_site_pt3.html). While playing around with a few options in phpMyAdmin I thought it would be cool to have a third column on the News page to list blog posts by category. So I included a column called category in the PHP table, but I can't figure out how to do the SQL queries and recordsets. Does anybody know how to do this?

        • 1. Re: How to display posts by category
          MurraySummers ACP/MVPs

          The easiest way would be to create another recordset on the page using SQL to sort the records by category, e.g.,

           

          SELECT news.post_id, news.title, news.category FROM news ORDER BY news.category

           

          You would need to just duplicate one of your recordset blocks and replace the SQL with this, and the recordset name with a new name.  Then you would need to add another repeat region on the page to list the records contained in this recordset.

           

          Does that get you down the road?

          • 2. Re: How to display posts by category
            Macjie123

            Thanks for the reply Murray, That's certainly getting me closer. But how about if I had a column on the news.php page similar to the Archives column, with the header 'Category" and links below it for say Category 1, Category 2, Category 3 and Category 4 (instead of the Month and Year) where clicking the category links would return articles for just that particular category?

            • 3. Re: How to display posts by category
              MurraySummers ACP/MVPs

              That could work, too.  Then you'd have to make a category landing page that would pull a recordset for only a single category.  Each of the links on the parent page would be like <a href="whatever.php?cat=aaaa">AAAA</a>, etc., and on the landing page youl would use this to parse the desired category from the URL -

               

              $cat=isset($_GET['cat'])?$_GET['cat']:"aaaa";

              // obviously "aaaa" is the default if no category is specified

               

              Does that help?

              • 4. Re: How to display posts by category
                Macjie123

                Thanks again Murray. It does help, and although plausible it does seem slightly elaborate. Is it not possible to add a query that would filter the results by category from a category column(s). I suppose like an additional filter to the Recent Posts results restricting the results to the required category?

                • 5. Re: How to display posts by category
                  MurraySummers ACP/MVPs

                  Sure it's possible to do that, but it's lots more elaborate.  You would need to read the recordset into an array, and then step through the array once for each different category, only writing the records that match that given category to the page.  But neither way is particularly difficult. For example, assuming your recordset is now contained in $records and your category list is in $categories:

                   

                  $header = false;

                  foreach ($categories as $category) {

                       if (!$header) {

                            echo "$category\r\n"; $header=true;

                            }

                       foreach ($records as $record) {

                            if ($record['category'] == $category) {

                                  echo "\t$record['title']";

                                 }

                       }

                  }

                   

                  I think that does it.

                  • 6. Re: How to display posts by category
                    MurraySummers ACP/MVPs

                    foreach ($categories as $category) {

                    $header = false;

                         if (!$header) {

                              echo "$category\r\n"; $header=true;

                              }

                         foreach ($records as $record) {

                              if ($record['category'] == $category) {

                                    echo "\t$record['title']";

                                   }

                         }

                    }

                    • 7. Re: How to display posts by category
                      Macjie123

                      Hey Murray,

                       

                      Having only experienced PHP as far as the three-part tutorial series mentioned above, I have to say you've lost me with this one!!

                       

                      I understand the concept of an array, but can you please be kind enough to walk me through the code you provided?

                      • 8. Re: How to display posts by category
                        MurraySummers ACP/MVPs

                        The code has a loop nested inside a loop.  The outer loop is stepping through the category values.  For each category value, the inner loop will step through each record in the recordset array, testing the category value for that record.  When the inner loop finds a record with a category equal to the outer loop's category, it prints the record's title.  There is an additional line in the outer loop that causes the category value to be printed once and only once for each category value.  So -

                         

                        foreach ($categories as $category)

                        This is the outer loop definition. With the $categories array (an array containing the category values, i.e., $categories[0] = category0, $categories[1]=category1, etc.), this loop will do the following code with $category sequentially taking the value of each category.

                         

                        $header = false;

                             if (!$header) {

                                  echo "$category\r\n"; $header=true;

                                  }

                         

                        This prints the category value ONCE each time the loop is incremented.

                         

                        foreach ($records as $record) {

                                  if ($record['category'] == $category) {

                                        echo "\t$record['title']";

                                       }

                         

                        This is the inner loop that steps through the $records array, and examines each record's category.  When it finds a match between the record's category (i.e., $record['category']) and the current value of the outer loop (i.e., $category), it prints the record's title (i.e., $record['title']).

                         

                        Once the inner loop completes, the outer loop increments and the process repeats for all the categories.

                         

                        Make sense?

                        • 9. Re: How to display posts by category
                          MurraySummers ACP/MVPs

                          And by the way, you would create an array from your recordset like this -

                           

                          After pulling the recordset, insert this code:

                           

                          do {

                               $catgories[ ] = $row_rsWhatever['category'];

                          } while ($row_rsWhatever = mysql_fetch_assoc($rsWhatever));

                           

                          ( for clarity, in this snippet I have inserted a space between "[" and "]" in the "$categories[ ]" usage above, but really, there should be no space, giving this "$categories[]" - see what I mean?  And the use of this will automatically increment the array's index value for each interation of the loop, resulting in the desired array of category values)

                          • 10. Re: How to display posts by category
                            Macjie123

                            I really appreciate the time and trouble you have gone to in providing this information Murray. However, after working with your code, I'm unable to relate this to building the recordset in Dreamweaver and amending the code accordingly. Such a shame the forum moderators haven't been able to answer mine and other's queries in the context of David Power's tutorial. Think I'll have to go work through PHP basics before coming back to this.

                            • 11. Re: How to display posts by category
                              Macjie123

                              I think I finally worked out how to do this building a seperate table for the categories and building recordsets in Dreamweaver. One final question Murray, is it necessary to build recordsets in order to implement PHP? Reading up on PHP tutorials over the last 2 days, it seems recordsets are a Dreamweaver construct and not a necessary component when building dynamic pages in PHP. Am I right?

                              • 12. Re: How to display posts by category
                                MurraySummers ACP/MVPs

                                Am I right?

                                 

                                No, you are completely wrong.  Recordsets are the heart and soul of data-driven pages.  Of course, many dynamic pages are not data-driven pages, and can work just fine using PHP without recordsets.  But any page that needs to retrieve data from a database must use a recordset or its equivalent to manage the reading of that data out of the database's tables.

                                 

                                Just so you'll know, there is a separate forum for PHP (and CF, etc.) questions - the Dreamweaver Application Development forum.  While it's likely that you will eventually encounter David Powers here answering questions, I'd say your chances of that encounter would be much higher over there.

                                 

                                I'm sorry you were not able to see your way through my explanation.  Perhaps if you took the time to post your code, I (or someone else here) could help you further.

                                • 13. Re: How to display posts by category
                                  Macjie123

                                  The reason I questioned the recordset is because all the tutorials I've read concentrate solely on PHP code, but never refer to recordset construction. This is where I'm falling down on this task Murray; I've failed to implement your code as I'm unable to write the correct database query with correct syntax in the SQL area of Dreamweaver's 'Recordset' dialogue box. can you, or any other contributer, assist with this please?

                                  • 14. Re: How to display posts by category
                                    MurraySummers ACP/MVPs

                                    Show us the PHP that you have on the page now, please.