Kryptix

I need to select data from 3 tables for one user. They all have a link but this query is too confusing for me. The tables are: rscd_players rscd_experience users It will find the user WHERE rscd.players.user = 'hello', it then needs to find the matching result in rscd_experiences WHERE rscd_experience.user = rscd_players.user and then find the matching result in users WHERE users.id = rscd_players.owner rscd_experience.user is the same as rscd_players.user users.id is the same as rscd_experience.owner I need to select the following: rscd_players.user rscd_players.username rscd_players.combat rscd_players.skill_total rscd_players.group_id rscd_players.banned rscd_players.muted rscd_players.login_date rscd_players.register_date rscd_players.register_ip rscd_players.login_ip 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 users.registered users.username users.group_id Obviously if you show me how to select just one I'll be able to add the rest myself. If you can help me with this I'll love you for ever, I really don't want to use 3 queries!

I need to add up multiple columns and then order it DESC the total amount. For example... column1 + column2 + column3 + column4 + column5 I need to add up the total values (all are INTs) and then order the records by the highest amount down to the lowest. How would I do that?

Here's what your help produced: http://www.rscemulation.net/highscores.html?highscores=skill_total Thanks!

Is there anyway that you could first find his position and then use LIMIT BY $usersPosition - 50, %usersPosition + 50 or have I got this all wrong?

This is the query I'm currently using to display the top 500 in a certain skill (defined by $_GET['highscores']) but I want to change it so you will have $_GET['character'] which searches `rscd_players` to check it exists, and then does a JOIN with $_GET['highscores'] to show the selected stat: SELECT `rscd_players`.`username`, `rscd_players`.`owner`, `rscd_players`.`user`, `rscd_experience`.`exp_" . $db->escape(trim(strtolower($_GET['highscores']))) . "` FROM `rscd_experience` JOIN `rscd_players` ON `rscd_experience`.`user` = `rscd_players`.`user` WHERE `rscd_players`.`highscores` = '0' AND `rscd_players`.`banned` = '0' AND (`rscd_players`.`group_id` = '4' OR '5') AND `rscd_players`.`login_date` >= UNIX_TIMESTAMP() - 2678400 ORDER BY `rscd_experience`.`exp_" . $db->escape(trim(strtolower($_GET['highscores']))) . "` DESC, `rscd_players`.`creation_date` ASC LIMIT 0, " . $db->escape($optionsArray['highscores_limit'])

Nice one, I was messing around with it and it clocked on just before you replied.

Nope, as it's constantly updating. I have to ORDER BY.

How would I order a SELECT by one column followed by another and another? So for example, I want to order them by their name first, then their age and then their DoB. I tried ORDER BY `name`, `age`, `DoB` but it doesn't give the results I'm expecting. Am I doing it right? Also, can you make say the name DESC, and then the age ASC separately?

I'm writing some highscores for a game and I want to allow users to search their own name. which will show them as if they were in the middle of the table, showing 50 results before and 50 results after in the actual position. Is there a way to do this in a query of it it multiple queries and using PHP?

Bingo. I knew it would be something simple. Thanks mate.

Solved. It was just a ADD INDEX.

WHERE `group_id` = '4' OR '5' AND `banned` = '0' AND `highscores` = '0' How would I do the above? I need to check whether the `group_id` is 4 OR 5 but it can't be done like that.

This is nothing to do with MySQL.

In all honesty, I'm not sure what that means. I'm pretty new to MySQL. How would I tell and what does 'indexes' do?

Thanks mate. This is the query now: SELECT `rscd_players`.`username`, `rscd_players`.`owner`, `rscd_players`.`user`, `rscd_experience`.`exp_attack` FROM `rscd_experience` JOIN `rscd_players` ON `rscd_experience`.`user` = `rscd_players`.`user` WHERE `rscd_players`.`highscores` = '0' ORDER BY `rscd_experience`.`exp_attack` DESC LIMIT 0, 100 This is perfect but it's taking 14 seconds to do the query on localhost. There's 10,000 entries in rscd_players and 10,000 entries in rscd_experience

