How to use optional AND or skip AND condition in mySQL query when user enter submit null value for that condition..

[EHTISHAM]

How to use optional AND or skip AND condition in mySQL query when user enter submit null value for that condition..

 

SELECT * FROM tbluser WHERE gender='male' AND mother_tongue='Tamil' AND religion_id='3' AND caste_id='374' AND (age BETWEEN 50 AND 70);

 

the database use doesn't have any null value.. So if user doesn't enter any value in any field then the query results null....

 

I am using this query for searching bride grooms in my matrimonial site(php)..

 

If anyone know how to come out from this prob.. help me..

 

Thanks..

Edited September 27, 2015 by EHTISHAM

[Barand]

If the user doesn't specify a value then leave that condition out of the WHERE clause. So if no value is supplied for "mother_tongue" then the WHERE clause will be

WHERE gender='male'  AND religion_id='3' AND caste_id='374' AND (age BETWEEN 50 AND 70)

[EHTISHAM]

 

If the user doesn't specify a value then leave that condition out of the WHERE clause. So if no value is supplied for "mother_tongue" then the WHERE clause will be

WHERE gender='male'  AND religion_id='3' AND caste_id='374' AND (age BETWEEN 50 AND 70)

but how i can modify the query dinamically.. and skip that particular and condition.. i guessed that something like if else condition may be use..

[Barand]

I use something like this

$where = array();
$whereclause = '';

if (trim($gender) != '') {
    $where[] = sprintf ("(gender = '%s')", $mysqli->real_escape_string($gender));
}
if (trim($mother_tongue) != '') {
    $where[] = sprintf ("(mother_tongue = '%s')", $mysqli->real_escape_string($mother_tongue));
}

// etc

if (count($where) > 0) {
    $whereclause = 'WHERE ' . join(' AND ', $where);
}

$sql = "SELECT * FROM tbluser " . $whereclause;

[EHTISHAM]

 

I use something like this

$where = array();
$whereclause = '';

if (trim($gender) != '') {
    $where[] = sprintf ("(gender = '%s')", $mysqli->real_escape_string($gender));
}
if (trim($mother_tongue) != '') {
    $where[] = sprintf ("(mother_tongue = '%s')", $mysqli->real_escape_string($mother_tongue));
}

// etc

if (count($where) > 0) {
    $whereclause = 'WHERE ' . join(' AND ', $where);
}

$sql = "SELECT * FROM tbluser " . $whereclause;

Thanks.. This works..  but may be i have prob in datatype..

 

my code smthing like this...

 

if(isset($_POST['btnSearch']))

{

$gen = $_POST['radGen'];      ------- (varchar)

$ageFrom = $_POST['txtAgeFrom'];      -------(varchar)

$ageTo = $_POST['txtAgeTo'];     -------- (varchar)

$mt = $_POST['selMotherTongueQ'];  --------(varchar)

$relig = $_POST['selReligionQ']; ---------(int)

$cast = $_POST['selCasteQ'];  ---------(int)

 

$where = array();

$whereclause = '';

 

if (trim($ageFrom) != '') {

    $where[] = sprintf ("(age = '%s')", $mysqli->real_escape_string($ageFrom));

}

if (trim($ageTo) != '') {

    $where[] = sprintf ("(age = '%s')", $mysqli->real_escape_string($ageTo));

}

if (trim($mt) != '') {

    $where[] = sprintf ("(mother_tongue = '%s')", $mysqli->real_escape_string($mt));

}

if (trim($relig) != '') {

    $where[] = sprintf ("(religion_id = '%s')", $mysqli->real_escape_string($relig));

}

if (trim($cast) != '') {

    $where[] = sprintf ("(caste_id = '%s')", $mysqli->real_escape_string($cast));

}

// etc

 

if (count($where) > 0) {

    $whereclause = 'WHERE ' . join(' AND ', $where);

}

 

$q = "SELECT u.user_id,u.name,u.gender,u.age,u.religion_id,r.religion,u.caste_id,c.caste,u.gautra,u.mangalik,u.mother_tongue,u.country_id,cnt.country,u.state_id,st.state,u.city,ed.work_area,ed.annual_income,uad.pro_pic_path,uad.about_user_desc,dp.p_desc FROM tbluserbasic u left join tblreligion r on u.religion_id=r.religion_id left join tblcaste c on u.caste_id=c.caste_id left join tblusereducation ed on u.user_id=ed.user_id left join tblcountry cnt on u.country_id=cnt.country_id left join tblstate st on u.state_id=st.state_id left join tbluseradditional uad on u.user_id=uad.user_id left join tbldesiredpartner dp on u.user_id=dp.user_id " . $whereclause;

 

..............

.....

}

