Hey everyone, I'm in the process of making a website for my World of Warcraft guild; within this website I'm adding a method ot keep track of events people attended, how much DKP they were worth and so on. To this end I'm creating a PHP file that allows me to easily add entries to display in the database. When a raid is created, a new row is created into my "raids" table which holds the name of the raid, the date/time, and the dkp worth (for those that don't know DKP is a method of buying items that drop in-game, you get DKP per raid etc). When adding a raid that occured, I need to modify the values in the "users" table as well as adding a new row to the raids table. Basically, people attend a raid, they are awarded CP and HP for attending the raid (the values are entered in a html form) and raids attended gets an addition of 1 (so I can do raidsattended/raidstotal*100). The users table contains two DKP rows (CP and HP) and a raids attended field. This is the UPDATE I am trying ot use currently: I need to set the "CP" field to its current value + the value in my form. This query works fine without the LIKE. $att is an input box on my form that consists of a list of people that attended the raid (in the form of "name1 name2 name3") and I wish to update CP, HP and raidsat for the people in this field. The only way I could see to do this is by using LIKE? Am I using it correctly? I'm rather new to SQL Hope I explained it ok :X Any help appreciated!
Hi. Your sql will not update the stats for all the users in the $att variable. The sql below would do it: UPDATE users SET cp=cp+$cpe, hp=hp+$hpe, raidsat=raidsat+1 WHERE username IN ('name1','name2','name3') Code (markup): I am not sure in what version of mysql the IN-part was added... If your mysql version does not support the IN-part, you could always add an OR between each name UPDATE users SET cp=cp+$cpe, hp=hp+$hpe, raidsat=raidsat+1 WHERE username ='name1' OR username='name2' OR username='name3' Code (markup): (You will have to do some basic string manipulation of the $att variable of course) Or you could do one update for each name... although that is not very efficient.
hi part of your pronlem is like will search for a phrase when two or more words are present. So if your names are in one big text field you need to break it up also allowing for names enetered with whitespaces and commas or so. Here is nice tutorial on how to do this explained on a serch function..... http://www.iamcal.com/publish/articles/php/search/ best of luck Expat
Thanks for the reply! However, the problem is that the $att variable will be holding up to 40 names that will change each time the script is run. @expat - The names are in one big text field and are broken up with a space between each so that would be my problem. I'll give that tutorial a read and have another go, thanks!
Use PHP Explode to break those names in an array then loop through it and perform an update for each individual one. Bit more effort for the database but it gets the job done without the complexity. UPDATE ... WHERE name = $current_name_from_loop Though preferably you should start using IDs.
Yeh, I wanted to use IDs originally -- however I wasn't entirely sure how to turn usernames entered in the field into memberids
You would have to learn using JOINs... If your database/website is going to be of a decent size you should at least set indexes at the appropriate columns. Setting indexes is easy to do in ie. phpMyAdmin.
$attendees = explode(" ", $att); foreach ($attendees as $value) { mysql_query("UPDATE users SET cp=cp+$cpe, hp=hp+$hpe, raidsat=raidsat+1 WHERE username = $value"); } Code (markup): Am I doing something wrong?
Looks OK technically. Not sure where those $cpe values etc. come from and whether they should be different for each attendee.
My bad, it works! Thanks so much everybody. To be an annoyance, I found another problem that I didn't even notice before. http://omni.oble.net/exiled/eps.php?go=viewmember&id=1 < It shows the raids people attended, however currently it's showing ALL the raids, even if they didn't attend. More bad usage of LIKE from me? mysql_query("SELECT * FROM users WHERE memberid = $id"); $getraids = mysql_query("SELECT * FROM raids WHERE att LIKE '%$username%' ORDER BY date"); while($r=mysql_fetch_array($getraids)){ extract($r); echo " $username <tr> <td valign=\"top\" width=\"15%\" >$date</td> <td valign=\"top\" width=\"35%\" ><a href=\"index.php?go=viewraid&id=$raidid\">$raid</a></td> <td valign=\"top\" width=\"30%\" >$note</td> <td valign=\"top\" width=\"10%\" >$cpe</td> <td valign=\"top\" width=\"10%\" >$hpe</td> </tr> "; } Code (markup): The users table contains the $username, $id is taken from the URL.
You need to put quotes around $value e.g UPDATE users SET cp=cp+$cpe, hp=hp+$hpe, raidsat=raidsat+1 WHERE username = '$value' Code (sql): Edit: Then again if it's working
NEVER!!!! I repeat... NEVER!!! ...use a $_GET value DIRECTLY in an SQL statement. It takes 2 seconds to DESTROY your database. Always sanitize it so if you are expecting an numerical ID only, at the very very least do if (is_numeric($_GET['id']) { SQL HERE } else { echo FUCK OFF HAcker! }
You could use mysql_real_escape_string() on the value from _GET which'll take care of some SQL injection avenues. Lookup mysql_real_escape_string(), addslashes and magic_quotes_gpc on php.net
We all were once and its surprising how tutorials skip on those things. http://www.unixwiz.net/techtips/sql-injection.html to see the trouble ahead... Mind you, you have good backups though, don't you?
Your current users table contains raidstat, which is a running count of the number of raids that user has attended. IMO, this is a bit messy and you can't really do querys like who have attended which raids because that information is never stored. A more natural way of doing it is to have 3 tables instead of 2. Assuming CP and HP are unique to each raid and does not vary from user to user. Your RAIDS table should have these fields: raidid (auto increment), name of raid, timestamp of the raid, CP and HP. Your USERS table should have these fields: playerid (auto increment), name of player and anything else that are unique to a player. To bring raids and users together, you need a RAIDSATTENDED table with only raidid and playerid. A relational database (mysql) can only handle one-to-many relationship, to handle a many-to-many relationship, you need the raids attended table. In other words, one Raid can be attended by many Users, one User can attend many Raids. If you want to know more, look up relational database normalizaton. One of the advantages of having a users table on its own is that you can add other useful Users related information at a later stage, e.g. their rank in the guild, their pvp rank, whatever. There is also no need to UPDATE the CP and HP fields in the USERS table because they are not recorded there. Yet you can still have that information to hand just by using one query: select users.name, sum(cp) as CP, sum(hp) as HP from raids, attended, users where raids.raidid=attended.raidid and attended.userid=users.userid group by users.userid Code (markup): I hope this makes some sense.