Skip navigation
Macjie123
Currently Being Moderated

How to display posts by category

Jul 21, 2012 5:50 AM

I completed David Powers 'Building your first dynamic website' tutorial series (http://www.adobe.com/devnet/dreamweaver/articles/first_dynamic_site_pt 3.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?

 
Replies
  • Currently Being Moderated
    Jul 21, 2012 6:21 AM   in reply to Macjie123

    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?

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 21, 2012 9:22 AM   in reply to Macjie123

    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?

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 21, 2012 11:46 AM   in reply to Macjie123

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 21, 2012 11:47 AM   in reply to MurraySummers

    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']";

                   }

         }

    }

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 22, 2012 6:46 AM   in reply to Macjie123

    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?

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 22, 2012 6:52 AM   in reply to MurraySummers

    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)

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 22, 2012 3:01 PM   in reply to Macjie123

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 22, 2012 4:57 PM   in reply to Macjie123

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

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points