DAT Blog Post Four

Week Four Sessions 5-6

What Have I Learned?

This week I learned quite a few key things as well as made a good start on my project. I learned about stored procedures and flow control, as well as UPDATE statements.

A stored procedure is a section of SQL statements that are stored within our server. These can perform a variety of tasks but do not run unless we call it.

Example:

In this example, we have a procedure called "createtbl." We would place our SQL queries between the begin and end. This would store this procedure which we then call using "call createtbl();"

drop procedure if exists createtbl;  /*Create Tables*/
delimiter //
create procedure createtbl()
begin
end //
delimiter ;
call createtbl();

An update statement is used to update specific data within a table or multiple tables. This can be done on a certain row or multiple rows based on our WHERE clause. We can also specify what columns we want to update so that we don’t need to change all of the data of a row.

Examples:

UPDATE `tbl_leaderboard` SET `totalScore`=600, `gamesPlayed`=5, `averageScore`=120, `player_score`=60, `userID`=1 WHERE `leaderboardID`=2;

UPDATE `tbl_leaderboard` SET `totalScore`=1200, `gamesPlayed`=2, `averageScore`=600, `player_score`=600, `userID`=3 WHERE `leaderboardID`=5;

UPDATE `tbl_player` SET `player_score`=5500, `colour`='Pink', `gameID`=1, `tileID`=4, `userID`=1 WHERE `playerID`=1;
UPDATE `tbl_player` SET `player_score`=20, `colour`='Violet', `gameID`=1, `tileID`=5, `userID`=3 WHERE `playerID`=2;

UPDATE `tbl_tile` SET `boardID`=4 WHERE `tileID`=2;
UPDATE `tbl_tile` SET `boardID`=2 WHERE `tileID`=1;

UPDATE `tbl_user` SET `username`= 'johnny', `user_password`='mypwd', `user_email`='fakeEmail@email', `user_loginAttempts`=3, `user_accountStatus`=false, `user_isAdmin`=false WHERE `userID`='2';
UPDATE `tbl_user` SET `username`= 'bobert', `user_password`='password51', `user_email`='randomEmail@email', `user_loginAttempts`=1, `user_accountStatus`=false, `user_isAdmin`=true WHERE `userID`='1';

Why Have I learned This?

Stored procedures are useful if we want to call a certain procedure later on, rather than all in one go. For example, creating a procedure that creates all of our tables before we call a procedure that inputs data into these tables. These procedures are cached for later use. I believe that the use of stored procedures will also come in very handy in future projects.

Update statements are very useful as we can manipulate data within our table after we have inserted our data. This will come in useful when we want to create the procedures for modifying existing users within our game. I believe that the use of update statements will also come in very handy in future projects.

How Have I learned This?

I learned this by researching what stored procedures and UPDATE queries are and how we implement them into our system. This was followed by doing a few test examples to see if I new what I was doing. This was not too much of a struggle as I picked up on it pretty quickly.