CODEWITHSUNDEEP
phpmysql
112233
112233

Reputation: 2466

mysql Query's IN statement goes unpredictable

I store availability for all registered user by three columns in a comma separated strings like below:

user 1:
teaching: english

morning = '';
afternoon = 'Thrs,Fri,Sat,Sun'; 
evening = 'Thrs,Fri,Sat,Sun';

User 2
teaching: english

morning = 'Sat';
afternoon = 'Sat,Sun,Fri';  
evening = 'Sat,Sun,Fri';

Say I search for tutor who're available afternoon and evening Saturdays. I thought query would return both of them as both user's afternoon and evening columns has 'Sat'. But it returns empty array.

This is my browser interpreted query. I ran this in phpmyadmin but doesn't return any result.

SELECT * , (3956 * 2 * ASIN(SQRT( POWER(SIN(('2.993518' - lat) * pi()/180 / 2), 2) +COS('2.993518' * pi()/180) * COS(lat * pi()/180) * POWER(SIN(('101.7874058' - lon) * pi()/180 / 2), 2) ))) as distance from posts WHERE posts.subname LIKE '%english%' AND posts.pricing <= '115' AND (posts.afternoon IN ('Sat') OR posts.evening IN ('Sat')) GROUP BY posts.UUID having distance <= '40' order by distance

The same way here I'm trying to seek users available on afternoon and evening 'Thursdays'. I suppose it should return user1 because only user1 one available on Thursdays but it doesn't return any result:

SELECT * , (3956 * 2 * ASIN(SQRT( POWER(SIN(('2.993518' - lat) * pi()/180 / 2), 2) +COS('2.993518' * pi()/180) * COS(lat * pi()/180) * POWER(SIN(('101.7874058' - lon) * pi()/180 / 2), 2) ))) as distance from posts WHERE posts.subname LIKE '%flute%' AND posts.pricing <= '115' AND (posts.afternoon IN ('Thrs') OR posts.evening IN ('Thrs')) GROUP BY posts.UUID having distance <= '40' order by distance

So when on earth it returns result ?

When I search for Saturday for morning,afternoon and evening. I just don't understand how this query perform the search.Why I'm not getting expected results, where's my mistake? It would be helpful if anyone help me with this because I've wasted many days on this!

FULL PHP code for the above:

<?php
JSON formatted data received via ajax
$return = '{"avail":["Wed-2,Thrs-2","Thrs-3"]}';

In the above -1 means for monrning, -2 for afternoon and -3 for evening.I'm categorising them below.'
$avail = $data['avail'];
$days = array();
$cols = array();

$size = sizeof($avail);
if(($avail != "")&&($size > 1)){
$periods = array();
foreach($avail as $v){
    list($day, $column) = explode("-", $v); // make sure you validated the data to avoid errors
   $periods[$column][] = "'" . mysql_escape_string($day) . "'"; //strtolower// PHP would automatically create an empty array if $periods[$column] was not defined
}
$intToCol = array(1 => "morning", 2 => "afternoon", 3 => "evening");
// $periods should now be identical to ["2" => ["'sun'", "'mon'"], "3" => ["'sun'"]]

$conditions = array();
foreach($periods as $int => $days){
    $dayString = implode(",", $days);
    $conditions[] = " FIND_IN_SET ($dayString,  posts." . $intToCol[$int].")" ;
}
$add_here = "AND (" . implode(" OR ", $conditions) . ")";
}else if(($avail != "")&&($size == 1))
{
    foreach($avail as $k=>$v)
        {
             $v;

            $array = explode('-', $v);
            $day =$array[0]; // Wed
            $column =  $array[1]; // 2

            if($column == 1)
            {
            $col = "morning";

            }
            if($column == 2)
            {
                $col = "afternoon";
            }
            if($column == 3)
            {
                $col = "evening";
            }

        }

    $add_here = " posts.".$col." = '".$day."' ";

    $sql = "SELECT * , (3956 * 2 * ASIN(SQRT( POWER(SIN(('$lat' - lat) *  pi()/180 / 2), 2) +COS('$lat' * pi()/180) * COS(lat * pi()/180) * POWER(SIN(('$lon' - lon) * pi()/180 / 2), 2) ))) as distance  from posts WHERE posts.subname LIKE '%$subject%' AND posts.pricing <= '$rate' ".$add_here."".$IsTutionCentre." GROUP BY posts.UUID having  distance <= '$distance' order by distance";

    $stmt =connection::$pdo->prepare($sql);
        $stmt->execute();
        $place=array();
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { 
               $place[] = $row;
               }
               $_SESSION['subject'] = $place;

    //send back to ajax call  made page
    echo json_encode($place);
}
?>

Upvotes: 0

Views: 46

Answers (1)

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

afternoon = 'Thrs,Fri,Sat,Sun';
evening = 'Thrs,Fri,Sat,Sun';

When a set of values are stored as csv in a single row field, use of 'IN' is not going to work. You have to use 'FIND_IN_SET'

Change: 

AND (posts.afternoon IN ('Sat') OR posts.evening IN ('Sat'))

To:

AND (    FIND_IN_SET( 'Sat', posts.afternoon )
      OR FIND_IN_SET( 'Sat', posts.evening ) )

You can apply the same for other day occurrences.

Documentation Reference:
FIND_IN_SET(str,strlist):

  • Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by “,” characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (“,”) character.

Upvotes: 1

Related Questions