Mafia Games Monday (MGM) – Writing SQL 201

February 29, 2016

Today is the intermediate tutorial on MySQL SELECT statements (hence the 201). If you need to review the previous tutorial, you can check that out here.

So take for example, we have the following relational tables below:

STUDENT(ID, LAST_NAME, FIRST_NAME, MIDDLE_INIT, SSN, DOB)
COURSE(ID, NAME, DESCR)
STUDENT_COURSE(SID, CID, SEMESTER_VAL, GRADE)


What if we wanted to get the course name, course description, student name, the most recent time the student took that course, and the grade for student ID 54356 and course ID 444?
This information would be useful for an office to get an understanding of whether or not a student took the course more than once, or what they got in that course at that point in time.

Doing this will utilize what’s called a sub-query or nested query. That is essentially a query that when run returns a result that is then used in the parent query to meet a condition.

The syntax looks like this:

SELECT data
FROM table
WHERE data1 = (SELECT data2
FROM table2
WHERE 1=1);

The SELECT statement that is within the parentheses is known as the sub-query.

Now how does this apply to the scenario above? Well, since we cannot guarantee that a student did not take a course more than once, we will have to get the last time the student took the course. Assuming that the SEMESTER_VAL value increases as the year goes further into the future and, we can write something like this:

SELECT NAME, DESCR, LAST_NAME, FIRST_NAME, MIDDLE_INIT, SEMESTER_VAL
FROM STUDENT_COURSE JOIN COURSE ON CID = ID JOIN STUDENT ON SID = ID
WHERE SEMESTER_VAL = (
SELECT SEMESTER_VAL
FROM STUDENT_COURSE
WHERE SID = SID
AND CID = CID)
AND SID = 54356
AND CID = 444;

Would this query work? Not as it’s currently written, no. There are a few things that we will have to do in order to make this query work.

We will have to use an alias. An alias is another way to identify the table so that the SQL engine knows how to properly run this query. Using aliases is a good practice to get into, especially when you are writing queries that use multiple tables. So, taking this scenario for example, we can then rewrite the query like this:

SELECT C.NAME, C.DESCR, S.LAST_NAME, S.FIRST_NAME, S.MIDDLE_INIT, SC.SEMESTER_VAL
FROM STUDENT_COURSE SC JOIN COURSE C ON SC.CID = C.ID JOIN STUDENT S ON SC.SID = S.ID
WHERE SC.SEMESTER_VAL = (
SELECT SC2.SEMESTER_VAL
FROM STUDENT_COURSE SC2
WHERE SC2.SID = SC.SID
AND SC2.CID = SC.CID)
AND SC.SID = 54356
AND SC.CID = 444;

Now will the query work? Well, no, because if the student took the course multiple times, that sub-query will return more than one row, which will cause this query to error out. This will require an aggregate function. An aggregate function is a way to perform some mathematical calculation on the value in the select statement before the result is returned. There are several aggregate functions, but today we will be using the MAX() function.

The MAX() function will allow us to get the maximum value for the column that is in the parentheses. So for example, if we did MAX(SID), we would get the largest student ID.

For the query above, we will have to use this function as such below:

SELECT C.NAME, C.DESCR, S.LAST_NAME, S.FIRST_NAME, S.MIDDLE_INIT, SC.SEMESTER_VAL
FROM STUDENT_COURSE SC JOIN COURSE C ON SC.CID = C.ID JOIN STUDENT S ON SC.SID = S.ID
WHERE SC.SEMESTER_VAL = (
SELECT MAX(SC2.SEMESTER_VAL)
FROM STUDENT_COURSE SC2
WHERE SC2.SID = SC.SID
AND SC2.CID = SC.CID)
AND SC.SID = 54356
AND SC.CID = 444;

And there you have it!

That’s all for now, until next time…keep that tech gear ticking.

~Goliath7470

Mafia Games Monday (MGM) – Documentation: Boring Yet Necessary (Part 2)

February 22, 2016

So a few weeks ago I posted a post titled “Documentation: Boring Yet Necessary“. If you haven’t read this post, read it before continuing on with this post so that I don’t have to re-explain what I already have explained. Done? Awesome. Let’s continue!

