CANNOT Get these variables to add up

Falky2015 · March 6, 2015

<?
//random numbers
$arr = array();
while ( count($arr) < 6 ) {
    $x = mt_rand(1,24);
    if ( !in_array($x,$arr) ) {
        $arr[] = $x;
        asort($arr);
    }
}
foreach($arr as $x){
$numbers[]=$x." ";
        }
for($i2=0;$i2<6;$i2++){
//start cycle
if($i2=="0"){
$no01=$numbers[$i2];
}
if($i2=="1"){
$no02=$numbers[$i2];
}
if($i2=="2"){
$no03=$numbers[$i2];
}
if($i2=="3"){
$no04=$numbers[$i2];
}
if($i2=="4"){
$no05=$numbers[$i2];
}
if($i2=="5"){
$no06=$numbers[$i2];
}
//end cycle
}
//connect to database

$sql91="SELECT * FROM l$table WHERE draw='$draw'";
$result91=mysql_query($sql91);
while($rows91=mysql_fetch_array($result91)){
$user=$rows91['user'];
$show1=$rows91['no01'];
$show2=$rows91['no02'];
$show3=$rows91['no03'];
$show4=$rows91['no04'];
$show5=$rows91['no05'];
$show6=$rows91['no06'];
//CHECK 01
if($rows91['no01']==$no01) {   
$MEMBER01=1;
}   
elseif($rows91['no02']==$no01) {   
$MEMBER01=1;
}  
elseif($rows91['no03']==$no01) {   
$MEMBER01=1;
} 
elseif($rows91['no04']==$no01) {   
$MEMBER01=1;
} 
elseif($rows91['no05']==$no01) {   
$MEMBER01=1;
} 
elseif($rows91['no06']==$no01) {   
$MEMBER01=1;
} 
else {
$MEMBER01=0;
}
//CHECK 02
if($rows91['no01'] == $no02) {   
$MEMBER02=1;
}   
elseif($rows91['no02'] == $no02) {   
$MEMBER02=1;
}  
elseif($rows91['no03'] == $no02) {   
$MEMBER02=1;
} 
elseif($rows91['no04'] == $no02) {   
$MEMBER02=1;
} 
elseif($rows91['no05'] == $no02) {   
$MEMBER02=1;
} 
elseif($rows91['no06'] == $no02) {   
$MEMBER02=1;
} 
else {
$MEMBER02=0;
}
//CHECK 03
if($rows91['no01'] == $no03) {   
$MEMBER03=1;
}   
elseif($rows91['no02'] == $no03) {   
$MEMBER03=1;
}  
elseif($rows91['no03'] == $no03) {   
$MEMBER03=1;
} 
elseif($rows91['no04'] == $no03) {   
$MEMBER03=1;
} 
elseif($rows91['no05'] == $no03) {   
$MEMBER03=1;
} 
elseif($rows91['no06'] == $no03) {   
$MEMBER03=1;
} 
else {
$MEMBER03=0;
}
//CHECK 04
if($rows91['no01'] == $no04) {   
$MEMBER04=1;
}   
elseif($rows91['no02'] == $no04) {   
$MEMBER04=1;
}  
elseif($rows91['no03'] == $no04) {   
$MEMBER04=1;
} 
elseif($rows91['no04'] == $no04) {   
$MEMBER04=1;
} 
elseif($rows91['no05'] == $no04) {   
$MEMBER04=1;
} 
elseif($rows91['no06'] == $no04) {   
$MEMBER04=1;
} 
else{
$MEMBER04=0;
}
//CHECK 05
if ($rows91['no01'] == $no05) {   
$MEMBER05=1;
}   
elseif($rows91['no02'] == $no05) {   
$MEMBER05=1;
}  
elseif($rows91['no03'] == $no05) {   
$MEMBER05=1;
} 
elseif($rows91['no04'] == $no05) {   
$MEMBER05=1;
} 
elseif($rows91['no05'] == $no05) {   
$MEMBER05=1;
} 
elseif($rows91['no06'] == $no06) {   
$MEMBER05=1;
} 
else{
$MEMBER05=0;
}
//CHECK 06
if ($rows91['no01'] == $no06) {   
$MEMBER06=1;
}   

elseif($rows91['no02'] == $no06) {   
$MEMBER06=1;
}  
elseif($rows91['no03'] == $no06) {   
$MEMBER06=1;
} 
elseif($rows91['no04'] == $no06) {   
$MEMBER06=1;
} 
elseif($rows91['no05'] == $no06) {   
$MEMBER06=1;
} 
elseif($rows91['no06'] == $no06) {   
$MEMBER06=1;
} 
else{
$MEMBER06=0;
}

$TOTALMEMBER=$MEMBER01+$MEMBER02+$MEMBER03+$MEMBER04+$MEMBER05+$MEMBER06;
?>

Basically what I am trying to do is check all 6 numbers that are randomly generated to the already stored user numbers in a mysql database. Its DRIVING me insane as I cannot work out why it wont work or the if statements wont equal each other. The database field is set to INT for the numbers.

Cheers

mac_gyver · March 6, 2015

you can get a count of how many correct matches there are directly in the sql query, without writing out all that code. also, once you have the $arr with the 6 numbers, you don't need the foreach() and for() loops. however, all of this will require that you set up your database table correctly, with the data normalized.

you need to store each number that the user has selected as a separate row in your table. your table would have columns for - draw, user, and num. yes, there will be 6 rows for each user for each draw.

the query to get the count of correct matches will look like this (untested) -

$query = "SELECT user, COUNT(*) as num_correct FROM your_table WHERE draw='$draw' AND num IN (".implode(',',$arr).") GROUP BY user";

if you only want the users that have matched all 6 numbers, the query would look like this -

$query = "SELECT user, COUNT(*) as num_correct FROM your_table WHERE draw='$draw' AND num IN (".implode(',',$arr).") GROUP BY user HAVING num_correct = 6";

Falky2015 · March 7, 2015

Thanks for the reply, yeah I just have the database setup like this as it was easier to display the results per person, I will give yours a go, but once I have how many winning numbers they have, I need to highlight their winning numbers on a results page and also for 3 or more numbers correct echo a prize amount eg, 3 will get 10 points, 4 will get 50, 5 will get 500, 6 will get 5000 points:

Tables - id - user - no01 - no02 - no03 - no04 - no05 - no06

1 jace 5 23 12 18 20 23

<?
$TOTALMEMBER=$MEMBER01+$MEMBER02+$MEMBER03+$MEMBER04+$MEMBER05+$MEMBER06;

//PROCESS IF THE INDIVIDUAL WON PRIZES
if($TOTALMEMBER==1){
}
if($TOTALMEMBER==2){
}
if($TOTALMEMBER==3){
$sql5="UPDATE winners SET points=points+'10' WHERE user='$user'";
$result5=mysql_query($sql5) or die ("SQL Error: $sql5<br>" . mysql_error());
}
if($TOTALMEMBER==4){
$sql5="UPDATE winners SET points=points+'50' WHERE user='$user'";
$result5=mysql_query($sql5) or die ("SQL Error: $sql5<br>" . mysql_error());
}
if($TOTALMEMBER==5){
$sql5="UPDATE winners SET points=points+'500' WHERE user='$user'";
$result5=mysql_query($sql5) or die ("SQL Error: $sql5<br>" . mysql_error());
}
if($TOTALMEMBER==6){
$sql5="UPDATE winners SET points=points+'5000' WHERE user='$user'";
$result5=mysql_query($sql5) or die ("SQL Error: $sql5<br>" . mysql_error());
}
?>

Falky2015 · March 7, 2015

I tried using your system and come up with this which works exactly how I want it to, its bloody long though LOL and took ages to code but with your code it made it a little easier. Iv tested this out and works a charm every single time so with your help and my way of making things 10x harder than they should be Iv fixed my issues and thanks for all your help ;-)

