Capturing Values From Database

[Smudly]

MySQL Version: 5.0.91

Hi, I am currently working on a Leveling system for users for my website. I'm creating an exp.php file that will contain how much exp is required for the next level up.

I am currently attempting to capture the user's Level which is stored inside the database. However, it isn't being set to my variable. I try echoing it out to see the output, but it comes up blank. Both Exp and Level come up blank. I'm obviously doing the query wrong, otherwise it would be showing.

The purpose of this file is to check what level the current user is (using sessions). Once it gets its level, there will be multiple if statements to determine how much more experience is required to level up. Once the experience is gained, the new level of the user will be increased by one.

A good part of this script is pseudocode (I'm currently not testing those sections).

<?php
session_start();
// This include redirects users to home page if they are not an admin
include('admin.php');
// Connect to database
include('inc/connect.php');

$userid = $_SESSION['userid'];

// Temporary Pseudocode, Ignore this for now.
if(level==1){
$exptolvl = 100;
	if($usersexp==$exptolevel){
	$level = 2;	
	}
}
// Temporary Pseudocode, Ignore this for now.
if (surfed){
$usersexp += 100;
}

// Query begins.
$expquery = mysql_query("SELECT * FROM users WHERE exp='$exp' AND level='$newlevel'");
$row = mysql_num_rows($expquery);

while($row = mysql_fetch_assoc($expquery))
{
    $exp = $row['exp'];
    $level = $row['level'];
}

echo $exp;
echo $level;


$inputexp = mysql_query("UPDATE users SET exp='$exp' and level='$newlevel' WHERE id='$userid'");

?>

And my table called "users" looks like:

id  	int(11)   	No   	    	 
username 	varchar(25) 	No  	  	 
level 	int(2) 	No  	1  	 
email 	varchar(64) 	No  	  	 
fname 	varchar(25) 	No  	  	 
lname 	varchar(25) 	No  	  	 
member 	tinyint(1) 	No  	  	 
referrer 	varchar(25) 	No  	  	 
joindate 	date 	No  	  	 
lastsurfed 	date 	No  	  	 
credits 	decimal(9,3) 	No  	  	 
exp 	int(6) 	No  	  	 
password 	varchar(32) 	No  	  	 
ip 	varchar(15) 	No  	  	 

I get no errors. It just isn't capturing the values correctly.

[NogDog]

Try something like this to see if there is a problem with the query:

// Query begins.
$query = "SELECT * FROM users WHERE exp='$exp' AND level='$newlevel'";
$expquery = mysql_query($query) or die(mysql_error() . "<br />\n$query");
if(mysql_num_rows($expquery) == 0)
{
   echo "No rows returned.<br />\n$query";
}
else
{
   while($row = mysql_fetch_assoc($expquery))
   {
      $exp = $row['exp'];
      $level = $row['level'];
      echo "<p>DEBUG: exp: '$exp', level: '$level'</p>\n";
   }
}

[big_nerd]

[edit]
I just saw NogDog's response, much better than mine, oh well.
[/edit]

I can give you your first tip:

For aguements sakes, Experience is 100, and Level is 10.

You are saying

$expquery = mysql_query("SELECT * FROM users WHERE exp='$exp' AND level='$newlevel'");

"Give me everyone that has Experience 100 and Levle 10" (Assuming $exp is 100 and $newlevel is 10)

$row = mysql_num_rows($expquery);

Then your assigning the number of returned rows (assume 20) to $row

while($row = mysql_fetch_assoc($expquery))

Then you are assigning $row as an array for EACH of the results - basically your last command was a waste of time, since you are over-writing $row already.

    $exp = $row['exp'];
    $level = $row['level'];

Then, 20x (assuming 20 users) you are making $level 10 and $experience 100 (assuming earlier values).

You are doing the SAME THING 20 times, as LEVEL and EXP are already known! (You used it in your SQL statement in the WHERE clause).

So - It is actually doing exactly what you are asking it to. Give me all users that have Experience of (assume) 100 and Level (assume) 10.

Then Make Experience 100 and Level 10

Then repeat above 19 more times (for a total of 20, hence 20 users)

..

I am not sure how the rest of your database works, but if you are able to explain a bit better (just explain like i did above, speak out the process) maybe we can help you fix it.

And provide examples, if possible, i.e. exp. 100 = level =10, 200 = 20, 300 = 30, etc.

Best of luck...