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
/*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
FetchAll will return a numeric array containing all the records in the result set in whatever format they were supposed to be in. So $row[0] will be the first row of the result, $row[1] will be the second, etc.
I think it must be to do with the line GROUP_CONCAT(CONCAT(tblorders.Quantity, ' of ', tblproducts.ProductName) SEPARATOR ', ') AS productnames but need something like it so each order displays the products in a group for example if one order has a bag of coal and a bag of kindling then it shows on the same line for the order
then the next order shows just the products ordered for that order
Yes, GROUP_CONCAT() is an aggerate function used with a GROUP BY … term in a query. Since your query doesn’t have a GROUP BY … term, it is grouping all the rows into one.
Your data is not normalized. The only place the customer name should be stored is in the customer(s) table. You should use the id (autoincrement primary index) from that table, i.e. the customer_id, to relate any customer data back to the customer it belongs to. The order(s) table should have one row per order, with the unique/one-time order information. You should have an order_item(s) table that has one row per each item in each order. The id (autoincrement primary index) from the order(s) table, i.e. the order_id, would be used in the order_item(s) table to relate the items back to the order they belong to. Once you do this, you would GROUP BY the order id in the order(s) table.
I got the customers stored in the customers table but with no customer number but has id number but customers are also stored in the tblorders db table as well, the code is from a system I got online as was the closest to the system I need to build but think I’ll see if I can find a better coded system and modify that instead of this one
It makes sense to have one row per order, bit odd how this one creates a new row for each product id in the tblorders db table
What it sounds like it’s doing is a lower level of normalization than would be considered “normal” (to reuse the word). It still “works”, but isnt particularly well guarded against redundancy.
Yeah sounds like it, I’ve looked around but can’t find any that has one row in the database for orders, all the ones I have found look like they add each product as a new row for same orders in the database table, don’t seem to be a table for orders and one for order items that I could find. I’ll have another look again
Hi, just a follow up on this. I think I have found a better system where customer name in just in the tbl_customers table and in tbl_invoice is just the order such as invoice_id, order_date, total, paid and due and is one row per order and there is another table called invoice_details which has invoice_id, product_id, product_code, product_name, qty, price, total, order_date so the database structure looks better. What I am trying to work out how to do is have a order page which it’s already got but it does not have the customer name in the html table on the page as was thinking it might be good to show the customer name in the html table so to do that would I need to have a customer_id or something in the tbl_invoice db table?
Is there some reason you are not designing, writing, and testing your own code/query(ies) to do this? We are here to help you with code you have written, not to get someone else’s bad design to work.
Invoicing and payments are handled in a separate table from the order(s). Derived values, such as totals, are not stored, they are calculated when needed so that they are always up to date for things like returns/refunds…