Kryptix

I signed up on here back in 2003-04 under the alias 'xa0s' but I can't remember the password. Was the database wiped at all? I looked through the member list but couldn't find the name either. I'd love to read some of my own posts from when I was like 14-15 or so.

Well, the table structure is simply: user | exp_attack | exp_defense | exp_strength ... etc I then JOIN the actual player table WHERE user = ID. That returns results like below when viewing the Attack highscores (for example): Bob | 13,385,385 Jim | 13,200,382 Tom | 10,285,386 Sam | 8,284,693 I then use a function to turn the XP into the level so I can display both the level and XP. Bob | 99 | 13,385,385 Jim | 99 | 13,200,382 Tom | 96 | 10,285,386 Sam | 93 | 8,284,693 Any help would be really good. I have no-idea about this.

It does not report that unless there is a rather large amount of text entered. My server is using MySQL 5.0.32 You're probably using quotes (" or ') in the text you're writing which are counting as the query and causing syntax errors. For example: VALUES ('hi title',NOW(),'who's up for some syntax errors?','this is some text')",$connect); Use PHP's htmlspecialchars() around the varaible before querying it.

Do you mean the whitespace in mysql_query()? If so, I removed that above as it was annoying me. I should have mentioned it but like you, I honestly didn't think it would make any difference. It was kind of at the back of my mind but I didn't want to mention it in case it was a stupid suggestion. Glad you got it sorted anyway.

Does anyone know of any good tutorials (video or text) explaining what a index really is, how it works and when (where) it should be used? I haven't really touched them before and I don't understand the whole "FULL TEXT" button in PhpMyAdmin and so on. I'm not sure what to add a index to and what not to add a index too.

Thanks for your help Mchl. I'll trust your experience and leave it how it is.

Never done this before so bare with me. Basically, right now (for example) we have 3 tables... `rscd_friends`, `rscd_ignores` and `rscd_quests`. Each table has it's own query and then loops through the results: result = Query("SELECT `friend` FROM `rscd_friends` WHERE `user` = " + User()); while(result) { addFriend(friend); } result = Query("SELECT `ignore` FROM `rscd_ignores` WHERE `user` = " + User()); while(result) { addIgnore(ignore); } result = Query("SELECT `quest_id`, `quest_stage` FROM `rscd_quests` WHERE `user` = " + User()); while(result) { addQuest(quest_id, quest_stage); } Now is it possible to basically do all of that in one query and return results like: friend | ignore | quest_id | quest_stage Bob Lucy 1 7 John NULL 2 6 Amy NULL NULL NULL Paul NULL NULL NULL Tony NULL NULL NULL I would then need just one loop and a check to see if the column is NULL before doing anything. Something like: while (result) { if (friend != NULL) { addFriend(friend); } if (ignore != NULL) { addIgnore(ignore); } if (quest_id && quest_stage != NULL) ( addQuest(quest_id, quest_stage); } } My questions: Is it possible to do? Is it worth doing it or should I leave 3 different queries and 3 different loops? Is it more effient and/or quicker to process? There will generally be 0-100 FRIENDS, 0-10 IGNORES and 0-5 QUESTS so there could be a lot of NULLs but we're having some issues with the player loading time. Currently we're using like 10 queries per-login and when 200 people are all spam-clicking trying to login when we put the game up it uses a lot of queries and drags the CPU down. If it is possible, how would the query go? I tried a few queries but I couldn't g et it to work as each column has to have it's own check. For example, `rscd_friend`.`user` MUST equal User(), `rscd_ignore`.`user` MUST equal User() and `rscd_quests`.`user` MUST equal User(). The same is used with 2 other queries that I hope to turn into one as well so any advice would be much appreciated.

