selfproduction Posted October 27, 2006 Share Posted October 27, 2006 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 Quote Link to comment Share on other sites More sharing options...
Psycho Posted October 27, 2006 Share Posted October 27, 2006 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'"; Quote Link to comment Share on other sites More sharing options...
selfproduction Posted October 30, 2006 Author Share Posted October 30, 2006 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><?phpinclude '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! Quote Link to comment Share on other sites More sharing options...
Psycho Posted October 30, 2006 Share Posted October 30, 2006 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]<?phpsql = 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] Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.