lotto script

[nisroc]

Hey all i an rather new to php and mysql. I am currently trying to  code a lottery script. In the database table (draws) I have number1, number2, number3, number4, number5, number6 and bonus. What i am currently trying is get the top 6 most called numbers and the top bonus called number. My problem is i am not sure how to write this out without using a sorting script. is this possible to do right in mysql?  

 

any help would be greatfull

 

[kicken]

Change your database structure so you store each number as a separate row, then you can just do a simple select with a group by to find the top numbers.

 

For example, have a table such as:

create table draw_numbers (
   drawId int
   , position tinyint
   , number int
)

drawId would link to another drawings table which has a row for each draw. position would be the number position in the draw, eg 1-7 (with 7 being the bonus) and number would be the number drawn

 

With that structure, to get the top bonus number you'd do:

select number, count(*) as totalDraws from draw_numbers where position=7 group by number order by totalDraws limit 1

On a side note, I've moved your thread to the PHP Coding help section. You originally posted in the Regex forum but the question has nothing to do with Regex.

[JIXO]

I use this syntax :

SELECT col_name, COUNT(col_name) most FROM table_name GROUP BY col_name ORDER BY most DESC;

Where col_name is the column name, table_name is the table name. For example, a database named Login with a users table ( called users ), which has a country column, to find out the most used value for column country :

SELECT country, COUNT(country) most FROM users GROUP BY country ORDER BY most DESC;

The result of the above SQL is :

+---------+------+
| country | most |
+---------+------+
|       0 |    6 |
|       3 |    3 |
|     195 |    1 |
|     225 |    1 |
|      14 |    1 |
+---------+------+

[rbrown]

Most lottery sites use this type format

 

12/3/2012 03-05-15-18-23-36

 

When you download their past numbers.

So using an good editor or speadsheeet program you can setup an array like this:

$past_numbers = array(
    '9/17/2009' => '16-19-23-26-36-38', '9/21/2009' => '10-13-14-19-26-30', '9/24/2009' => '03-04-22-25-36-39');

Then

foreach ($past_numbers as $key => $value) { //$key is the date. $value are the numbers 
    //echo $value.'<br>';
    $add = explode('-', $value);
    $array[] = $add['0']; // first number
    $array[] = $add['1']; // second number
    $array[] = $add['2']; // third number
    $array[] = $add['3']; // fourth number
    $array[] = $add['4']; // fifth number
    $array[] = $add['5']; // Sixth number
}

foreach ($past_numbers as $key => $value) {
    //echo "<br>'$key' => '$value',"; // Test to see the past numbers array output
}

echo "<hr>";

$votecounts = array_count_values($array);
//arsort($votecounts); // will flip the list
//print_r($votecounts);
foreach ($votecounts as $choice => $count) {
    echo "<br>Number $choice was picked $count times.";
    $count_it[$choice] = $count;
}

Or you can put the past numbers in a database

 

I left the "test" comments and added others in there to help you learn and for you to play with if you want...

So you can see what it does when you uncomment the lines.

 

The above was used for Sweet Millions... 6 number no bonus...

 

If you want the bonus then just use the same foreach loop though the array again but only have this in the foreach:

    $array[] = $add['6'];

And it will give the list of just the bonus numbers.

 

Or you can set the array to $array_bonus['6'] or whatever then change the $votecounts line $votecounts_bonus and loop through it again.

 

Have fun playing with it...

[nisroc]

 

I use this syntax :

SELECT col_name, COUNT(col_name) most FROM table_name GROUP BY col_name ORDER BY most DESC;

Where col_name is the column name, table_name is the table name. For example, a database named Login with a users table ( called users ), which has a country column, to find out the most used value for column country :

SELECT country, COUNT(country) most FROM users GROUP BY country ORDER BY most DESC;

The result of the above SQL is :

+---------+------+
| country | most |
+---------+------+
|       0 |    6 |
|       3 |    3 |
|     195 |    1 |
|     225 |    1 |
|      14 |    1 |
+---------+------+

this worked thanks

[JIXO]

You are welcome nisroc.