Database date changing display (Full Version)

All Forums >> [Web Development] >> ASP, PHP, and Database



Message

steveg -> Database date changing display (8/31/2009 11:43:22)

I have an Access database that contains fixtures for our hockey team.

I also have a database that contains Next Home game and Next Away game details along with Last score.

I wouldn't need the Next Game database if I could generate the Next game display from the fixture database.

Is it possible to generate some code that will display some fields of information like day, opposition, date and face off time based upon a comparison to the current date. For example if there was a game on Saturday 5th September, could the next game details identify the record with the game that is next in the list and then when Saturday has passed look for the next game in the list.

We play away and home every week. I also like to show the score of the last game. This data is also kept in the fixture list so could also be displayed in a similar manner, if it can be done at all.

I don't know if I have been clear on this and I will happily explain in more detail if required.

The website is www.chelmsfordchieftains.com and the Next game and last result details can be seen on the home page. If you select team Information from the menu you can choose Fixtures/Results and see the fixtures data table that I have and would like to use as the master.

This would mean that we would not need to update this info every week and it would be automatic.

Any and all help would be gladly received.[:D]

Steve



steveg -> RE: Database date changing display (9/3/2009 6:59:56)

I guess this is a little trickier than I first thought? [:(]



TexasWebDevelopers -> RE: Database date changing display (9/3/2009 11:25:54)

Not really--its just that there is nothing to help code-wise without having a lot more information. Here is my general suggestion. If the information you want to pull from the database has a date column that can be used as the field to key on when to display the information then you can call data from the database based on that date.

For instance, if you want to display last weeks results and next weeks new information then code the sql something like this:

sqlresults = SELECT * FROM [results_table] WHERE [date_column] BETWEEN date() AND date()-7

sqlnextgame = SELECT * FROM [nextgame_table] WHERE [date_column] BETWEEN date() AND date()+7

In other words pull the data whose date is today backwards seven days or today plus seven days into the future.



steveg -> RE: Database date changing display (9/3/2009 11:28:43)

OK. That makes sense. I will create my DRW, give it the Spooky diet and then tweak the selection criteria. Should that process work?

Thanks

Steve



swoosh -> RE: Database date changing display (9/3/2009 11:33:52)

TWD,

What happens when future game dates are as follows:

January 30
Janurary 31

I'm just trying to learn something here. If the above is the case, which I believe from his site it does happen a few times, how will the 30th date take precedence and how will it exclude the 31st date?



steveg -> RE: Database date changing display (9/3/2009 11:39:39)

I think that in our case the two consecutive games will be differentiated by Home and AWAY (although I need to check as there could be two games AWAY consecutively, albeit unlikely from a travelling perspective. Thinking about it we may have a midweek game that would be in the range).

I guess I need to pick the next game in the one week range as opposed to potentially two games (both home or away) from that range.

How would I restrict it to the next game only.

Steve



steveg -> RE: Database date changing display (9/3/2009 12:13:48)

I have just tried this: 
"SELECT * FROM fixtures WHERE (Date >= Date() AND Date <= Date()+7)"


and got these results:

Day Date Home/Away Opponents Face Off
Saturday 05-Sep-09 Away MK Thunder 19:00:00
Sunday 06-Sep-09 Home Haringey Greyhounds 18:30:00
Sunday 04-Oct-09 Away Haringey Greyhounds 17:30:00
Saturday 07-Nov-09 Away MK Thunder 19:00:00
Sunday 08-Nov-09 Home Cardiff Devils 18:30:00
Sunday 06-Dec-09 Home Invicta Dynamos 18:30:00
Sunday 03-Jan-10 Away Invicta Dynamos 17:15:00
Sunday 07-Feb-10 Home Oxford City Stars 18:30:00
Saturday 06-Mar-10 Away Bracknell Hornets 18:30:00
Sunday 07-Mar-10 Home Cardiff Devils 18:30:00

Not quite what I expected. [:)]

Steve



steveg -> RE: Database date changing display (9/3/2009 12:28:13)

I have also used Between instead of >= and <= with the same results.

When I change the Date()+7 to Date()+1 I get

Day Date Home/Away Opponents Face Off Competition
Sunday 03-Jan-10 Away Invicta Dynamos 17:15:00 League

Which baffles me even more.

It would appear that the date function is not treated as a consecutive number? (20090905, 20090906 etc.)

Steve



TexasWebDevelopers -> RE: Database date changing display (9/3/2009 13:44:51)

OK--first, it looks like your column name is "Date" (?) and that is a forbidden field name.

quote:

"SELECT * FROM fixtures WHERE (Date >= Date() AND Date <= Date()+7)"


WHERE (Date >= Date() -- this would give you everything from today to infinity
AND Date <=Date()=7 -- PLUS this gives you everything from seven days out back to today.

The results you got are the results you asked for.



steveg -> RE: Database date changing display (9/3/2009 17:24:08)

OK. I've changed the field name to FDate and my code is now:

"SELECT * FROM fixtures WHERE FDate BETWEEN Date() AND Date()+7"

and I get the same 10 results that I got previously.

If I change the last variable to date()+1 I get 1 fixture from 3 Jan 2010??

Steve



TexasWebDevelopers -> RE: Database date changing display (9/3/2009 18:24:12)

So what is the format of the fdate column? It probably should be date/time? I'm guessing you have it as text? What country is the server in (makes a difference as to how the date/time field is formatted --American vs British)?



steveg -> RE: Database date changing display (9/4/2009 7:45:07)

e FDate field is a Date/Time field. I have selected short date in the database but it is still displaying as hh:mm:ss??

The server is a UK server.

Steve



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.078125