need some sql help

Discussion in 'OT Technology' started by JoeyJoJoJuniorShabadoo, Jun 3, 2008.

  1. JoeyJoJoJuniorShabadoo

    JoeyJoJoJuniorShabadoo Live Free or Die

    Joined:
    Nov 9, 2004
    Messages:
    4,347
    Likes Received:
    0
    Location:
    http://www.stogietime.com
    I am a noob at sql and need some help figuring out the best way to do something.


    I have a database table called pictures, it has columns like this

    pic_id user_id pictureaddress ipaddress year month day time

    where year, month, day and time are when the picture was taken. What I want to do is I want to be able to query on this database for a certain userid and get all their pictures. Thats no problem, but what I also want to do is I want to have a treeview that would start with the year, then goto month, then goto day type of thing. But I don't know an efficient way of getting that information out of the sql table. So basically I would want to have a call to the database to first get however many years there are, then go in and have the where clause for the year and see what months to display, and then use a where clause with year and month, and see what days to put on there. I know I could probably do it with the distinct keyword so I would do something like select distinct year from mytable where user_id = 23 and that should give me back a list of 3 or 4 years depending on what is in the database correct? THen I would have to go through each year and do something like select distinct month from mytable where user_id = 23 and year = 2008 then I would have to do the same for day. This doesnt seem very efficient. Is there a way to give one sql statement that will give me back all the information that I need using joins and junk, or no?
     
  2. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    Code:
    SELECT
        `t`.`year` AS `Year`,
        `t`.`month` AS `Month`,
        `t`.`day` AS `Day`,
        `t.`pic_id` AS `ID`,
        `t`.`pictureaddress` AS `URL`,
        `t`.`ipaddress` AS `IP`
    FROM
        `table_name` t
    WHERE
        `t`.`user_id` = '123456'
    ORDER BY
        `t`.`year` DESC, 
        `t`.`month` ASC,
        `t`.`day` ASC;
    
    That will give you output such as (hypothetical):

    Code:
    Year, Month, Day, ID, URL, IP
    2008, 03, 01, 576, http://path, 127.0.0.1
    2008, 05, 14, 142, http://path, 127.0.0.1
    2008, 05, 30, 123, http://path, 127.0.0.1
    2007, 04, 12, 001, http://path, 127.0.0.1
    2007, 12, 01, 235, http://path, 127.0.0.1
    2005, 07, 24, 405, http://path, 127.0.0.1
    2004, 09, 17, 667, http://path, 127.0.0.1
    2004, 08, 03, 341, http://path, 127.0.0.1
    2001, 01, 01, 507, http://path, 127.0.0.1
    2001, 02, 01, 984, http://path, 127.0.0.1
    2001, 04, 01, 124, http://path, 127.0.0.1
    2001, 05, 05, 455, http://path, 127.0.0.1
    
    now given that output you can start your tree:

    take the year and store it in a variable.
    print the year
    take the month and store it in a variable.
    indent once
    print the month
    indent once
    print the day

    compare the year of the 2nd entry
    if the same as the stored year, then indent once and check the month, if it's the same as the stored month, indent and print the date, otherwise store the month and print it, then indent and print the date.
    if the years did not match, store the new year in the variable and print the year. then indent and print the month, indent and print the day. so on, and so forth.

    rinse and repeat.


    ONE sql statement, and some logic.
     
  3. JoeyJoJoJuniorShabadoo

    JoeyJoJoJuniorShabadoo Live Free or Die

    Joined:
    Nov 9, 2004
    Messages:
    4,347
    Likes Received:
    0
    Location:
    http://www.stogietime.com
    Thank you, I appreciate your help, thats the kind of sql statement I was looking for.
     

Share This Page