I have created a simple SQL statement to display which products customers have purchased from a woo commerce store.
This is working great but i’m struggling to find a SQL function that repeats rows based on a value, maybe one doesn’t exist and PHP is required, any help would be great.
Thanks
CURRENT VIEW
ORDER ID. | PRODUCT NAME. | QTY
-------------------------------------
123 | APPLE | 3
124 | ORANGE | 2
125 | PEAR | 1
DESIRED VIEW
ORDER ID. | PRODUCT NAME. | QTY
-------------------------------------
123 | APPLE | 3
123 | APPLE | 3
123 | APPLE | 3
124 | ORANGE | 2
124 | ORANGE | 2
125 | PEAR | 1
CODE
<?php
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT wppc_wc_customer_lookup.customer_id, wppc_wc_customer_lookup.first_name,
wppc_wc_customer_lookup.last_name, wppc_wc_customer_lookup.email,
wppc_wc_order_stats.customer_id, wppc_wc_order_stats.total_sales,
wppc_wc_order_stats.order_id, wppc_wc_order_stats.date_created, wppc_wc_order_stats.status,
wppc_woocommerce_order_items.order_id, wppc_woocommerce_order_items.order_item_name,
wppc_woocommerce_order_items.order_item_id, wppc_woocommerce_order_itemmeta.order_item_id,
wppc_woocommerce_order_itemmeta.meta_id, wppc_woocommerce_order_itemmeta.meta_key,
wppc_woocommerce_order_itemmeta.meta_value FROM wppc_wc_customer_lookup
LEFT JOIN wppc_wc_order_stats ON wppc_wc_customer_lookup.customer_id =
wppc_wc_order_stats.customer_id
LEFT JOIN wppc_woocommerce_order_items ON wppc_wc_order_stats.order_id =
wppc_woocommerce_order_items.order_id
LEFT JOIN wppc_woocommerce_order_itemmeta ON wppc_woocommerce_order_items.order_item_id =
wppc_woocommerce_order_itemmeta.order_item_id
WHERE wppc_woocommerce_order_itemmeta.meta_key = '_qty' AND
wppc_woocommerce_order_items.order_item_name = 'Product Name' AND
wppc_wc_order_stats.status = 'wc-completed'
ORDER BY wppc_wc_order_stats.date_created DESC";
$result = $conn->query($sql);
echo "<table border="1"><tr>";
echo "<tr><th>Order ID</hr><th>First Name</hr><th>Last Name</hr><th>Email</hr><th>Total</hr>
<th>Order Date</hr><th>Product Name</hr><th>Quantity</hr>";
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<tr><td>".$row["order_id"]. " </td><td> " .$row["first_name"] . " </td><td> " .
$row["last_name"] . " </td> <td> " . $row["email"] . " </td><td> £".$row["total_sales"]
. " </td> <td>".$row["date_created"] . " </td> <td> ".$row["order_item_name"] . " </td> <td>
".$row["meta_value"] . " </td></tr> ";
}
} else {
echo "0 results";
}
echo "</table>";
$conn->close();
?>
This is working great but i’m struggling to find a SQL function that repeats rows based on a value, maybe one doesn’t exist and PHP is required, any help would be great.
2
Answers
the idea is to make multiple select queries with different where clauses;
for instance in this case we need query like this:
so we can create mysql procedure to generate such query depending on maximum qty like so
and then you can call this procedure
If you are running MySQL 8.0, you can do this with a recursive query. Starting from your existing query, that would be:
In earlier versions, you could use a table of numbers. Here is an example that would quantities up to 3:
You can expand the subquery with more
union all
s to handle greater quantities.