<?php
//random numbers
$arr = array();
while ( count($arr) < 6 ) {
    $x = mt_rand(1,24);
    if ( !in_array($x,$arr) ) {
        $arr[] = $x;
        asort($arr);
    }
}
foreach($arr as $x){
$numbers[]=$x." ";
        }
for($i2=0;$i2<6;$i2++){
//start cycle
if($i2=="0"){
$no01=$numbers[$i2];
}
if($i2=="1"){
$no02=$numbers[$i2];
}
if($i2=="2"){
$no03=$numbers[$i2];
}
if($i2=="3"){
$no04=$numbers[$i2];
}
if($i2=="4"){
$no05=$numbers[$i2];
}
if($i2=="5"){
$no06=$numbers[$i2];
}
//end cycle
}
$sql91="SELECT * FROM $table WHERE draw='$draw'";
$result91=mysql_query($sql91);
while($rows91=mysql_fetch_array($result91)){
$THEuser=$rows91['user'];
//END THE RANDOM NUMBERS NOW---------
$query1= "SELECT user, COUNT(*) as num_correct FROM lotto WHERE draw='$draw' AND user='$THEuser' AND no01 IN (".implode(',',$arr).")";
$result1 = mysql_query($query1) or die($query1."<br/><br/>".mysql_error());
while($rows1=mysql_fetch_array($result1)){
if($rows1['num_correct']==1){
$MEMBER01=1;
}else{
$MEMBER01=0;
}
}
$query2= "SELECT user, COUNT(*) as num_correct FROM lotto WHERE draw='$draw' AND user='$THEuser' AND no02 IN (".implode(',',$arr).")";
$result2 = mysql_query($query2) or die($query2."<br/><br/>".mysql_error());
while($rows2=mysql_fetch_array($result2)){
if($rows2['num_correct']==1){
$MEMBER02=1;
}else{
$MEMBER02=0;
}
}
$query3= "SELECT user, COUNT(*) as num_correct FROM lotto WHERE draw='$draw' AND user='$THEuser' AND no03 IN (".implode(',',$arr).")";
$result3 = mysql_query($query3) or die($query3."<br/><br/>".mysql_error());
while($rows3=mysql_fetch_array($result3)){
if($rows3['num_correct']==1){
$MEMBER03=1;
}else{
$MEMBER03=0;
}
}
$query4= "SELECT user, COUNT(*) as num_correct FROM lotto WHERE draw='$draw' AND user='$THEuser' AND no04 IN (".implode(',',$arr).")";
$result4 = mysql_query($query4) or die($query4."<br/><br/>".mysql_error());
while($rows4=mysql_fetch_array($result4)){
if($rows4['num_correct']==1){
$MEMBER04=1;
}else{
$MEMBER04=0;
}
}
$query5= "SELECT user, COUNT(*) as num_correct FROM lotto WHERE draw='$draw' AND user='$THEuser' AND no05 IN (".implode(',',$arr).")";
$result5 = mysql_query($query5) or die($query5."<br/><br/>".mysql_error());
while($rows5=mysql_fetch_array($result5)){
if($rows5['num_correct']==1){
$MEMBER05=1;
}else{
$MEMBER05=0;
}
}
$query6= "SELECT user, COUNT(*) as num_correct FROM lotto WHERE draw='$draw' AND user='$THEuser' AND no06 IN (".implode(',',$arr).")";
$result6 = mysql_query($query6) or die($query6."<br/><br/>".mysql_error());
while($rows6=mysql_fetch_array($result6)){
if($rows6['num_correct']==1){
$MEMBER06=1;
}else{
$MEMBER06=0;
}
}
$TOTALMEMBER=$MEMBER01+$MEMBER02+$MEMBER03+$MEMBER04+$MEMBER05+$MEMBER06;
echo "$THEuser got $TOTALMEMBER numbers correct for draw $draw<br><br>";
}
?>

