Making a lottery with mysql query problem... help needed

[ccadic]

Hi All.
I'm trying to evaluate the capability of making a lottery using mysql
I'm not an expert in sql queries.
What I'm trying to do is to find a query which will test if a combinaison of number exists in a collectionof grids


Example:
Player 1 chooses this grid: 1 3 5 7 9
Player 1 chooses this grid: 1 5 7 8 9
Player 3 chooses this grid: 1 2 3 4 5
The combinaison to win is: 1 2 3 4 5

I would like to have this query (select)

Query 1: Please sort the grids which have 5 digits identical to the combinaison
Query 2: Please sort the grids which have 2 digits (and only two) identical to the winning combinaison
Quesry 3: Please sort the grids which have only one digit identical in the combinaison


Has anyone worked on this before ?
A help would be welcome as presently I plan to develop a brute force analysing code using PERL ... which will be slow.


CREATE TABLE `grids` (
`id_grille` int(10) unsigned NOT NULL auto_increment,
`id_user` int(11) NOT NULL default '0',
`date_validation` date NOT NULL default '0000-00-00',
`time_validation` time NOT NULL default '00:00:00',
`boule1` int(11) NOT NULL default '0',
`boule2` int(11) NOT NULL default '0',
`boule3` int(11) NOT NULL default '0',
`boule4` int(11) NOT NULL default '0',
`boule5` int(11) NOT NULL default '0',
`boule6` int(11) NOT NULL default '0',
`complementary` int(11) NOT NULL default '0',
`free` int(11) NOT NULL default '0',
PRIMARY KEY (`id_grille`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

--
-- Contenu de la table `grilles`
--

INSERT INTO `grids` VALUES (1, 1, '2006-02-08', '15:28:39', 1, 2, 3, 4, 5, 6, 7, 1);

-- --------------------------------------------------------

--
-- Structure de la table `loterie`
--

CREATE TABLE `loterie` (
`id_loterie` int(10) unsigned NOT NULL auto_increment,
`nom` varchar(220) NOT NULL default '',
`description` text NOT NULL,
`rang1` decimal(3,2) NOT NULL default '0.00',
`rang2` decimal(3,2) NOT NULL default '0.00',
`rang3` decimal(3,2) NOT NULL default '0.00',
`rang4` decimal(3,2) NOT NULL default '0.00',
`rang5` decimal(3,2) NOT NULL default '0.00',
`rang6` decimal(3,2) NOT NULL default '0.00',
`statut` int(1) NOT NULL default '0',
`permanente` int(1) NOT NULL default '0',
`date_debut` date NOT NULL default '0000-00-00',
`date_fin` date NOT NULL default '0000-00-00',
PRIMARY KEY (`id_loterie`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

--
-- Contenu de la table `loterie`
--

INSERT INTO `loterie` VALUES (1, 'MagaLoto', 'Loterie quotidienne avec des cadeaux à gagner.', 0.01, 0.05, 0.10, 1.00, 20.00, 99.99, 1, 1, '0000-00-00', '0000-00-00');

-- --------------------------------------------------------

--
-- Structure de la table `tirages` / WINNING COMBINAISON
--

CREATE TABLE `tirages` (
`id_tirage` int(11) NOT NULL auto_increment,
`date_tirage` date NOT NULL default '0000-00-00',
`time_tirage` time NOT NULL default '00:00:00',
`boule1` int(11) NOT NULL default '0',
`boule2` int(11) NOT NULL default '0',
`boule3` int(11) NOT NULL default '0',
`boule4` int(11) NOT NULL default '0',
`boule5` int(11) NOT NULL default '0',
`boule6` int(11) NOT NULL default '0',
`complementaire` int(11) NOT NULL default '0',
PRIMARY KEY (`id_tirage`)
) TYPE=MyISAM AUTO_INCREMENT=7 ;

--
-- Contenu de la table `tirages`
--

INSERT INTO `tirages` VALUES (1, '2006-02-08', '15:41:20', 99, 97, 19, 54, 29, 96, 56);
INSERT INTO `tirages` VALUES (2, '2006-02-09', '01:45:10', 36, 31, 42, 40, 20, 38, 35);
INSERT INTO `tirages` VALUES (3, '2006-02-09', '01:47:48', 2, 38, 6, 32, 5, 19, 45);
INSERT INTO `tirages` VALUES (4, '2006-02-09', '01:47:49', 14, 44, 36, 10, 23, 28, 6);
INSERT INTO `tirages` VALUES (5, '2006-02-09', '01:50:30', 38, 45, 25, 15, 12, 14, 32);
INSERT INTO `tirages` VALUES (6, '2006-02-09', '01:50:49', 23, 45, 28, 19, 31, 15, 41);

[raf]

i would just create a table with the palyers and a table with the players PK and a number they choose (so six records in this table for each player.). Both columns need to be of a numeric type + indexed

after your winning combination is created, you just run

SELECT COUNT(*) numrec, playerID FROM player_number WHERE numbercolumns In (1,2,3,4,5,6) GROUP BY playerID ORDER BY numrec DESC

then you just loop through your resultset displaying them.

If you wanna limit the number of returned reocrds, then you can use a HAVING clause, like

SELECT COUNT(*) numrec, playerID FROM player_number WHERE numbercolumns In (1,2,3,4,5,6) GROUP BY playerID HAVING numrec In (5,2,1) ORDER BY numrec DESC

as long as you don't have more then a million players for each draw, you'll still have a system that is more then performant enough (your resultset wount even take a second to be composed). If you have a lott of players, then the most time will be consumed in processing the resultset and displaying it.