CODEWITHSUNDEEP
phppdo
Bram Raaijmakers
Bram Raaijmakers

Reputation: 67

PDO fetchall query, show multiple results but only 1 per ID

My question wasn't clear so I'm editing a bit.

I have a website where people can bid on stuff and on their account page they can track there bids. But if one person bids twice on a product they see that product twice. So I only want to show there highest bid per product.

This is my query:

$objDatabaseAds->prepare('SELECT * FROM bieden WHERE ownerid = :ownerid ORDER BY prijs DESC');

Greetings,

Bram

EDIT

$objGetHighest = $objDatabaseAds->prepare('SELECT * FROM bieden WHERE ownerid = :ownerid GROUP BY adid');
$objGetHighest->bindParam('ownerid', $member_id);
$objGetHighest->execute();
$objGetHighestFetch = $objGetHighest->fetchAll(PDO::FETCH_ASSOC);

foreach ($objGetHighestFetch as $key) {
  $adID = $key['adid'];
  //This echoes 3 values which is good!!

  $objGetBid = $objDatabaseAds->prepare('SELECT * FROM bieden WHERE adid = :adid');
  $objGetBid->bindParam('adid', $adID);
  $objGetBid->execute();
  $objGetBidFetch = $objGetBid->fetchAll(PDO::FETCH_ASSOC);
}

So this is what I got right now. The $adID echoes 3 values out of the 4 ads where 2 of them have the same ID so this is good. But then I do another query and then I get then I see the bids of the ID which he shows once.

Table

Upvotes: 2

Views: 59

Answers (2)

angvee
angvee

Reputation: 257

Try this. just add GROUP BY adid, it works like you want. Cheers! hope helpfully.

$objGetHighest = $conn->prepare("SELECT * FROM tabs WHERE ownerid = :ownerid GROUP BY adid");
$objGetHighest->bindParam('ownerid', $member_id);
$objGetHighest->execute();
$objGetHighestFetch = $objGetHighest->fetchAll(PDO::FETCH_ASSOC);

foreach ($objGetHighestFetch as $key) {
  $adID = $key['adid'];
  //This echoes 3 values which is good!!
  //echo $adID."<br />";

  $objGetBid = $conn->prepare("SELECT * FROM tabs WHERE adid = '$adID' GROUP BY adid ");
  $objGetBid->execute();
  $objGetBidFetch = $objGetBid->fetchAll(PDO::FETCH_ASSOC);
  print_r($objGetBidFetch);
}

Upvotes: 2

angvee
angvee

Reputation: 257

if want to get one higher rows, you can try this, hope will help you. Cheers!

$query = $objDatabaseAds->prepare('SELECT * FROM bieden WHERE ownerid = :ownerid ORDER BY prijs DESC LIMIT 1');

$query->execute();

while($data = $query->fetch(PDO::FETCH_ASSOC)){
     echo $data['column_name'];
     echo $data['ownerid'];
}

Upvotes: 2

Related Questions