Below is my database structure:

PostUUID   subid  pricing Name   Poscode
abc1        1     100     userA  56000
abc2        2     100     userA  56000

abc3        1     100     userB  56000
abc4        2     100     userB  56000

abc5        1     100     userC  56000
abc6        2     100     userC  56000

Now I want to perform search on this table called 'posts'.

Say I want look for PostUUID(field) where the details are as below:

**-subid =1,2

-postcode=56000

-pricing-100**

In the above database image, there are 3 users meeting these criteria, namely vani,kalni and nair.

So the result should return 6 rows with unique PostUUID.
My query:

$a=$data['sub'];
    foreach($a as $v=>$k) 
      {

        $key[]=$v;

      }
    //variables
     $key2=implode(',',$key);
    $postcode=$data['postcode'];
    $rate=$data['rate'];

    $statement = $pdo->prepare("SELECT * FROM posts,subjects WHERE posts.subid IN (:key2) AND posts.pricing=:rate2  AND posts.Poscode=:postcode2 AND subjects.subid=posts.subid ORDER BY posts.Poscode DESC LIMIT 60");

        $statement->execute(array(':key2' => $key2,':rate2'=>$rate,':postcode2'=>$postcode));

    $json = array();
     while( $row = $statement->fetch()) {
        // array_push($json, array($row['Name'], $row['PostUUID']));
         array_push($json, array("name" => $row['Name'], "id" => $row['PostUUID'],"rate" => $row['pricing'],"postcode" => $row['Poscode'],"subject" => $row['subname'],"reputation" => $row['Reputation'],"plus" => $row['ReviewPlus'],"neg" => $row['ReviewNeg'],"weekM" => $row['week_morning'],"weekA" => $row['week_afternoon'],"weekE" => $row['week_evening'],"endM" => $row['weekend_morning'],"endA" => $row['weekend_afternoon'],"endE" => $row['weekend_evening']));
     }

        header('Content-Type: application/json');

        echo json_encode($json);

The result shown, 6 rows with same PostUUID. It simply iterated twice for each user as each user has two matching records for the above search criteria thus repeats same data.

Can anyone tell me what's wrong in my query please?

It is because pdo will carry out the statement as "SELECT * FROM posts,subjects WHERE posts.subid IN ('1,2') AND posts.pricing='100' AND posts.Poscode='56000' AND subjects.subid=posts.subid ORDER BY posts.Poscode DESC LIMIT 60"
There is several solution provided online. Here is one of the link I refer: http://stackoverflow.com/questions/1586587/pdo-binding-values-for-mysql-in-statement
And here is the codes I modified and tested:

$key = array(1,2);
$in = join(',', array_fill(0, count($key), '?'));
$statement = $pdo->prepare("SELECT * FROM posts WHERE posts.subid IN (".$in.") AND posts.pricing=?  AND posts.Poscode=? ORDER BY posts.Poscode DESC LIMIT 60");
$result = array_merge($key, array($rate,$postcode));
$statement->execute($result);

$json = array();
while( $row = $statement->fetch()) {
    array_push($json, array("name" => $row['Name'], "id" => $row['PostUUID'],"rate" => $row['pricing'],"postcode" => $row['Poscode'],"subject" => $row['subname'],"reputation" => $row['Reputation'],"plus" => $row['ReviewPlus'],"neg" => $row['ReviewNeg'],"weekM" => $row['week_morning'],"weekA" => $row['week_afternoon'],"weekE" => $row['week_evening'],"endM" => $row['weekend_morning'],"endA" => $row['weekend_afternoon'],"endE" => $row['weekend_evening']));
}
header('Content-Type: application/json');

echo json_encode($json);

Thank you so much, I tried your answer and it solved my problem. But when I use my own array , instead of hardcoding it like array(1,2); it doesn't get the second iteration. But the value echoed by array($key2) as (1,2). Below is my code:

$a=$data['sub'];

foreach($a as $v=>$k) 
  {

    $key0[]=$v;

  }
 $key2=implode(',',$key0);
 echo $key2;//this return 1,2

$key = array($key2);
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.