Multiple Count Statement

Failing_Solutions · June 17, 2011

Hi, I'm trying to wrap my head around how to count how many times pairs, triples, quads appear in my database. Then return the ones that appear the most.

What I'm doing is working on a way to find "hot numbers" from a Keno game.

Each game there are 20 numbers drawn.

The numbers can range from 1 to 80.

There are 306 games a day.

I want to count how many times: (1,2) (1,3) (1,4) (1,5)... (1,80) then loop to (2,3) (2,4) (2,5)...(2,80) all the way up to (79,80). Count how many time each pair appeared that day then return the pair that appeared the most. So I could say on 6/16/2011 the hottest pair was (X,Y)

I have 2 issues.

1 I'm not sure how to count up to max making sure I get all the possible pairs without hitting duplicates.

2, I'm not sure how to return the results.

As for counting I tried this,

<?php
$i=1;
$j=2;
While ($i<=80 ) {
While ($j<=80) {
echo "$i,$j<br>";
$j++;
}
echo "$i,$j<br>";
$i++;
}
?>

But only got 1,80 then 2,81: 3,81.. ect.

As for problem 2, I tried to just find just 1 number that appeared the most times in 1 day but not having much luck.

$x=1;
While ($x<=80) {
$hot1=mysql_query("Select Count(*) as Hot_Single FROM june122011 Where $x IN (`A`,`B`,`C`,`D`,`E`,`F`,`G`,`H`,`I`,`J`,`K`,`L`,`M`,`N`,`O`,`P`,`Q`,`R`,`S`,`T`) and Game_Date='2011/6/15'");
$hot1results = mysql_fetch_array($hot1);
$x++;
}
echo $hot1results['Hot_Single'];

Any help is appreciated.

Psycho · June 17, 2011

If my math is correct there are 3,160 different pair combinations. With trips and quads the number will be exponential! I really don't see an easy way to do this without processing every record. You definitely don't want to do this with the database. So, you would have to query all the records and have a loop in PHP to process all the records to find the most common pair.

Failing_Solutions · June 17, 2011

That makes since, can you point me to an example of how you search an array for criteria? And extract the results into something you can echo / print.

Everything I know about PHP and SQL has been self taught and from help on these forums. Any example you can give or show me would be very helpful.

Thank you.

Psycho · June 17, 2011

