SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    SitePoint Addict Phil-man's Avatar
    Join Date
    Nov 2000
    Posts
    291
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi everybody...

    Suppose I have a database that contains a concert schedule, with, among other things, the date for each concert (2001-03-01). I'm using PHP to display all concert dates that haven't already passed, in chronological order. What I want to do, however, is put the name of the month and the year prior to the first concert of each month. So it would look something like this:

    FEBRUARY, 2001

    - 02/24/01... <venue> <contact info>...
    - 02/25/01... <venue> <contact info>...
    ...
    ...

    MARCH, 2001

    - 03/01/01... <venue> <contact info>...
    - 03/10/01... <venue> <contact info>...

    You get the idea. What's the best way to do this? I can think of one way to do it (but I'm guessing there's a better way): Create another table to store the month/year values and do a JOIN to connect the two tables. Then I could do a nested loop on a single query result to get something that looks like the above. Or, I suppose I could use PHP to convert the first date to the appropriate month/year and then check each subsequent date to see if it has the same month/year value. As soon as it doesn't have the same value, I would do a new paragraph, convert the new month/year, and start over.

    1) Is there a way to do this that is better than both of these?
    2) If not, which of these do you think is better, in terms of performance and other measures?

    Thanks for any help. Have a great day...

  2. #2
    imagine no limitations exbabylon's Avatar
    Join Date
    Dec 2000
    Location
    Idaho, USA
    Posts
    452
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how about adding just one more field to your existing table? Label it month, or whatever... then it would be easy, without having to use two tables to perform it.
    Blamestorming: Sitting around in a group discussing why a deadline was missed or a project failed and who was responsible.

    Exbabylon- Professional Internet Services

  3. #3
    SitePoint Addict Phil-man's Avatar
    Join Date
    Nov 2000
    Posts
    291
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So basically you're suggesting I use the PHP method that I described above, except that the month would come right out of the database instead of having PHP convert it from the date field, correct? Then I guess the question would be, is that (creating an extra field in the existing table) a better solution? And if so, I'm guessing it would make more sense to have PHP do the conversion on the front end and write it to the database so the user doesn't have to enter a full date and then also the month spelled out. Any other thoughts? Thanks again.

  4. #4
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll assume your using mySQL.

    1) Don't add an extra column for month. That just adds data redundancy and breaks the third normal form. To think of it practically, if you want to update the concert date but forget to also update the month column (or erroneously update it) you now have corrupt data. Or, if your user is typing in these details from a form if they make a typo when typing in the month or accidently select the wrong month from your pull-down list - again corrupt data

    2) SQL is your friend. It is a powerful data manipulation language. So I suggest you can do all your date formating so you end up with a very useful result set from your sql query.

    PHP Code:
    $sql "
       SELECT
          YEAR(date) AS year,
          MONTH(date) as month,
          MONTHNAME(date) as month_name,
          DAYOFMONTH(date) as month_day
       FROM Concerts
       ORDER BY year, month, month_day "
    ;

    $result mysql_query($sql); 
    3) now just iterate throught the result set adding a new subheading everytime a new year/month pops up.

    PHP Code:
    $year 0;
    $month 0;

    while ( 
    $concert mysql_fetch_array($result) {

       if ( ! (
    $concert["year"] == $year && $concert["month"] == $month) ) {
          
    // print out a new heading (month/year)
          
    $year $concert["year"];
          
    $month $concert["month"];
          }

       
    // now print out the concert details

    You don't need to initialise $year and $month as I did (setting them to zero) - but I just do out of habit It aids my understanding of the logic of the code when I read back over it.
    Last edited by freakysid; Feb 19, 2001 at 22:30.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •