1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Searching for Age Range by Date of Birth

Discussion in 'PHP' started by audax, Nov 15, 2009.

  1. #1
    Hey there. I'm trying to build a script that searches for an age range based off of a user's date of birth. For some reason, people aren't showing up, and they are the people that are the extreme ends of the age ranges. For instance, I have an age range of 18 to 29 year olds, but some 29 year olds don't show up. Here's the script I'm running:

    
    $age = cleanInput($_GET['age']);
    $thisyear = date('Y');
    
    if($age == '18-29') 
    {
       $youngest = $thisyear - 18;  $oldest = $thisyear - 29;
    }
    
    $oldest = $oldest.date('-m-d');
    $youngest = $youngest.date('-m-d');
    PHP:
    Then the query:

    $query = "SELECT * FROM actors WHERE (dob <= '$youngest' AND dob >= '$oldest')";
    PHP:
    The dob field in the database is in the form Y-m-d. Is there a different way to do it that I might be able to use? Any help would be appreciated.
    SEMrush
     
    audax, Nov 15, 2009 IP
    SEMrush
  2. Wrighty

    Wrighty Peon

    Messages:
    199
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #2
    You could work out what the youngest person's date of birth would be ... Time-(18*365*24*60) .. then convert it to a UNIX time or something...

    I can give you further information if you don't understand exactly what I mean! :)
     
    Wrighty, Nov 15, 2009 IP
  3. audax

    audax Peon

    Messages:
    83
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Are you suggesting mktime? How would I implement that?
     
    audax, Nov 15, 2009 IP
  4. mastermunj

    mastermunj Member

    Messages:
    687
    Likes Received:
    13
    Best Answers:
    0
    Trophy Points:
    35
    #4
    why not use following query?

    
    SELECT *, (DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW()) - TO_DAYS(dob)), '%Y') + 0) AS age FROM actors HAVING age BETWEEN $start_age AND $end_age
    
    PHP:
    where
    $start_age is minimum of age range to search - e.g. 18
    $end_age is maximum of age range to search - e.g. 29
     
    mastermunj, Nov 15, 2009 IP
  5. audax

    audax Peon

    Messages:
    83
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #5
    I'm trying that query and for some reason I'm getting an error stating that the syntax is incorrect. I have additional parameters that I'm search for as well, so how would I add those in? (gender, class, etc)
     
    audax, Nov 16, 2009 IP
  6. mastermunj

    mastermunj Member

    Messages:
    687
    Likes Received:
    13
    Best Answers:
    0
    Trophy Points:
    35
    #6
    you can use where in that case..

    
    SELECT *, (DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW()) - TO_DAYS(dob)), '%Y') + 0) AS age FROM actors WHERE gender = 'M' and class = 'XYZ' HAVING age BETWEEN $start_age AND $end_age;
    
    PHP:
     
    mastermunj, Nov 16, 2009 IP
  7. audax

    audax Peon

    Messages:
    83
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #7
    EDIT: Scratch what I just said. It's working perfectly now! Thanks for your help!
     
    audax, Nov 16, 2009 IP
  8. mastermunj

    mastermunj Member

    Messages:
    687
    Likes Received:
    13
    Best Answers:
    0
    Trophy Points:
    35
    #8
    they should be actual age as explained in my example.
    glad that it works for you :)
     
    mastermunj, Nov 16, 2009 IP
  9. reubenrd

    reubenrd Well-Known Member

    Messages:
    429
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    108
    #9
    Was just wandering if mastermunj query can be converted to allow for multiple age groups.

    For example

    (18- 24) & (25 - 34) & (35 - 44).....
    Or if one wanted to miss out the age group (25 - 34) WOULD have a query like...
    (18-24) & (35 - 44) ....

    Is this possible
     
    reubenrd, Feb 2, 2012 IP
  10. mastermunj

    mastermunj Member

    Messages:
    687
    Likes Received:
    13
    Best Answers:
    0
    Trophy Points:
    35
    #10
    @reubenrd, There is no direct way to prepare ranges and group them.

    Following approaches would be suggested.
    1. If such queries are frequent and data size is large, having a separate column is best option in all sense.
    2. If you however want it to be part of query only, then its tricky and goes like this..

    
    SELECT field1, field2, IF(age BETWEEN 18 TO 24, '18-24', IF(age BETWEEN 25 TO 34, '25-34', IF(age BETWEEN 35 TO 44, '35-44', 'Above 44'))) AS AgeGroup FROM table WHERE condition GROUP BY AgeGroup
    
    Code (markup):
    You can replace IF with CASE statement if wish to. Check out MySQL manual for that.
     
    mastermunj, Feb 6, 2012 IP