Jump to content

help with db search criteria


lovephp
Go to solution Solved by requinix,

Recommended Posts

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

Link to comment
Share on other sites

  • Solution

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 by requinix
  • Like 1
Link to comment
Share on other sites

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 by requinix
v3
Link to comment
Share on other sites

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 :-)

Link to comment
Share on other sites

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"]);
                    }

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

  • Like 2
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

  • Like 1
Link to comment
Share on other sites

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'
Link to comment
Share on other sites

 

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.

  • Like 1
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 by lovephp
Link to comment
Share on other sites

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();
        }
Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.