I've now selected each column rather than using a wildcard and it seems to be taking a fraction longer. I thought the whole point was to avoid bringing back data you didn't use, thus making it quicker? I've knocked off possibly 20 columns by doing it this way: SELECT `owner`, `group_id`, `login_date`, `login_ip`, `combat`, `x`, `y`, `fatigue`, `combatstyle`, `block_chat`, `block_private`, `block_trade`, `block_duel`, `block_global`, `cameraauto`, `onemouse`, `soundoff`, `showroof`, `autoscreenshot`, `combatwindow`, `haircolour`, `topcolour`, `trousercolour`, `skincolour`, `headsprite`, `bodysprite`, `male`, `skulled`, `kills`, `deaths`, `exp_attack`, `exp_defense`, `exp_strength`, `exp_hits`, `exp_ranged`, `exp_prayer`, `exp_magic`, `exp_cooking`, `exp_woodcut`, `exp_fletching`, `exp_fishing`, `exp_firemaking`, `exp_crafting`, `exp_smithing`, `exp_mining`, `exp_herblaw`, `exp_agility`, `exp_thieving`, `cur_attack`, `cur_defense`, `cur_strength`, `cur_hits`, `cur_ranged`, `cur_prayer`, `cur_magic`, `cur_cooking`, `cur_woodcut`, `cur_fletching`, `cur_fishing`, `cur_firemaking`, `cur_crafting`, `cur_smithing`, `cur_mining`, `cur_herblaw`, `cur_agility`, `cur_thieving` FROM `rscd_players` JOIN `rscd_experience` ON `rscd_players`.`user` = `rscd_experience`.`user` JOIN `rscd_curstats` ON `rscd_players`.`user` = `rscd_curstats`.`user` WHERE `rscd_players`.`user` = '13201441' It seems like everything I thought made stuff more effient actually doesn't unless it's possibly on a lot larger scale? I have 12,000 records in each 3 tables making 36,000 records total, I'm not sure if that makes any difference.

Original queries: SELECT * FROM `rscd_players` WHERE `user` = '13201441'; SELECT * FROM `rscd_experience` WHERE `user` = '13201441'; SELECT * FROM `rscd_curstats` WHERE `user` = '13201441'; New query: SELECT * FROM `rscd_players` JOIN `rscd_experience` ON `rscd_players`.`user` = `rscd_experience`.`user` JOIN `rscd_curstats` ON `rscd_players`.`user` = `rscd_curstats`.`user` WHERE `rscd_players`.`user` = '13201441' However, I believe they're pretty much the same. The first two queries (rscd_players and rscd_experience) were being averaging 0.0007 to query, whilst the 3rd (rscd_curstats) was taking an average of 0.0063-0.0080 even though there's the same amount of columns and rows, and every single column is a INT(2). That doesn't make sense to me considering rscd_players has over 15 more columns and lots of VARCHAR(255)'s yet only takes 0.0007 on average. Anyway, the JOIN query was taking 0.0067-0.0090 on average. Still, that is very, very little difference. Is there really much difference when joining queries or should I just make life easier and use multiple queries every time?

As you probably know by now I run a game and I'm trying to get the most effient queries as possible. When they login it queries the database about 8 times and I'm sure after the help from you I can reduce this to 4 queries with JOINs, but is less queries always better? How would I test which is quicker and more effient? Is it a case of just querying and adding up the total query time and do a side-by-side comparison?

Cheers for the attempt but it errored as well: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') as kd FROM `rscd_players` ORDER BY kd DESC LIMIT 0, 30' at line 2

SELECT `username`, `kills`, `deaths`, CASE WHEN `deaths` OR `kills` = '0' THEN `kills` AS 'kd' ELSE `kills` / `deaths` AS 'kd' END FROM `rscd_players` ORDER BY `kd` DESC #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS 'kd' ELSE `kills` / `deaths` AS 'kd' END FROM `rscd_players` ORDER BY `kd` DE' at line 1 Could someone help me with that please? Basically, if `deaths` or `kills = '0' then `kills` should be `kd`, else divide `kills` by `deaths` and set that as `kd`. Where am I going wrong?

Out of curiosity, have you tried LIMIT 0, 100 to see if it's defaulting at 1 or whatever?

Have you used PhpMyAdmin? Do they display OK on there? The code looks absolutely fine.

I edited the above post again. Sorry. Also wrap `tags` around users please.

I'm not sure but try using mysql_fetch_assoc() instead of mysql_fetch_object() as well as $person['id'] rather than $person->id. I can't think of any other reason in all honesty. :-\ EDIT: I've just seen you haven't declared the connection when querying. I believe you need to use: $query = mysql_query("SELECT * FROM users WHERE `Country`='Australia'", $connection);