mac_gyver · March 7, 2015

i suggest you go back and reread what i posted about normalizing the data. the query i posted only needs to be ran ONE time for a correctly normalized data table.

to display the picks once the data is normalized, you simply use ORDER BY user, num in the query to get the rows in the correct order for display. to highlight any matching number as you are looping over the rows, would simply involve using in_array() between the picked number and the $arr of random numbers (you do realize that you need to store the $arr of numbers as the winning numbers for the particular draw, since each time the page gets requested the random numbers will be regenerated.) when the user changes, you would finish the previous user display and start the display for the new user.

Barand · March 7, 2015

Draw results for week would look like this

+------------+------+
| draw_date  | num  |
+------------+------+
| 2015-03-07 |    1 |
| 2015-03-07 |    4 |
| 2015-03-07 |    8 |
| 2015-03-07 |   18 |
| 2015-03-07 |   19 |
| 2015-03-07 |   23 |
+------------+------+

The numbers for each member in the draw would look like this

+--------+------------+------+
| member | draw_date  | num  |
+--------+------------+------+
|      1 | 2015-03-07 |    1 |
|      1 | 2015-03-07 |    4 |
|      1 | 2015-03-07 |    9 |
|      1 | 2015-03-07 |   18 |
|      1 | 2015-03-07 |   19 |
|      1 | 2015-03-07 |   22 |
|      2 | 2015-03-07 |    2 |
|      2 | 2015-03-07 |    7 |
|      2 | 2015-03-07 |    8 |
|      2 | 2015-03-07 |   16 |
|      2 | 2015-03-07 |   19 |
|      2 | 2015-03-07 |   24 |
|      3 | 2015-03-07 |    1 |
|      3 | 2015-03-07 |    4 |
|      3 | 2015-03-07 |    8 |
|      3 | 2015-03-07 |   19 |
|      3 | 2015-03-07 |   21 |
|      3 | 2015-03-07 |   23 |
+--------+------------+------+

