...

View Full Version : Capturing Values From Database



Smudly
06-16-2010, 04:10 PM
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.

Fou-Lu
06-16-2010, 04:22 PM
Is this all for a single user ($_SESSION['userid'])?
This is likely the problem why the printout won't work:



// 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;

* Note that both the $exp and $level variables printed will only ever be the last record from sql.

With both $exp and $newlevel being null, the effective usage of the query returns no results (according to your structure, level can never be interpreted as 0 as its default value is always 1 or more).
The query would likely be more benefitial for using a single user, if the intent is to only use a single user (which it looks to me like it is). So, simply query for the exp and level fields of the user table where id = $userid.
Now, if your using an algorithm to determine experience, I'd actually not store the level at all and only the experience. This way, you can dynamically determine the level of a user based on a particular experience value. If gains are shifted based on experience, you can simply run an update query with an algorithm to update the particular value, and no selection will ever be required.

Smudly
06-16-2010, 05:25 PM
Alright, thank you for the tips. I tried using:


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

but it keeps it from working.

It works if I leave it as:


"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
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'");

?>

Fou-Lu
06-16-2010, 05:39 PM
This select: SELECT `id` FROM users WHERE id='$userid', likely works, but you don't retrieve the 'id' from the $row. * will work, or you can change it to exp and level SELECT `exp`, `level` FROM users WHERE `id` = '$userid' (userid should only be surrounded with '' if its a string, if its a number, go without).

$newlevel and $newexp are both undefined, which is interpreted as the closest datatype to the one available for the column. Running the update sets exp and level to 0, since the if block will never execute (there is no $level, or $exp values to start with, so the if will always be false for both).

How do you increment the experience values? Are they always += 100 for example, or a mathematical algorithm of sorts, or are they hard coded values?

Smudly
06-16-2010, 06:04 PM
The exp will always be += 100. Everytime the user views someone's website, they gain 100 exp.

I fixed the if statement, and it increments the exp and level as it is supposed to.

Now to implement the if statement that determines if a page has been viewed.

I'll keep working on it, and will most likely create a separate thread if I need additional assistance.

Thanks very much!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum