Looking For Advice, Select 2 or 3 Numbers for 20 columns

[Failing_Solutions]

Hello folks, my first post here. Hopefully I'm in good company.

 

I'm need advice on how to achieve this. What I'm doing is trying to find how many times a set of numbers appears in any columns in a specific database table. This is for a Keno game, I'm trying to see how many times X,Y numbers come up in a day, or X,Y,Z numbers. 20 numbers are drawn each game.

 

I'm okay at php and sql but without doing a kathousand queries I'm not sure how I can achieve what I want to do.

 

Database is setup up like this GameId,A,B,C,D,E,F....T,Multiplier

A gameid then a set of 20 numbers lowest to highest in columns A through T, followed by a multiplier

 

 

SQL doesn't seem to have a way to select 2 different numbers from any columns. So I'm not sure exactly what to do. Is there a way I can get the results in php then search the array results? I'm not sure.

 

Here is all I have so far. Note.. I started with just getting all the data to make sure I had a connection. I then spent the next 3 hours trying to figure out how to write an sql statement to do this, but it doesn't look like its possible, so I'm hoping the answer is in PHP.

 

<?php
include 'connectdb.php';
?>
<?php
if(isset($_POST['num1'])){
$num1=$_POST['num1'];
$num2=$_POST['num2'];
echo"$num1";
echo"$num2";
}
$quer=mysql_query("Select * From june122011");
$noticia=mysql_fetch_array($quer);
if (isset($_POST['num1'])) {
$quer=mysql_query("SELECT * FROM june122011 WHERE A = '$num1' AND T = '$num2'");
    $result=mysql_fetch_array($quer);
echo"<table><border='1px'><tr><td>Drawing</td><td>Yes</td><td>Yes</td><td>Multiplier</td></tr>";
While ($result = mysql_fetch_array($quer)) { 
echo "<tr align='center'><td>$result[GameID]</td>";
echo "<td>$result[A]</td>";
echo "<td>$result[T]</td>";
echo "<td><font color='#FF0000'>X$result[Multiplier]</font></td></tr>";
}
} else {
echo"<table border='1px'><tr><td>Drawing</td><td>Pick 1</td><td>Pick 2</td><td>Pick 3</td><td>Pick 4</td><td>Pick 5</td><td>Pick 6</td><td>Pick 7</td><td>Pick 8</td><td>Pick 9</td><td>Pick 10</td><td>Pick 11</td><td>Pick 12</td><td>Pick 13</td><td>Pick 14</td><td>Pick 15</td><td>Pick 16</td><td>Pick 17</td><td>Pick 18</td><td>Pick 19</td><td>Pick 20</td><td>Multiplier</td></tr>";
While ($noticia = mysql_fetch_array($quer)) { 
echo "<tr align='center'><td>$noticia[GameID]</td>";
echo "<td>$noticia[A]</td>";
echo "<td>$noticia[b]</td>";
echo "<td>$noticia[C]</td>";
echo "<td>$noticia[D]</td>";
echo "<td>$noticia[E]</td>";
echo "<td>$noticia[F]</td>";
echo "<td>$noticia[G]</td>";
echo "<td>$noticia[H]</td>";
echo "<td>$noticia[i]</td>";
echo "<td>$noticia[J]</td>";
echo "<td>$noticia[K]</td>";
echo "<td>$noticia[L]</td>";
echo "<td>$noticia[M]</td>";
echo "<td>$noticia[N]</td>";
echo "<td>$noticia[O]</td>";
echo "<td>$noticia[P]</td>";
echo "<td>$noticia[Q]</td>";
echo "<td>$noticia[R]</td>";
echo "<td>$noticia[s]</td>";
echo "<td>$noticia[T]</td>";
echo "<td><font color='#FF0000'>x$noticia[Multiplier]</font></td></tr>";
}
echo "</table>";
}
?>
    <form action="keno.php" method="post">
<table width="44%" border="0">
  <tr>
    <td><input type="text" name="num1" id="num1" /></td>
    <td><input type="text" name="num2" id="num2" /></td>
    <td><input type="text" name="num3" id="num3" /></td>
  </tr>
  <tr>
    <td><input type="submit" name="Submit" id="Submit" value="Submit" /></td>
    <td> </td>
    <td> </td>
  </tr>
</table>
</form>

[silkfire]

Let PHP generate the list with letters A-T separated with quotes, commas and space.

 

Then make the query:

 

SELECT row WHERE "$number" IN (A.....T)

 

A...T is the string with "A, B, C.....T"

 

Good luck!

[Failing_Solutions]

Yes that makes since somewhat.

 

Since the list is already generated in the code above you're suggesting I use that array to find the values from the input boxes by searching the array rows?. I've never done anything like that before do you know of a place I can see a sample of how something like that would work.

 

Really do appreciate the reply.

 

[silkfire]

Well I assumed you only wanted to search for 1 (one) number at a time in the A-T columns? Or am I wrong?

[Failing_Solutions]

I need to check for all occurrences of 2 and 3 different numbers in each row.

 

So I can say, On June 12th, 2011 your numbers were drawn X times.  The numbers being whatever is posted in the text boxes.

 

I've actually got 2 numbers working because I could do that with SQL. Since I know that 1 will ALWAYS be in column A and 80 will always be in column T since those are the lowest and highest numbers. I've attached an image of what it looks like on my local server.

 

The trouble is that a number like 3 may not always be in column C because if it is the lowest number drawn in that game then it will be in column A. So I have to check each row for 2 or 3 numbers then total up the amount of times they appeared in the same game.

 

This is how it looks (default) all games:

 

This is how it looks with 2 numbers

 

 

I hope that explains what I'm trying to accomplish.

[Psycho]

Building upon silkfire's suggestion, the following query should give you the number of times that a "set" of numbers has appeared in any drawing. To be more specific it is the number of drawings that contained all three of the target numbers. The pattern of the query should be obvious so you can modify it for any quantity of target numbers.

 

SELECT count(`GameId`)
FROM june122011
WHERE $num1 IN (`A`,`B`,`C`,`D`,`E`,`F`,`G`,`H`,`I`,`J`,`K`,`L`,`M`,`N`,`O`,`P`,`Q`,`R`,`S`,`T`)
  AND $num2 IN (`A`,`B`,`C`,`D`,`E`,`F`,`G`,`H`,`I`,`J`,`K`,`L`,`M`,`N`,`O`,`P`,`Q`,`R`,`S`,`T`)
  AND $num3 IN (`A`,`B`,`C`,`D`,`E`,`F`,`G`,`H`,`I`,`J`,`K`,`L`,`M`,`N`,`O`,`P`,`Q`,`R`,`S`,`T`)

 

Although I used the table name you described above, "june122011", you should not use different tables to separate records by date. have one table and add a column for the date.

[Failing_Solutions]

Thank you

 

I see what you're doing there.

 

I didn't know you could use IN on columns rather only on fields in the columns. So that is a big help.

 

I also appreciate the advise on the table info. I actually have another field that is holding the date which eventually will be another variable.

 

Thank you both, I'll  do more testing after work tomorrow.

[Failing_Solutions]

This worked very well.

 

Thank you for the help!