Query to SQL database not returning actual values

[muskelmann098]

Hello,

 

I have been making a kind of browser-based game, and I have written a function that will get the total number of resources from my database, and the echo it.

Unfortunately, it is returning only the name of the variable I put in, and not the values from the table. It might be that I'm only tired after a whole day of coding, but I just can't see what's causing the error.

 

Would someone here care to look over it?

 

function totRes($resource){
global $conn, $name;
	$result = mysql_query("SELECT camp FROM `Villages` WHERE username = '$name'");
	$cnamearr = mysql_fetch_array($result);
	$cname = $cnamearr['camp'];
	$totres_que = mysql_query("SELECT 'Oil' FROM `Villages` WHERE camp = '$cname'");
	$totres_arr = mysql_fetch_array($totres_que);
	$totres = $totres_arr["Oil"];
	echo $totres;
	}

 

 

Thanks a lot folks!

[mrMarcus]

lose the single-quotes around Oil:

 

$totres_que = mysql_query("SELECT 'Oil' FROM `Villages` WHERE camp = '$cname'");

 

to:

 

$totres_que = mysql_query("SELECT `Oil` FROM `Villages` WHERE camp = '$cname'");

 

from now on, use trigger_error() to check for mysql errors like so:

 

$totres_que = mysql_query("SELECT 'Oil' FROM `Villages` WHERE camp = '$cname'") or trigger_error (mysql_error());

 

as well, don't use global variables anymore.  pass these variables as arguments in the function:

 

<?php
function totRes($resource, $name){
      $result = mysql_query("SELECT camp FROM `Villages` WHERE username = '$name'") or trigger_error (mysql_error());
      $cnamearr = mysql_fetch_array($result);
      $cname = $cnamearr['camp'];
      $totres_que = mysql_query("SELECT `Oil` FROM `Villages` WHERE camp = '$cname'") or trigger_error (mysql_error());
      $totres_arr = mysql_fetch_array($totres_que);
      $totres = $totres_arr["Oil"];
      echo $totres;
      }
?>

 

it appears you aren't using $conn anyways, so i removed it.

[muskelmann098]

Thanks, that solution didn't actually work, but it got me thinking of something that did

$conn was necessary since it's what allows me to connect to the database, but thanks for the "trigger_error" tip. I have a feeling I'll be using it a lot.

 

Again, thanks for your quick reply

[Psycho]

You have no error handling so, if the query fails, you will have no way of knowing. Although I suspect it is because you delineated the field 'oil' with single quotes instead of back quotes in the second query. (Edit: I guess it is something else, but the debugging below should help)

 

Plus, there is no reason to do two queries to the exact same table. If they were two different tables you could use a JOIN, but it's the same table. The queries make no sense to me. The first query gets the value of 'camp' where the username matches, then you get the record wich has the same value for camp. Wouldn't that be the same record from the first query?

 

Try the code below. You should add propper error handling/reporting in your application. I would also suggest passing $name to the function instead of making it a global - and the global $conn is not even used.

 

function totRes($resource)
{
    global $name;

    $query = "SELECT 'Oil'
              FROM `Villages`
              WHERE username = '$name'",
    $result = mysql_query($query);

    //Temporary debugging
    if (!$result)
    {
       echo "There was a problem running the query:<br />\n{$query}\n";
       echo "<br /><br />Error:<br />" . mysql_error();
       exit();
    }

    $totres = mysql_result($result, 0);
    echo $totres;
}

[mrMarcus]

where are you using $conn to connect to the database within that function?

 

anyways .. al you're doing with the function is echo'ing $totres which is a single value from the database.  if you want usage of the entire return of the query, you need to return that:

 

<?php
function totRes($resource, $name){
      $result = mysql_query("SELECT camp FROM `Villages` WHERE username = '$name'") or trigger_error (mysql_error());
      $cnamearr = mysql_fetch_array($result);
      $cname = $cnamearr['camp'];
      $totres_que = mysql_query("SELECT `Oil` FROM `Villages` WHERE camp = '$cname'") or trigger_error (mysql_error());
      $totres_arr = mysql_fetch_array($totres_que);
      return $totres_arr;
      }
?>

 

for a function to actually be useful, it generally reserves the ability to be multi-functional .. what i mean by that is, if you are just doing a query to accommodate one item within your script/site, then just plug that directly into your code.  if you are going to be reusing this function in several different areas of the site, then everything's ok.

[muskelmann098]

Thanks for all the tips, it's really appreciated. I would expect that I'm not always following any recommended coding standards because I'm only self-taught, and kind of a novice too.

 

Anyway, what I'm trying to do is to make a game, kind of like "Travian" if anyone here has played it. It might be way over my head, but my plan is to learn from it as I go along. Right now, I'm trying to get a system down for the "payment" of resources whenever a field is upgraded. Here is what I have at the moment:

 

<?php
include("database.php");
include("createcamp.php");
include("login.php");
include("resformula.php");
$name = $_SESSION['username'];

/*This function levels up the field when the user asks for it. 
So far it's working perfectly! */
function resUpdt($resnum){
	global $conn, $name; // I know you told me, but I haven't got to fixing this one yet. Will do soon though.
	$result = mysql_query("SELECT camp FROM `Villages` WHERE username='$name'");
	$cnamearr = mysql_fetch_array($result);
	$cname = $cnamearr['camp'];
	$level_result = mysql_query("SELECT * FROM Villages 
	WHERE camp = '$cname'");
	$levelarr = mysql_fetch_array($level_result);
	$level = $levelarr["$resnum"];
	$newlevel = ($level + 1);
	$result = mysql_query("UPDATE Villages SET $resnum = '$newlevel' WHERE camp = '$cname'");
	}

/*This function holds the cost of each resource depending on 
which field is being upgraded. My problem is how to get the 
values here subtracted from the total number of resources
stored in the SQL table.*/

function cost($res){
switch($res){
	case oil:
		$oil_cost = 70;
		$iron_cost = 100;
		$wood_cost = 100;
		$crop_cost = 40;
	case iron:
		$oil_cost = 110;
		$iron_cost = 60;
		$wood_cost = 100;
		$crop_cost = 40;
	case wood:
		$oil_cost = 100;
		$iron_cost = 110;
		$wood_cost = 60;
		$crop_cost = 40;
	case crop:
		$oil_cost = 90;
		$wood_cost = 85;
		$iron_cost = 85;
		$crop_cost = 20;
	}
}

/*This is where I want to level-up the field (and that works),
but also subtract the costs of the upgrade (stored above)
from the total number. The way it's set up now is just 
something I jotted down to tell myself what I want it
to do in the end. It's probably not even close to working.*/
switch($_GET['action']) {
case 'crop_1': 
	resUpdt(crop1);
	$oil = totRes(Oil) - cost(oil);     
	$iron = totRes(Iron) - cost(iron);
	$wood = totRes(Wood) - cost(wood);
	$crop = totRes(Crop) - cost (crop);

	break;
/* This switch statement continues, but it's very large and it
does the same for ever case so I'm not going to post all of it. 

 

So there you have it! That's what I want to do, and I have to admit, it seems like a huge undertaking, but hopefully with a push in the right direction, I will find a way.

 

Thanks a lot for your help so far. This is definitely one of the best PHP help sites out there!