Thanks cags, this is what I have now: SELECT `user`, `exp_attack` FROM `rscd_experience` JOIN `rscd_players` ON `rscd_experience`.`user` = `rscd_players`.`user` WHERE `rscd_players`.`highscores` = '0' #1052 - Column 'user' in field list is ambiguous How do I get around that? Also, how would I also select 'username' and 'owner' from 'rscd_players' from that same record that 'highscores' is in within the same query?

Blah, I'm not sure how to do this one but I know as soon as I see the query I'll kick myself. I have two tables, 'experience' and 'player' I need to select the columns 'id' and 'attack' from the first table 'experience' but ONLY if the 'player' table has the column 'on' set to '0' where the 'id' of 'experience' matches the 'id' of 'player' Does that make sense? I'm basically making a highscore list and I need to select the top 20 from 'experience' but only if the player had 'on' set to '0' in his 'player' table where all the settings are stored. Any help would be good. I'm stuck.

He never got back to me. Anyone else?

Yeah, I didn't think anyone would know.

Trust be told, I don't actually know. I was using two different versions of the game. One made it crash and one didn't. To diagnose the difference would take hours as it queries something like 50-200 times a minute and it used to sometimes take over 3 hours to crash. It would close MySQL though with no errors what-so-ever. I even updated to the latest version.

function encode_username($username) { $username = strtolower($username); $clean = ''; for($i = 0;$i < strlen($username);$i++) { $c = ord($username{$i}); if($c >= 97 && $c <= 122) { $clean .= chr($c); } else if($c >= 48 && $c <= 57) { $clean .= chr($c); } else { $clean .= ' '; } } $clean = trim($clean); if(strlen($clean) > 12) { $clean = substr($clean, 0, 12); } $hash = '0'; for($i = 0;$i < strlen($clean);$i++) { $c = ord($clean{$i}); $hash = bcmul($hash, 37); if($c >= 97 && $c <= 122) { $hash = bcadd($hash, (1 + $c) - 97); } else if($c >= 48 && $c <= 57) { $hash = bcadd($hash, (27 + $c) - 48); } } return $hash; } If Apache has been started for (around) a day the following function causes the script to time out. If I restart Apache it works perfectly. Sometimes it takes 2-3 days to 'break' and other times it's just 1 day. Has anyone got any ideas? It's really starting to get annoying.

Anyone?

It turns out it was a query causing it to crash, but I still don't know which one. No error was given what-so-ever, it just shut down MySQL. All sorted now though.

Alright, thanks for your help (again) mate.

Thanks. Here's the current code that I've just written. It's working fine but is there anyway to make it more efficient? } else if ($page == "test") { $check = $db->query("SELECT `amount` FROM `rscd_bank` WHERE `owner` = '" . $pun_user['id'] . "' AND `id` = '1295'") or die("error"); $check = $db->fetch_assoc($check); $check = $check['amount']; if ($check == 0) { $slot = $db->query("SELECT `slot` FROM `rscd_bank` WHERE `owner` = '" . $pun_user['id'] . "' ORDER BY `slot` DESC LIMIT 1") or die("error"); $slot = $db->fetch_assoc($slot); $slot = $slot['slot'] + 1; $insert = $db->query("INSERT INTO `rscd_bank` (`owner` , `id` , `amount` , `slot`) VALUES ('" . $pun_user['id'] . "', '1295', '1', '" . $slot . "');"); $slot++; } else { $check++; $update = $db->query("UPDATE `rscd_bank` SET `amount` = '" . $check . "' WHERE `owner` = '" . $pun_user['id'] . "' AND `id` = '1295'") or die("error"); } ?> <div class='menu'> <div class='m_title'>Subscribe</div> <div class='m_content jtext'> Thank you for your purchase! <?php if ($check == 0) { echo "A new RSCEmulation Subscription Card has been added into slot " . $slot . " of your bank account."; } else { echo "Another RSCEmulation Subscription Card has been added to your bank account."; } ?> </div> </div>