Compare 2 tbls and then edit one of them

[mr3k]

Hello! I've been stuck with this for a while now. First I want to say that this is a school project and not something illegal bet site.

 

The first MySQL table have results from games and the other table contains users bets on different games..

My problem is that I don't know how to make the two tables compare and then by that change some numbers in MySQL table.

 

So I want the code to look if the team1 and team2 on both tables are the same. Then look if the bets (1,X or 2) are the same. If they are, change (in table "Bets") w to 1 and if now change (in table "Bets") l to w.

 

I think my problem might be that i don't know how to make the while loop...

 

This is my code:

 

 

$tbl_name="Bets";

 

$tbl_name2="results";

 

//connect to MySQL and chose database

mysql_connect("$host", "$username", "$password")or die("Connection could not be Established");

mysql_select_db("$db_name")or die("Could not select DB");

 

$loginusername=$_SESSION['loginusername'];

 

$sql="SELECT * FROM $tbl_name WHERE user='$loginusername'";

$r=mysql_query($sql);

$i=0;

$betarray=array(array());

while($rows=mysql_fetch_array($r))

{

$betarray[$i]=$rows;

$i++;

}

 

 

 

foreach($betarray as $insidearray)

foreach($betarray2 as $insidearray1)

{

while($rows=mysql_fetch_array($r))

{

$team1 = $insidearray['team1'];

$team2 = $insidearray['team2'];

if($team1 == $insidearray1['team1'] && $team2 == $insidearray1['team2'])

{

$team1 = $insidearray['team1'];

$team2 = $insidearray['team2'];

$x12 = $insidearray['1x2real']

 

if($x12 == $insidearray1['1x2'])

{

$update="UPDATE $tbl_name SET w='1' WHERE user='$loginusername' AND team1='$team1' AND team2='$team2'";

mysql_query($update);

}

else

{

$update="UPDATE $tbl_name SET l='1' WHERE user='$loginusername' AND team1='$team1' AND team2='$team2'";

mysql_query($update);

}

 

}

 

 

}

}

}

 

 

 

 

Thanks in Advance!

[Fadion]

You'll have to create a relation between the 2 tables, otherwise you're using MySQL simply as a data storage solution, not as a RDBMS.

 

table "games"

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

id | team1 | team2 | result

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

1 | Barcelona | Manchester Utd | 3-1

2 | Milan | Inter | 0-2

 

table "bets"

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

id | game_id | bet | won

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

1 | 1 | X | 0

2 | 2 | 2 | 1

 

What you can do here is insert matches in the "games" table, together with the result (will be set when the match finishes, obviously). In the bets table, you have the played bets with a reference to the "games" table (game_id) that specifies what game has been played and the final bet (1, X or 2). You also have a field "won" that is set to "1" if the bet is a win (correct guess), or "0" if the bet lost - this field is set when the match finishes.

 

Writing PHP code to validate bets should be trivial right now. Take a look at the database scheme and return here if you have problems with code.

[mr3k]

You'll have to create a relation between the 2 tables, otherwise you're using MySQL simply as a data storage solution, not as a RDBMS.

 

table "games"

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

id | team1 | team2 | result

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

1 | Barcelona | Manchester Utd | 3-1

2 | Milan | Inter | 0-2

 

table "bets"

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

id | game_id | bet | won

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

1 | 1 | X | 0

2 | 2 | 2 | 1

 

What you can do here is insert matches in the "games" table, together with the result (will be set when the match finishes, obviously). In the bets table, you have the played bets with a reference to the "games" table (game_id) that specifies what game has been played and the final bet (1, X or 2). You also have a field "won" that is set to "1" if the bet is a win (correct guess), or "0" if the bet lost - this field is set when the match finishes.

 

Writing PHP code to validate bets should be trivial right now. Take a look at the database scheme and return here if you have problems with code.

 

Thanks for your answer.

Maybe I should show you how my table looks right now.

 

"Bets" table:

id |  user  | team1  | team2  | date  | matchdate  | amount | league  | 1x2  | 1x2real  | odds  | won | w |  l  |

38 | test | FC Barcelona | Manchester Utd  | 2011-05-28 13:20:42  | 28/05/2011 | 20 | ~Champions League~ | odd1  | 1 |2.05 | 0 | 0 | 0|

 

"results" table:

id | team1 | team2 | date | t1 | t2 | 1x2  |

63 | FC Barcelona | Manchester Utd | 28/05/2011 | 3 | 1 | 1  |

 

So, I want to see if the team1 && team2 in "bets" == team1 && team2 in "results"

then see if 1x2real from "bets" == 1x2 in "results"

        if thats true change w in "bets" to 1

        if thats false change l in "bets" to 1

 

Do you understand what I mean?

 

[mr3k]

Another question as I have is:

 

How do I do a whileloop that look if team1 in table bets == team1 in table results?

 

Thanks in advance!