Creating a counter for occurrences of lottery numbers in MYSQL longtext column. This may require PHP

Question

I may need PHP or some other language to do this job, though A solution in MYSQL directly is preferred.

I've gotten together a list of the winning lottery numbers for the New York state lottery for the last year. I've created a 4 column table in MYSQL to store the data. One for Date, one for the winning lottery number, one for the bonus number, and one for the payout. After doing a little research on how to store the lottery numbers, which I considered an array of ints, I found a recommendation to use the longtext data type. The data is entered with a pipe separating each value like this: 01|34|35|36|56|59. The numbers are always in ascending order, are given two digits, and do not go above 59.

The next step in my process is to create a table that shows how often each number happens. Here is my pseudo code for this thing:

1 Create table "Number_Frequency"
2 Create column "Number"(Type int)
3 Create column "Frequency"(Type int, initial value 0)
4 Look at table "new_table"
5 Look at column "numbers"
6 Look at first row
7 Look at first two digits
  7A If this two digit number isn't in the column "Number" in the table "Number_Frequency", add to column "Number" in table "Number_Frequency" and set corresponding "Frequency" to 1
  7B Else if number is already in column "Number" increase the value of frequency by 1
8 Look for a pipe symbol
  8A If there is a pipe symbol, repeat all parts of step 7 for the next two digits after the pipe symbol.
  8B Else if there is another row, look at the first two digits of that row, and start from step 7A
  8C Else terminate.

As you can see I have a very clear idea of what I want to do. I have a few simple questions for SO: Can this be done exclusively in MYSQL? And more importantly, is there a major, glaring reason why one shouldn't bother trying? If there is, what is that reason?

Answer 1

Storing a fixed-size set of numbers as a concatenated string is madness.

Consider storing them in 6 separate columns, each of an integer type. Alternatively, store them in a table with just two columns: draw_id and value; each draw has 6 entries in this table. It then becomes trivial to construct a query to look for the number of occurrences of a given value.

So something like:

draw_values
===========

draw_id       value
-------------------
538           1
538           34
538           35
538           36
538           56
538           59
539           5
539           10
539           13
539           27
539           38
539           56
...


draw
====

draw_id       winner_id       payout
---------------------------------------
538           127740          1000000
539           839820          1500000
...

where draw_id is a foreign key into your main table, which contains the one-off details for each draw (e.g. the payout).