22 Replies Latest reply on Sep 2, 2007 2:19 AM by Günter Schenk

    Archive by month

      I am using dreamweaver to build a site where I need to pull archived links by month in a side bar. For example: Jan,Feb, Mar etc. When you click the month it will show the links that have been submitted in that month only. I can pull them all but I want it to build the side bar so when there are posts in that month they will show so if for example march has no posts it wont shot in the list. I would also like to do it my year if possible. Hope that made sense.

      Thanks
        • 1. Re: Archive by month
          Günter Schenk Level 4
          Hi,

          just to make sure I´m not misunderstanding -- do you want to accomplish this using the "Dreamweaver Developer Toolbox" extension (that´s the forum you´re posting in), or with "Dreamweaver" in general ?

          Günter Schenk
          Adobe Community Expert, Dreamweaver
          • 2. Re: Archive by month
            Level 1
            I am using "Dreamweaver Developer Toolbox"... Maybe just dreamweaver in general. Im not really sure. This seems like it would be simple. All I have is a link table with id, title, url, and date. I just want to list the months that have links in a side bar and them when you click the month is shows all links for that month.
            • 3. Re: Archive by month
              Günter Schenk Level 4
              Hi,

              I´m also not quite sure what to use best in this case. Normally I´d have suggested to implement ADDT´s "Nested Repeat Region wizard", which by default works on a master + detail table, but (as I heard, but never tested myself) can also be configured to use the same table as "master" respectively "detail", just with different fields.

              However, what makes your scenario a little more complicated, is the fact that you´ll have to use an "advanced" query which will extract the "year" respectively "month" values from a standard MySQL date field (YYYY-MM-DD) -- example:

              SELECT id, title, url, MONTH(date) AS link_month, YEAR(date) AS link_year FROM your_table ORDER BY link_year DESC, link_month DESC

              ADDT´s "Nested Repeat Region wizard" will be creating the queries, but it certainly won´t be creating *this* advanced query -- means you´ll probably have to try yourself and edit & extend the generated query with the required "alias" columns [e.g. MONTH(date) AS *link_month*] to have those columns at hand for displaying them in the page and sorting correctly.

              Apart from that -- you could certainly use DW´s native "Show if recordset not empty" behaviour to hide those months from view which don´t have any entries.

              Günter Schenk
              Adobe Community Expert, Dreamweaver
              • 4. Re: Archive by month
                Level 1
                Thanks for the reply. While I have been waiting I have come up with this. I have is set now where I am pulling the months on the page based on the months in the database by using this:
                SELECT dshot.date, COUNT(dshot.id) AS OrderTotal FROM dshot GROUP BY EXTRACT(MONTH FROM dshot.date)

                I found some reference online and it works so far. When I view the page by using this:


                It will show the months from the DB. I am new to all this but think im making good progress. All I need to do now is make it when you clink the month link it will show all days from that month. What that be hard from where im at?

                adding the OrderTotal after will show how many are in that month. In the database I have march having 2 entries and on my page it renders out March 2
                • 5. Re: Archive by month
                  Günter Schenk Level 4
                  Hi,

                  the detail page that´s supposed to display links related to year X and month Y, will have to receive these values as URL parameters in order to show the matching links -- that said, I´d still suggest to add...

                  MONTH(date) AS link_month, YEAR(date) AS link_year

                  ...to whatever query, otherwise you won´t have an "identifier" at all which can be passed to the detail page -- and this one would need a query like...

                  SELECT title, url, YEAR(date) AS year, MONTH(date) AS month, DAY(date) AS day FROM table_name WHERE year = [use GET variable "year"] AND month = [use GET variable "month"] ORDER BY day ASC

                  Does this make sense ?

                  Günter Schenk
                  Adobe Community Expert, Dreamweaver
                  • 6. Re: Archive by month
                    Level 1
                    I think so. Ill see if I can make it work and let you know.
                    • 7. Re: Archive by month
                      Level 1
                      Nope I guess I am lost now...
                      • 8. Re: Archive by month
                        Günter Schenk Level 4
                        at what point are you "lost" -- what doesn´t work ?

                        Günter Schenk,
                        Adobe Community Expert, Dreamweaver
                        • 9. Re: Archive by month
                          Level 1
                          Hi, Im lost at how to display the records per month. Here is the code that I have at this point. Thanks for any help.<br /><br />mysql_select_db($database_okconnect, $okconnect);<br />$query_bymonth = "SELECT dshot.date, COUNT(dshot.id) AS total FROM dshot GROUP BY EXTRACT(MONTH FROM dshot.date) ORDER BY dshot.`date` DESC";<br />$bymonth = mysql_query($query_bymonth, $okconnect) or die(mysql_error());<br />$row_bymonth = mysql_fetch_assoc($bymonth);<br />$totalRows_bymonth = mysql_num_rows($bymonth);<br /><br />And here is the html part of it.<br /><br /><?php do { ?><br />     <p><a href="dailyshot.php?month=""<?php echo $row_bymonth['date']; ?>"><?php echo date("F,Y", strtotime($row_bymonth['date'])); ?> - <?php echo $row_bymonth['total']; ?></a></p><br />                    <?php } while ($row_bymonth = mysql_fetch_assoc($bymonth)); ?>
                          • 10. Re: Archive by month
                            Günter Schenk Level 4
                            Hi,

                            my problem is :: from seeing your code alone I can´t know *what* does not work exactly -- please be more specific in regards to your descriptions.

                            However, the query alone can be simplified -- by deleting some "dshot." instances, because you´re just querying one table:

                            SELECT id, date, COUNT(id) AS total FROM dshot GROUP BY EXTRACT(MONTH FROM date) ORDER BY date DESC

                            I have now added the "id" to the query abobe, because you can also pass this one to the details page, example:

                            a href="details.php?id=">more...

                            oops, sorry -- one huge problem with these forums is :: they often don´t display embedded html/php code well :-(

                            Günter Schenk,
                            Adobe Community Expert, Dreamweaver
                            • 11. Re: Archive by month
                              Level 1
                              I have fixed things so far. The only thing im having trouble with now id displaying the total records by date,name,url by month.

                              Let me try to be more specific. I have a list that will now list the months if there is a date in that month. Now all I want to do is pull all the info from that month when I click say september,october etc.

                              I have it pulling like this:

                              August 3 (it has three entries for the month 8)
                              july 1 ( has 1 entry)

                              August and july are links and when I click them I want it to display all entries by the id,date,name,url.

                              How that makes a little more sense
                              • 12. Re: Archive by month
                                Günter Schenk Level 4
                                Hi,

                                >>
                                August and july are links and when I click them I want it to display all entries by the id,date,name,url.
                                >>

                                as I said before -- I´m pretty certain that you´ll have to add the extra "month" and "year" - extraction stuff I suggested above [MONTH(date) AS link_month, YEAR(date) AS link_year] to both your query and - follwing - as URL parameter/values to the link you´re mentioning here. Please think about this :: the detail page can only *know* what it´s supposed to show when clearly being informed about the to-be-displayed year & month.

                                as these forums usually keeps messing up posted code, I´ll try to explain it this way:

                                1. the link is, say, pointing to "details.php"

                                2. add parameter 1 :: ?month=

                                3. add parameter 2 :: &year=

                                Hope this works -- however I fear I can´t give you any more advice than what I did up to now !

                                Günter Schenk,
                                Adobe Community Expert, Dreamweaver
                                • 13. Re: Archive by month
                                  Level 1
                                  Thanks. Ill get it sorted...
                                  • 14. Re: Archive by month
                                    eokorie Level 1
                                    Hi,

                                    I did something sort of similar for my blog so that I could list articles by month-year and display the total number of articles for that particular month.


                                    SELECT COUNT(blg_article_art.id_art) AS ArticleTotal, blg_article_art.date_art
                                    FROM blg_article_art
                                    GROUP BY EXTRACT(YEAR_MONTH FROM blg_article_art.date_art)


                                    This is the php code that I used to send year and month information:


                                    "> ()




                                    Don't know if this helps.
                                    • 15. Re: Archive by month
                                      Level 1
                                      Thanks for the reply. I think your code got messed up. Still have a few issues but am getting it I think.

                                      I have is set up to where it will list out the months like January, February, March etc only if they have at least 1 record for that month in the database. The only issue I am having now is being able to display all the records by month when I click the month. I have tries the stuff suggested and im getting nothing still.
                                      • 16. Re: Archive by month
                                        Günter Schenk Level 4
                                        >>
                                        The only issue I am having now is being able to display all the records by month when I click the month
                                        >>

                                        what are the variables you´re passing to the "details" page, and what content to they display when looking at the "master" page´s source code ?

                                        Günter Schenk
                                        Adobe Community Expert, Dreamweaver
                                        • 17. Re: Archive by month
                                          Level 1
                                          I have this. $query_bymonth = "SELECT id, dname, durl, date, COUNT(id) AS total FROM dshot GROUP BY EXTRACT(MONTH FROM date) ORDER BY date DESC "; and this....

                                          a href="shot2.php?month=">






                                          ouputs this:

                                          January 2007 1 (1=total records)
                                          December 2007 3
                                          etc etc...

                                          I have this on the detail page..

                                          $colname_months = "-1";
                                          if (isset($_GET['month'])) {
                                          $colname_months = $_GET['month'];
                                          }
                                          mysql_select_db($database_okconnect, $okconnect);
                                          $query_months = sprintf("SELECT * FROM dshot WHERE `date` = %s", GetSQLValueString($colname_months, "date"));
                                          $months = mysql_query($query_months, $okconnect) or die(mysql_error());
                                          $row_months = mysql_fetch_assoc($months);
                                          $totalRows_months = mysql_num_rows($months);

                                          I am wanting to output the info for the months like this.

                                          $colname_months = "-1";
                                          if (isset($_GET['date'])) {
                                          $colname_months = $_GET['date'];
                                          }
                                          mysql_select_db($database_okconnect, $okconnect);
                                          $query_months = sprintf("SELECT * FROM dshot WHERE `date` = %s", GetSQLValueString($colname_months, "date"));
                                          $months = mysql_query($query_months, $okconnect) or die(mysql_error());
                                          $row_months = mysql_fetch_assoc($months);
                                          $totalRows_months = mysql_num_rows($months);
                                          • 18. Re: Archive by month
                                            Günter Schenk Level 4
                                            Hi,

                                            for being able to display the matching links for e.g. month 3 and year 2007, all your "strtotime" formattings in the link parameters will not help you any further, because this is not how the date (0000-00-00) is getting stored in the database -- it´s all numeric (YYYY-MM-DD, e.g. 2007-09-02), and that´s why I already advised you to add [MONTH(date) AS link_month, YEAR(date) AS link_year] to your query and pass these separated parameters/values ("link_month" respectively "link_year") to "shot2.php".

                                            If you´re querying a MySQL date field like you do now (SELECT * FROM dshot WHERE `date` = %s), you can´t get any results, because there is no month "January" stored in the date field ! Your URL parameter "month" will have to pass a strictly numeric value from 01 - 12.

                                            In this context it´s also obvious that you´re passing *way* to many needless and in fact disturbing information () along with the "month" parameter -- you don´t need that, in particular with a "month" info which *only* has to be 01-12 and nothing else.

                                            In addition to that :: I don´t see any *year* (e.g. 2007) passed to "shot2.php" -- how do you want to extract the matching records without this important component ? Passing the month alone will not be enough info once your "links list" has entries spanning more than just one year -- when it´s all in e.g. 2007, the month is sufficient, but on the long run you need more than this.

                                            Günter Schenk
                                            Adobe Community Expert, Dreamweaver
                                            • 19. Re: Archive by month
                                              Level 1
                                              OK... I have fixed things up a little better. Sorry. I am new to all this..

                                              I have this for my query:
                                              $query_bymonth = "SELECT id, dname, durl, MONTH(date) AS link_month, YEAR(date) AS link_year FROM dshot ORDER BY link_year DESC, link_month DESC ";

                                              I am biliding my list like this for now just to get it working I will format the display date later.


                                              a href="shot2.php?month=&year=">






                                              I hope that is more simplified and a better way as suggested.

                                              The only thing that isnt clicking for me from your suggestions is this

                                              WHERE year = [use GET variable "year"] AND month = [use GET variable "month"]

                                              this part to be exact [use GET variable "year"] (.and for month.)
                                              • 20. Re: Archive by month
                                                Level 1
                                                Nevermind... I got the part I didint understand.
                                                • 21. Re: Archive by month
                                                  Level 1
                                                  I GOT IT!!! Works how I want. Thank you for your support and patients.
                                                  • 22. Re: Archive by month
                                                    Günter Schenk Level 4
                                                    You´re welcome :-)

                                                    Günter Schenk
                                                    Adobe Community Expert, Dreamweaver