DAT Blog Post Six

Week Six Sessions 9-10

What Have I Learned?

This week I learned about different types of Join statements. These being:

Inner Joins – Returns all records that have matching values in both tables

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNERJOINCustomers ON Orders.CustomerID = Customers.CustomerID;

Left Joins – Is used to return all records from the left table and the matching records from the right table.

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFTJOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDERBY Customers.CustomerName;

Right Joins – Is used to return all records from the right table and the matching records from the left table.

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHTJOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDERBY Orders.OrderID;

I also made a big start in coding the functionality side of my game. Through this, I have learned a lot about c# and how to run SQL procedures within c# to manipulate data.

Here is an example of how I would update some data:

 public void SetLoggedIn()
        {
            //"UPDATE tbl_user SET user_loginStatus = 1 WHERE(userID = @user)"
            Connect database = new Connect();
            String username = usernameField.Text;
            DataTable table = new DataTable();
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            MySqlCommand command = new MySqlCommand("UPDATE tbl_user SET user_loginStatus = 1 WHERE(username = @user)", database.getConnection());
            command.Parameters.Add("user", MySqlDbType.VarChar).Value = username;
            adapter.SelectCommand = command;
            adapter.Fill(table);
        }

Here is an example to see if the username already exists:

        public Boolean checkUsername()
        {
            Connect database = new Connect();
            String username = usernameField.Text;

            DataTable table = new DataTable();
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            MySqlCommand command = new MySqlCommand("SELECT * From tbl_user WHERE `username` = @uname", database.getConnection());
            command.Parameters.Add("uname", MySqlDbType.VarChar).Value = username;
            adapter.SelectCommand = command;
            adapter.Fill(table);

            /*Check to see if username already exists*/
            if (table.Rows.Count > 0)
            {
                /*Login Success*/
                return true;
            }
            else
            {
                /*Login Error*/
                return false;
            }
        }

Why Have I learned This?

I believe that I have learned this as it is an important skill to learn when we want to select data from multiple tables. This will be useful when I want to create my leaderboard. I can select matching data from the user and the player tables to retrieve usernames, scores, etc.

How Have I learned This?

I have learned this by doing some research into what Join statements are and how I implement them into my code. I did some tests on the tables that I have created for my game to see if it works as expected.