Jump to content

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


EHTISHAM

Recommended Posts

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

 

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..

Link to comment
Share on other sites

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

 

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

 

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)

Link to comment
Share on other sites

 

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..

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.