Menu
I am trying to make a system which automatically prints out orders, and I am facing a problem right now. I am trying to make a string in a format which the printer is understanding, and right now, i need to gather the data from database before sending. Now my problem is that in some cases the order_id is the same. Here is an example:
+----------+----------------------+
| order_id | product_name |
+----------+----------------------+
| 1 | Pizza with cheese |
+----------+----------------------+
| 1 | Coca-Cola Zero |
+----------+----------------------+
| 2 | Spaghetti |
+----------+----------------------+
| 3 | Lasagna |
+----------+----------------------+
So what i am trying to is gather all the "product_name"'s in one string which have the same "order_id", so it display it as: "Pizza with cheese, Coca-Cola Zero"
Is there any way to do that?
Thanks in advance.
EDIT: I don't know if I mentioned it above clearly, but I want to make a PHP script that are displaying it. Sorry if I am confusing.
688
You can select for this
select order_id, count(*), group_concat(product_name)
from orders
group by order_id
having count(*) > 1;
This gives you all order_ids which have multiple entries with the number of entries and the orders concatenated together. If you don't need the number leave out the count(*) in the column list.
If you want all orders leave out the having
select order_id, count(*), group_concat(product_name)
from orders
group by order_id
And using that from PHP
$conn = new mysqli($host, $user, $pass, $db);
// use the appropriate select
$result = $conn->query('select order_id, group_concat(product_name) '
. 'from orders group by order_id');
while ($row = $result->fetch_array()) {
// process the result row
}
This is of course just a brief outline. You must add the error handling and fill in what you want to do with the result.
80
You could use
SELECT order_id, GROUP_CONCAT(product_name SEPARATOR ', ') AS product_names FROM orders GROUP BY order_id;
Example in PHP:
<?php
$db_server = 'localhost';
$db_user = 'user';
$db_pass = 'pass';
$db_name = 'database';
$con = mysql_connect($db_server, $db_user, $db_pass)
or die('Could not connect to the server!');
mysql_select_db($db_name)
or die('Could not select a database.');
$sql = "SELECT order_id, GROUP_CONCAT(product_name SEPARATOR ', ')";
$sql .= " AS product_names FROM orders GROUP BY order_id";
$result = mysql_query($sql)
or die('A error occured: ' . mysql_error());
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-type" content="text/html;charset=UTF-8" />
<title>test</title>
</head>
<body>
<h1>orders</h1>
<table>
<tr><th>orders_id</th><th>product_names</th></tr>
<?php
while ($row = mysql_fetch_assoc($result)) {
printf("<tr><td>%d</td><td>%s</td></tr>\n", $row['order_id'],
$row['product_names']);
}
?>
</table>
</body>
</html>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