So what should this code documentation look like? Well, it really all depends upon personal preference, but here are some tips on what you should be doing in your code:

  1. Have a documentation block at the top of every file – This is a good practice to have. Basically, this documentation should include your name, the file name, the creation date, and a basic description of the file. A sample of this is below:
    /**
    Author:       G7470
    Filename:     build.php
    Date Created: 02/15/2016
    Description:  This file allows a user to build a new building, which will allow them to collect some valuables.
    **/
  2. Document each function or method – If you are creating a function and/or a class method to handle a particular “function”, that should be documented with what that function does, what is needed to run the function or method, and what is returned from that function or method. A sample of this is below:
    /**
    Name: get_user_level
    Parameters:
    $userid - integer - The user's ID
    Return:
    $level - integer - The user's level
    Description: This function given the user's ID will calculate their level and return this value.
    **/
    function get_user_level($userid) { 
  3. Document any other ambiguous code – Any other code that is a pretty large block should be documented, including large if statements, for loops, while loops, switch statements, etc. A sample of this is below:
    /** For each user, add 50 points to their account **/
    while($useri = $db->fetch_row($uq)) { 

Get the idea now? Essentially, the main idea of doing such documentation is to provide anybody reading your code with some understanding of how it all comes together. Without that, your code appears to be messier than it really is, and anybody reading it will be clueless as to what they need to do.

What about general documentation though? Well, again, it depends on what you feel is best to provide for the user, but my suggestion is to provide the five basic areas for each “module” or “feature”:

  1. Functional Overview – The basic idea of what the “module” or “feature” will be doing for your site.
  2. Updated/New Files – This can provide the reader of where the code for that “module” or “feature” is located.
  3. Technical Details – Any specific technical details, like for example if the user must have x amount of coins to do something, that’s something to note here.
  4. Database Information – Any database tables that were updated or new should go here.
  5. Other – Any other information of importance should go here.

With that, I’ll use your imagination as to what this document should look like. My suggestion would be to make this document as formal as possible. If you create one-off documents that are difficult to read and/or follow, then there’s no point in providing the documentation at all!

That’s all for now, until next time…keep that tech gear ticking.

~Goliath7470

Apple Versus The FBI: Is Privacy At Stake?

February 18, 2016

Privacy has certainly been a big issue since the creation of the Internet, and especially within the past few years, with the Patriot Act certainly at the forefront of this discussion. Well, that issue of privacy has come up again, but this time, it is from the FBI.

The FBI has asked Apple to essentially unlock the iPhone used by Syed Rizwan Farook, who was one of the shooters at San Bernardino massacre a few months ago. If you don’t know about that massacre, look it up, although the only relativity it has to this post is that it was a mass shooting.

Apple has placed encryption on the iPhone, and a feature that if attempts to unlock the iPhone fail 10 times, any data on the iPhone is wiped. The FBI obviously would not want this to happen, so they are asking Apple to either unlock the iPhone completely or remove this feature from this phone so that they can use brute force to unlock it.

What do you think Apple would do in this situation? Probably go ahead and comply one way or another, right?

No, they refuse to do it.

Talks between Apple and the FBI got so heated that they ceased completely, and this caused the Justice Department to file a court order to either remove the feature or unlock the phone.

Apple is going to appeal this court order.

According to Apple, “the software the F.B.I. wants it to create does not exist” and that doing so would “create a ‘backdoor’ to get around its own safeguards”. They wrote a 1,100-word letter to Apple customers about this, which you can check out here.

As someone in the tech industry, I can see both sides to this argument; however, I would be very surprised if Apple actually wins this case. As mentioned on the New York Times article by Ira Rubinstein, a senior at the New York University Information Law Institute, “this was a fight that was inevitable”, and I do agree with that. With all of the privacy concerns that have been stemming in recent years, I am not surprised whatsoever that something like this has come to the forefront. Apple can make this software, and with that phone unlocked, it could lead to a crackdown on anyone involved in the San Bernardino shooting, but I can see why Apple would not want to do this.

Now, all we can do is sit tight and see what the verdict is.

What do you think will happen? Do you believe that this is a breach of privacy?

That’s all for now, until next time…keep that tech gear ticking.

~Goliath7470

Self-Driving Car Is Coming

February 11, 2016

The self-driving car has had a lot of news coverage within the last year, including the incident of one of these cars getting pulled over for driving too slowly. However, as with any technological advance, there are always legal components that are holding its development back. One of these was the US National Traffic Safety Administration (or NHTSA for short).

NHTSA

Originally, the term “driver” has always referred to the person that is holding the steering wheel and pressing the pedals. In our minds, that makes perfect sense because all of our cars currently require to have someone behind the wheel “driving” the car, right? Well, with Google’s autonomous car, that all changed – the computer system is “driving” the car, not any human. This legality had to be addressed in order for Google’s autonomous car to move forward.

google_car

Well, Google managed to do just that! The NHTSA on February 4th of this year modified the term “driver” to be for the case of the autonomous car the self-driving system as the “driver”.

The NHTSA stated: “In this instance, an item of motor vehicle equipment, the Self-Driving System, is actually driving the vehicle.”

So what does this mean for Google? Well, this means that according to BBC, the autonomous cars are “one crucial step closer to being allowed on public roads”, which is quite a big step!

I am curious to see what the future holds, but if you were thinking that these cars would not be on the road in your lifetime, I would suggest thinking again. They are coming soon, and best believe when they do, they will be on the road.

Now do I want a autonomous car? Well, call me old-fashioned, but I do like driving. Heck, I would much rather drive a manual transmission car than an automatic. For the USA audiences, that may be a bit surprising to hear! At any rate though, these cars are coming, and it won’t be long before they are the most popular vehicle on the road.

That’s all for now, until next time…keep that tech gear ticking.

~Goliath7470

Mafia Games Monday (MGM) – Code Consistency

February 8, 2016

So over the years of being a developer and a game owner I have seen A LOT of different code. I know, rather shocking. I have seen code in several different languages, different architectures, and much more.

However, when I begin a project, there is one thing that I almost always see: a lack of code consistency.

What is this, you ask?

Shurg Question

Well, take for example this small block of PHP code to say “Hello ” for each user:

$con = mysqli_connect('host','db_user', 'db_pass', 'db_name') or die("Error ".mysqli_error($con));
$userq = mysqli_query($con, "SELECT * FROM users");
while($useri = mysqli_fetch_array($userq)) {
echo "Hello :" . $useri['username'] . "! <br />";
}

Now that looks pretty simple, doesn’t it? Now, take this example:
$con = mysqli_connect('host','db_user', 'db_pass', 'db_name') or die("Error ".mysqli_error($con));
$uq = mysqli_query($con, "SELECT * FROM users JOIN u_other ON users.uid = u_other.uID JOIN u_inv ON u_other.uID = u_inv.uinv_ID");
while($useri = mysqli_fetch_row($uq)) {
echo "Hello :" . $useri[1] . "! <br />";
}

Now, by looking at that code, do you know what all it’s doing? You might (so kudos to you if you do), but in actuality it is doing the exact same thing as the previous example. Now from a consistency standpoint, there’s nothing wrong with that if these were two separate applications, but if you had both of these in the same application, that wouldn’t make much sense now, would it?

That is what I mean by code consistency – ensure your structure and methods are similar (or the same) throughout the entire application.

Let’s take one more example for good measure. It will be similar code, but look at the differences between this block:

$con = mysqli_connect('host','db_user', 'db_pass', 'db_name') or die("Error ".mysqli_error($con));
$userq = mysqli_query($con, "SELECT * FROM users");
while($useri = mysqli_fetch_array($userq)) {
  if($useri['red'] <> 0) {
    $redq = mysqli_query($con, "SELECT * FROM red_stuff WHERE ruid = {$useri['uid']}");
    if(mysqli_num_rows($redq) > 0) {
      $redu = mysqli_fetch_array($redq);
      echo $useri['username'] . "'s red value is: " . $redu['redval'] . " <br /> ";
    }
    else {
      echo "Error running query for user: " . $useri['username'] . " <br />";
    }
  }
  else {
    echo "No red value for: " . $useri['username'] . " <br />";
  }
}

and this block:

$con = mysqli_connect('host','db_user', 'db_pass', 'db_name') or die("Error ".mysqli_error($con));
$userq = mysqli_query($con, "SELECT * FROM users");
while($useri = mysqli_fetch_array($userq)) {
if($useri['red'] <> 0) {
$redq = mysqli_query($con, "SELECT * FROM red_stuff WHERE ruid = {$useri['uid']}");
if(mysqli_num_rows($redq) > 0) {
$redu = mysqli_fetch_array($redq);
echo $useri['username'] . "'s red value is: " . $redu['redval'] . " <br /> ";
}
else {
echo "Error running query for user: " . $useri['username'] . " <br />";
}
}
else {
echo "No red value for: " . $useri['username'] . " <br />";
}
}

What are the differences that you saw?

Well, if you looked at it very closely, the code itself is exactly the same; however, isn’t the first example a little easier to follow? Keep this in mind when you are writing any sort of code – you want anyone that sees your code to be able to read it.

But I’m getting off-track here. What does this have to do with consistency?

Well again, if you have both of these blocks of code in your application, that’s not only a problem with duplication of code, but also with consistency. It’s difficult to read when the way that they are written are not the same!

Ok, I’m sold that consistency is important. What could I do about it? Where do I start?

Well, the best place to start is to go through the last thing that you have personally written and see what you did there. As a developer, I can tell you that my most recent work is better than my first. That’s true with any industry, really. So anyways, start from there and structure your code based off of that for the rest of your application. If you indent your code between the braces one tab length, then do that throughout – that kind of thing.

Remember – the more consistent your code base is in your application, the easier it will be to change and/or fix!

That’s all for now, until next time…keep that tech gear ticking.

~Goliath7470

Robot Wars!

February 4, 2016

So I know I have posted several different things about robots. Many of them have been for a positive cause, whether that is to help track inventory or helping deliver goods (posted on my facebook page before).

But today, what about if those robots were out to kill you?

Yes, real life Terminators are being built – many even more powerful than the drones that are performing air strikes.

A panel of experts met at the World Economic Forum in order to discuss the robotics industry, as well as the question “What If: Robots Go to War?”

If you want to watch this discussion, you can watch it here.

A main point that was brought up during the panel discussion was by Roger Carr, Chairman of BAE Systems. He said: “Lethal autonomous robots have no emotion or sense of mercy”. Sounds like a real life Terminator, doesn’t it?

The panel also “stressed the need for human operators to oversee LARs”, which again just feels like another prompt from the Terminator! If you haven’t seen any of the Terminator movies, now would be a good time to watch them to get these references…

Anyways, I’m not saying it’s time to put that tinfoil hat on your head, but maybe it’s just time to consider where the world of technology is heading, and what really should be on the forefront of our discussion of new policies.

That’s all for now, until next time…keep that tech gear ticking.

~Goliath7470

PS: Credit for this discussion goes to the following articles by CNN and TechNewsWorld.

Mafia Games Monday (MGM) – Writing SQL 103

February 1, 2016

This is my third installment of the “Writing SQL” series. If you’re curious about the other two, you can check them out here and here.

So today is time to update some data!

Consider the following scenario:

A student (ID 54356) has just recently gotten married and changed their last name to ‘Beser’. The following are the relational tables below:

STUDENT(ID, LAST_NAME, FIRST_NAME, MIDDLE_INIT, SSN, DOB)
COURSE(ID, NAME, DESCR)
STUDENT_COURSE(SID, CID, SEMESTER_VAL)

We want to keep up with what their current last name is!

This will involve some new syntax:

UPDATE: This keyword is to signify that you want to change some data on a table.
SET: This keyword is to signify which field(s) you would like to update on said table to what value.

This would be another query. Now, this is what an update query would look like:

UPDATE table
SET field = value
WHERE 1=1;

So, taking the scenario above, we can then write the following query to perform the update:

UPDATE STUDENT
SET LAST_NAME = 'Beser'
WHERE ID = 54356;

That’s the query right there! Pretty simple, isn’t it? Wait a minute though! Why did I surround “Beser” in single quotes?

There’s a pretty simple answer: “Beser” is what is called a string type. If you want to write any string within a SQL query, you have to surround it in quotes. This guarantees that the SQL engine will understand that “Beser” is in fact a string. Why single quotes though? Well, if I used double quotes, some SQL engines may not recognize it, and instead treat it like you’re attempting to label the field and/or column. Labeling will come in a later installment.

Well wait, what if this was the scenario?

A student (ID 54356) has just dropped a course (ID 589) for the current semester (SEMESTER_VAL 22015).

Keeping history aside, you just want to delete it, as they are no longer enrolled. That would involve writing a DELETE query.

Doing this will involve some new syntax:

DELETE: This keyword is to signify that you want to delete data from a table.

The syntax would look like this:

DELETE FROM table
WHERE 1=1;

So, taking the scenario above, we can then write the following query to perform the delete:

DELETE FROM STUDENT_COURSE
WHERE SID = 54356
AND CID = 589
AND SEMESTER_VAL = 22015;

Starting to look a little more advanced now, aren’t we? But that query will accomplish the job!

Tune in on my next installment for some more advanced SQL writing!

That’s all for now, until next time…keep that tech gear ticking.

~Goliath7470