0

I'm developing a web-based tool that can help analyze number intervals that occurs in a 6-digit lottery.

Let us focus on a certain number first. Say 7

enter image description here

The sql query I've done so far:

SELECT * FROM `l642` WHERE `1d`=7 OR `2d`=7 OR `3d`=7 OR `4d`=7 OR `5d`=7 
OR `6d`=7 ORDER BY `draw_date` DESC LIMIT 2

This will pull the last two latest dates where number 7 is present

I'm thinking of using DATEDIFF but I'm confused on how to get the previous value to subtract it on the latest draw_date

My goal is to list the intervals of numbers 1-42 and I'll plan to accomplish it using PHP.

Looking forward to your help

leipzy
  • 11,676
  • 6
  • 19
  • 24

1 Answers1

0

A few ideas spring to mind.

(1) First, since you perfectly have your result set ordered, use PHP loop on the two rows getting $date1 =$row['draw_date']. Then fetch next/last row and set $date2 =$row['draw_date']. With these two you have 

$diff=date_diff($date1,$date2);

as the difference in days.

(2) A second way is to have mysql return datediff by including a rownumber in the resultset and doing a self-join with aliases say alias a for row1 and alias b for row2.

datediff(a.draw_date,b.drawdate).

How one goes about getting rownumber could be either:

(2a) rownumber found here: With MySQL, how can I generate a column containing the record index in a table?

(2b) worktable with id int auto_increment primary key column with select into from your shown LIMIT 2 query (and a truncate table worktable between iterations 1 to 42) to reset auto_increment to 0.

The entire thing could be wrapped with an outer table 1 to 42 where 42 rows are brought back with 2 columns (num, number_of_days), but that wasn't your question.

So considering how infrequent you are probably doing this, I would probably recommend not over-engineering it and would shoot for #1

Community
  • 1
  • 1
Drew
  • 24,851
  • 10
  • 43
  • 78
  • I don't understand any of this - but maybe the OP does. – Strawberry Jul 12 '15 at 08:29
  • It seems date_diff simply solves this. Though sometimes only 1 record is returned which is inevitable. Anyway Thank you – leipzy Jul 12 '15 at 09:29
  • I would think with just about any amount of historical data short of a nearly empty table that 2 rows is inevitable for any of the 42 – Drew Jul 12 '15 at 12:30
  • Yes that's right. There's a chance that the number didnt occured on a given history time frame – leipzy Jul 15 '15 at 06:23
  • also possible that #17 never shows up ever in the history of all data – Drew Jul 15 '15 at 06:27