Page 1 of 1

Display contents of database by date.

Posted: Tue Jun 24, 2008 9:30 am
by Darragh3277
Hi all, i'm new to devnet. I've been browsing it for a while now and its helped me out with a lot of stuff in the past so i'd like to say a general thank you to everyone first.

Now on to my question, i'm sorry if I can't explain it perfectly, as i'm not too sure how to describe it.

I'm making website for a local sports league. In my database I have 2 tables one to display and store the league data and one to display the results of each indivual match. I have built a form where I input the date, teams, scores. This for then updates both tables.

I can make the league table and results update and display perfectly. To display my results I use SORT BY DESC date. However, I would like to able to display my results by having a seperate table for each date. Starting from the newest to oldest. e.g:

Date 10/5/08
Team A 1 - 2Team B
Team C 0 - 2Team D

Date 03/5/08
Team E 2 - 2Team F
Team G 3 - 2Team H

I don't want to have to manually edit and add a new table each week. I was hoping that everytime I update my database, my page would automatically update itself starting with the newest date and descending to the oldest date.

Any help would be great! Thanks

Re: Display contents of database by date.

Posted: Tue Jun 24, 2008 9:34 am
by Kieran Huggins
Ack!

just store the date of the results in the results table, you can query and order based on that very easily:

Code: Select all

id    |    team_1    |    team_2    |    score_1    |    score_2    |    date

Re: Display contents of database by date.

Posted: Tue Jun 24, 2008 10:17 am
by Darragh3277
Kieran Huggins wrote:Ack!

just store the date of the results in the results table, you can query and order based on that very easily:

Code: Select all

id    |    team_1    |    team_2    |    score_1    |    score_2    |    date
Thanks for the quick reply!

Thats how I have my table set up. It's just i'm unsure on how to dynamically update my page as I add new dates?

Re: Display contents of database by date.

Posted: Tue Jun 24, 2008 10:28 am
by Kieran Huggins
I would assume that every time the page is viewed it runs the same SQL to retrieve the info from the database...

like:

Code: Select all

// get the data
$result = MySQL_query("SELECT * FROM results ORDER BY date DESC");
 
// display it
$last_date = null;
while($row = MySQL_fetch_assoc($result)){
  if ($row['date'] != $last_date) {
    echo "<h2>".$row['date']."</h2>";
    $last_date = $row['date'];
  }
  echo "<p>".$row['team_1'].": ".$row['score_1']." - ".$row['team_2'].": ".$row['score_2']."</p>";
}

Re: Display contents of database by date.

Posted: Tue Jun 24, 2008 10:35 am
by Darragh3277
Cheers! Thats perfect thanks!