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 Code:

<?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.

I am not sure about your error from briefly looking at the code, however I word of advice,

if you have a table with multiple cells of info you are not going to interact with then limit the cells you are selecting in your statements:

PHP Code:

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

//becomes

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


This is from the top of my head so im not 100% about the `'s ive used. But its along the right lines.

try using the correct variable in your query. $exp has no value in your SELECT query.

AND avoid using SELECT * in queries it is inefficient and leads to wasted resources on the server.

Alright, thank you for the tips. I tried using:

PHP Code:

"SELECT `id` FROM users WHERE id='$userid'" 


but it keeps it from working.

It works if I leave it as:

PHP Code:

"SELECT * FROM users WHERE id='$userid'" 


Not sure why.

Here is my updated code. It echoes out Level and Exp now, however after refreshing the page, the values are reset to 0. The original values in the database and shown on the page are: Level = 1, Exp = 100.

Any idea what could cause it to reset?
I'm not getting any errors.

Here is my updated code.

PHP Code:

<?php
session_start();
include('admin.php');
include('inc/connect.php');

$userid = $_SESSION['userid'];

// Temporary Pseudocode
if($level==1){
    $exptolvl = 100;
    if($exp==$exptolevel){
    $newlevel = 2;    
    }
}

// Temporary Pseudocode
// if (surfed){
// $newexp += 100;
// }

$expquery = mysql_query("SELECT * FROM users WHERE id='$userid'");
$row = mysql_num_rows($expquery);

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

echo $exp."<br />";
echo $level;

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

?>