Not really sure what to title this topic. However, I've made some Highscores for my RuneScape Classic private server but I'm unsure on how to do something. Basically, I'm half way through making a character information page which will list their items and skill levels but next to the actual level (and experience) I want to put their global rank that they appear in the Highscores for that skill. I managed to generate the rank for the current Highscores page but simply using $rank++; as it loops through the results. How would I go about doing it for the character information page? All the experience is stored in a separate table (`rscd_experience`) whilst the players name and other details are stored in `rscd_players`. I also would like to allow players to search for a users name which will put them at the position they're in and continue the Highscores from that position rather than the top if that makes any sense? For example, you search for a character and it shows their rank in every skill separately: After you click onto one of those stats it shows the Highscores from the position that he's currently at (showing some results above and some below): Could someone explain to me how that's done? I'm pretty sure it will be a cron-job or something which generates all the data every few hours so they can actually store the rank and it only changes when they do another big looping query. Is it possible to do it on-the-fly?

Great, I'll give it a go. Thank-you.

The board doesn't let you edit the post and after I had posted it I kept playing around with it and got further by myself, I obviously wouldn't want something answered that I've already done so I updated the thread. I'm sorry but what should I have done, waited until you answer a question I didn't need answering any more? Does aliasing columns slow things down at all? It actually makes writing PHP a LOT easier to understand but I want the query to be as quick as possible.

Now taken it down to: SELECT `u`.`group_id` AS 'u.group_id', `u`.`username` AS 'u.username', `u`.`num_posts` AS 'u.num_posts', `u`.`last_post` AS 'u.last_post', `u`.`registered` AS 'u.registered', `u`.`registration_ip` AS 'u.registration_ip', `u`.`last_visit` AS 'u.last_visit', `u`.`sub_expires` AS 'u.sub_expires', `p`.`username` AS 'p.username', `p`.`group_id` AS 'p.group_id', `p`.`owner` AS 'p.owner', `p`.`combat` AS 'p.combat', `p`.`skill_total` AS 'p.skill_total', `p`.`x` AS 'p.x', `p`.`y` AS 'p.y', `p`.`fatigue` AS 'p.fatigue', `p`.`combatstyle` AS 'p.combatstyle', `p`.`block_chat` AS 'p.block_chat', `p`.`block_private` AS 'p.block_private', `p`.`block_trade` AS 'p.block_trade', `p`.`block_duel` AS 'p.block_duel', `p`.`block_global` AS 'p.block_global', `p`.`cameraauto` AS 'p.cameraauto', `p`.`onemouse` AS 'p.onemouse', `p`.`soundoff` AS 'p.soundoff', `p`.`showroof` AS 'p.showroof', `p`.`autoscreenshot` AS 'p.autoscreenshot', `p`.`combatwindow` AS 'p.combatwindow', `p`.`skulled` AS 'p.skulled', `p`.`creation_date` AS 'p.creation_date', `p`.`creation_ip` AS 'p.creation_ip', `p`.`login_date` AS 'p.login_date', `p`.`login_ip` AS 'p.login_ip', `p`.`online` AS 'p.online', `p`.`banned` AS 'p.banned', `p`.`muted` AS 'p.muted', `p`.`deaths` AS 'p.deaths', `p`.`kills` AS 'p.kills', `p`.`highscores` AS 'p.highscores', `e`.`exp_attack` AS 'e.exp_attack', `e`.`exp_defense` AS 'e.exp_defense', `e`.`exp_strength` AS 'e.exp_strength', `e`.`exp_hits` AS 'e.exp_hits', `e`.`exp_ranged` AS 'e.exp_ranged', `e`.`exp_prayer` AS 'e.exp_prayer', `e`.`exp_magic` AS 'e.exp_magic', `e`.`exp_cooking` AS 'e.exp_cooking', `e`.`exp_woodcut` AS 'e.exp_woodcut', `e`.`exp_fletching` AS 'e.exp_fletching', `e`.`exp_fishing` AS 'e.exp_fishing', `e`.`exp_firemaking` AS 'e.exp_firemaking', `e`.`exp_crafting` AS 'e.exp_crafting', `e`.`exp_smithing` AS 'e.exp_smithing', `e`.`exp_mining` AS 'e.exp_mining', `e`.`exp_herblaw` AS 'e.exp_herblaw', `e`.`exp_thieving` AS 'e.exp_thieving' FROM `rscd_players` AS p JOIN `rscd_experience` AS e ON `p`.`user` = `e`.`user` JOIN `users` AS u ON `p`.`owner` = `u`.`id` WHERE `p`.`username` = 'Kryptix' Is there anyway that I can get rid of all the AS in the column selects?

