Distinct query isn't giving any results with AND operator

Question

Here is the query that i am trying to execute, but i get no results, if you need more information to help regarding this kindly ask in the comments below i would appreciate any assistance Thanks.

SELECT DISTINCT * 
FROM `users`  
WHERE ((`height` BETWEEN "139" AND "170" 
        AND `language` = "english")  
       AND (`active` = "1" 
            AND `gender` IN (4525, 4526) 
            AND DATEDIFF(CURDATE(), `birthday`) / 365 >= "22" 
            AND DATEDIFF(CURDATE(), `birthday`) / 365 <= "55" 
            AND `country` = "IT") 
       AND `id` NOT IN (SELECT `block_userid` 
                        FROM `blocks` 
                        WHERE `user_id` = 1)  
       AND `id` NOT IN (SELECT `like_userid` 
                        FROM `likes` 
                        WHERE `user_id` = 1)  
       AND `id` NOT IN (SELECT `user_id` 
                        FROM `likes` 
                        WHERE `like_userid` = 1)  
       AND `id` <> "1" )  
ORDER BY 
    `xlikes_created_at` DESC, `xvisits_created_at` DESC, 
    `xmatches_created_at` DESC, `is_pro` DESC 
LIMIT 12 OFFSET 0;

I tried OR operator but certainly i am not looking for OR i want all conditions to be true then want the results.

