Display contents of database by date.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Darragh3277
Forum Newbie
Posts: 12
Joined: Tue Jun 24, 2008 9:19 am

Display contents of database by date.

Post 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
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: Display contents of database by date.

Post 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
Darragh3277
Forum Newbie
Posts: 12
Joined: Tue Jun 24, 2008 9:19 am

Re: Display contents of database by date.

Post 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?
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: Display contents of database by date.

Post 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>";
}
Darragh3277
Forum Newbie
Posts: 12
Joined: Tue Jun 24, 2008 9:19 am

Re: Display contents of database by date.

Post by Darragh3277 »

Cheers! Thats perfect thanks!
Post Reply