Updating table based on results entered

[ade2901]

Hi all,

 

I have a bit of an issue. I have a page that allows the user ot input the results/scores of football fixtures (currently there are 6 fixtures displayed). When the user clicks submit it then updates the results fine. I then want it to update the league table, however, I keep getting the following error:

 

0 successfully updated.
Back to main page UPDATE division1 SET played=played+1, draw=draw+1, gf=1, ga=1, pts=pts+1 WHERE teamID=teamAID[0]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[0]' at line 1.

 

This shows that it only updated the first record rather than all of the fixtures (as it's supposed to) showing it has just stopped going through the loop after the first result, despite the score entered being 2:1 to the home team therefore it shouldn't be checking the draw query.

 

I'll now show you my code (see below comment reading; /PROBLEMS OCCURING HERE WHEN UPDATING LEAGUE TABLE*********);

<?php

$host="localhost"; // Host name
$username=""; // Mysql username
$password=""; // Mysql password
$db_name=""; // Database name
$tbl_name=""; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");


$submit = $_POST['submit'];
$fixtureID = $_POST['fixtureID'];
$teamAScore = $_POST['teamAScore'];
$teamBScore = $_POST['teamBScore'];
$teamAID = $_POST['teamAID'];
$teamBID = $_POST['teamBID'];


$limit = count($fixtureID);

for($i=0;$i<$limit;$i++) {
    $fixtureID[$i] = mysql_real_escape_string($fixtureID[$i]);
    $teamAScore[$i] = mysql_real_escape_string($teamAScore[$i]);
    $teamBScore[$i] = mysql_real_escape_string($teamBScore[$i]);
$teamAID [$i] = mysql_real_escape_string($teamAID[$i]);
$teamBID [$i] = mysql_real_escape_string($teamBID[$i]);

    $query = "UPDATE fixture SET teamAScore=$teamAScore[$i], teamBScore=$teamBScore[$i] WHERE fixtureID = $fixtureID[$i]";

    if(mysql_query($query))
        echo "$i successfully updated.<br/><a href='div1Results.php'>Back to main page</a>";
	//If fixtures update then update league table

//PROBLEMS OCCURING HERE WHEN UPDATING LEAGUE TABLE************************************************

		if ($teamAScore[$i] > $teamBScore[$i])
		{

			//Team A update for team A win
			$teamAWin = "UPDATE division1 SET played=played+1, won=won+1, gf=".$teamAScore[$i].", ga=".$teamBScore[$i].", pts= pts+3  WHERE teamID=teamAID[$i]";
		    $resultTeamA = mysql_query($teamAWin) or die( "$teamAWin: " .mysql_error( ) ); 

			//Team B update for teamB loss
			$teamBLoss = "UPDATE division1 SET played=played+1, lost=lost+1, gf=".$teamBScore[$i].", ga=".$teamAScore[$i]." WHERE teamID=teamBID[$i]";
		    $resultTeamB = mysql_query($teamBLoss) or die( "$teamBLoss: " .mysql_error( ) );
		} 

		if ($teamAScore[$i] < $teamBScore[$i])
		{
			//Team A update for team A win
			$teamALoss = "UPDATE division1 SET played=played+1, lost=lost+1, gf=".$teamAScore[$i].", ga=".$teamBScore[$i]." WHERE teamID=teamAID[$i]";
		    $resultTeamALoss = mysql_query($teamALoss) or die( "$teamAloss: " .mysql_error( ) ); 

			//Team B update for teamB loss
			$teamBWin = "UPDATE division1 SET played=played+1, won =won+1, gf=".$teamBScore[$i].", ga=".$teamAScore[$i].", pts=pts+3 WHERE teamID=teamBID[$i]";
		    $resultTeamBWin = mysql_query($teamBWin) or die( "$teamBWin: " .mysql_error( ) );
		} 

		if ($teamAScore[$i] == $teamBScore[$i])
		{
			//Team A update for team A win
			$teamADraw = "UPDATE division1 SET played=played+1, draw=draw+1, gf=".$teamAScore[$i].", ga=".$teamBScore[$i].", pts=pts+1 WHERE teamID=teamAID[$i]";
		    $resultTeamADraw = mysql_query($teamADraw) or die( "$teamADraw: " .mysql_error( ) ); 

			//Team B update for teamB loss
			$teamBDraw = "UPDATE division1 SET played=played+1, draw=draw+1, gf=".$teamBScore[$i].", ga=".$teamAScore[$i].", pts=pts+1 WHERE teamID=teamBID[$i]";
		    $resultTeamBDraw = mysql_query($teamBDraw) or die( "$teamBDraw: " .mysql_error( ) );
		}

    else
        echo "$i encountered an error.<br/>";
}  

// close connection
mysql_close();
?>

 

All help would be much appreciated as I am really stuck here!

 

Any questions about my problem please just ask.

 

Many thanks in advance,

 

Aidan

 

[requinix]

"WHERE teamID=teamAID[$i]"

Does that look right to you?

[ade2901]

Oh dear, initially when writing this reply I said yes however on second thought I don't need the counter value passed to it do I? But then how does it determine which teamID it is referring to as it'll change per fixture and that php is linked to a form with multiple fixtures. I can't try it at this moment in time as I'm not at home any longer.

[Psycho]

I think you are missing the point of requinix's post. That query (and others) is trying to reference an array value - but you are not referencing the array correctly. The array name doesn't have a dollar sign in front of it to indicate it as a variable. It should look something like this

"WHERE teamID=$teamAID[$i]"

[ade2901]

Ah yes, that was a schoolboy mistake! Thanks for pointing that out, can't believe I missed it. I'll alter that straight away when I get back to my machine. Strange what you do when you've looked at it for too long!

 

Thanks, I appreciate both of you pointing that out. Fingers crossed it'll work now and update the league table correctly.

[Psycho]

I always recommend creating your queries as string variables and, if there is an error, echoing the query to the page so you can verify that the query is formed how you think it should be.

 

It should be understood that you would only do this in a development environment. You would never want to display queries on the page in a production environment.