Jump to content


Photo

coding a scavenger hunt script; having a small problem


  • Please log in to reply
3 replies to this topic

#1 selfproduction

selfproduction
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 27 October 2006 - 02:31 PM

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:

$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();
?>

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

#2 Psycho

Psycho
  • Moderators
  • Move along, nothing to see here
  • 11,892 posts
  • LocationCanada

Posted 27 October 2006 - 04:26 PM

This line
$result = mysql_query($sql) or die(mysql_error());

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
$result2 = mysql_fetch_array($result);

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'";
The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net

#3 selfproduction

selfproduction
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 30 October 2006 - 03:16 PM

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:

<?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();
?>


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 :
<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>";
?>

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!


#4 Psycho

Psycho
  • Moderators
  • Move along, nothing to see here
  • 11,892 posts
  • LocationCanada

Posted 30 October 2006 - 09:00 PM

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:

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

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

<?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();
?>

The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users