function GetSearchResults($user_id, $limit, $offset, $country = true) {
    $where_or = array();
    $where_and = array();
    $u = auth();

    // main query
    $query = 'SELECT DISTINCT * FROM `users`';

    // Filters
    $where = ' WHERE ( ';
    // must be verified
    $where_and[] = '`active` = "1"';
    //$where_and[] = '`privacy_show_profile_match_profiles` = "1"';

    //********** public search params *****************//
    // check gender from post or from session
    $genders = null;
    if( isset($_SESSION['_gender']) && $_SESSION['_gender'] !== '') {
        $genders = Secure( $_SESSION['_gender'] );
    }

    if( isset($_POST['_gender']) && $_POST['_gender'] !== '') {
        $_SESSION[ '_gender' ] = $_POST['_gender'];
        $genders = Secure( $_POST['_gender'] );
    }

    if( $genders == null || $genders == 'male' || $genders == 'female' ) {
        $genders = '4525, 4526';
    }

    if( is_array($genders)) {
        $genders = @implode( ',' , $genders );
    }

    if( strpos( $genders, ',' ) === false ) { 
        $where_and[] = '`gender` = "'. $genders .'"';
    } else {
        $where_and[] = '`gender` IN ('. $genders .')';
    }

    // check age from post or from session
    if(isset($_POST['_age_from']) && !empty($_POST['_age_from']) && isset($_POST['_age_to']) && !empty($_POST['_age_to']) ) {
        $where_and[] = 'DATEDIFF(CURDATE(), `birthday`)/365 >= "'. Secure($_POST['_age_from']) .'" AND DATEDIFF(CURDATE(), `birthday`)/365 <= "'. Secure($_POST['_age_to']) . '"';
    } else {
        if(isset( $_SESSION['_age_from'] ) && isset( $_SESSION['_age_to'] )) {
            $where_and[] = 'DATEDIFF(CURDATE(), `birthday`)/365 >= "'. Secure($_SESSION['_age_from']) .'" AND DATEDIFF(CURDATE(), `birthday`)/365 <= "'. Secure($_SESSION['_age_to']) . '"';
        } else {
            $where_and[] = 'DATEDIFF(CURDATE(), `birthday`)/365 >= "20" AND DATEDIFF(CURDATE(), `birthday`)/365 <= "55"';
        }
    }

    $query_country = '';
    if( $u->show_me_to == '' ) {
        if ((isset($_POST['_lat']) && !empty($_POST['_lat']) && isset($_POST['_lng']) && !empty($_POST['_lng']))
            ||
            (isset($_SESSION['_lat']) && !empty($_SESSION['_lat']) && isset($_SESSION['_lng']) && !empty($_SESSION['_lng']))
         ) {
             $lat = 0;
             $lng = 0;
             $located = 7;

             if(isset( $_SESSION['_lat'] ) ) $lat = Secure($_SESSION['_lat']);
             if(isset( $_POST['_lat'] ) ) $lat = Secure($_POST['_lat']);

             if( isset( $_SESSION['_lng'] ) ) $lng = Secure($_SESSION['_lng']);
             if( isset( $_POST['_lng'] ) ) $lng = Secure($_POST['_lng']);

             if( isset( $_SESSION['_located'] ) ) $located = Secure($_SESSION['_located']);
             if( isset( $_POST['_located'] ) ) $located = Secure($_POST['_located']);

             $distance = 'ROUND( ( 6371 * acos(cos(radians(' . $lat . ')) * cos(radians(`lat`)) * cos(radians(`lng`) - radians(' . $lng . ')) + sin(radians(' . $lat . ')) * sin(radians(`lat`)))) ,1) ';
             $where_and[] = $distance . ' <= ' . $located;
         }
    } else {
        if($country == true) {
            $query_country = ' OR `country` = "' . $u->show_me_to . '"';
            $where_and[] = '`country` = "' . $u->show_me_to . '"';
        }
    }

    //******************* Looks Filters ************************//
    if( isset($_POST['_height_from']) && !empty($_POST['_height_from']) && isset($_POST['_height_to']) && !empty($_POST['_height_to']) ) {
        $where_or[] = '`height` BETWEEN "'. Secure($_POST['_height_from']) .'" AND "'. Secure($_POST['_height_to']) .'"';
    }

    if( isset($_POST['_body']) && !empty($_POST['_body']) ) {
        if( strpos( Secure( $_POST['_body'] ), ',' ) === false ) {
            $where_or[] = '`body` = "'. Secure($_POST['_body']) . '"';
        } else {
            $where_or[] = '`body` IN ('. Secure($_POST['_body']) .')';
        }
    }

    //******************* Background Filter ********************//
    if( isset($_POST['_language']) && !empty($_POST['_language']) ) {
        $where_or[] = '`language` = "'. Secure($_POST['_language']) .'"';
    }

    if( isset($_POST['_ethnicity']) && !empty($_POST['_ethnicity']) ) {
        if( strpos( Secure( $_POST['_ethnicity'] ), ',' ) === false ) {
            $where_or[] = '`ethnicity` = "'. Secure($_POST['_ethnicity']) . '"';
        } else {
            $where_or[] = '`ethnicity` IN ('. Secure($_POST['_ethnicity']) .')';
        }
    }

    if( isset($_POST['_religion']) && !empty($_POST['_religion']) ) {
        if( strpos( Secure( $_POST['_religion'] ), ',' ) === false ) {
            $where_or[] = '`religion` = "'. Secure($_POST['_religion']) . '"';
        } else {
            $where_or[] = '`religion` IN ('. Secure($_POST['_religion']) .')';
        }
    }

    //******************* LifeStyle filter *********************//
    if( isset($_POST['_relationship']) && !empty($_POST['_relationship']) ) {
        if( strpos( Secure( $_POST['_relationship'] ), ',' ) === false ) {
            $where_or[] = '`relationship` = "'. Secure($_POST['_relationship']) .'"';
        } else {
            $where_or[] = '`relationship` IN ('. Secure($_POST['_relationship']) .')';
        }
    }

    if( isset($_POST['_smoke']) && !empty($_POST['_smoke']) ) {
        if( strpos( Secure( $_POST['_smoke'] ), ',' ) === false ) {
            $where_or[] = '`smoke` = "'. Secure($_POST['_smoke']) . '"';
        } else {  
            $where_or[] = '`smoke` IN ('. Secure($_POST['_smoke']) .')';
        }
    }

    if( isset($_POST['_drink']) && !empty($_POST['_drink']) ) {
        if( strpos( Secure( $_POST['_drink'] ), ',' ) === false ) {
            $where_or[] = '`drink` = "'. Secure($_POST['_drink']) . '"';
        } else {
            $where_or[] = '`drink` IN ('. Secure($_POST['_drink']) .')';
        }
    }

    //******************* More Filter **************************//
    if( isset($_POST['_interest']) && !empty($_POST['_interest']) ) {
        $where_or[] = '`interest` like "%'. Secure($_POST['_interest']) .'%"';
    }

    if( isset($_POST['_education']) && !empty($_POST['_education']) ) { 
        if( strpos( Secure( $_POST['_education'] ), ',' ) === false ) {
            $where_or[] = '`education` = "'. Secure($_POST['_education']) .   '"';
        } else {
            $where_or[] = '`education` IN ('. Secure($_POST['_education']) .')';
        }
    }

    if( isset($_POST['_pets']) && !empty($_POST['_pets']) ) {
        if( strpos( Secure( $_POST['_pets'] ), ',' ) === false ) {
            $where_or[] = '`pets` = "'. Secure($_POST['_pets']) .'"';
        } else {
            $where_or[] = '`pets` IN ('. Secure($_POST['_pets']) .')';
        }
    }
if( !empty($where_or) ){
    $where = $where . '('. implode($where_or, ' AND ') . ') ';
}
if( !empty($where_and) ){
    if( !empty($where_or) ) {
        $where = $where . ' AND (' . implode($where_and, ' AND ') . ')';
    }else{
        $where = $where . ' (' . implode($where_and, ' AND ') . ')';
    }
}

if( isset( $_REQUEST['access_token'] ) ) {
    $uid = GetUserFromSessionID(Secure($_REQUEST['access_token']));
    $u->id = $uid;
}

if( isset( $u->id ) ) {
    // to exclude blocked users
    $notin = ' OR `id` NOT IN (SELECT `block_userid` FROM `blocks` WHERE `user_id` = ' . $u->id . ') ';
    // to exclude liked and disliked users users
    $notin .= ' OR `id` NOT IN (SELECT `like_userid` FROM `likes` WHERE `user_id` = ' . $u->id . ') ';
    $notin .= ' OR `id` NOT IN (SELECT `user_id` FROM `likes` WHERE `like_userid` = ' . $u->id . ') ';
    $notin .= ' OR `id` <> "' . $u->id . '" ';
}

$custom_sql = [];
if(isset($_POST['custom_profile_data'])){
    $count = 100;
    for($i = 0 ; $i <= $count ; $i++ ){
        if(isset($_POST['fid_' . $i])){
            if(!empty($_POST['fid_' . $i])){
                $custom_sql[] = ' id IN (SELECT `user_id` FROM `userfields` WHERE `fid_' . $i .'` = "'.Secure($_POST['fid_' . $i]) . '") ';
            }
        }
    }
}

$custom_sql_text = '';
if(!empty($custom_sql)){
    $custom_sql_text .= ' AND ( ';
    $custom_sql_text .= implode(' OR ', $custom_sql);
    $custom_sql_text .= ' ) ';
}

if( $limit == 0 ) {
    $limit = 20;
}
$orderBy = ' ORDER BY ';
$orderBy .= '`xlikes_created_at` DESC';
$orderBy .= ',`xvisits_created_at` DESC';
$orderBy .= ',`xmatches_created_at` DESC';
$orderBy .= ',`is_pro` DESC';
$query = $query . ' ' . $where . $notin . ') ' . $custom_sql_text . $query_country . $orderBy . ' LIMIT '.$limit.' OFFSET '.$offset.';';
return $query;

}

