CODEWITHSUNDEEP
phphtmlmysql
user2510479
user2510479

Reputation: 1540

selecting age group using dob during search

I am trying to implement a search where the user can search for candidates above or below 18 years.

The html for the age component in the search:

  Age<select id ='eighteen' name="eighteen" size="1" >
  <option value="Below Eighteen">Below Eighteen</option>
  <option value="Above Eighteen">Above Eighteen</option>
  <option value="Any">Any</option>
   </select>

The php code:

$age = trim($_POST['eighteen']);    

$query2 = mysql_query("SELECT * FROM p_candidate WHERE YEAR(CURDATE())-YEAR(`age`) >= 18");
$query = mysql_query("SELECT * FROM p_candidate WHERE YEAR(CURDATE())-YEAR(`age`) < 18");
if ($age =="Below Eighteen") {$dob = $query;}
if ($age == "Above Eighteen") {$dob = $query2;}



$Odata = mysql_query("SELECT * FROM p_candidate WHERE(`gender` LIKE '%".$Gender."%') AND (`age` LIKE '%".$dob."%')") or die(mysql_error());

When I run this code without filling the gender field in the search form, I do not get any results. How can I get this to work? Which part do I need to modify?

My db is in mysql and the DOB of candidates are stored in a column called age

Upvotes: 1

Views: 1004

Answers (3)

user2510479
user2510479

Reputation: 1540

Here is what I finally used - it works now

if ($age == "Above Eighteen") {
$Odata = mysql_query("SELECT * FROM p_candidate WHERE(`gender` LIKE '%".$Gender."%') AND     (YEAR(CURDATE())-YEAR(`age`) >= 18)") or die(mysql_error());}

if ($age == "Below Eighteen") {
$Odata2 = mysql_query("SELECT * FROM p_candidate WHERE(`gender` LIKE '%".$Gender."%') AND     (YEAR(CURDATE())-YEAR(`age`) < 18)") or die(mysql_error());}

Upvotes: 0

GuiGreg
GuiGreg

Reputation: 341

Force the a default value for your select so the field will be always filled with something. Like this: 

 Age<select id ='eighteen' name="eighteen" size="1" >
  <option value="Below Eighteen">Below Eighteen</option>
  <option value="Above Eighteen">Above Eighteen</option>
  <option value="Any" selected>Any</option>
  </select>

Upvotes: 1

Ed Gibbs
Ed Gibbs

Reputation: 26343

To see if they're at least 18 years old, you can subtract 18 years from the current date and compare their birth date (which appears to be the age column) to that.

18 or older:

SELECT * FROM p_candidate WHERE age <= CURDATE() - INTERVAL 18 YEAR

Under 18:

SELECT * FROM p_candidate WHERE age > CURDATE() - INTERVAL 18 YEAR

I'd also avoid using LIKE when you don't have to. If you're checking for gender F or for gender M then just say WHERE gender = 'F' or WHERE gender = 'M'. You gain nothing by over-using LIKE, and you risk slowing down otherwise optimizable queries.

Upvotes: 1

Related Questions