MySQL How to find a player in large database

[Saint]

Hello,

I have no idea whether tutorial like this was created or not, anyway I will show you how to quickly find a player in large database. We will use simple SQL queries.

Why did I write it?

Well, I remember when I was starting with SQL servers. Whenever I had to find player I was really angry, because I had to browse whole database to find him. Time passed and I learned SQL a bit and I decided that I'll try to help people who just started with SQL servers.

Step 1 - we are starting!

I assume that you already have an otserver and database. Go to 'players' and click SQL button. You'll see what's on screen below.

Step 2 - simple searching!

Now it's time to write your first SQL query to find certain player!

Now I'll explain what we've done.

SELECT * FROM `players` WHERE NAME = "GOD Ashri on Nahgor"

SELECT - this statement is used to select data from database.
SELECT * - it selects EVERYTHING
'players' - it's table's name
WHERE - clause is used to select data conditionally, by adding it to already existing SELECT query
WHERE NAME = "Nickname" - there's no need to explain, right? ;-)

LITTLE TIP!

You don't have to write manually column's names. You can use this little box and double click on name you wanted.

Step 3 - and what if we want to sort players by levels?

Well, it's simple, lets go!

SELECT `name`,`level` 
FROM `players` 
ORDER BY `level` DESC;

What does ORDER BY 'level' DESC mean? It sorts player from highest level to smallest. If you write it without 'DESC' it'll sort from smallest to highest. It's easy, isn't it? ;-)

Step 4 - useful queries (for lazy people)

Sort players by magic level

SELECT `name` , `level` , `maglevel`
FROM `players`
ORDER BY `maglevel` DESC ;

Last logged players

SELECT `name`,`lastlogin` 
FROM `players` 
ORDER BY `lastlogin` DESC;

I hope this will help. If it helped you, I'd be thankful if you rep++ me. This should motivate me to write more tutorials! ;-)

Yours,
Damian.

 

[exique]

Or you just search!
Simply just press the button called "Szukaj" on your pic or on English version "Search" Much much easier

 

[Saint]

Or you just search!
Simply just press the button called "Szukaj" on your pic or on English version "Search" Much much easier

Well, you are right, but in my opinion it's better to know simple SQL queries. OTServ owners should know what are they doing and how it works ;-) This "Search" function is based on queries as well so basically it doesn't matter whether you use "Search" or query. Of course using "Search" is much easier but what is the point of using something without knowing how it works? ;P

 

[ExibeR]

Well, you are right, but in my opinion it's better to know simple SQL queries. OTServ owners should know what are they doing and how it works ;-) This "Search" function is based on queries as well so basically it doesn't matter whether you use "Search" or query. Of course using "Search" is much easier but what is the point of using something without knowing how it works? ;P

But search buttons would be better for those OTServ owners that doesn't know what they're doing. Why do it the hard way?

 

[Saint]

I know that it's better for them, anyway they shoould spend some time and learn basics, shouldn't they? ;-)

 

[ExibeR]

Well, would be better to learn them how to adjust and add queries, as well as removing them, instead of learning them a harder way to search.

 

[exique]

Yes.. Still it's a lot easier searching...
And yes.. I know the query

 SELECT *
FROM `players`
WHERE `name` LIKE 'God Drimdrom'
LIMIT 0 , 30

 

[Saint]

Well... I tried to show people one way of doing things. Maybe it wasn't good choice and you're right.

 

[Lappen92]

ok thanks

 

[hodleo]

noobs guide lol there are many ways to search a player