Just a note about all your quoted numeric values: if `height` is a varchar/char/text/etc data type, "14000000" is between "139" and "170"... but that could give you more rows than you expect, not less. — Uueerdo, May 28 '19 at 16:16
MInd the SQL injections? As iam pretty sure the function `Secure()` is not that secure as it most likely is using `mysqli_real_escape_string()` without setting a charset first like the manual saids.. — Raymond Nijland, May 28 '19 at 16:37
@Uueerdo Thanks guys but it returns 0 rows http://prnt.sc/nuj8e9 even though i have varchar, please note that i have relevant data in the database that i am looking for using this query. — Junaid Raza, May 28 '19 at 17:25
This is where you should be looking [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php), forget to mention the link in mine other comment. — Raymond Nijland, May 28 '19 at 17:26
Change the first age check to `birthday <= CURDATE() - INTERVAL 24 YEAR`; etc. That gets rid of the leap year bug. — Rick James, May 29 '19 at 17:28

Juan Carlos Oropeza · Answer 1 · 2019-05-28T15:52:54.233

1

Why you need distinct? usually users are unique

SELECT DISTINCT * FROM `users`

Dont save numeric values as strings

`height` BETWEEN "139" AND "170"

You have the gender condition repeated: And I don't want to sound insensitive but those are a lot of genders.

