help with db search criteria

lovephp · May 20, 2015

Mates below is the $post details that i give users to search db

Array ( [gender] => female [religion] => Christian [language] => English [caste] => Catholic [state] => Florida [employment] => Employed [mstatus] => Never Married [agefrom] => 18 [ageto] => 25

the query i could do something like

select * from some_db WHERE gender LIKE %$query% OR religion LIKE %$query% etc etc

but when it comes to age if you can see [agefrom] => 18 [ageto] => 25 my issue is in dob field i store date of birth like

12-12-1980 so how on earth i make it happen to calculate age range between 18-25? how do i even convert the date of birth to age and then do the search query?

Really appreciate your valuable time and help in advance,

Cheers

requinix · May 20, 2015

Best way is to build a list of all conditions to apply.

conditions = array()

if gender is provided {
	conditions[] = gender is <value>
}
if religion is provided {
	conditions[] = religion is <value>
}
if language is provided {
	conditions[] = language is <value>
}
if caste is provided {
	// probably should be combined with the religion one instead
	conditions[] = caste is <value>
}
if state is provided {
	conditions[] = state is <value>
}
if employment is provided {
	conditions[] = employment is <value>
}
if mstatus is provided {
	conditions[] = mstatus is <value>
}
if agefrom and ageto are provided {
	conditions[] = age between agefrom and ageto
}

query = SELECT ...
if conditions {
	query .= WHERE implode(AND, conditions)
}

If the age thing can be open-ended then it's a little more complicated:

if agefrom or ageto are provided {
	if agefrom and ageto are provided {
		conditions[] = age between agefrom and ageto
	} else if agefrom is provided {
		conditions[] = age greater than or equal to agefrom
	} else { // ageto is provided
		conditions[] = age less than or equal to ageto
	}
}

Also works fairly well if you need to conditionally join in other tables.

For age specifically, figure out what the dates are rather than figuring out a person's age. With SQL it's as simple as

birthdate BETWEEN CURDATE() - INTERVAL agefrom YEAR AND CURDATE() - INTERVAL ageto YEAR

or with PHP

$date = date("Y-m-d", strtotime("-{$n} years"));

Edited May 20, 2015 by requinix

lovephp · May 20, 2015

appreciate it mate. but its kinda tough for me doing it like you explained i would not be able to. could you help me out complete the code? would really be thanklful :-)

requinix · May 20, 2015

What do you have so far?

Also,

birthdate BETWEEN CURDATE() - INTERVAL agefrom YEAR AND CURDATE() - INTERVAL ageto YEAR

is incorrect.

1. The dates are in the wrong order: since agefrom and ", which won't work.

2. Off by one error. Consider if agefrom=ageto: the two dates will always be the same and the query would only return people born on exactly that date. There needs to be a gap of a year, and that gap goes on the older side.

Together,

$agetogap = $ageto + 1;

"birthdate BETWEEN CURDATE() - INTERVAL {$agetogap} YEAR AND CURDATE() - INTERVAL {$agefrom} YEAR"

Edited May 20, 2015 by requinix
v3

lovephp · May 21, 2015

ok here is my db structure

--
-- Table structure for table `profiles`
--

