Database Administration
Through the database, you can do many things to your site. From changing membership levels to renaming members and from deleting specific posts to updating groups of members. So in this simple guide, we will cover some of the most common questions we get on how to manage the database.
IMPORTANT NOTE: Any command executed here will most likely have an impact on your system, so when trying them out, please be sure to create a backup of your database and verify how the query you will execute is written so that there are no errors that could cause other issues on the database since any change in the database in most cases is permanent.
SELECTING THE DATABASE AND TABLE TO USE
1. To be able to start editing your database, we first need to access it. To do this, first select the MySQL Database Manage from the Developer Hub:
2. Click on MySQL Database:
3. When we are viewing the database manager, you should see a list of all of your databases (Typically 2 or 3). Click on your database from the Left sidebar:
4. After selecting the database correctly, there should be a long list of tables in the left sidebar, select the users_data table from the list:
5. After selecting the users_data table. Click on the SQL Tab on the top center of the page:
6. (The query code should mention users_data somewhere as seen below):
7. In this box we will do all of our tests
SELECTING INFORMATION
Since we are now using the users_data table from your database, If you wish to see all of your members, we can use the SELECT MySQL statement and then execute the query we type and pressing CTRL + ENTER:
SELECT * FROM users_data
The query above will show all members from your users_data table. The asterisk (*) means ALL in many languages including SQL. In this case, it means all columns of the users_data table.
If you wished to only see the First and Last name along with the email address, you could change the asterisk for the specific columns like this:
SELECT first_name, last_name, email FROM users_data
Now if we wished to see all members that have a membership level of 1, we would do the following by using the WHERE clause (The membership level variable in the database is called subscription_id):
SELECT first_name, last_name, email FROM users_data WHERE subscription_id = 1
The WHERE clause is used to set what parameters need to match in order for the query to output a result. If the members match the WHERE clause, they will appear in the output result. If they do not match, they will be skipped. But what happens if you not only want to see all members that have a membership level of 1 but also are active members. We would do the following by using the AND operator:
SELECT first_name, last_name, email FROM users_data WHERE subscription_id = 1 AND active = 2
Here we use the AND operator to tell the query that the WHERE clause is extended to not only matching a member with a membership level of 1, but also will need to match members that have an active state (Active state value is 2). Apart from the AND operator, we have the NOT and OR operators.
The NOT operator works by negating any value found directly after it, so for example in the following line:
SELECT first_name, last_name, email FROM users_data WHERE NOT subscription_id = 1
We see that the NOT is used to tell the WHERE clause to find all members that ARE NOT of a membership level equal to 1. Another way of doing the NOT operator is by concatenating the exclamation sign with the equal sign like in the following example:
SELECT first_name, last_name, email FROM users_data WHERE subscription_id != 1
This query simply means the same as the previous one. The difference is, instead of writing the word NOT into the query, we are simply appending an exclamation mark to the equal sign. Now in the following query, we can see the use of OR with a NOT:
SELECT first_name, last_name, email, subscription_id FROM users_data WHERE NOT subscription_id = 1 OR active = 2
Here we see that the WHERE clause will match all members that are NOT from the membership level with a value of OR any members that are active (Have a value of 2 in the active column). So if the member matches one or the other, it will send the information to the output result.
What if we wanted to find all members between a range of values. We could do it like this:
SELECT first_name, last_name, email FROM users_data WHERE subscription_id BETWEEN 1 AND 4
In this example, it will show all members that have a membership level ranging from 1 to 4 (Including the number 1 and 4. So all members with values in 1,2,3,4 will appear). Another way of setting a range but at the same time specifying multiple values to be matched would be by using the IN operator.
SELECT first_name, last_name, email FROM users_data WHERE subscription_id IN (1,2,3,4)
The difference is that with the IN operator you can set which values to match. The IN operator its like using multiple OR operators.
UPDATING INFORMATION
So you want to update information on your database or simply want to fix a mistake previously made. The way we change information on the system is by using the UPDATE MySQL statement.
Let's say you wish to migrate all of your members from their membership level 2 to membership level 1. We can do this by running the following query:
UPDATE users_data SET subscription_id = 2 WHERE subscription_id = 1
This will look for all members that match a membership level of 2 and will turn their membership level to 1.
Now, what if we did not want to apply this to all members, only to members that are active members, we would then mix the examples from the SELECT statements with the UPDATE statement like so:
UPDATE users_data SET subscription_id =3 WHERE subscription_id =0 AND active = 2
Here we are simply doing the same as the previous query, except that it is now also looking for members that are active. With the UPDATE statement, we can use it with the AND, OR, IN and BETWEEN operators, similar to the examples from the SELECT statement.
DELETING INFORMATION
Deleting is a permanent action and it is done with the DELETE MySQL statement. If you wished to delete all members that had a membership level of 5 for example, we would run the following query:
DELETE FROM users_data WHERE subscription_id = 5
This WILL delete all members that have a membership level of 5.
If you wanted to delete all members that had a membership level of 3 and were NOT an active member, we would do the following:
DELETE FROM users_data WHERE subscription_id = 5 AND active != 2
As you can see, I am using the shorthand way of the NOT operator.
