MySQL/php people...

Discussion in 'OT Technology' started by venger240, Oct 28, 2005.

  1. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN
    Ok, I need a little help. First, let me say that im definetly more of a design guy. However I do know a little bit of mysql/php. Im working on a project for my company. Basically we perform audits on our employees and from every audit there comes a few things. A name, the managers name, the date, the score, and the case type. My job is to setup a database were we can store all of this information and sort by various fields. I can set everything up but im having a problem with sorting by date. I am not sure the best way of going about setting this up. At first I thought about setting the date up individually such as day, month, and year. However if they wanted to sort from 09/23/05 - 10/23/05 I wouldnt be sure of how to do that.

    Im sorry if this doesnt make any sense, I have been at work on this nonstop and I cannot figure out the solution. Any ideas? Thanks.
     
  2. Penguin Man

    Penguin Man Protect Your Digital Liberties

    Joined:
    Apr 27, 2002
    Messages:
    21,696
    Likes Received:
    0
    Location:
    Edmonton, AB
    Use MySQL's Date type.
     
  3. RaginBajin

    RaginBajin Have you punched a donkey today?

    Joined:
    Dec 24, 2001
    Messages:
    8,740
    Likes Received:
    0
    Location:
    NoVA
    Penguin Man is right.. Having it aas a date field lets you use a ton of Mysql features to figure out your data.
     
  4. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    yeah, individual fields per portion of the date will cause you mucho problems.
     
  5. Joe_Cool

    Joe_Cool Never trust a woman or a government. Moderator

    Joined:
    Jun 30, 2003
    Messages:
    299,206
    Likes Received:
    516
    :werd:

    Never code program logic to do something, when you can have the database do the work for you.
     
  6. CyberBullets

    CyberBullets I reach to the sky, and call out your name. If I c

    Joined:
    Nov 13, 2001
    Messages:
    11,865
    Likes Received:
    0
    Location:
    BC, Canada/Stockholm, Sweden
    what you need to do is store it as a long in seconds from the unix epoc.


    or just use the date type from mysql ;)
     
  7. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN
    Ok, see I knew there was an easy solution. I guess I had tried this before and it was really messed up the way it put the date. If I remember it was *like 06102005 or something weird like that? Lets say for example I do use the date type and I wanted to do the following: table (audits)

    Date Name Score
    10/22/05 John 55
    10/23/05 Sally 56
    10/24/05 Sue 58
    10/22/05 Jack 60
    10/15/05 Jack 55

    Can someone show me an example of how I could pull the files from 10/15/05 - 10/23/05 assuming I setup using the date type.

    Thanks for your help.
    10/23/05 John 60
     
  8. Slid.

    Slid. I'm a guy.

    Joined:
    Oct 25, 2001
    Messages:
    1,928
    Likes Received:
    0
    Location:
    NH
    SELECT * FROM audits WHERE date BETWEEN '2005-10-15' AND '2005-10-23'
     
  9. Slid.

    Slid. I'm a guy.

    Joined:
    Oct 25, 2001
    Messages:
    1,928
    Likes Received:
    0
    Location:
    NH
    I haven't used BETWEEN for a while but I think that if you want a range from the 15th to the 23rd you might actually have to go

    BETWEEN '2005-10-14' AND '2005-10-22'

    See what works for you.
     
  10. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN
    Ok, the between command, got that.

    Could this work as well?

    SELECT * FROM audits WHERE date > '2005-10-15' AND < '2005-10-23'

    Sorry guys, i really am a newb at this.
     
  11. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN
    Also, what would the line be if I wanted to average the scores between that date range? Once again, thanks for all the help.
     
  12. Penguin Man

    Penguin Man Protect Your Digital Liberties

    Joined:
    Apr 27, 2002
    Messages:
    21,696
    Likes Received:
    0
    Location:
    Edmonton, AB
    BETWEEN '2005-10-14' AND '2005-10-24' actually, since it's exclusive of the endpoints. At least that's how we do it in Oracle. IIRC, Date is part of SQL standard, so I assume that it's handled the same way in most DB's.
     
  13. Penguin Man

    Penguin Man Protect Your Digital Liberties

    Joined:
    Apr 27, 2002
    Messages:
    21,696
    Likes Received:
    0
    Location:
    Edmonton, AB
    SELECT AVG(score)
    FROM audits
    WHERE date BETWEEN '2005-10-14' AND '2005-10-24'
     
  14. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN
    Awesome, thank you so much for the help. I hate to keep asking questions but I have another.

    I'm assuming that if I just use curdate, it will put it in the date field correctly? What im worried about are some of our "less bright" people messing up the proper way to enter the date and then it fucks up that row... If I can just use curdate and it puts it in correctly, then I will not even have to worry about that.
     
  15. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN
    NM, just tested the curdate and it w*orked perfect. Thanks!
     
  16. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN

    This doesnt seem to work. Its probably my fault though. Below is the code that I am using, can you verify that im using it in the right way?


    PHP:
    $year1 $_POST['year1']; 
    $day1 $_POST['day1']; 
    $month1 $_POST['month1']; 

    $year2 $_POST['year2']; 
    $day2 $_POST['day2']; 
    $month2 $_POST['month2']; 

    $start "{$year1}-{$month1}-{$day1}"
    $end "{$year2}-{$month2}-{$day2}"

    $sql "SELECT AVG(score) FROM af WHERE date BETWEEN '".$start."' AND '".$end."' ";

    $ret mysql_query($sql) or die(mysql_error());

    echo 
    $ret;

    ?>

     
  17. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,915
    Likes Received:
    8
    Location:
    Los Angeles
    What is the datatype of your "score" field?
     
  18. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    I'd debug start and end to see if they're actual dates
     
  19. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,915
    Likes Received:
    8
    Location:
    Los Angeles
    They are. We discovered this in a thread posted earlier today.

    I have a feeling it has something to do with the datatype or the actual score.
     
  20. CyberBullets

    CyberBullets I reach to the sky, and call out your name. If I c

    Joined:
    Nov 13, 2001
    Messages:
    11,865
    Likes Received:
    0
    Location:
    BC, Canada/Stockholm, Sweden
    why not post the structure of the database?
     
  21. Penguin Man

    Penguin Man Protect Your Digital Liberties

    Joined:
    Apr 27, 2002
    Messages:
    21,696
    Likes Received:
    0
    Location:
    Edmonton, AB
    :werd:

    AVG will only work if you have score as some numeric type I believe. It would also help to know what error it's giving you back. Try entering the query on the commandline instead of via PHP (or in phpmyadmin or somewhere where you can get feedback).
     
  22. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,915
    Likes Received:
    8
    Location:
    Los Angeles
    This is exactly where I was going.
     
  23. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN

    Sorry guys, i have been out of town. score is an INT. Im not at work and I dont have access to my files to post the full structure type. I dont have access to a command line because the server that all of this is on, isnt one or my servers, so I just have very limited access to it.

    Any other ideas? Thanks again for all the help.
     
  24. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN
    Is there a different way I could go about it, such as Adding the column of scores, then dividing by the number of entries?
     
  25. CyberBullets

    CyberBullets I reach to the sky, and call out your name. If I c

    Joined:
    Nov 13, 2001
    Messages:
    11,865
    Likes Received:
    0
    Location:
    BC, Canada/Stockholm, Sweden
    you could manually add them up then divide by the number of rows, assuming each row has a value (assuming it is normalized properly)
     

Share This Page