MySQL outputting multiple rows if QTY > 1

Question

Is there any way to output multiple table rows if a certain field in the table is greater than 1.

Here's my example:

I'm building an auction website, where we sell tickets for a raffle. The tickets are stored in a table like so:

id, order_id, product_id, qty, price

When the time comes to print the tickets, I want to dump all of it into a CSV. So far, I'm doing this query (simplifying, omitting INNER JOIN):

SELECT id, order_id, product_id, qty, price FROM order_details

And then running something like the following loop on it:

foreach($rows as $row) {
   for($i = 0; $i < $row['qty']; $i++) {
      $tickets[] = $row;
   }
}

so that I get a separate entry for each qty (so that people get the correct amount of entries...).

Is there any way to accomplish this in SQL itself, so that each row is multiplied x times, where x is a certain field in the table (qty in this example)?

Answer 1

You can accomplish this purely in MySQL using a blackhole table and a trigger

Set up tables
First create the blackhole table you're going to insert to and the memory (or temporary table) the blackhole will reroute to.

CREATE TABLE Blackhole1 LIKE order_details ENGINE = BLACKHOLE;

CREATE TABLE temp_order_results LIKE order_details ENGINE = MEMORY;

Set up trigger
Now create a trigger on the blackhole table that will reroute the insert to the memory table, duplicating the rows with qty > 1.

DELIMITER $$

CREATE TRIGGER ai_Blackhole1_each AFTER INSERT ON blackhole1 FOR EACH ROW
BEGIN
  DECLARE ACount INTEGER;
  SET ACount = new.qty;
  WHILE ACount > 1 DO BEGIN
    INSERT INTO temp_order_results 
    VALUES (new.id, new.order_id, new.product_id, 1, new.price)
    SET ACount = ACount - 1;
  END; END WHILE;
END $$

DELIMITER ;

Statements to do the query
Now do a insert .. select into the blackhole

INSERT INTO blackhole1 
  SELECT id, order_id, product_id, qty, price FROM order_details;

And a select on temp_order_results.

SELECT id, order_id, product_id, qty, price FROM order_details;

Answer 2

To expand on @zdennis' answer, you could do this in MySQL:

    SELECT order_details.*
      FROM order_details
INNER JOIN kilo
           ON kilo.i < order_details.qty;

where the "kilo" relation has the integers 0 - 999, a contrivance adapted from a post by xaprb:

CREATE TABLE deca (i integer not null);
INSERT INTO deca (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
CREATE VIEW kilo (i) AS
      SELECT iii.i * 100 + ii.i * 10 + i.i
        FROM deca iii
  CROSS JOIN deca ii
  CROSS JOIN deca i;

Answer 3

There's not really a performance reason to. MySQL has a couple of strong suits: sorting, indexing, searching, storing, etc. You might as well do this in PHP.

Answer 4

I think this might be possible in Sql Server or Oracle by using a recursive common table expression (CTE) that joins the original table to itself and includes Qty-1 as an expression in place of Qty in the select list of the CTE. Sadly, last I heard MySql doesn't support CTEs yet.

Another option is to build a simple sequence table that just includes a numeric column and rows that start with 1 and end with the largest number you'll realistically have in the Qty column of your original table. You can join this to your orders table with a WHERE clause limiting the digits results to less than the Qty field and duplicate the rows this way. To quickly build the sequence table, create a digits table with records for 0 through 9 and cross join it to itself once for each power of 10.

Answer 5

The appropriate response is likely to use dual connect by level. See this question for related information: How can I return multiple identical rows based on a quantity field in the row itself?

Although this doesn't work in MySQL, see: How do I make a row generator in MySQL?

If you're using MySQL you'll need to be content with doing it in PHP or doing something gross (like the trigger that Johan posted). I'd vote to simply do it in PHP if that was the case.

Answer 6

I was required to do the same thing in order to avoid a cursor. My solution is for SQL Server and is really simple because for my case, qty is never greater than 99, so here is a sample using temporary tables:

create table #t (
     id int
    ,qty int
)

insert into #t values (1,2)
insert into #t values (2,3)

create table #n (
     id int
)

insert into #n values (1)
insert into #n values (2)
insert into #n values (3)
insert into #n values (4)
insert into #n values (5)

select t.*
from #t t
inner join #n n on
    n.id <= t.qty

You just need to insert into #n the max qty you expect (in my case 99).