Editing MySQL (section)

=== Filter rows ===

Well, now that we managed to isolate a few columns, let's see about filtering through rows. We'll return to selecting all columns tho. Conditions for which rows you want returned and which you don't are defined in the WHERE portion of the SELECT statement.

==== Strings ====

Execute this:

SELECT *<br>
FROM ss13_player<br>
WHERE lastadminrank = "Player"

This will return a list of people, who have their 'lastadminrank' column set to "Player". '''Note the single =''' So no admins will be in the returned list. So how do we get admins? Simple.

SELECT *<br>
FROM ss13_player<br>
WHERE lastadminrank != "Player"

As you know != means "not equal". It is however case sensitive. It also doesn't allow you to do any clever filtering. Let's look at LIKE:

==== LIKE ====

SELECT *<br>
FROM ss13_player<br>
WHERE ckey LIKE "%abc%"

This will return a list of players, whose ckey contains the letters 'abc'. The % signs before and after abc mean that anything can be located before or after abc. LIKE also ignores case, so all of the following will work: "'''abc'''n", "aocwegijaw'''abc'''maobr", "'''abc'''", "'''ABC'''", "'''AbC'''", "E'''Abc'''", "aWAEGaewgaWEG'''aBc'''aegawe". Your example might require you to replace the string abc with a different character combination, depending on which ckeys you have logged.

==== Numbers ====

Numbers use what you'd expect. >, >=, <=, <, =, !=

SELECT *<br>
FROM ss13_player<br>
WHERE id < 50

will return all lines where the id is lower than 50.

==== Date and time ====

The ss13_player table contains two fields which are of type 'datetime': firstseen and lastseen.

You can compare them directly with >, <, =, !=. '''Note the single ='''. For example: If you want to only select people who have only logged in once, and then never again:

SELECT *<br>
FROM ss13_player<br>
WHERE firstseen = lastseen

===== DATE() =====

You can however also select people who were first seen on a particular date. You'll need to use a function for that tho:

SELECT *<br>
FROM ss13_player<br>
WHERE DATE(firstseen) = "2013-04-19"

This will return a list of players who were first seen on 19 April 2013. If you want to get people who joined after April 17 2013, execute the following: (NOTE, This will only show people who joined on 18 April or later! Use >= if you want to include 17 April.)

SELECT *<br>
FROM ss13_player<br>
WHERE DATE(firstseen) > "2013-04-17"

===== DATEDIFF(), NOW() =====

Okay, so now you know how to select people who joined after a constant date. But what about if you want to only select people who joined in the last 7 days? The date keeps changing all the time, the date in the select statement, however, remains the same. The fix is pretty easy:

SELECT *<br>
FROM ss13_player<br>
WHERE DATEDIFF(Now(),firstseen) < 7

This will select everyone who joined 7 or fewer days ago.