0

i have a group by query which fetch the data, How much qty a user ordered within a date range which results like that.

select od.item_code, o.user_id, SUM(od.total_qty) as qty_sold from `orders` as `o` inner join `order_details` as `od` on `od`.`order_id` = `o`.`order_id` where ( date(`o`.`order_receive_date`) >= '2022-02-01' and date(`o`.`order_receive_date`) <= '2022-02-09' ) group by `od`.`item_code`, `o`.`user_id`
product_id user_id qty_ordered
11 U123 5
22 U123 1
22 U124 9
11 U124 0
11 U312 1
22 U312 1

now I want to get this data in following format

product_id U123 U124 U312
11 5 0 1
22 1 9 1

please guide me how can i do this either in php , laravel or directly using MySQL query thanks in advance

Zia
  • 76
  • 2
  • 11
  • 1
    Check https://stackoverflow.com/questions/1241178/mysql-rows-to-columns – Ergest Basha Apr 15 '22 at 16:13
  • 1
    A simple looping over the data and building a new array would accomplish that. Give it a try. The in-SQL solutions seem rather heavy in comparison. – Markus AO Apr 15 '22 at 18:10
  • @MarkusAO you are right SQL solution would be heavy . can you please guide me about looping on data – Zia Apr 15 '22 at 19:03

1 Answers1

0
<?php
$sql = "select od.item_code, o.user_id, SUM(od.total_qty) as qty_sold from `orders` as `o` inner join `order_details` as `od` on `od`.`order_id` = `o`.`order_id` where ( date(`o`.`order_receive_date`) >= '2022-02-01' and date(`o`.`order_receive_date`) <= '2022-02-09' ) group by `od`.`item_code`, `o`.`user_id`";
$result = $conn->query($sql);
$list_array = array();
$column_names = array();
if ($result->num_rows > 0) {
    // output data of each row
    while ($row = $result->fetch_assoc()) {
        $column_array[$row['product_id']][$row['user_id']] = $row['qty_ordered'];
        if (!in_array($row['user_id'], $column_names)) {
            $column_names[] = $row['user_id'];
        }
    }
}
$conn->close();
?>

<html>
    <body>
        <table>
            <tr>
                <th>
                Product id
                </th>
                <?php 
                foreach($column_names as $v)
                {
                echo '<td>'.$v.'</td>';
                }
                ?>
            </tr>
            <?php 
            foreach($list_array as $product_id=>$row)
            {?>
            <tr>
            <td><?php echo $product_id;?></td>
                <?php 
                foreach($column_names as $v)
                {
                if(isset($row[$v]))
                {
                echo '<td>'.$row[$v].'</td>';
                }
                else
                {
                echo '<td>0</td>';
                }

                }
                ?>
            <tr>
        <?php } ?>
        </table>
    </body>
</html>
Paresh
  • 16
  • 2
  • This is called pivot table, for more details http://www.artfulsoftware.com/infotree/qrytip.php?id=78 and this is an example to do it https://paiza.io/projects/e/sUmyH7GkETQwmwrgB1NOOw?theme=twilight – Pascal Tovohery Apr 17 '22 at 14:08
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Cristik Apr 17 '22 at 17:06