Error pulling data from sql?

jamesjohnson88 · Jul 21, 2009

The code itself explains what i'm trying to do.

<?php
error_reporting(E_ALL);
//SCORE ADDITION CODE.

//Connect & Select.
$con = mysql_connect("localhost","u08105199","orly?");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("u08105199", $con);

//Declare variables.
$leagueName = mysql_real_escape_string($_POST["leagueName"]);
$player1 = mysql_real_escape_string($_POST["playerOneName"]);
$player2 = mysql_real_escape_string($_POST["playerTwoName"]);
$result = mysql_real_escape_string($_POST["result"]);
$comments = mysql_real_escape_string($_POST["comments"]);
//Get timestamp variable.
$timestamp=date("d/m/Y");

//Pull from sql table.
$player1gp = mysql_query("SELECT * FROM $leagueName WHERE PlayerName = $player1 VALUES (GamesPlayed)"); ///////////////////////////
$player1wins = mysql_query("SELECT * FROM $leagueName WHERE PlayerName = $player1 VALUES (Wins)");      ///////////////////////////
$player1losses = mysql_query("SELECT * FROM $leagueName WHERE PlayerName = $player1 VALUES (Losses)");  ///  GET PLAYER 1 INFO  ///
$player1draws = mysql_query("SELECT * FROM $leagueName WHERE PlayerName = $player1 VALUES (Draws)");    ///////////////////////////
$player1points = mysql_query("SELECT * FROM $leagueName WHERE PlayerName = $player1 VALUES (Points)");  ///////////////////////////

$player2gp = mysql_query("SELECT * FROM $leagueName WHERE PlayerName = $player2 VALUES (GamesPlayed)"); ///////////////////////////
$player2wins = mysql_query("SELECT * FROM $leagueName WHERE PlayerName = $player2 VALUES (Wins)");      ///////////////////////////
$player2losses = mysql_query("SELECT * FROM $leagueName WHERE PlayerName = $player2 VALUES (Losses)");  ///  GET PLAYER 2 INFO  ///
$player2draws = mysql_query("SELECT * FROM $leagueName WHERE PlayerName = $player2 VALUES (Draws)");    ///////////////////////////
$player2points = mysql_query("SELECT * FROM $leagueName WHERE PlayerName = $player2 VALUES (Points)");  ///////////////////////////

//Do calculation code.
$player1winpercent = ($player1gp/$player1wins) * 100;
$player2winpercent = ($player2gp/$player2wins) * 100;

if ($player1winpercent > $player2winpercent)
{
	$percentDiff = $player1winpercent - $player2winpercent;
	if ($percentDiff < 10)
	{
		$player1pts = 18;
		$player2pts = 18;
		$player1minus = -11;
		$player2minus = -11;
	}
	else if ($percentDiff < 20)
	{
		$player1pts = 17;
		$player2pts = 19;
		$player1minus = -12;
		$player2minus = -10;
	}
	else if ($percentDiff < 30)
	{
		$player1pts = 16;
		$player2pts = 20;
		$player1minus = -13;
		$player2minus = -9;
	}
	else if ($percentDiff < 40)
	{
		$player1pts = 15;
		$player2pts = 21;
		$player1minus = -14;
		$player2minus = -8;
	}
	else if ($percentDiff < 50)
	{
		$player1pts = 14;
		$player2pts = 22;
		$player1minus = -15;
		$player2minus = -7;
	}
	else if ($percentDiff < 60)
	{
		$player1pts = 13;
		$player2pts = 23;
		$player1minus = -16;
		$player2minus = -6;
	}
	else if ($percentDiff < 70)
	{
		$player1pts = 12;
		$player2pts = 24;
		$player1minus = -17;
		$player2minus = -5;
	}
	else if ($percentDiff < 80)
	{
		$player1pts = 11;
		$player2pts = 25;
		$player1minus = -18;
		$player2minus = -4;
	}
	else if ($percentDiff < 90)
	{
		$player1pts = 10;
		$player2pts = 26;
		$player1minus = -19;
		$player2minus = -3;
	}
	else
	{
		$player1pts = 9;
		$player2pts = 27;
		$player1minus = -20;
		$player2minus = -2;
	}
}
else
{
	$percentDiff = $player2winpercent - $player1winpercent;
	$percentDiff = $player1winpercent - $player2winpercent;
	if ($percentDiff < 10)
	{
		$player2pts = 18;
		$player1pts = 18;
		$player1minus = -11;
		$player2minus = -11;
	}
	else if ($percentDiff < 20)
	{
		$player2pts = 17;
		$player1pts = 19;
		$player1minus = -10;
		$player2minus = -12;
	}
	else if ($percentDiff < 30)
	{
		$player2pts = 16;
		$player1pts = 20;
		$player1minus = -9;
		$player2minus = -13;
	}
	else if ($percentDiff < 40)
	{
		$player2pts = 15;
		$player1pts = 21;
		$player1minus = -8;
		$player2minus = -14;
	}
	else if ($percentDiff < 50)
	{
		$player2pts = 14;
		$player1pts = 22;
		$player1minus = -7;
		$player2minus = -15;
	}
	else if ($percentDiff < 60)
	{
		$player2pts = 13;
		$player1pts = 23;
		$player1minus = -6;
		$player2minus = -16;
	}
	else if ($percentDiff < 70)
	{
		$player2pts = 12;
		$player1pts = 24;
		$player1minus = -5;
		$player2minus = -17;
	}
	else if ($percentDiff < 80)
	{
		$player2pts = 11;
		$player1pts = 25;
		$player1minus = -4;
		$player2minus = -18;
	}
	else if ($percentDiff < 90)
	{
		$player2pts = 10;
		$player1pts = 26;
		$player1minus = -3;
		$player2minus = -19;
	}
	else
	{
		$player2pts = 9;
		$player1pts = 27;
		$player1minus = -2;
		$player2minus = -20;
	}
}

