0

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?

Edited by Kalaivani Nair

2
Contributors
2
Replies
27
Views
2 Years
Discussion Span
Last Post by Kalaivani Nair
1

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);
0

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);
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.