Then to see how many matches each member has for the draw

SELECT 
  draw_date
, member
, COUNT(*) as matches
FROM member_number m
    INNER JOIN draw_result d USING (draw_date, num)
WHERE d.draw_date = '2015-03-07'
GROUP BY draw_date, member

+------------+--------+---------+
| draw_date  | member | matches |
+------------+--------+---------+
| 2015-03-07 |      1 |       4 |
| 2015-03-07 |      2 |       2 |
| 2015-03-07 |      3 |       5 |
+------------+--------+---------+

If you want to see which numbers match for each member then

SELECT 
          m.draw_date
        , member
        , m.num
        , CASE 
            WHEN d.num IS NULL 
            THEN ''
            ELSE 'Yes'
          END as matched  
        FROM member_number m
            LEFT JOIN draw_result d USING (draw_date, num)
        WHERE m.draw_date = '2015-03-07'
        ORDER BY m.draw_date, member, m.num

+------------+--------+------+---------+
| draw_date  | member | num  | matched |
+------------+--------+------+---------+
| 2015-03-07 |      1 |    1 | Yes     |
| 2015-03-07 |      1 |    4 | Yes     |
| 2015-03-07 |      1 |    9 |         |
| 2015-03-07 |      1 |   18 | Yes     |
| 2015-03-07 |      1 |   19 | Yes     |
| 2015-03-07 |      1 |   22 |         |
| 2015-03-07 |      2 |    2 |         |
| 2015-03-07 |      2 |    7 |         |
| 2015-03-07 |      2 |    8 | Yes     |
| 2015-03-07 |      2 |   16 |         |
| 2015-03-07 |      2 |   19 | Yes     |
| 2015-03-07 |      2 |   24 |         |
| 2015-03-07 |      3 |    1 | Yes     |
| 2015-03-07 |      3 |    4 | Yes     |
| 2015-03-07 |      3 |    8 | Yes     |
| 2015-03-07 |      3 |   19 | Yes     |
| 2015-03-07 |      3 |   21 |         |
| 2015-03-07 |      3 |   23 | Yes     |
+------------+--------+------+---------+

Sign In

CANNOT Get these variables to add up

Recommended Posts

Falky2015

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

Falky2015

Link to comment

Share on other sites

Falky2015

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information