CREATE TABLE IF NOT EXISTS `profiles` (
`UserID` int(11) NOT NULL AUTO_INCREMENT,
`ProfileBY` varchar(100) NOT NULL,
`Fname` varchar(100) NOT NULL,
`Lname` varchar(100) NOT NULL,
`Gender` varchar(6) NOT NULL,
`Religion` varchar(100) NOT NULL,
`Language` varchar(100) NOT NULL,
`Caste` varchar(100) NOT NULL,
`State` varchar(100) NOT NULL,
`Employment` varchar(100) NOT NULL,
`mstatus` varchar(100) NOT NULL,
`dob` varchar(12) NOT NULL,
PRIMARY KEY (`UserID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `profiles`
--

[code]

here is how am trying to get all age from database

[php]

$result = mysql_query("SELECT * FROM profiles");
                           while ($row = mysql_fetch_array($result)){
                       $dob= $row["dob"];
                       }

                       $birthDate = "".$dob."";
                       $birthDate = explode("-", $birthDate);
                       $age = (date("md", date("U", mktime(0, 0, 0, $birthDate[0], $birthDate[1], $birthDate[2]))) > date("md") ? ((date("Y")- $birthDate[2])-1):(date("Y")-$birthDate[2]));

[/php]

then here are the complete coding i tried to implant your way to do the search but i don't know what to do next. with the contions and values for query your way.

[php]

    <?php
                       include("manage/connect.php");
                   if(isset($_POST['submit'])){

                       $gender = $_POST['gender'];
                       $religion = $_POST['religion'];
                       $caste = $_POST['caste'];
                       $state = $_POST['state'];
                       $employment = $_POST['employment'];
                       $mstatus = $_POST['mstatus'];
                       $agefrom = $_POST['agefrom'];
                       $ageto = $_POST['ageto'];


                       $result = mysql_query("SELECT * FROM profiles");
                           while ($row = mysql_fetch_array($result)){
                       $dob= $row["dob"];
                       }

                       $birthDate = "".$dob."";
                       $birthDate = explode("-", $birthDate);
                       $age = (date("md", date("U", mktime(0, 0, 0, $birthDate[0], $birthDate[1], $birthDate[2]))) > date("md") ? ((date("Y")- $birthDate[2])-1):(date("Y")-$birthDate[2]));


$conditions = array()
           if ($gender !='') {
               $conditions[] = gender is <value>
           }
           if ($religion !='') {
               $conditions[] = religion is <value>
           }
           if ($language !='') {
               $conditions[] = language is <value>
           }
           if ($caste !='') {
               $conditions[] = caste is <value>
           }
           if ($state !='') {
               $conditions[] = state is <value>
           }
           if ($employment !='') {
               $conditions[] = employment is <value>
           }
           if ($mstatus !='') {
               $conditions[] = mstatus is <value>
           }
           if ($agefrom !='' && $ageto !='') {
               $conditions[] = age between agefrom and ageto
           }
query = SELECT ...
if $conditions {
   query .= WHERE implode(AND, $conditions)
}

                       }
                       ?>

[/php]

how do i do the rest? thanks :-)

lovephp · May 21, 2015

i have changed the dob format to 1980-09-16

the following code calculates and outpot age of all users

function userAge($userAge){
                            $age = strtotime($userAge);
                                if($age === false){
                                  return false;
                                     }     
                        list($y1,$m1,$d1) = explode("-",date("Y-m-d",$age));     
                        $now = strtotime("now");     
                        list($y2,$m2,$d2) = explode("-",date("Y-m-d",$now));     
                        $age = $y2 - $y1;     
                        if((int)($m2.$d2) < (int)($m1.$d1))
                        $age -= 1;         
                        return $age;
                    }
                        
                        
                        $result = mysql_query("SELECT * FROM profiles");
                        while ($row = mysql_fetch_array($result)){
                        echo $age = userAge($dob= $row["dob"]);
                    }

Barand · May 21, 2015

Age calculating is much simpler with DateTime class.

function age($dob) 
{
    $d1 = new DateTime($dob);
    return $d1->diff(new DateTime())->y;
}
echo age('1980-09-16') ;   // 34

lovephp · May 21, 2015

ok i need not convert age of users the following query does the work

SELECT * FROM profiles WHERE YEAR(FROM_DAYS(DATEDIFF(CURDATE(), dob))) BETWEEN $agefrom AND $ageto

need help with the search criteria

$conditions = array()
            if ($gender !='') {
                $conditions[] = gender is <value>
            }
            if ($religion !='') {
                $conditions[] = religion is <value>
            }
            if ($language !='') {
                $conditions[] = language is <value>
            }
            if ($caste !='') {
                $conditions[] = caste is <value>
            }
            if ($state !='') {
                $conditions[] = state is <value>
            }
            if ($employment !='') {
                $conditions[] = employment is <value>
            }
            if ($mstatus !='') {
                $conditions[] = mstatus is <value>
            }
            if ($agefrom  !='' && $ageto !='') {
                $conditions[] = age between agefrom and ageto
            }
query = SELECT ...
if $conditions {
    query .= WHERE implode(AND, $conditions)
}

how do i acheive to use the criteria with the above code?

thanks

Barand · May 21, 2015

You need to convert the "gender is <value>" pseudocode statements to PHP/SQL condition statements EG

gender = '$gender'

And same with the join() statement at the end

... WHERE implode(' AND ', $conditions)

Don't forget to sanitize all your POST variable with real_escape_string() before using them in the SQL statement.

lovephp · May 21, 2015

still not getting it mate. this is kind of new to me. could you show me example please?

also this following query display all records of whichever gender selected

SELECT * FROM profiles WHERE Gender LIKE '%".$gender."%' OR Religion LIKE '%".$religion."%' AND YEAR(FROM_DAYS(DATEDIFF(CURDATE(), dob))) BETWEEN $agefrom AND $ageto

what is wrong im doing?

Barand · May 21, 2015

In your query, if religion if left empty then you search for ... OR religion LIKE '%%' which will select all records.

Only put conditions for which there are values in the query.

If you mixing OR and AND conditions, use (...) to specify the logic requires

EG

A OR B AND C

do you want

A OR (B AND C)

which it will use with no parenthesis, or do you want

(A OR B) AND C

Edited May 21, 2015 by Barand

lovephp · May 21, 2015

cool thanks, so now my code looks like this, please take a look and tell me if its ok or not?

<?php
include("manage/connect.php");
    if(isset($_POST['submit'])){                                                
            $gender = mysql_real_escape_string($_POST['gender']);
            $religion = mysql_real_escape_string($_POST['religion']);
            $caste = mysql_real_escape_string($_POST['caste']);
            $state = mysql_real_escape_string($_POST['state']);
            $employment = mysql_real_escape_string($_POST['employment']);
            $mstatus = mysql_real_escape_string($_POST['mstatus']);
            $agefrom = mysql_real_escape_string($_POST['agefrom']);
            $ageto = mysql_real_escape_string($_POST['ageto']);                    
            
            $criteria = array();
            if($gender !='')
            {
                $criteria[] = "Gender LIKE = '%".$gender."%'";
            }
            if($religion !='')
            {
                $criteria[] = "Religion LIKE = '%".$religion."%'";
            }
            if($caste !='')
            {
                $criteria[] = "Caste LIKE = '%".$caste."%'";
            }
            if($state !='')
            {
                $criteria[] = "State LIKE = '%".$state."%'";
            }
            if($employment !='')
            {
                $criteria[] = "Employment LIKE = '%".$employment."%'";
            }
            if($mstatus !='')
            {
                $criteria[] = "Maritalstatus LIKE = '%".$mstatus."%'";
            }
            if($agefrom && $ageto !='')
            {
                $criteria[] = "YEAR(FROM_DAYS(DATEDIFF(CURDATE(), dob))) BETWEEN '".$agefrom."' AND '".$ageto."'";
            }
    
        echo $query = "SELECT * FROM profiles WHERE " . implode(' AND ', $criteria);    
        while ($row = mysql_fetch_array($query)){
                echo $age = userAge($dob= $row["dob"]).'';
        }
}
?>

everything seems ok but no results from database displays. what would be the issue?

Barand · May 21, 2015

if($agefrom && $ageto !='')

should be

if($agefrom !='' && $ageto !='')

You should check that there are search conditions before adding the "WHERE ". join()" bit otherwise you end up with an invalid syntax of

SELECT * FROM profiles WHERE

(See requinix's example in reply #2 ^ )

Are you sure there are records that meet all the entered criteria?

lovephp · May 21, 2015

oh yes i corrected the

$agefrom !='' && $ageto !=''

and yes there are data in table which i tried to search but nothing displays even tho i echoed i only get to the the echoed query as

SELECT * FROM profiles WHERE Gender LIKE = '%female%' AND YEAR(FROM_DAYS(DATEDIFF(CURDATE(), dob))) BETWEEN '18' AND '30'

Barand · May 21, 2015

LIKE =

No!. One or the other, preferably "=".

Use LIKE only when the user enters a partial value and preferably only use like with a wild card at the end. If you use '%...%' then SQL can not use indexes and will scan the whole table slowing down your query.

lovephp · May 21, 2015

oh got it. now i removed all LIKE =

if($gender !='')
            {
                $criteria[] = "Gender = '$gender'";
            }
            if($religion !='')
            {
                $criteria[] = "Religion '$religion'";
            }
            if($caste !='')
            {
                $criteria[] = "Caste '$caste'";
            }
            if($state !='')
            {
                $criteria[] = "State '$state'";
            }
            if($employment !='')
            {
                $criteria[] = "Employment '$employment'";
            }
            if($mstatus !='')
            {
                $criteria[] = "Maritalstatus '$mstatus'";
            }

still no output all i get is

SELECT * FROM profiles WHERE Gender = 'female' AND YEAR(FROM_DAYS(DATEDIFF(CURDATE(), dob))) BETWEEN '18' AND '25'

what am i doing wrong? or is it because no mysql_query is used so not getting any results?

Barand · May 21, 2015

You still need the "="

$criteria[] = "Religion '$religion'";

Try outputting the contents from mysql_error() after calling mysql_query()

lovephp · May 21, 2015

did this

$query = "SELECT * FROM profiles WHERE " . implode(' AND ', $criteria);
echo 'Error: '.mysql_error();

nothing shows still not even an error

also added '='

if($gender !='')
            {
                $criteria[] = "Gender = '".$gender."'";
            }
            if($religion !='')
            {
                $criteria[] = "Religion = '".$religion."'";
            }
            if($caste !='')
            {
                $criteria[] = "Caste = '".$caste."'";
            }
            if($state !='')
            {
                $criteria[] = "State = '".$state."'";
            }
            if($employment !='')
            {
                $criteria[] = "Employment = '".$employment."'";
            }
            if($mstatus !='')
            {
                $criteria[] = "Maritalstatus = '".$mstatus."'";
            }

Edited May 21, 2015 by lovephp

Barand · May 21, 2015

I suggest you look up mysql_error in the php manual and see how it really should be used.

lovephp · May 21, 2015

I suggest you look up mysql_error in the php manual and see how it really should be used.

sorry i did this way, have edited the post

$query = "SELECT * FROM profiles WHERE " . implode(' AND ', $criteria);
        $result = mysql_query($query);
        if(!$result){
        echo 'failed: '.mysql_error();
        }

lovephp · May 21, 2015

wow finally fixed it

$query = "SELECT * FROM profiles WHERE " . implode(' AND ', $criteria);
        $result = mysql_query($query);
        while ($row = mysql_fetch_array($result)){
                echo $age = userAge($dob= $row["dob"]).'';
        }

now it shows results,

thanks you all soo much, you all taught me quite alot of new things, really appreciate it

cheers

Sign In

help with db search criteria

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

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