//Find winner.
	switch ($result)
	{
	case "defeated":
	  mysql_query("UPDATE $leagueName SET GamesPlayed=GamesPlayed+1, Wins=Wins+1, Points=Points+$player1pts
	WHERE PlayerName = '$player1' LIMIT 1") or die(mysql_error());
	  mysql_query("UPDATE $leagueName SET GamesPlayed=GamesPlayed+1, Losses=Losses+1, Points=Points+$player2minus
	WHERE PlayerName = '$player2' LIMIT 1") or die(mysql_error());
	  echo ("$timestamp - $player1 $result $player2 in the league - $leagueName.");
	  break;
	case "lost to":
	  mysql_query("UPDATE $leagueName SET GamesPlayed=GamesPlayed+1, Wins=Wins+1, Points=Points+$player2pts
	WHERE PlayerName = '$player2' LIMIT 1") or die(mysql_error());
	  mysql_query("UPDATE $leagueName SET GamesPlayed=GamesPlayed+1, Losses=Losses+1, Points=Points+$player1minus
	WHERE PlayerName = '$player1' LIMIT 1") or die(mysql_error());
	  echo ("$timestamp - $player1 $result $player2 in the league - $leagueName.");
	  break;
	case "drew with":
	  mysql_query("UPDATE $leagueName SET GamesPlayed=GamesPlayed+1, Draws=Draws+1, Points=Points+10
	WHERE PlayerName = '$player1' LIMIT 1") or die(mysql_error());
	  mysql_query("UPDATE $leagueName SET GamesPlayed=GamesPlayed+1, Draws=Draws+1, Points=Points+10
	WHERE PlayerName = '$player2' LIMIT 1") or die(mysql_error());
	  echo ("$timestamp - $player1 $result $player2 in the league - $leagueName.");
	  break;
	default:
	  echo "No result selected!";
	}


//RESULT LOG CODE.
// Create result string.
$addresult = "$timestamp - $player1 $result $player2 in the $leagueName league.";

// Store results.
mysql_query("INSERT INTO Result_Log (League, Result, Comment, `Timestamp`)
VALUES ('$leagueName', '$addresult', '$comments', '$timestamp')") or die(mysql_error()); // problem is in this query...

mysql_close($con);
?>

Upon testing, it seems that the winner always gets +18 and the loser -12, despite how many wins or losses they already have. So I assume that the variable assigned from sql have been done incorrectly.

Any advice will be greatly appreciated.

DaiLaughing · Jul 21, 2009

Unless this is a form of PHP/MySQL I have never met (which is possible ) this seems all wrong.

You seem to be using many queries to get one set of results. Then you seem to be using the result without extracting the data. Why not:

$query="SELECT * FROM '$leagueName' WHERE PlayerName = '$player1'";
$result=mysql_query($query) or die ("Query to get data about player failed: ".mysql_error());

The added advantage is this will give you an error if it fails (dies). Then:

$row=mysql_fetch_array($result)) // assuming there can only be one row

$player1gp=$row[GamesPlayed];

and so on for the other variables.

Bjom · Jul 21, 2009

@dai laughing: a) Or die() sucks . b) Use [ php] [/code] tags, please.

@:

A SELECT statement doesn't have a "VALUES" keyword, see manual
what do you want to achieve there?
The result of a query is not a number, but rather a query-result. You need to extract the value from that (as dai pointed out correctly). See here
Instead of writing a big bunch of code and then posting all of it with a statement that translates to "it doesn't work", add a small chunk every time and test if it is doing what you want. A very simple but working way for doing so is to test your variables by printing them to the screen with a var_dump - a better way is getting a debugger.

DaiLaughing · Jul 21, 2009

Sorry about the PHP tags, did mean to and got distracted by only posting small chunks in the end.

What is better than or die()?

Bjom · Jul 21, 2009

Or die is bad because it ends code abruptly without letting you do any real error-handling (show it on screen, log it, suppress it, whatever....)

Better is to throw an exception or to trigger an error. For example by using trigger_error() which is as simple as or die:

$rst = $conn->query('SELECT `blah` FROM `myTable`) or trigger_error('Query failed: ' . conn->error, E_USER_ERROR);

DaiLaughing · Jul 21, 2009

Not meaning to highjack this thread (but doing it anyway) won't that then allow the page to carry on processing with perhaps unpredictable results? I guess if you had many elements you might prefer one to fail and the rest to carry on. Thanks for the suggestion.

Bjom · Jul 21, 2009

That depends on what you pass as the second argument (or on your catch block in case of an exception).

If you do it like in the example, then an equivalent of E_ERROR is raised which is a fatal error, which in turn ends the script. If you passed E_USER_NOTICE instead, then you could let the script continue - see the flexibility? Also have a look here: error constants

Bjom

jamesjohnson88 · Jul 22, 2009

Thanks for the help guys, i think I see what to do now. I was using multiple queries in the code code because I didn't know how to assign the column data to a variable so I just took a wild guess. I'll recode it all tomorrow and let you know how it goes.

Sorry it took so long for me to reply, i've had a busy few days.

James

jamesjohnson88 · Jul 22, 2009

Ok, i've changed the code (see below) but i'm not getting any output of any kind. Any idea's what is causing the problem? Using existing sql data is brand new to me, so I don't really have any which part is causing the problem.

In the original code, the if/switch statements all worked fine. I believe it is the assignment of the variables that is the problem.

<?php
error_reporting(E_ALL);
//SCORE ADDITION CODE.

//Connect & Select.
$con = mysql_connect("localhost","u08105199","11011100");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("u08105199", $con);

//Declare variables.
$leagueName = mysql_real_escape_string($_POST["leagueName"]);
$player1 = mysql_real_escape_string($_POST["playerOneName"]);
$player2 = mysql_real_escape_string($_POST["playerTwoName"]);
$result = mysql_real_escape_string($_POST["result"]);
$comments = mysql_real_escape_string($_POST["comments"]);
//Get timestamp variable.
$timestamp=date("d/m/Y");

//Pull from sql table.
$p1result = mysql_query("SELECT * FROM $leagueName WHERE PlayerName = $player1") or trigger_error('Query failed: ' . conn->error, E_USER_ERROR);
$p2result = mysql_query("SELECT * FROM $leagueName WHERE PlayerName = $player2") or trigger_error('Query failed: ' . conn->error, E_USER_ERROR);

//Assign p1 variables.
$row=mysql_fetch_array($p1result));
$player1gp=$row["GamesPlayed"];
$player1wins=$row["Wins"];
$player1losses=$row["Losses"];
$player1draws=$row["Draws"];
$player1points=$row["Points"];

//Assign p2 variables.
$row2=mysql_fetch_array($p2result));
$player2gp=$row2["GamesPlayed"];
$player2wins=$row2["Wins"];
$player2losses=$row2["Losses"];
$player2draws=$row2["Draws"];
$player2points=$row2["Points"];

var_dump($player1gp,$player1wins,$player1losses,$player1draws,$player1points,$player2gp,$player2wins,$player2losses,$player2draws,$player2points);

//Do calculation code.
$player1winpercent = ($player1gp/$player1wins) * 100;
$player2winpercent = ($player2gp/$player2wins) * 100;

if ($player1winpercent > $player2winpercent)
{
	$percentDiff = $player1winpercent - $player2winpercent;
	if ($percentDiff < 10)
	{
		$player1pts = 18;
		$player2pts = 18;
		$player1minus = -11;
		$player2minus = -11;
	}
	else if ($percentDiff < 20)
	{
		$player1pts = 17;
		$player2pts = 19;
		$player1minus = -12;
		$player2minus = -10;
	}
	else if ($percentDiff < 30)
	{
		$player1pts = 16;
		$player2pts = 20;
		$player1minus = -13;
		$player2minus = -9;
	}
	else if ($percentDiff < 40)
	{
		$player1pts = 15;
		$player2pts = 21;
		$player1minus = -14;
		$player2minus = -8;
	}
	else if ($percentDiff < 50)
	{
		$player1pts = 14;
		$player2pts = 22;
		$player1minus = -15;
		$player2minus = -7;
	}
	else if ($percentDiff < 60)
	{
		$player1pts = 13;
		$player2pts = 23;
		$player1minus = -16;
		$player2minus = -6;
	}
	else if ($percentDiff < 70)
	{
		$player1pts = 12;
		$player2pts = 24;
		$player1minus = -17;
		$player2minus = -5;
	}
	else if ($percentDiff < 80)
	{
		$player1pts = 11;
		$player2pts = 25;
		$player1minus = -18;
		$player2minus = -4;
	}
	else if ($percentDiff < 90)
	{
		$player1pts = 10;
		$player2pts = 26;
		$player1minus = -19;
		$player2minus = -3;
	}
	else
	{
		$player1pts = 9;
		$player2pts = 27;
		$player1minus = -20;
		$player2minus = -2;
	}
}
else
{
	$percentDiff = $player2winpercent - $player1winpercent;
	$percentDiff = $player1winpercent - $player2winpercent;
	if ($percentDiff < 10)
	{
		$player2pts = 18;
		$player1pts = 18;
		$player1minus = -11;
		$player2minus = -11;
	}
	else if ($percentDiff < 20)
	{
		$player2pts = 17;
		$player1pts = 19;
		$player1minus = -10;
		$player2minus = -12;
	}
	else if ($percentDiff < 30)
	{
		$player2pts = 16;
		$player1pts = 20;
		$player1minus = -9;
		$player2minus = -13;
	}
	else if ($percentDiff < 40)
	{
		$player2pts = 15;
		$player1pts = 21;
		$player1minus = -8;
		$player2minus = -14;
	}
	else if ($percentDiff < 50)
	{
		$player2pts = 14;
		$player1pts = 22;
		$player1minus = -7;
		$player2minus = -15;
	}
	else if ($percentDiff < 60)
	{
		$player2pts = 13;
		$player1pts = 23;
		$player1minus = -6;
		$player2minus = -16;
	}
	else if ($percentDiff < 70)
	{
		$player2pts = 12;
		$player1pts = 24;
		$player1minus = -5;
		$player2minus = -17;
	}
	else if ($percentDiff < 80)
	{
		$player2pts = 11;
		$player1pts = 25;
		$player1minus = -4;
		$player2minus = -18;
	}
	else if ($percentDiff < 90)
	{
		$player2pts = 10;
		$player1pts = 26;
		$player1minus = -3;
		$player2minus = -19;
	}
	else
	{
		$player2pts = 9;
		$player1pts = 27;
		$player1minus = -2;
		$player2minus = -20;
	}
}

//Find winner.
	switch ($result)
	{
	case "defeated":
	  mysql_query("UPDATE $leagueName SET GamesPlayed=GamesPlayed+1, Wins=Wins+1, Points=Points+$player1pts
	WHERE PlayerName = '$player1' LIMIT 1") or trigger_error('Query failed: ' . conn->error, E_USER_ERROR);
	  mysql_query("UPDATE $leagueName SET GamesPlayed=GamesPlayed+1, Losses=Losses+1, Points=Points+$player2minus
	WHERE PlayerName = '$player2' LIMIT 1") or trigger_error('Query failed: ' . conn->error, E_USER_ERROR);
	  echo ("$timestamp - $player1 $result $player2 in the league - $leagueName.");
	  break;
	case "lost to":
	  mysql_query("UPDATE $leagueName SET GamesPlayed=GamesPlayed+1, Wins=Wins+1, Points=Points+$player2pts
	WHERE PlayerName = '$player2' LIMIT 1") or trigger_error('Query failed: ' . conn->error, E_USER_ERROR);
	  mysql_query("UPDATE $leagueName SET GamesPlayed=GamesPlayed+1, Losses=Losses+1, Points=Points+$player1minus
	WHERE PlayerName = '$player1' LIMIT 1") or trigger_error('Query failed: ' . conn->error, E_USER_ERROR);
	  echo ("$timestamp - $player1 $result $player2 in the league - $leagueName.");
	  break;
	case "drew with":
	  mysql_query("UPDATE $leagueName SET GamesPlayed=GamesPlayed+1, Draws=Draws+1, Points=Points+10
	WHERE PlayerName = '$player1' LIMIT 1") or trigger_error('Query failed: ' . conn->error, E_USER_ERROR);
	  mysql_query("UPDATE $leagueName SET GamesPlayed=GamesPlayed+1, Draws=Draws+1, Points=Points+10
	WHERE PlayerName = '$player2' LIMIT 1") or trigger_error('Query failed: ' . conn->error, E_USER_ERROR);
	  echo ("$timestamp - $player1 $result $player2 in the league - $leagueName.");
	  break;
	default:
	  echo "No result selected!";
	}


//RESULT LOG CODE.
// Create result string.
$addresult = "$timestamp - $player1 $result $player2 in the $leagueName league.";

// Store results.
mysql_query("INSERT INTO Result_Log (League, Result, Comment, `Timestamp`)
VALUES ('$leagueName', '$addresult', '$comments', '$timestamp')") or trigger_error('Query failed: ' . conn->error, E_USER_ERROR);

mysql_close($con);
?>

<html>
<br />
<br />
<a href="http://student.dc.lincoln.ac.uk/~u08105199/admin.php">Click here</a> to go back to the homepage!
</html>

DaiLaughing · Jul 23, 2009

It's too early in the morning for me to work through the code. Try putting in echo or print_r lines to see where the variables contain what you think they should contain. Start with $_POSt and work you way down until the data vanishes.

Bjom · Jul 23, 2009

What do you mean "no output"? not even from the vardump? Then your program exits and you have error reporting level set too low to report anything on screen....

the one var_dump() is a start, but that's not what I meant. litter the whole code with it (when everything works you'll remove them). You need to KNOW all the time what is going on in your code.

start like below OR much better: get eclipse, with eclipse PDT and on top of that XDEBUG. that lets you execute code line by line and shows you variables and whatnot.

<?php 
error_reporting(E_ALL); 
//SCORE ADDITION CODE. 

//Connect & Select. 
$con = mysql_connect("localhost","u08105199","11011100"); 
vardump($con);
if (!$con) 
{ 
die('Could not connect: ' . mysql_error()); 
} 
mysql_select_db("u08105199", $con); 

//Declare variables. 
$leagueName = mysql_real_escape_string($_POST["leagueName"]); 
vardump($leagueName);
$player1 = mysql_real_escape_string($_POST["playerOneName"]); 
vardump($player1);
$player2 = mysql_real_escape_string($_POST["playerTwoName"]); 
...
$result = mysql_real_escape_string($_POST["result"]); 
...
$comments = mysql_real_escape_string($_POST["comments"]); 
//Get timestamp variable. 
$timestamp=date("d/m/Y"); 
vardump($timestamp)

//Pull from sql table. 
$p1result = mysql_query("SELECT * FROM $leagueName WHERE PlayerName = 
$player1") or trigger_error('Query failed: ' . conn->error, E_USER_ERROR); 
vardump($p1result);
$p2result = mysql_query("SELECT * FROM $leagueName WHERE PlayerName = $player2") or trigger_error('Query failed: ' . conn->error, E_USER_ERROR); 
...
//Assign p1 variables. 
$row=mysql_fetch_array($p1result)); 
vardump($row);
$player1gp=$row["GamesPlayed"]; 
vardump($player1gp);
$player1wins=$row["Wins"]; 
...
$player1losses=$row["Losses"]; 
...
$player1draws=$row["Draws"]; 
...
$player1points=$row["Points"]; 
...

Error pulling data from sql?

Jjamesjohnson88

DDaiLaughing

BBjom

DDaiLaughing

BBjom

DDaiLaughing

BBjom

Jjamesjohnson88

Jjamesjohnson88

DDaiLaughing

BBjom