For example: SELECT `users`.`group_id` AS 'users.group_id', `users`.`username` AS 'users.username', `users`.`num_posts` AS 'users.num_posts', `users`.`last_post` AS 'users.last_post', `users`.`registered` AS 'users.registered', `users`.`registration_ip` AS 'users.registration_ip', `users`.`last_visit` AS 'users.last_visit', `users`.`sub_expires` AS 'users.sub_expires', `rscd_players`.`username` AS 'rscd_players.username', `rscd_players`.`group_id` AS 'rscd_players.group_id', `rscd_players`.`owner` AS 'rscd_players.owner', `rscd_players`.`combat` AS 'rscd_players.combat', `rscd_players`.`skill_total` AS 'rscd_players.skill_total', `rscd_players`.`x` AS 'rscd_players.x', `rscd_players`.`y` AS 'rscd_players.y', `rscd_players`.`fatigue` AS 'rscd_players.fatigue', `rscd_players`.`combatstyle` AS 'rscd_players.combatstyle', `rscd_players`.`block_chat` AS 'rscd_players.block_chat', `rscd_players`.`block_private` AS 'rscd_players.block_private', `rscd_players`.`block_trade` AS 'rscd_players.block_trade', `rscd_players`.`block_duel` AS 'rscd_players.block_duel', `rscd_players`.`block_global` AS 'rscd_players.block_global', `rscd_players`.`cameraauto` AS 'rscd_players.cameraauto', `rscd_players`.`onemouse` AS 'rscd_players.onemouse', `rscd_players`.`soundoff` AS 'rscd_players.soundoff', `rscd_players`.`showroof` AS 'rscd_players.showroof', `rscd_players`.`autoscreenshot` AS 'rscd_players.autoscreenshot', `rscd_players`.`combatwindow` AS 'rscd_players.combatwindow', `rscd_players`.`skulled` AS 'rscd_players.skulled', `rscd_players`.`creation_date` AS 'rscd_players.creation_date', `rscd_players`.`creation_ip` AS 'rscd_players.creation_ip', `rscd_players`.`login_date` AS 'rscd_players.login_date', `rscd_players`.`login_ip` AS 'rscd_players.login_ip', `rscd_players`.`online` AS 'rscd_players.online', `rscd_players`.`banned` AS 'rscd_players.banned', `rscd_players`.`muted` AS 'rscd_players.muted', `rscd_players`.`deaths` AS 'rscd_players.deaths', `rscd_players`.`kills` AS 'rscd_players.kills', `rscd_players`.`highscores` AS 'rscd_players.highscores', `rscd_experience`.`exp_attack` AS 'rscd_experience.exp_attack', `rscd_experience`.`exp_defense` AS 'rscd_experience.exp_defense', `rscd_experience`.`exp_strength` AS 'rscd_experience.exp_strength', `rscd_experience`.`exp_hits` AS 'rscd_experience.exp_hits', `rscd_experience`.`exp_ranged` AS 'rscd_experience.exp_ranged', `rscd_experience`.`exp_prayer` AS 'rscd_experience.exp_prayer', `rscd_experience`.`exp_magic` AS 'rscd_experience.exp_magic', `rscd_experience`.`exp_cooking` AS 'rscd_experience.exp_cooking', `rscd_experience`.`exp_woodcut` AS 'rscd_experience.exp_woodcut', `rscd_experience`.`exp_fletching` AS 'rscd_experience.exp_fletching', `rscd_experience`.`exp_fishing` AS 'rscd_experience.exp_fishing', `rscd_experience`.`exp_firemaking` AS 'rscd_experience.exp_firemaking', `rscd_experience`.`exp_crafting` AS 'rscd_experience.exp_crafting', `rscd_experience`.`exp_smithing` AS 'rscd_experience.exp_smithing', `rscd_experience`.`exp_mining` AS 'rscd_experience.exp_mining', `rscd_experience`.`exp_herblaw` AS 'rscd_experience.exp_herblaw', `rscd_experience`.`exp_thieving` AS 'rscd_experience.exp_thieving' FROM `rscd_players` JOIN `rscd_experience` ON `rscd_players`.`user` = `rscd_experience`.`user` JOIN `users` ON `rscd_players`.`owner` = `users`.`id` WHERE `rscd_players`.`username` = 'Kryptix'