The best approach I can think of would be to have a cron job run each day to process all the records from the previous day. The script should query all the draws for the previous day. Then process each draw to determine the double, triple and quadruple combinations (of course, you would have to determine how to handle if there are ties!). As I process each record, I would probably determine a combinations (e.g. 3 & and create an array entry for it using those values as the key - and set the value to 1. Then, as I process the rest of the records, if I run across 3 & 8 again, I would increment the value of that array key by 1. Once all the records are processed, I would just grab the key of the highest value in the array.

Here is some code. However, I don't know how long this would take to run to process the trips and quads - so I commented that section out. I didn't test this, but the logic seems right to me. If you do get it to run for the pairs, go ahead and uncommen the trips section - then the quads.

<?php

$query = "SELECT `A`,`B`,`C`,`D`,`E`,`F`,`G`,`H`,`I`,`J`,`K`,`L`,`M`,`N`,`O`,`P`,`Q`,`R`,`S`,`T` FROM draws WHERE draw_date = '$yesterday'";
$result = mysql_query($query);

//Arrays to hold values
$pairs = array();
$trips = array();
$quads = array();

$while($draw = mysql_fetch_assoc($result))
{
    foreach($draw as $num1)
    {
        foreach($draw as $num2)
        {
            //identify all pair combninations
            if($num1==$num2) { continue; }
            $pairKey = "$num1-$num2";
            if(!isset($pairs[$pairKey]))
            {
                //Create the pair record if not exist
                $pairs[$pairKey] = 0;
            }
            //Increment pair record by 1
            $pairs[$pairKey] = 0;
/*
            //Identify all trip combinations
            foreach($draw as $num3)
            {
                //identify all pair combninations
                if($num1==$num2 || $num1==$num3 || $num2==$num3) { continue; }
                $tripKey = "$num1-$num2=$num3";
                if(!isset($trips[$tripKey]))
                {
                    //Create the pair record if not exist
                    $trips[$tripKey] = 0;
                }
                //Increment pair record by 1
                $trips[$tripKey] = 0;

                //Identify all quad combinations
                foreach($draw as $num4)
                {
                    //identify all pair combninations
                    if($num1==$num2 || $num1==$num3 || $num1==$num4 ||
                       $num2==$num3 || $num2==$num4 || $num3==$num4) { continue; }
                    $quadKey = "$num1-$num2=$num3";
                    if(!isset($quads[$quadKey]))
                    {
                        //Create the pair record if not exist
                        $quads[$quadKey] = 0;
                    }
                    //Increment pair record by 1
                    $quads[$quadKey] = 0;
                } #END Quad Check
            }#END Trip check
*/
        }#END pair check
    }
}


//Sort arrays highest to lowest
arsort($pairs);
arsort($trips);
arsort($quads);

//Get most common 
$mostCommonPair = key($pairs); //E.g. 15-37
$mostCommonTrip = key($trips); //E.g. 8-14-35
$mostCommonQuad = key($quads); //E.g. 16-32-40-52

?>

Failing_Solutions · June 18, 2011

Wow that is certainly great work there. I really appreciate the effort and your time.

I'm still digesting it to understand just how it works.

Right now, I have it running just the pairs on my keno page its not taking to long to load the page which is good.

I'll work on incorporating a cron job once I start working on the final page design.

One thing I did notice, and maybe its something I'm overlooking right now is that while the code is giving me a pair, its not necessarily the most drawn pair. Any ideas why that would be? Maybe I'm not echoing it correctly??

Here is the code I'm running (basically your code) just removed the $ from the while and added an echo and a valid select statement.

<?php

$query = "SELECT `A`,`B`,`C`,`D`,`E`,`F`,`G`,`H`,`I`,`J`,`K`,`L`,`M`,`N`,`O`,`P`,`Q`,`R`,`S`,`T` FROM june122011 WHERE Game_Date = '2011/6/16'";
$result = mysql_query($query);

//Arrays to hold values
$pairs = array();
$trips = array();
$quads = array();

while($draw = mysql_fetch_assoc($result))
{
    foreach($draw as $num1)
    {
        foreach($draw as $num2)
        {
            //identify all pair combninations
            if($num1==$num2) { continue; }
            $pairKey = "$num1-$num2";
            if(!isset($pairs[$pairKey]))
            {
                //Create the pair record if not exist
                $pairs[$pairKey] = 0;
            }
            //Increment pair record by 1
            $pairs[$pairKey] = 0;
/*
            //Identify all trip combinations
            foreach($draw as $num3)
            {
                //identify all pair combninations
                if($num1==$num2 || $num1==$num3 || $num2==$num3) { continue; }
                $tripKey = "$num1-$num2=$num3";
                if(!isset($trips[$tripKey]))
                {
                    //Create the pair record if not exist
                    $trips[$tripKey] = 0;
                }
                //Increment pair record by 1
                $trips[$tripKey] = 0;

                //Identify all quad combinations
                foreach($draw as $num4)
                {
                    //identify all pair combninations
                    if($num1==$num2 || $num1==$num3 || $num1==$num4 ||
                       $num2==$num3 || $num2==$num4 || $num3==$num4) { continue; }
                    $quadKey = "$num1-$num2=$num3";
                    if(!isset($quads[$quadKey]))
                    {
                        //Create the pair record if not exist
                        $quads[$quadKey] = 0;
                    }
                    //Increment pair record by 1
                    $quads[$quadKey] = 0;
                } #END Quad Check
            }#END Trip check
*/
        }#END pair check
    }
}


//Sort arrays highest to lowest
arsort($pairs);
arsort($trips);
arsort($quads);

//Get most common 
$mostCommonPair = key($pairs); //E.g. 15-37
$mostCommonTrip = key($trips); //E.g. 8-14-35
$mostCommonQuad = key($quads); //E.g. 16-32-40-52
echo "<font color='#660000' ><br><b>The Most Common Pairs is ".$mostCommonPair."</b></font>";
?>

For June 16th it pulls 24-16 as the pairs. Which if I punch in manually comes up as 21 hits. But the pair 35, 77 was drawn 31 times. Here is some screen shots to show what I mean on the results.

I can put this on my test server if you want to play with it, right now i'm just running it on Xammp.

Here is the initial Load Page (note the Hot Pair at the bottom)

Here is the Count using the manual query for those 2 numbers.

And finally here is a the count on 35,77 manually entered.

Again, I'm not sure if there is something I need to do like a Max, or Count on the $mostCommonPair. I've never used those outside of SQL and not sure how they exactly work in PHP. I certainly didn't expect you to spend this much time on a question like this, it really is appreciated. If nothing else I'm learning something. So thank you very much.

Psycho · June 18, 2011

I think I may know what is happening. But, before I explain that, I would say the first order of business is to do a print_r on the array $pairs and inspect it. Look at some of the results to see if they at least look valid. Then do some queries to verify they are correct. For example, if it shows a key of '16-32' with a value of 20, then do a query to validate:

$query = "SELECT *
          FROM june122011
          WHERE 16 IN (`A`,`B`,`C`,`D`,`E`,`F`,`G`,`H`,`I`,`J`,`K`,`L`,`M`,`N`,`O`,`P`,`Q`,`R`,`S`,`T`)
            AND 32 IN (`A`,`B`,`C`,`D`,`E`,`F`,`G`,`H`,`I`,`J`,`K`,`L`,`M`,`N`,`O`,`P`,`Q`,`R`,`S`,`T`)";
$result = mysql_query($query);
echo mysql_num_rows($result);

OK, so this is what I think the problem is. I made an assumption that you would be storing the numbers in a logical order of smallest to largest. That way, when you do the loops in the code you would always get something like "16-24" and never "24-16". Since the numbers are apparently in a random order between the draws you may have 35 & 77 as the REAL most common pair, but it is being processed as 20 occurrences of "35 & 77" and 11 occurrences of "77 & 35".

To resolve this you need to put the number in order when creating the keys. Change this

$pairKey = "$num1-$num2";

To this

$pairKeyAry = array($num1, $num2);
sort($pairKeyAry);
$pairKey = implode('-', $pairKeyAry);

Of course, that is just my guess based on the facts I have at hand. you need to do the test and validation.

Failing_Solutions · June 18, 2011

Yes the numbers aren't in specific columns, they are however arranged from lowest to highest for each game. I've attached my SQL insert statement to this post.

As for the the array, I think its not counting correctly.

I ran a print_r and got alot of pairs, but the value is always 0.

Here is a small example of what is outputted with print_r($pairs) *Note all have values of =>0 did I forget an increment step?

( [8-65] => 0 [8-29] => 0 [16-18] => 0 [16-24] => 0 [16-34] => 0 [8-24] => 0 [6-75] => 0 [6-34] => 0 [6-38] => 0 [6-49] => 0 [6-63] => 0 [16-45] => 0 [16-71] => 0 [18-75] => 0 [24-26] => 0 [24-29] => 0 [24-49] => 0 [18-65] => 0 [18-63] => 0 [16-73] => 0 [18-26] => 0 [18-38] => 0 [18-49] => 0 [6-32] => 0 [6-29] => 0 [58-59] => 0 [44-67] => 0 [59-74] => 0 [60-67] => 0 [60-74] => 0 [43-60] => 0 [43-44] => 0 [40-74] => 0 [41-43] => 0 [41-58] => 0 [41-74] => 0 [65-74] => 0 [1-6] => 0 [1-75] => 0 [6-16] => 0 [6-24] => 0 [6-26] => 0 [1-71] => 0 [1-63] => 0 [1-24] => 0 [1-32] => 0 [1-38] => 0 [1-45] => 0 [24-67] => 0 [24-73] => 0 [49-73] => 0 [49-71] => 0 [49-80] => 0 [63-71] => 0 [63-73] => 0 [49-63] => 0 [45-75] => 0 [38-67] => 0 [38-71] => 0 [38-73] => 0 [45-49] => 0 [65-80] => 0 [67-73] => 0 [2-21] => 0 [2-25] => 0 [2-27] => 0 [2-36] => 0 [2-9] => 0 [2-3] => 0 [67-75] => 0 [71-73] => 0 [71-80] => 0 [73-75] => 0 [38-65] => 0 [38-49] => 0 [29-32] => 0 [29-34] => 0 [29-38] => 0 [29-45] => 0 [26-80] => 0 [26-73] => 0 [26-32] => 0 [26-34] => 0 [26-38] => 0 [26-71] => 0 [29-63] => 0 [29-71] => 0 [34-45] => 0 [34-49] => 0 [34-67] => 0 [38-45] => 0 [32-73] => 0 [32-49] => 0 [29-73] => 0 [29-75] => 0 [29-80] => 0 [32-45] => 0 [40-65] => 0 [40-60] => 0 [37-69] => 0 [37-61] => 0 [37-79] => 0 [43-47] => 0 [43-61] => 0 [37-47] => 0 [37-43] => 0 [29-61] => 0 [29-69] => 0 [29-72] => 0 [29-74] => 0 [43-69] => 0 [43-74] => 0 [58-69] => 0 [58-79] => 0 [61-79] => 0 [69-74] => 0 [56-61] => 0 [47-79] => 0 [47-56] => 0 [47-58] => 0 [47-69] => 0 [47-74] => 0 [29-58] => 0 [29-56] => 0 [25-43] => 0 [25-37] => 0 [25-47] => 0 [25-56] => 0 [25-69] => 0 [25-29] => 0 [25-27] => 0 [23-61] => 0 [23-69] => 0 [23-72] => 0 [23-79] => 0 [25-79] => 0 [27-29] => 0 [28-72] => 0 [29-37] => 0 [29-43] => 0 [29-47] => 0 [28-69] => 0 [28-47] => 0 [27-37] => 0 [27-69] => 0 [28-37] => 0 [28-43] => 0 [69-79] => 0 [72-74] => 0 [35-43] => 0 [35-37] => 0 [35-44] => 0 [35-58] => 0 [35-60] => 0 [35-36] => 0 [33-67] => 0 [33-35] => 0 [33-40] => 0 [33-43] => 0 [33-65] => 0 [35-74] => 0 [36-37] => 0 [37-65] => 0 [40-43] => 0 [40-44] => 0 [40-58] => 0 [37-60] => 0 [37-40] => 0 [36-40] => 0 [36-41] => 0 [36-58] => 0 [36-74] => 0 [31-74] => 0 [31-44] => 0 [7-41] => 0 [7-65] => 0 [7-67] => 0 [20-60] => 0 [7-40] => 0 [7-37] => 0 [4-29] => 0 [7-29] => 0 [7-35] => 0 [7-36] => 0 [27-35] => 0 [27-40] => 0 [29-44] => 0 [29-59] => 0 [29-60] => 0 [31-37] => 0 [29-41] => 0 [29-40] => 0 [27-41] => 0 [29-31] => 0 [29-33] => 0 [29-36] => 0 [2-60] => 0 [2-69] => 0 [11-80] => 0 [11-60] => 0 [14-15] => 0 [14-33] => 0 [14-35] => 0 [11-57] => 0 [11-54] => 0 [10-57] => 0 [11-33] => 0 [11-44] => 0 [11-51] => 0 [14-51] => 0 [14-54] => 0 [24-54] => 0 [24-74] => 0 [24-79] => 0 [26-51] => 0 [18-57] => 0 [15-60] => 0 [14-56] => 0 [14-74] => 0 [14-79] => 0 [15-18] => 0 [10-56] => 0 [10-54] => 0 [53-67] => 0 [50-70] => 0 [53-70] => 0 [70-77] => 0 [71-77] => 0 [50-53] => 0 [41-77] => 0 [35-70] => 0 [41-53] => 0 [41-70] => 0 [41-71] => 0 [1-3] => 0 [1-14] => 0 [3-33] => 0 [3-44] => 0 [3-57] => 0 [10-51] => 0 [3-26] => 0 [1-60] => 0 [1-51] => 0 [1-54] => 0 [1-56] => 0 [1-57] => 0 [26-54] => 0 [26-74] => 0 [7-64] => 0 [7-54] => 0 [7-69] => 0 [13-24] => 0 [13-33] => 0 [7-34] => 0 [7-13] => 0 [5-69] => 0 [6-37] => 0 [6-54] => 0 [6-58] => 0 [13-34] => 0 [13-37] => 0 [16-33] => 0 [16-36] => 0 [16-37] => 0 [16-54] => 0 [16-31] => 0 [16-22] => 0 [13-54] => 0 [13-58] => 0 [13-64] => 0 [16-17] => 0 [5-34] => 0 [5-13] => 0 [54-57] => 0 [54-60] => 0 [54-79] => 0 [57-74] => 0 [51-79] => 0 [51-60] => 0 [33-54] => 0 [35-57] => 0 [44-54] => 0 [44-79] => 0 [2-5] => 0 [2-6] => 0 [2-54] => 0 [2-57] => 0 [2-58] => 0 [2-64] => 0 [2-37] => 0 [2-34] => 0 [2-7] => 0 [2-17] => 0 [2-24] => 0 [2-33] => 0 [33-77] => 0 [33-71] => 0 [30-42] => 0 [25-71] => 0 [30-79] => 0 [31-42] => 0 [31-69] => 0 [25-60] => 0 [25-50] => 0 [25-35] => 0 [25-36] => 0 [25-42] => 0 [25-46] => 0 [31-79] => 0 [35-71] => 0 [50-60] => 0 [50-69] => 0 [50-71] => 0 [60-71] => 0 [42-76] => 0 [42-71] => 0 [36-50] => 0 [36-69] => 0 [36-76] => 0 [42-69] => 0

Here is the code with your changes suggested.

$query = "SELECT `A`,`B`,`C`,`D`,`E`,`F`,`G`,`H`,`I`,`J`,`K`,`L`,`M`,`N`,`O`,`P`,`Q`,`R`,`S`,`T` FROM june122011 WHERE Game_Date = '2011/6/16'";
$result = mysql_query($query);

//Arrays to hold values
$pairs = array();
$trips = array();
$quads = array();

while($draw = mysql_fetch_assoc($result))
{
    foreach($draw as $num1)
    {
        foreach($draw as $num2)
        {
            //identify all pair combination's
            if($num1==$num2) { continue; }
            $pairKeyAry = array($num1, $num2);
		sort($pairKeyAry);
		$pairKey = implode('-', $pairKeyAry);
            if(!isset($pairs[$pairKey]))
            {
                //Create the pair record if not exist
                $pairs[$pairKey] = 0;
            }
            //Increment pair record by 1
            $pairs[$pairKey] =0;
/*
            //Identify all trip combinations
            foreach($draw as $num3)
            {
                //identify all pair combninations
                if($num1==$num2 || $num1==$num3 || $num2==$num3) { continue; }
                $tripKey = "$num1-$num2-$num3";
                if(!isset($trips[$tripKey]))
                {
                    //Create the pair record if not exist
                    $trips[$tripKey] = 0;
                }
                //Increment pair record by 1
                $trips[$tripKey] = 0;

                //Identify all quad combinations
                foreach($draw as $num4)
                {
                    //identify all pair combninations
                    if($num1==$num2 || $num1==$num3 || $num1==$num4 ||
                       $num2==$num3 || $num2==$num4 || $num3==$num4) { continue; }
                    $quadKey = "$num1-$num2-$num3-$num4";
                    if(!isset($quads[$quadKey]))
                    {
                        //Create the pair record if not exist
                        $quads[$quadKey] = 0;
                    }
                    //Increment pair record by 1
                    $quads[$quadKey] = 0;
                } #END Quad Check
            }#END Trip check
*/
        }#END pair check
    }
}


//Sort arrays highest to lowest
arsort($pairs);
print_r($pairs);
arsort($trips);
arsort($quads);

//Get most common 
$mostCommonPair = key($pairs); //E.g. 15-37
$mostCommonTrip = key($trips); //E.g. 8-14-35
$mostCommonQuad = key($quads); //E.g. 16-32-40-52
echo "<font color='#660000' ><br><b>The Most Common Pairs is ".$mostCommonPair."</b></font>";
?>

I have the manual part working as if I test 2 numbers on my page I'll get accurate counts for that pair. Tested this in SQL. This function you're helping me with here will be in a info box stating hot pair for $date was x,y , hot trips were x,y,z etc..

[attachment deleted by admin]

Failing_Solutions · June 18, 2011

Also what is weird and I really don't understand. Is now it is saying the top pair is 8-65. Which I tested (screen shot below) and it come up 22 times. Which as seen above 35, 77 is 30 times.

Failing_Solutions · June 18, 2011

Wondering if this part of the code should have like a counter on it..?

 //Increment pair record by 1
            $pairs[$pairKey] =0;

Psycho · June 18, 2011

Yeah, my copy/paste error. Use

$pairs[$pairKey]++;

Failing_Solutions · June 18, 2011

That looks like it was the solution.

It seems to be working well now. With this one caveat.

In my print_r I was getting values like this..

[10-35] => 72 [35-60] => 70 [42-58] => 68 [35-76] => 68 [35-78] => 66 [32-58] => 64 [1-74] => 64 [29-35] => 64 [35-77] => 64 [33-56] => 64 [20-35] => 62 [12-23] => 62 [35-58] => 62 [56-77] => 60 [10-70] => 60

After checking 10-35, I found it was drawn 35 times which seems like a probable high. Each pair their-after counts down so it seems like its getting the highest to lowest. I switched the code back to the original you wrote.

This:

 //identify all pair combninations
            if($num1==$num2) { continue; }
            $pairKey = "$num1-$num2";
            if(!isset($pairs[$pairKey]))

Now I am seeing this

[35-10] => 36 [10-35] => 36 [60-35] => 35 [35-60] => 35 [42-58] => 34 [58-42] => 34 [35-76] => 34 [76-35] => 34 [78-35] => 33 [35-78] => 33 [33-56] => 32 [58-32] => 32 [56-33] => 32 [35-77] => 32 [1-74] => 32 [74-1] => 32

As you can see this creates the same pair twice except formatted different 35-10=>36 10-36=>36 add them together and you get 72 which is exactly what the other code was doing ie ( [10-35] => 72 )

Now this pair was drawn 35 times, but its counted as 36 times I had the same issue before in my count statement so I just added a -1 to the count to get the possible games one. I'm not sure why we both are getting an extra count in our code I just assumed it has something do with the way arrays are built. In the end though it looks like it works in that it will get the highest pair.

I'm very happy with this.

Thank you very much for your help, I couldn't have done it anytime soon without your help.

Failing_Solutions · June 18, 2011

Just wanted to add, the quads is certainly going to cause page load problems. I think what I'll do is run these queries and save the date, pairs, trips and quads in another table entry so I can call them directly. I already have to get the numbers from the lottery daily so running these 3 queries and saving them isn't that big of a deal.

Eventually what I hope to get is a API link to the numbers as they are being drawn and turn this into a page that track hot numbers on a per draw basis so people can log on to the page and find trending pairs, trips, quads ongoing. That will be a challenge but is down the road. Maybe one day it will become an app

Really do appreciate the help. I'm going to mark this as solved.

Thanks again!

Sign In

Multiple Count Statement

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information