UPDATE help

[MountainXBGL]

Hello all,

I recently started php and mysql and am learning as I go. I have gotten a lot accomplished in a short time more out of necessity than anything. Anyway...I have ran into a portion of my script that isn't working correctly and I am at a loss. The entire page script I will post but all is working except for the UPDATE portion. I will highlight in red the areas....oh...and remember that I am kinda new at this so excuse any code etiquette thats ummm...questionable...suggestions are very much welcome.

 

<?php

include ("../../login/include/session.php");

?>

<?

$hteam = $_POST["home_team"];

$ateam = $_POST["away_team"];

$week_id = $_POST["week_id"];

$season_id = $_POST["season_id"];

$game_id = $_POST["game_id"];

$h_q1 = $_POST["h_q1"];

$h_q2 = $_POST["h_q2"];

$h_q3 = $_POST["h_q3"];

$h_q4 = $_POST["h_q4"];

$h_final = $_POST["h_final"];

$a_q1 = $_POST["a_q1"];

$a_q1 = $_POST["a_q2"];

$a_q1 = $_POST["a_q3"];

$a_q1 = $_POST["a_q4"];

$a_final = $_POST["a_final"];

$game_recap = $_POST["game_recap"];

if ($h_final > $a_final){

$winner=$hteam;

$loser = $ateam;

}

if ($a_final > $h_final){

$winner=$ateam;

$loser=$hteam;

}

//Check for game stat existance

$query = "SELECT game_status FROM game_scores WHERE game_id=$game_id";

$result = mysql_query($query) or die ("Problem checking game status");

$row=mysql_fetch_assoc($result);

if ($row["game_status"] != ""){

echo "Game data already exists. Contact Admin if this is an error.";

if ($row["game_status"] ==1){

echo "<br><a href=\"http://stadiumxbox.com\">Return to Home Page</a>";

echo "<br /><a href=\"http://stadiumxbox.com/leagues/ncaa/stat_page.php?game_id=$game_id\">Enter Individual Stats</a>";

}

}

else{

//Insert game score data

$query = "INSERT INTO game_scores VALUES ('NULL', '$season_id', '$week_id', '$game_id', '$home_team', '$away_team', '$h_q1', '$a_q1', '$h_q2', '$a_q2', '$h_q3', '$a_q3', '$h_q4', '$a_q4', '$h_final', '$a_final', 1, '$game_recap')";

mysql_query($query);

echo "Submission Complete <br><br>";

 

 

 

//Begin updating of wins and losses

 

$sql2 ="UPDATE ncaa_teams SET wins=wins+1 WHERE team_name=$winner";

mysql_query($sql2);

$sql3 ="UPDATE ncaa_teams SET losses=losses+1 WHERE team_name=$loser";

mysql_query($sql3);

?>

<br /><br /><a href="http://stadiumxbox.com">Return to Home Page</a><br />

<a href="http://stadiumxbox.com/leagues/ncaa/stat_page.php?game_id=<?echo$game_id;?>">Enter Individual Stats</a>

<?

}

?>

 

 

Also...

$sql2 ="UPDATE ncaa_teams SET wins=wins+1 WHERE team_name=$winner";

mysql_query($sql2);

$sql3 ="UPDATE ncaa_teams SET losses=losses+1 WHERE team_name=$loser";

mysql_query($sql3);

 

 

If I wanted to add a field to be updated....would I do it with:

 

....SET wins=wins+1, games_played=games_played+1 and same for the ....WHERE clause:

....WHERE team_name=$whatever AND season_id=$whatever

 

 

 

 

[DarkWater]

Here:

$a_q1 = $_POST["a_q1"];

$a_q1 = $_POST["a_q2"];

$a_q1 = $_POST["a_q3"];

$a_q1 = $_POST["a_q4"];

 

You keep overwriting $a_q1.

[MountainXBGL]

Thank you for the catch...I hadn't noticed that yet because I don't actually use those in any calculations...

 

I don't believe those affect the UPDATE though...the update portion deals with a different table all together.

 

I do appreciate the catch though.

[DarkWater]

Make your queries:

 

$result1 = mysql_query($sql2) OR die(mysql_error());

[MountainXBGL]

Thanks Dark.

 

I made the changes so that now the update portion looks like:

$sql2 ="UPDATE ncaa_teams SET wins=wins+1, games_played=games_played+1 WHERE team_name=$winner AND season_id=$season_id";

$result1 = mysql_query($sql2) OR die(mysql_error());

$sql3 ="UPDATE ncaa_teams SET losses=losses+1, games_played=games_played+1 WHERE team_name=$loser AND seson_id=$season_id";

$result2 = mysql_query($sql3) OR die(mysql_error());

 

It returned this error:

Unknown column 'Oklahoma' in 'where clause'

 

I put in fake data for an Oklahoma and Ohio State game with Oklahoma being the winner so the error was in the first update line.  Oklahoma by myadminphp is a valid entry in the team_name field.

 

I also put in a couple lines just to echo what $winner and $loser were so I can see that the variables are working and they are.

 

Any ideas?

[DarkWater]

Yup.  You need to enclose $winner and $loser in ' ' inside the query because they're strings.

[MountainXBGL]

That did the trick...well...I still had a proble..the fields that I was trying to increment had a default of null and it wouldn't increment from null so once I went back into phpmyadmin and put 0 in the fields..it worked fine.

 

Thanks Dark...I bought a book 4 weeks ago and started beating my brains out on php...I am learning but it takes a bit.