Is it worth itemizing all the columns if you're only not using maybe 10 out of 50 (ish)? With `rscd_experience` I'm using all but 2 columns. Is it worth itemizing them or should I just use *? I don't mind, it's already done, but I wanna do the quickest way. Here's the current query: SELECT `users`.`group_id`, `users`.`username`, `users`.`num_posts`, `users`.`last_post`, `users`.`registered`, `users`.`registration_ip`, `users`.`last_visit`, `users`.`sub_expires`, `rscd_players`.`username`, `rscd_players`.`group_id`, `rscd_players`.`owner`, `rscd_players`.`combat`, `rscd_players`.`skill_total`, `rscd_players`.`x`, `rscd_players`.`y`, `rscd_players`.`fatigue`, `rscd_players`.`combatstyle`, `rscd_players`.`block_chat`, `rscd_players`.`block_private`, `rscd_players`.`block_trade`, `rscd_players`.`block_duel`, `rscd_players`.`block_global`, `rscd_players`.`cameraauto`, `rscd_players`.`onemouse`, `rscd_players`.`soundoff`, `rscd_players`.`showroof`, `rscd_players`.`autoscreenshot`, `rscd_players`.`combatwindow`, `rscd_players`.`skulled`, `rscd_players`.`creation_date`, `rscd_players`.`creation_ip`, `rscd_players`.`login_date`, `rscd_players`.`login_ip`, `rscd_players`.`online`, `rscd_players`.`banned`, `rscd_players`.`muted`, `rscd_players`.`deaths`, `rscd_players`.`kills`, `rscd_players`.`highscores`, `rscd_experience`.`exp_attack`, `rscd_experience`.`exp_defense`, `rscd_experience`.`exp_strength`, `rscd_experience`.`exp_hits`, `rscd_experience`.`exp_ranged`, `rscd_experience`.`exp_prayer`, `rscd_experience`.`exp_magic`, `rscd_experience`.`exp_cooking`, `rscd_experience`.`exp_woodcut`, `rscd_experience`.`exp_fletching`, `rscd_experience`.`exp_fishing`, `rscd_experience`.`exp_firemaking`, `rscd_experience`.`exp_crafting`, `rscd_experience`.`exp_smithing`, `rscd_experience`.`exp_mining`, `rscd_experience`.`exp_herblaw`, `rscd_experience`.`exp_agility`, `rscd_experience`.`exp_thieving` FROM `rscd_players` JOIN `rscd_experience` ON `rscd_players`.`user` = `rscd_experience`.`user` JOIN `users` ON `rscd_players`.`owner` = `users`.`id` WHERE `rscd_players`.`username` = 'Kryptix' Also, where would I put DISTINCT() so I only pull back one row in case there's a double somewhere? One more thing... I have a duplicate column name in `users` as I have in `rscd_players`, they both appear as `username` when I query in PhpMyAdmin. How would I call each one when using PHP? Is it possible to use $query['user'.'username'] or $query['user.username'] to split them up or do I have to put AS 'user.group_id' (etc) after every column I select in the query?

I've been working on this and came up with: SELECT * FROM `rscd_players` INNER JOIN `rscd_experience` ON `rscd_players`.`user` = `rscd_experience`.`user` JOIN `users` ON `rscd_players`.`owner` = `users`.`id` WHERE `rscd_players`.`user` = '265184190476866' However, what difference would INNER JOIN make compared to just JOIN and OUTER JOIN? I don't see any difference what-so-ever. Is there anyway to make it faster (or less resourceful) or is that perfect (except * obviously)?

Could you possibly give me an example? I'm quite new to this.

I believe so. Could you show me how I could do that with the linking? I can select everything else and use all the WHERE clauses. Thanks.