[EHTISHAM]

 

Thanks.. This works..  but may be i have prob in datatype..

 

my code smthing like this...

 

if(isset($_POST['btnSearch']))

{

$gen = $_POST['radGen'];      ------- (varchar)

$ageFrom = $_POST['txtAgeFrom'];      -------(varchar)

$ageTo = $_POST['txtAgeTo'];     -------- (varchar)

$mt = $_POST['selMotherTongueQ'];  --------(varchar)

$relig = $_POST['selReligionQ']; ---------(int)

$cast = $_POST['selCasteQ'];  ---------(int)

 

$where = array();

$whereclause = '';

 

if (trim($ageFrom) != '') {

    $where[] = sprintf ("(age = '%s')", $mysqli->real_escape_string($ageFrom));

}

if (trim($ageTo) != '') {

    $where[] = sprintf ("(age = '%s')", $mysqli->real_escape_string($ageTo));

}

if (trim($mt) != '') {

    $where[] = sprintf ("(mother_tongue = '%s')", $mysqli->real_escape_string($mt));

}

if (trim($relig) != '') {

    $where[] = sprintf ("(religion_id = '%s')", $mysqli->real_escape_string($relig));

}

if (trim($cast) != '') {

    $where[] = sprintf ("(caste_id = '%s')", $mysqli->real_escape_string($cast));

}

// etc

 

if (count($where) > 0) {

    $whereclause = 'WHERE ' . join(' AND ', $where);

}

 

$q = "SELECT u.user_id,u.name,u.gender,u.age,u.religion_id,r.religion,u.caste_id,c.caste,u.gautra,u.mangalik,u.mother_tongue,u.country_id,cnt.country,u.state_id,st.state,u.city,ed.work_area,ed.annual_income,uad.pro_pic_path,uad.about_user_desc,dp.p_desc FROM tbluserbasic u left join tblreligion r on u.religion_id=r.religion_id left join tblcaste c on u.caste_id=c.caste_id left join tblusereducation ed on u.user_id=ed.user_id left join tblcountry cnt on u.country_id=cnt.country_id left join tblstate st on u.state_id=st.state_id left join tbluseradditional uad on u.user_id=uad.user_id left join tbldesiredpartner dp on u.user_id=dp.user_id " . $whereclause;

 

..............

.....

}

 

and i have to make query having where condition like this..

 

WHERE (u.gender='$gen' AND (u.mother_tongue='$mt') AND u.religion_id='$relig' AND u.caste_id='$cast' AND u.age BETWEEN $ageFrom AND $ageTo)

[Barand]

you age conditions should be

 

if (trim($ageFrom) != '') {

    $where[] = sprintf ("(age >= '%s')", $mysqli->real_escape_string($ageFrom));

}

if (trim($ageTo) != '') {

    $where[] = sprintf ("(age <= '%s')", $mysqli->real_escape_string($ageTo));

}

[EHTISHAM]

 

you age conditions should be

 

if (trim($ageFrom) != '') {

    $where[] = sprintf ("(age >= '%s')", $mysqli->real_escape_string($ageFrom));

}

if (trim($ageTo) != '') {

    $where[] = sprintf ("(age <= '%s')", $mysqli->real_escape_string($ageTo));

}

 

thanks but still it shows error... 

 

Notice: Undefined variable: mysqli in C:\xampp\htdocs\xyz.com\classes\action.php on line 421

 

Fatal error: Call to a member function real_escape_string() on a non-object in C:\xampp\htdocs\xyz.com\classes\action.php on line 421

 

 

 

i am using PDO..

[Barand]

Then use your PDO object and PDO methods.

[EHTISHAM]

Then use your PDO object and PDO methods.

Thanks a lot for your co-operation.. Finally, i did it..