Trying to display all records for a specific customer

[ianhaneybs]

Hi

I'm trying to make a system for someone I know and I have a customers page with a link to view orders and created another page to display the orders made for that specific customer name, I have got some php code on the page but it looks like it's only getting the first record of the customer name instead of all the records for the customer name

The code on the customer_orders.php page is below

<?php

                                /*error_reporting(E_ALL);
                                ini_set('display_errors', 1);*/

                                $CustomerName = $_GET['customername'];
                                //echo htmlentities($CustomerName);

                                $sql = "SELECT 
                                                    tblorders.InvoiceNumber,
                                                    tblorders.LorryName,
                                                    tblorders.CustomerName,
                                                    tblorders.DeliveryDate,
                                                    tblorders.PaymentMode,
                                                    tblorders.DeliveryMethod,
                                                    GROUP_CONCAT(CONCAT(tblorders.Quantity, ' of ', tblproducts.ProductName) SEPARATOR ', ') AS productnames, 
                                                    tblorders.InvoiceGenDate
                                                FROM tblorders 
                                                JOIN tblproducts ON tblproducts.id = tblorders.ProductId
                                                JOIN tblcustomers ON tblorders.CustomerName = tblcustomers.customername
                                                WHERE tblorders.CustomerName = :CustomerName";

                                $statement = $dbh->prepare($sql);
                                $statement->bindValue(':CustomerName', $CustomerName);
                                $result = $statement->execute();

                                if(!$result)
                                {
                                    //Query failed
    echo "Query failed";
    //Add debugging code
                                }
                                elseif(!$statement->rowCount())
{
    //No results returned
    echo "No user found for user " . htmlentities($CustomerName);
   //Add debugging code
}
                                else
{
    //A record was returned, display results
    $row = $statement->fetch(PDO::FETCH_ASSOC);

                                    ?>

                                <tbody>
                                    <tr>
                                        <td><?php echo $row['InvoiceNumber']; ?></td>
                                        <td><?php echo $row['LorryName'] ;?></td>
                                        <td><?php echo htmlentities(date("d-m-Y", strtotime($row['DeliveryDate'])));?></td>
                                        <td><?php echo $row['PaymentMode'];?></td>
                                        <td><?php echo $row['DeliveryMethod'];?></td>
                                        <td><?php echo htmlentities(date("d-m-Y", strtotime($row['InvoiceGenDate'])));?></td>
                                        <td><?php echo $row['productnames'];?></td>
                                        <td class="project-actions text-right">
                                        <a class="btn btn-primary btn-sm" href="#">View Invoice</a></td>
                                    </tr>
                            </tbody>

                            <?php

    //echo "Start Date: {$row['CustomerName']}<br/>\n";
}

$statement->closeCursor();
                                ?>

I'm not sure where I have gone wrong, I got the code from online and trying to do this code myself, could anyone take a look please and see what I have done wrong

[Reverend Jim]

I don't work with PHP although I've done a fair bit of SQL and SQL selects return recordsets that you have to step through to get all of the returned records so I'm thinking you'll need to process the records in a loop. Your comment

/A record was returned, display results

implies that you are expecting only one record. Perhaps try the select in a SQL console to see if you are actually getting back what you expect. What database engine are you using?

[Biiim]

You got a GROUP_CONCAT() in the SELECT statement so the query will group everything as one, since no GROUP BY was specified.

You don't want GROUP_CONCAT anything in tblorders if you want to view all the orders for that Customer.

Add to the bottom after the WHERE clause: GROUP BY tblorders.order_id

This will force it to make one row for each order, then you can play around with the GROUP_CONCAT() to do what you want it to but I am not sure you need it. I'm guessing you want to group it by each order and maybe by each product as well, that would be GROUP BY tblorder.order_id, tblproducts.product_id

Then I would throw that into a an associative PHP array and display it how I wanted foreach($returned_result_set as $row){var_dump($row);}

you could use PHP arrays to put all products into an array for each order as well:

$order_data = [];
foreach($returned_data as $row){
    $order_data[$row['order_id']] = $row;
}

foreach($order_data as $key=>$row){
    echo 'Order ID: '.$key.'  Product:'.$row['product_id'].'<br>';
}

Maybe have typoed in there I just wrote this from my head

[gottaloveit]

Agree with Biiim, you need to loop through the results. example below. note I took the body out of the loop.

<?php

                                /*error_reporting(E_ALL);
                                ini_set('display_errors', 1);*/

                                $CustomerName = $_GET['customername'];
                                //echo htmlentities($CustomerName);

                                $sql = "SELECT 
                                                    tblorders.InvoiceNumber,
                                                    tblorders.LorryName,
                                                    tblorders.CustomerName,
                                                    tblorders.DeliveryDate,
                                                    tblorders.PaymentMode,
                                                    tblorders.DeliveryMethod,
                                                    GROUP_CONCAT(CONCAT(tblorders.Quantity, ' of ', tblproducts.ProductName) SEPARATOR ', ') AS productnames, 
                                                    tblorders.InvoiceGenDate
                                                FROM tblorders 
                                                JOIN tblproducts ON tblproducts.id = tblorders.ProductId
                                                JOIN tblcustomers ON tblorders.CustomerName = tblcustomers.customername
                                                WHERE tblorders.CustomerName = :CustomerName";

                                $statement = $dbh->prepare($sql);
                                $statement->bindValue(':CustomerName', $CustomerName);
                                $result = $statement->execute();

                                if(!$result)
                                {
                                    //Query failed
    echo "Query failed";
    //Add debugging code
                                }
                                elseif(!$statement->rowCount())
{
    //No results returned
    echo "No user found for user " . htmlentities($CustomerName);
   //Add debugging code
}
                                else
{
?>
                                <tbody>
 <?php
    //A record was returned, display results
    while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {

                                    ?>


                                    <tr>
                                        <td><?php echo $row['InvoiceNumber']; ?></td>
                                        <td><?php echo $row['LorryName'] ;?></td>
                                        <td><?php echo htmlentities(date("d-m-Y", strtotime($row['DeliveryDate'])));?></td>
                                        <td><?php echo $row['PaymentMode'];?></td>
                                        <td><?php echo $row['DeliveryMethod'];?></td>
                                        <td><?php echo htmlentities(date("d-m-Y", strtotime($row['InvoiceGenDate'])));?></td>
                                        <td><?php echo $row['productnames'];?></td>
                                        <td class="project-actions text-right">
                                        <a class="btn btn-primary btn-sm" href="#">View Invoice</a></td>
                                    </tr>
<?php } ?>
                            </tbody>

                            <?php

    //echo "Start Date: {$row['CustomerName']}<br/>\n";
}

$statement->closeCursor();
                                ?>