Pdo group by

[geatzo]

Im trying to build a trading script ive coded the listing part just fine and the offering part i am now trying to display how many offers there are on each item.

 

The offer on is what they are offering on so i have a table with all the items in then in the offer table i store the id of the offered on item

 

    $stmt = $db->prepare("SELECT count(*) FROM trade_offers WHERE offer_on = ?");
$stmt->execute([$result->id]);
$count = $stmt->fetchColumn();

so i would like to group by offer_on so atm my code would show 2 results but there is only 1 offer with 2 items. So the person has offered 2 items in exchange for item with id 1

[geatzo]

    $query2 = mysqli_query($link,"SELECT * FROM `trade_offers` WHERE `offer_on`='{$result->id}' GROUP BY `offer_on`");
            $numOffers = mysqli_num_rows($query2);

This does what i need it todo buts its mysql and id like to use pdo
    

[mac_gyver]

6 minutes ago, geatzo said:

$result->id

this implies you are doing this based on the result of some other query. don't do that. use a single appropriate type of JOIN query to get the data that you want all at once.

at the sql query statement level, the syntax is the same for both the mysqli and PDO extensions. i recommend that you build any sql query statement in a php variable, so that the sql query syntax is separated as much as possible from the php syntax -

$sql = "SELECT count(*) FROM trade_offers WHERE offer_on = ? GROUP BY offer_on";

 

[geatzo]

12 minutes ago, mac_gyver said:

this implies you are doing this based on the result of some other query. don't do that. use a single appropriate type of JOIN query to get the data that you want all at once.

at the sql query statement level, the syntax is the same for both the mysqli and PDO extensions. i recommend that you build any sql query statement in a php variable, so that the sql query syntax is separated as much as possible from the php syntax -

$sql = "SELECT count(*) FROM trade_offers WHERE offer_on = ? GROUP BY offer_on";

 

so like this 

        $stmt = $db->prepare("SELECT count(*) FROM trade_offers WHERE offer_on = ? GROUP BY offer_on");
$stmt->execute([$result->id]);
$count = $stmt->fetchColumn();

which shows 2 results still instead of the 1  i am doing a while loop to display all the users items i then display how many offers are on each item inside the while loop.

Edited May 4 by geatzo

[Barand]

perhaps you should show the while loop and your query  which gets $result->id too.