Search users by age

graham23s · April 20, 2007

Hi Guys,

i have got PHP to read peoples ages by doing some basic math:


CODE
$birthday = $row["birthday"]; 
         $birthmonth = $row["birthmonth"];
         $birthday = $row["birthyear"]; 
         $dobtimestamp = strtotime($birthday.$birthmonth.date('Y'));
         if($dobtimestamp < time()) {  //Birthday has not been reached.
          $age = (date('Y') - $birthday) -1;
         }else{ //Birthday has been reached
          $age = date('Y') - $birthday;
         }


i know do:


CODE
echo $age


and the age of the user is worked out.

but when it comes to a search page i was wanting to do something like:


CODE
Search For Ages Between:
<select name="age">
<option value="22">22</option>
<option value="23">23</option>
<option value="24">24</option>
</select>
And:
<select name="age">
<option value="40">40</option>
<option value="41">41</option>
<option value="42">42</option>
</select>


^ this is just a basic example, i was wanting to search for users by say 25 and 40 then display the results, i was wondering if there were any tutorials on how to do this i have no idea where to start initially and advice would be appreciated:)

cheers guys

Graham

Barand · April 20, 2007

2 ways spring to mind

if range is 25 to 40, calculate the earliest dob at which someone could be 25 and the dob at which someone could be 40 then search between those dates.

Alternatively, write a custom MySQL function "AGE(dob)", if your version of MySQL supports functions, and then simple use ".. WHERE AGE(dob) BETWEEN 25 AND 40 .."

graham23s · April 20, 2007

Hi Mate,

Thanbks for the reply, heres my search code just now:

   if(!isset($_GET['gender']) || ($_GET['gender'] == "Prefer Not To Say")) { 
   
      echo "<br /><p>Sorry, You Never Selected A Gender</p>"; 
      exit;
      
   } elseif (($_GET['gender'] != "Male") && ($_GET['gender'] != "Female")) { 
   
   die("Error about not using 'M' || 'F' goes here"); 
   
   } 

   /* Has the sort method been specified? */ 
   $sort = ""; 

   if($_GET['Sort'] == "Ascending") { 
      $sort = "ASC"; 
   } else { 
      $sort = "DESC"; 
   } 

   /* Assign the gender now we know it's safe */ 
   $gender = $_GET['gender']; 
   // Begin gender search...///////////////////////////////////////////////////////////// 

   // Begin birthyear search...//////////////////////////////////////////////////////////    
   if(!isset($_GET['birthyear']) || !is_numeric($_GET['birthyear'])) { 
      echo "<br /><p>Sorry, You Never Selected A Year Of Birth</p>"; 
      exit;
   } 

   $birthyear = $_GET['birthyear']; 
   // Begin birthyear search...//////////////////////////////////////////////////////////   
      
   // Begin country search...////////////////////////////////////////////////////////////
   if(!isset($_GET['country']) || ($_GET['country'] == "Prefer Not To Say")) { 
      echo "<br /><p>Sorry, You Never Selected A Country</p>"; 
      exit; 
   } 

   $country = $_GET['country']; 
   // Begin country search...////////////////////////////////////////////////////////////
   
   /* Compile the SQL query */    
   $sql =    "SELECT * FROM `membership` "; 
   $sql .=   "WHERE `gender`='" . $gender . "' "; 
   $sql .=   "AND `birthyear`='" . $birthyear . "' "; 
   $sql .=   "AND `country`='" . $country . "' "; 
   $sql .=   "ORDER BY `id` " . $sort; 

   /* Query */ 
   $result = mysql_query($sql) or die(mysql_error()); 

   /* Display query results: */ 
   while ($row = mysql_fetch_array($result)){ 
            $id= $row['id']; 
            $username = $row['username']; 
            $gender = $row['gender']; 
            $headline = $row['headline'];
            $photo = $row['photo'];
            $country = $row['country'];
                
           // Display the users details in a box...
           echo "<br /><div id=\"search_profile\">";
           echo "<fieldset><legend>$username</legend>";
           echo "<table width=\"40%\" border=\"1\">";
           echo "<tr>";
           echo "<td bgcolor=\"#E2E2E2\">From: $country</td><tr><td bgcolor=\"#E2E2E2\"><img src='uploads/$photo' border='0' width=\"50\" height=\"50\"/><tr></td><td bgcolor=\"#E2E2E2\"><a href=\"profile.php?id=$id\">View Profile</a></td><tr><td bgcolor=\"#E2E2E2\">$headline</td>";
           echo "</tr>"; 
           echo "</div>";
           echo "</table><br /></fieldset><br />";
}

it uses 3 inputs to make a search it is far to basic lol, could either of your ways be incorporated into my current code?

cheers

Graham

Barand · April 20, 2007

Your age selects need different names

