Woocommerce Question posted in
The official Woocommerce documentation can be found here.

MySQLi Repeat Row Depending on Column Quantity Value – Woocommerce

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> &pound".$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.

Tags:

2

Answers


  1. 0

    the idea is to make multiple select queries with different where clauses;

    for instance in this case we need query like this:

    SELECT * FROM table WHERE QTY > 0
UNION ALL
(SELECT * FROM table WHERE QTY > 1)
UNION ALL
(SELECT * FROM table WHERE QTY > 2)
ORDER BY `ORDER ID`

    so we can create mysql procedure to generate such query depending on maximum qty like so

    CREATE PROCEDURE GetAllProducts()
BEGIN
    select max(QTY) - 1 into @maxQty from table;
    set @resQry = 'SELECT * FROM table WHERE QTY > 0';
    set @i = 0;

    label1:
    LOOP
        IF @i = @maxQty THEN
            leave label1;
        END IF;
        SET @i = @i + 1;
        SET @resQry = CONCAT(@resQry, ' UNION ALL (SELECT * FROM table WHERE QTY > ', @i, ')');
    END LOOP label1;
    SET @resQry = CONCAT(@resQry, ' ORDER BY `ORDER ID`');
    PREPARE stmt FROM @resQry;
    EXECUTE stmt;
END;

    and then you can call this procedure 

    CALL GetAllProducts();
    Login or Signup to reply.
  2. 0

    If you are running MySQL 8.0, you can do this with a recursive query. Starting from your existing query, that would be:

    with recursive 
    data  as ( <... your query goes here ...> ),
    rcte as (
        select order_id, product_name, qty, 1 rn from data
        union all 
        select order_id, product_name, qty, rn + 1 from rcte where rn < qty
)
select order_id, product_name, qty from rcte

    In earlier versions, you could use a table of numbers. Here is an example that would quantities up to 3:

    select d.*
from ( <... your query goes here ...> ) d
inner join (select 1 qty union all select 2 union all select 3) n 
    on n.qty <= d.qty

    You can expand the subquery with more union alls to handle greater quantities.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search