`gender` IN (4525,4526,1275,1277,1278,1279,1280,1281,1282,1283)

You have 2 date diff, and second one doesn't have a comparator. Also Datediff return a numeric value not a string

DATEDIFF(CURDATE(), `birthday`)/365 >= "24"
DATEDIFF(CURDATE(), `birthday`)/365  "16"

Now if the missing operator on DATEDIFF doesn't solve the problem. Go direct to the DB remove all conditions and start adding those one by one until you found which one cause return 0 rows.

edited May 28 '19 at 15:52

answered May 28 '19 at 15:47

Juan Carlos Oropeza

47,252
12
78
118

Thanks for the reply, if execute the query one by one without AND operator it works fine, also where query contains more genders because in actual i have ability to add genders dynamically so i posted results of the query i have two genders in the code however that should not be the problem it should look for the available genders. also birthday is >= "24" and <= "55" 16 query is for excluding already liked users, Thanks again. – Junaid Raza May 28 '19 at 15:53
You said thanks because you solve the problem? I dont see <= "55" on the code. If you keep adding conditions one by one which one cause to get 0 rows? BTW Im just talking about the first query. I think the php code is irrelevant. You should test the query direct on DB and then update the php code when you have a working query – Juan Carlos Oropeza May 28 '19 at 15:55
Thanks for the swift response, sorry my bad i made mistake and wrote <= 16 however in actual its <= 55 while i was testing, however it didn't work when i execute queries one by one it works but the actual issue is when i execute with AND operator. – Junaid Raza May 28 '19 at 16:00
What you mean one by one? I mean add one run, add another run .... until you found the condition causing the error – Juan Carlos Oropeza May 28 '19 at 16:02
For example SELECT DISTINCT * FROM `users` WHERE (`height` BETWEEN "139" AND "170" AND `language` = "english"); – Junaid Raza May 28 '19 at 16:03
Again, add conditions until you found the error. And I cant do anything else for you unless you provide sample data current and expected result. – Juan Carlos Oropeza May 28 '19 at 16:06
Alright thanks but can you just confirm if i didn't make any mistake there please i mean in the code as it appears? – Junaid Raza May 28 '19 at 16:08
1

I wont check your code. You had to check your query first. Then you can fix your code. – Juan Carlos Oropeza May 28 '19 at 16:09
Do you have any row in your table where all these conditions will be true? it might be just there is actually no data matching your above conditions. – I kiet May 28 '19 at 16:19
Yes i have data that matches the query. – Junaid Raza May 28 '19 at 16:29
@Junaid data that matches **all** the conditions? Add sample data to the question, including rows you think should be included in the results. – Uueerdo May 28 '19 at 18:19
1

Put it this way, if `DISTINCT *'` has any impact on a result set then you have far bigger problems to contend with! – Strawberry May 28 '19 at 20:41

Distinct query isn't giving any results with AND operator

1 Answers1