The forums have permanently moved to forum.kirupa.com. This forum will be kept around in read-only mode for archival purposes. To learn how to continue using your existing account on the new forums, check out this thread.


Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Database / Query Design Help

  1. #1

    Database / Query Design Help

    I'm creating an application that will retrieve information from a MySQL database depending on the current date.

    The table structure is someting like this:

    ID DATE LOCATION
    0 01/02/2007 London
    1 02/02/2007 Paris
    2 14/04/2007 Mexico
    3 22/04/2007 Fiji
    4 11/06/2007 Toronto
    5 29/09/2007 Sydney

    What I want to do is select the location by the current date, so the select statement would be something like:

    "SELECT * WHERE date='02/02/2007'";

    From the table above this would select 'Paris' as the location. But as you can see, there is not a different location for every single day. Some locations remain the same for months.

    So, my question is this:

    I don't want to have to enter the location for every single day of the next year into the database.

    ie:

    ID DATE LOCATION
    1 02/02/2007 Paris
    2 03/02/2007 Paris
    3 04/02/2007 Paris
    4 05/02/2007 Paris
    5 06/02/2007 Paris

    This would take far too long and waste resources. But how then, if the current date is not one that is specifically specified in the "date" column, could I select the location from the current date?

    I hope this makes sense. Any help would be very much appreciated.
    Last edited by duncanhall; January 31st, 2007 at 08:58 AM.

  2. #2
    based on what you've said thusfar, here's what i would do:
    Code:
    Locations
    ---------------
    location_id (PK)
    city
    country
    
    Events
    ---------------
    event_id (PK)
    date_start
    date_end
    location_id (FK)

  3. #3
    Couldnt you search the database on month and year to get rid of most entries then count backwards using the current entries.. using your example:

    ID DATE LOCATION

    0 01/02/2007 London
    1 07/02/2007 Paris
    2 14/04/2007 Mexico
    3 22/04/2007 Fiji
    4 11/06/2007 Toronto
    5 29/09/2007 Sydney
    then searches month and year:

    ID DATE LOCATION

    0 01/02/2007 London
    1 07/02/2007 Paris

    then counts back form say you entered the 6th, it would count back until it hit a place which is London. and would output that enry

    well theres a system now if you want the code I can do it for you, but its pretty simple

  4. #4
    ok this could be done quite easily if you change your tabel layout.
    i.e.
    id, date_in, date_out, location

    find the location where the date is in the range of date_in, date_out.

    Hope that this helps...

  5. #5
    Ok, so my SQL knowledge is probably one of my weakest areas.

    Firstly, thanks to all of you for the replies.

    bwh2:
    My interpretation of your idea is that I have an event, which has an ID. I use the event ID to lookup a location ID, from which I then gather the city and country of the location.

    If this understanding is right, then I don't think it will work (for this situation). Basically, I have a person who is going to be in different locations at different times. I have list of where and when he will be, but no event to tie this to. So for example, I know that on the 30th of March he will be in Hong Kong for 3 weeks. If I access this application on the 12th of April, I have no way of saying "This is the event he is attending, where is the event?". What I have to say is - "Today is the 12th of April, where does that mean he is?".

    borrob, I'm not exactly sure how I would implement yours. It would seem that searching for the location between a range of dates could return more than one location.

    So, drummer, it's down to you. I followed most of what you said, and get the idea of eradicating most of the unneeded entries, but you could explain a little further?

  6. #6
    Quote Originally Posted by duncanhall View Post
    bwh2:
    My interpretation of your idea is that I have an event, which has an ID. I use the event ID to lookup a location ID, from which I then gather the city and country of the location.

    If this understanding is right, then I don't think it will work (for this situation). Basically, I have a person who is going to be in different locations at different times. I have list of where and when he will be, but no event to tie this to. So for example, I know that on the 30th of March he will be in Hong Kong for 3 weeks. If I access this application on the 12th of April, I have no way of saying "This is the event he is attending, where is the event?". What I have to say is - "Today is the 12th of April, where does that mean he is?".
    it works just fine. change the word "Event" to "Trip". then you're just going to use a BETWEEN statement. in other words:
    Code:
    Trips
    -------------
    trip_id
    location_id
    date_start
    date_end
    
    Locations
    -------------
    location_id
    city
    country
    Code:
    /* what trip is he on right now? */
    SELECT a.*,b.city,b.country
    FROM Trips a
    INNER JOIN Locations b
    ON a.location_id = b.location_id
    WHERE CURDATE()
    BETWEEN a.date_start AND a.date_end

  7. #7
    Ok, I pretty much watched that soar over my head, but I think have the knack (and the text books) to figure it out. Just a couple of things though - what does the 'ON' keyword refer to; and is this assuming that my 'dates' are in the MySQL format of date? This is going to be a Flash application so I was just going to put them in the format of "31/01/2007" because Im going to be getting the date via ActionScript, then use that as the variable in my select statement.

  8. #8
    yes, make the datatype "date" for your date_start and date_end columns. you can always format the date output later. but it should be stored as "date".

    the ON is part of the INNER JOIN statement. so when you join tables, you link them up ON a common field. in this case, the common field is location_id.

    we use an ID field as the link because we don't want to duplicate data. so instead of putting 'Paris' in a location column of the Trips table, querying is faster and the db is easier to maintain if we use location_id, then hold the name 'Paris' in Locations table. you can read the Relational Database Design tutorial here for more info. basically this is called normalizing the data.

    so the ON just refers to the columns we are linking together. `a` and `b` are just alias names. in other words, it's easier to type `a` than to type `Trips`. without the alias named, we could write that query like this:
    Code:
    /* what trip is he on right now? */
    SELECT Trips.*,Locations.city,Locations.country
    FROM Trips
    INNER JOIN Locations
    ON Trips.location_id = Locations.location_id
    WHERE CURDATE()
    BETWEEN Trips.date_start AND Trips.date_end
    it's the same thing, just no alias names

  9. #9
    Ahh, it's all slowly coming back to me now. Thanks a lot, that was a lot clearer without the alias names. Now all I have to is enter latitude and longitude values for 67 different locations!

  10. #10

  11. #11
    Yeah, I'm on it. Pretty laborious though. It should be a pretty sweet project hopefully, although the dude's leaving in 3 weeks so I haven't got long.

  12. #12
    i would push it to Job Offers and offer up $25. have them enter it to an excel sheet, then you can straight import that. just make the excel sheet the same format as your Locations table.

  13. #13
    I just found a flaw in mine, if you search say may the 1st then it wouldnt go back into the last month

    Looks like Bwh's idea wins (...as always)

  14. #14

  15. #15
    yeah, but one day I'll get there

    might also be because all the web dev/design jobs I get dont have any PHP in them so I hardly know any

Page 1 of 2 12 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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

Home About kirupa.com Meet the Moderators Advertise

 Link to Us

 Credits

Copyright 1999 - 2012