<form>
Search For Ages Between:
<select name="age1">
<option value="22">22</option>
<option value="23">23</option>
<option value="24">24</option>
</select>
And:
<select name="age2">
<option value="40">40</option>
<option value="41">41</option>
<option value="42">42</option>
</select>
<input type='submit' name='action' value='Search'>
</form>

Then

<?php
$age1 = $_GET['age1'];
$age1 = $_GET['age2'];
$sql = "SELECT * FROM mytable
        WHERE YEAR(dob) BETWEEN YEAR(CURDATE() - INTERVAL $age2 YEAR) AND YEAR(CURDATE() - INTERVAL $age1 YEAR)"
?>

graham23s · April 21, 2007

Hi Baryy,

here is my complete code:

   // The search code.../////////////////////////////////////////////////////////////// /
    
   // Begin gender search.../////////////////////////////////////////////////////////////    
   if(!isset($_GET['gender']) || ($_GET['gender'] == "Prefer Not To Say")) { 
   
      echo "<br /><p>Sorry, You Never Selected A Gender</p>"; 
      exit;
      
   } elseif (($_GET['gender'] != "Male") && ($_GET['gender'] != "Female")) { 
   
   die("Error about not using 'M' || 'F' goes here"); 
   
   } 

   $sort = ""; 

   if($_GET['Sort'] == "Ascending") { 
      $sort = "ASC"; 
   } else { 
      $sort = "DESC"; 
   } 

   $gender = $_GET['gender']; 
   $age1 = $_GET['min_age'];
   $age1 = $_GET['max_age'];
      
   // Begin country search...////////////////////////////////////////////////////////////
   if(!isset($_GET['country']) || ($_GET['country'] == "Prefer Not To Say")) { 
      echo "<br /><p>Sorry, You Never Selected A Country</p>"; 
      exit; 
   } 

   $country = $_GET['country']; 
   // Begin country search...////////////////////////////////////////////////////////////
   
   /* Compile the SQL query */    
   $sql =    "SELECT * FROM `membership` "; 
   $sql .=   "WHERE `gender`='" . $gender . "' "; 
   $sql .=   "AND birthyear(dob) BETWEEN birthyear(CURDATE() - INTERVAL $max_age YEAR) AND birthyear(CURDATE() - INTERVAL $min_age YEAR)";
   $sql .=   "AND `country`='" . $country . "' "; 
   $sql .=   "ORDER BY `id` " . $sort; 

   /* Query */ 
   $result = mysql_query($sql) or die(mysql_error()); 

   /* Display query results: */ 
   while ($row = mysql_fetch_array($result)){ 
            $id= $row['id']; 
            $username = $row['username']; 
            $gender = $row['gender']; 
            $headline = $row['headline'];
            $photo = $row['photo'];
            $country = $row['country'];
                
           // Display the users details in a box...
           echo "<br /><div id=\"search_profile\">";
           echo "<fieldset><legend>$username</legend>";
           echo "<table width=\"40%\" border=\"1\">";
           echo "<tr>";
           echo "<td bgcolor=\"#E2E2E2\">From: $country</td><tr><td bgcolor=\"#E2E2E2\"><img src='uploads/$photo' border='0' width=\"50\" height=\"50\"/><tr></td><td bgcolor=\"#E2E2E2\"><a href=\"profile.php?id=$id\">View Profile</a></td><tr><td bgcolor=\"#E2E2E2\">$headline</td>";
           echo "</tr>"; 
           echo "</div>";
           echo "</table><br /></fieldset><br />";
}

i changes age1 and 2 to min_age and max_age but i get a mysql error on the age search any ideas on how to remedy this?

cheers mate

Graham

Barand · April 21, 2007

What is your column name for the column holding their date of birth (I used "dob" in my example but you need to substitute yours) and is it a DATE type field?

Unless you defined a custom MYSQL function "birthyear()" use YEAR()

graham23s · April 21, 2007

Hi Barry,

This is the dump for my table:

CREATE TABLE `membership` (
  `id` int(15) NOT NULL auto_increment,
  `username` varchar(30) NOT NULL,
  `password` varchar(32) NOT NULL,
  `email` varchar(30) NOT NULL,
  `marstatus` varchar(20) NOT NULL,
  `children` varchar(20) NOT NULL,
  `height` varchar(20) NOT NULL,
  `weight` varchar(20) NOT NULL,
  `hair` varchar(20) NOT NULL,
  `eye` varchar(20) NOT NULL,
  `birthmonth` varchar(10) NOT NULL,
  `birthday` varchar(15) NOT NULL,
  `birthyear` varchar(20) NOT NULL,
  `gender` varchar(20) NOT NULL,
  `ethnicity` varchar(20) NOT NULL,
  `country` varchar(50) NOT NULL,
  `iam1` varchar(10) NOT NULL,
  `iam2` varchar(10) NOT NULL,
  `occupation` varchar(50) NOT NULL,
  `headline` varchar(50) NOT NULL,
  `description` varchar(255) NOT NULL,
  `lookingfor` varchar(255) NOT NULL,
  `photo` varchar(100) NOT NULL,
  `imgfile_url` varchar(200) NOT NULL,
  `agreed_terms` varchar(10) NOT NULL,
  `join_date` date NOT NULL default '0000-00-00',
  `ip` varchar(20) NOT NULL,
  `login` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=85 ;

-- 
-- Dumping data for table `membership`

cheers

Graham

Barand · April 21, 2007

try

SELECT * FROM mytable
WHERE birthyear BETWEEN YEAR(CURDATE() - INTERVAL $max_age YEAR) AND YEAR(CURDATE() - INTERVAL $min_age YEAR)

graham23s · April 21, 2007

Hi Mate,

This is my overall query:

   /* Compile the SQL query */    
   $sql =    "SELECT * FROM `membership` "; 
   $sql .=   "WHERE `gender`='" . $gender . "' "; 
   $sql .=   "AND birthyear BETWEEN YEAR(CURDATE() - INTERVAL $max_age YEAR) AND YEAR(CURDATE() - INTERVAL $min_age YEAR))";
   $sql .=   "AND `country`='" . $country . "' "; 
   $sql .=   "ORDER BY `id` " . $sort;

but i'm getting a syntax error when it runs, heres the full search code:

   // Begin gender search.../////////////////////////////////////////////////////////////    
   if(!isset($_GET['gender']) || ($_GET['gender'] == "Prefer Not To Say")) { 
   
      echo "<br /><p>Sorry, You Never Selected A Gender</p>"; 
      exit;
      
   } elseif (($_GET['gender'] != "Male") && ($_GET['gender'] != "Female")) { 
   
   die("Error about not using 'M' || 'F' goes here"); 
   
   } 

   $sort = ""; 

   if($_GET['Sort'] == "Ascending") { 
      $sort = "ASC"; 
   } else { 
      $sort = "DESC"; 
   } 

   $gender = $_GET['gender']; 
   $min_age = $_GET['min_age'];
   $max_age = $_GET['max_age'];
      
   // Begin country search...////////////////////////////////////////////////////////////
   if(!isset($_GET['country']) || ($_GET['country'] == "Prefer Not To Say")) { 
      echo "<br /><p>Sorry, You Never Selected A Country</p>"; 
      exit; 
   } 

   $country = $_GET['country']; 
   // Begin country search...////////////////////////////////////////////////////////////
   
   /* Compile the SQL query */    
   $sql =    "SELECT * FROM `membership` "; 
   $sql .=   "WHERE `gender`='" . $gender . "' "; 
   $sql .=   "AND birthyear BETWEEN YEAR(CURDATE() - INTERVAL $max_age YEAR) AND YEAR(CURDATE() - INTERVAL $min_age YEAR))";
   $sql .=   "AND `country`='" . $country . "' "; 
   $sql .=   "ORDER BY `id` " . $sort;

if that helps any

cheers

Graham

Barand · April 21, 2007

What does

echo $sql;

give?

graham23s · April 21, 2007

Hi Barry,

this is the echo:

SELECT * FROM `membership` WHERE `gender`='Male' AND birthyear BETWEEN YEAR(CURDATE() - INTERVAL 22 YEAR) AND YEAR(CURDATE() - INTERVAL 18 YEAR))AND `country`='Denmark' ORDER BY `id` DESC

this is the error underneath it:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')AND `country`='Denmark' ORDER BY `id` DESC' at line 1

thanks mate

Graham

Barand · April 21, 2007

Remove the extra ) from here

YEAR))AND

graham23s · April 21, 2007

Thanks Tons barry that has worked perfectly:)

cheers

Graham

Barand · April 21, 2007

It's crude as it doesn't take into account the month and day and whether their birthday has passed in the current year. But you have a real problem selecting date ranges when it is split over year, month and day fields rather than a single YYYY-MM-DD date field.

graham23s · April 21, 2007

So i could have really made 1 date field in mysql instead of these 3:

  `birthmonth` varchar(10) NOT NULL,
  `birthday` varchar(15) NOT NULL,
  `birthyear` varchar(20) NOT NULL,

That sounds a lot better i wasn't 100% sure when coding it, it was the right way to go lol

cheers

Graham

Barand · April 21, 2007

Yes

birthdate DATE NOT NULL

then insert something like

INSERT INTO user (name, birthdate) VALUES ('Barand', '1949-01-22')

Use date() function when outputting to format it any way you like

echo (date('jS F, Y', strtotime($row['birthdate']));  // ==> 22nd January, 1949

If you need just the year or the day, for instance, you can

SELECT MONTH(birthdate) as month, DAY(birthdate) as day ....

graham23s · April 21, 2007

damn that looks a lot better and more what i was actually after in the first place, i think i will re-write the registration section and modify the mysql database.

thanks again for the help mate

Graham

Sign In

Search users by age

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information