Trying to display all records for a specific customer

I

ianhaney

New member
Hi

It's my first time of posting and 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: 
<?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);

    $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
 
Hi,
I assume you are using mysql.
Do you have customerId do filter by number instead the string?
Can you share the result of var_dump?
Code: 
$row = $statement->fetch(PDO::FETCH_ASSOC);
var_dump($row);
 
Try this:
PHP: 
<?php
// Error reporting for debugging
error_reporting(E_ALL);
ini_set('display_errors', 1);

$CustomerName = $_GET['customername'];

// SQL query to retrieve customer orders
$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
        GROUP BY tblorders.InvoiceNumber"; // Added GROUP BY to ensure proper aggregation

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

if (!$result) {
    echo "Query failed";
} elseif (!$statement->rowCount()) {
    echo "No user found for user " . htmlentities($CustomerName);
} else {
    // Fetch all records for the customer
    while ($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
    }
}

$statement->closeCursor();
?>

The SQL query uses a JOIN operation that may not be correctly configured to retrieve all relevant records. If there are discrepancies in the CustomerName field across the tables, it could lead to missing records.
If the CustomerName in the tblorders table does not exactly match the customername in the tblcustomers table (e.g., due to case sensitivity or leading/trailing spaces), the query will not return the expected results.
The code currently fetches only one record using $statement-&gt;fetch(PDO::FETCH_ASSOC);. If multiple records exist for the customer, they will not be displayed.
The revised code should fix these for you. Hope it helps.
 
You must log in or register to reply here.
Back
Top