MySQL Stored Proc & Temp Table

Hello DB gurus, I need your help.

I am working on a receiving report where I need to print multiple QR codes of each item. Example:

Receiving Report: 00001

Items     Quantity
---------    -------------
Item A         15
Item B         10
Item C         25

I am using MySQL db and the above sample is stored in a table called RRDetails. Of course, the structure is just an abstract.

Although I only have 3 items, I need to print 50 qr codes ( 15 for Item A, 10 for Item B and 25 for Item C). I was told that in order to accomplish this, I have to write a stored procedure in mysql, that stored proc would create a temporary table and then be populated from the number of quantities of the filtered rows from RRDetails.

Anyone can lead me to the solution?

Thank you all!

I am unsure why you need a temporary table, you have all you need above. Could you not loop through the recordset, first to last and use another loop to print the labels as you know how many you need? Pseudo code below (its not XOJO code).

[code]while not end of recordset
for count as integer = 1 to [quantity]
print the label for current Item
next

move to next record

end while[/code]

Hi Paul, thanks for the reply. I forgot to say that I’ll be printing these qr codes through a 3rd party reporting tool.

Have you actually got 15 records of Item A? If so you could consider a view, i.e. if your table is something like below and you just use a ‘distinct’ query to get your example. If so you could consider creating a view and point your reporting tool at that.

Item A - 1
Item A - 2
Item A - 3

Item A - 15
Item B - 1

Item B - 10
Item C - 1

Item C -25

No, Paul, the actual records (based on my example above) are only 3. I did not use distinct. In a way, I need to multiple each record based on their respective receipt quantity.

The reporting tool that I use has no problem at all, except that it relies to the number of rows (records) it can access.

Stored procedures may be the way to go depending on where your reporting tool is located (if desktop i may have been tempted to write out a temporary file and use that), take a look at the example here which may give you a few pointers to do what you need (with a few changes).

https://stackoverflow.com/questions/8812470/how-to-insert-multiple-rows-based-on-a-quantity-value-in-one-row

Paul, thank you for keeping through with me on this one and for pointing me to this link. Obviously, I am not DB savvy but am willing to learn as the need arises. I will study the link that you provided. Kudos to you! Big thanks!

I’ve done this by creating a table named Copies. It has a single column named ‘NumCopies’. Add records to this table, 1 through 25 (or the maximum number you may need.)

Then use a query or view like this:
SELECT * FROM [YourTable], Copies WHERE Copies.NumCopies<=[YourTable.Quantity]

This has the effect of multiplying the records. You should add an ORDER BY so the QR codes print in the order you expect.