Jump to content

Archived

This topic is now archived and is closed to further replies.

selfproduction

coding a scavenger hunt script; having a small problem

Recommended Posts

I'm coding a scavenger hunt project with php & mysql; I've been able to get through all the bumps so far. People participating register a user account; login; etc. I have three tables; users, items, and found. When they find an item; the item name, their username, and a timestamp gets inserted into the 'found' table. I would also like to work in points; where when they find an item; 50 points gets added to the 'user_pts' field in the table 'users.' This is where I'm running into problems;

This is the code I have for that:

[code]$reward = "50";
$sql = "SELECT user_pts FROM users WHERE username = '$username'";
$userpts = mysql_query($sql) or die(mysql_error());
$new_pts = $userpts + $reward;

$sql = "UPDATE users SET user_pts = '$new_pts' WHERE username = '$username'";
$result = mysql_query($sql) or die(mysql_error());

echo "You were rewarded with ";
echo $userpts;
echo " points! Congrats!";
exit();
?>[/code]

The user_pts field is set up as int(10) unsigned zerofill null.

I know that the second echo will be $reward when I'm done; but when it wasn't working, I changed it to $userpts to try and figure out where the problem was. And I suppose I found it; It's outputting this on the found page: You were rewarded with Resource id #4 points! Congrats!

Can anyone help me out here? I have no idea where it's pulling "Resource id #4" from. >.< I'm pretty new to php; this is my third script I've coded. So take it easy on me. :P

Share this post


Link to post
Share on other sites
This line
[code]$result = mysql_query($sql) or die(mysql_error());[/code]

Creates a pointer to the results (Resource id #4) it does not set the $results variable to the results themselves.

You need to add this
[code]$result2 = mysql_fetch_array($result);[/code]

However, I think a better approach would be to add the points to each applicable found record. You could then get the total points for a user with this query:

$sql = "SELECT SUM(user_pts) as score FROM found WHERE username = '$username'";

Share this post


Link to post
Share on other sites
Oh, okay, I get it. Thanks!

And as for your suggestion, I wanted to go that route but don't understand exactly how to do it. This is the found script so far:

[code]<?php
include ('dbconfig.php');
include ('header2.php');

$itemfound=$_POST['item'];
    if(!$itemfound){
        echo "You must answer the riddle! Please do back and try again.";
    }

$sql = mysql_query("INSERT INTO found (finder, item, time)
        VALUES('$username', '$itemfound', now())")
        or die (mysql_error());

if(!$sql){
  echo "There has been an error. Please contact the webmaster.";
} else {
echo "You found the ";
echo $itemfound;
echo "! Congrats!";
}
$reward = "50";
$sql = "SELECT user_pts FROM users WHERE username = '$username'";
$userpts = mysql_query($sql) or die(mysql_error());
$new_pts = $userpts + $reward;

$sql = "UPDATE users SET user_pts = '$new_pts' WHERE username = '$username'";
$result = mysql_query($sql) or die(mysql_error());

echo "You were rewarded with ";
echo $userpts;
echo " points! Congrats!";
exit();
?>

[/code]

Could you further explain how to execute your suggestion? The reason I went with the users table to store user_pts is so that I could display a scoreboard easily.

Scoreboard.php :
[code]<table border="0" cellspacing="2" cellpadding="2">
<tr>
<th>Nickname</th>
<th>Username</th>
<th>Points</th>
</tr>

<?php

include 'dbconfig.php';
$query="SELECT * FROM users";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

$i=0;
while ($i < $num) {

$nickname=mysql_result($result,$i,"nickname");
$username=mysql_result($result,$i,"username");
$points=mysql_result($result,$i,"user_pts");
?>

<tr>
<td><? echo $nickname; ?></td>
<td><a href="http://www.neopets.com/randomfriend.phtml?user=<? echo $username; ?>"><? echo $username; ?></a></td>
<td><? echo $points; ?></td>
</tr>

<?php
$i++;
}


echo "</table>";
?>[/code]

If I may also ask, I've come across a couple questions.

1. Originally, I wanted this to work so that when a page loads it runs the script or if they click a certain link/image link, it runs the script and then loads a different page. I want the script to work for all found items without having to recode it for each item; and the page that loads afterwards would be different each time. Is there a way for me to do this at this point? Right now, the script is executed via a form/the user clicks a button.

2. I want to have the $itemfound be checked against the items table to ensure it exists before inserting it into the found table; but I can't get that to work. How would I go about that?

I'm really sorry to be such a bother and really appreciate any and all help!

Share this post


Link to post
Share on other sites
I'm not going to go through the entire code and rewrite it to work with the points in the found table, but I'll provide what I think you will need to get started. Plus, you should do a little research on how relational databases work. If you uderstand that then it will all make sense.

Anyway in the code to insert a found item, you need to add the points to the record like this:

[code]<?php
sql = mysql_query("INSERT INTO found (finder, item, time, points)
        VALUES('$username', '$itemfound', now(), '$points')")
?>[/code]

And you can remove all the rest of the code that gets the user's current points and updates the total in the user table. For your scoreboard you could get the total score for each user with this

[code]<?php
$query="SELECT users.*, SUM(found.points) as score
            FROM users, found
            WHERE users.username = found.finder
            GROUP BY users.username";

$result=mysql_query($query);

echo "<table>";

while ($row = mysql_result($result)) {
    echo "<tr>";
    echo "<td>$row[nickname]</td>";
    echo "<td><a href=\"http://www.neopets.com/randomfriend.phtml?user=$row[nickname]\">$username</a></td>";
    echo "<td>$row[score]</td>";
    echo "</tr>";

}

echo "</table>";
mysql_close();
?>[/code]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.