Search users by age

[graham23s]

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]

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]

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]

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]

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]

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]

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]

try

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

[graham23s]

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]

What does

 

echo $sql;

 

give?

[graham23s]

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]

Remove the extra ) from here

 

YEAR))AND

[graham23s]

Thanks Tons barry that has worked perfectly:)

 

cheers

 

Graham

[Barand]

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]

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]

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]

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