PHP MYSQL CONCAT
Get the solution ↓↓↓I'm doing some voluntary work for a group of local businesses delivering food to vulnerable people during COVID.
An example of an order:
Order No | Customer Name | Address | Quantity | Item Name | Notes
===================================================
#1234 | Freda Smith |3 High St | 1 | Loaf Bread | Wholemeal
#1234 | Freda Smith |3 High St | 2 | 500ml Milk | Skimmed
#1234 | Freda Smith |3 High St | 1 | Tea | Eng. Breakfast
I need to print all the details of order No #1234 onto one ticket, like this:
#1234, Freda Smith, 3 High St
1 X Loaf Bread, Wholemeal
2 X 500ml Milk, skimmed
1 X Tea, Eng. Breakfast
I have toyed withCONCAT
andCONCAT_GROUP
and have tried to figure out by separating theSELECT
from the HTML formatting but can't get what I need. I'm close but no cigar!
EDIT:
SELECT IS: $sql = "SELECT id,
Order_Number,
Full_Name,
Address,
Time_To_Deliver,
CONCAT(Quantity, Item_Name, Notes)
FROM orders
CONCAT_GROUP BY Order_Number";
$users = $mysqli->query($sql);
while($user = $users->fetch_assoc()){
Answer
Solution:
Your current design has redundant data you should normalize your database structure by maintaining separate table for order & order items further more you can add a customer table also.
As per your current design you can loop through all of the rows and conditionally showing each order no. only once.
select id,
order_number,
full_name,
address,
time_to_deliver,
quantity,
item_name,
notes
from orders
order by order_number asc
Hereorder by
clause is important so that you will get an ordered list of items for each order. Once you have all the results from query you can loop through your data as
$currentParent = false;
while ($orderItem = $result->fetch_assoc()) {
if ($currentParent != $orderItem['order_number']) {
// display common detail of each order here
echo '<h1>' . $orderItem['order_number'] .' '. $orderItem['full_name'] .' '. $orderItem['address']'</h1>';
$currentParent = $orderItem['order_number'];
}
echo '<p>' . $orderItem['quantity'] .' '. $orderItem['item_name'] . ' '. $orderItem['notes'] .'</p>'; // here list rest of the information of order iterms
...
}
Generated markup will be like
<h1>#1234, Freda Smith, 3 High St</h1>
<p>1 X Loaf Bread, Wholemeal</p>
<p>2 X 500ml Milk, skimmed </p>
<h1>#1235, Freda Smith, 3 High St</h1>
<p>1 X Tea, Eng. Breakfast</p>
//....
Although you can use aggregation withgroup_concat
(see ) directly on database level but it has limitations of character limit which can be adjusted but not a good approach to follow when you can handle this type of situation at application layer
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: your lock file does not contain a compatible set of packages. please run composer update
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.