.NET VB.NET Chat Room Tutorial With PHP & SQL [Intermediate]

Introduction

So this was requested by a few members here, and I thought it would be a good idea to do something different. I've seen tons of chats clients made in VB.NET that use sockets and TCP/IP, the tutorials only show how this would work for local networks. So I'm going to show how to do a chat room in vb.net that utilizes a webserver(PHP & some SQL to store our user information). So we could use this globally, and anyone with internet connection could connect and chat.

First of all I'll mention the disadvantages & advantages of using a webserver as a chat room whatever in the way I did it.

Advantages
+ It can be fast if you got a nice web-host
+ It's a good learning experience, from this tutorial you can learn a lot about PHP, SQL and coding in general(vb.net specifically)

Disadvantages
- Really and truly, this should be done with sockets but meh.
- This creates tons of requests to your web-host per minute but you can get a free one like x10 hosting that don't care.

Also, I'm not really focusing on security, I'm focusing on actually teaching people valuable skills and fundamentals. The only security, there is, is that the password is hashed + salted. And using PDO to prevent SQL Injections. Though the messages are not encoded or whatever.

Because I do not feel its necessary, if the user is really egar, they can learn how to do it for themselves. From the tutorial I'm writing out, I've done a lot of work on my side. And I'm actually going over every single variable declaration and function declaration and making sure I explain them fully. Both the PHP code, SQL Code and VB.NET code. I'm not just spitting out the source code. I'm actually being a TEACHER, which is something I think we don't see enough in this section.

And that's my justification for not encoding the messages or worried too much about security.

Setup MySQL Database

Okay so let's get started.
First we're going to start with our server side code. So this is the PHP and SQL stuff.

Firstly, I recommend that you go check out my first tutorial on how to do a login and registration system using vb.net php and sql. What this tutorial is is an extension of the previous one.

https://hackforums.net/showthread.php?tid=4610630

So let's begin.

We login to our cPanel webhost and we setup our database.

Setup SQL Tables

So we're going to create 3 tables. tblLogin, tblMessage and tblMessageLog.

tblLogin will store all the login information of our users. And in that table we will have 5 columns
UserID, Username, Password, IsBanned, DateRegistered.

[Image: aHUNVjr.png]

the UserID is unique ID that is used identify each user, just like you an I have a unique HF UserID.

Next is tblMessage. This will store all the information about each message that is sent by each of our users.

[Image: MfviUrY.png]

So again, MessageID to identify each message, UserID to identify which user sent it, the content of the message, and what time it was sent by.

now our final table. tblMessageLog.

[Image: 2kegL0q.png]

This table is used when a message is read by any user. So when a user reads a message, it will appear in this table, so the MessageID of the message that was read, and the UserID of the user who has READ the message.

PHP Server Side Code

Note: I don't know PHP as well as I do C# and VB.NET so bare my sloppy code.

Step 1 we create a db.php which we will place code required to connect to our database and sql database where. So you need to create a database, with some users and stuff. Once you've learned how to do that else where, you can proceed.

We will call this
db.php

PHP Code 


<?php
// Connection details
//Change me
$db_host = "localhost";
$db_name = ""; // database name
$db_user = ""; // datebase user
$db_pass = ""; // database password

$connstr = "mysql:host=$db_host;dbname=$db_name";

try 
{
    $con = new PDO($connstr, $db_user, $db_pass);   
}
catch (exception $e) 
{
    echo "error";
    //you might want to display a more meaningful error
    exit();
}

?>

We're now going to create the php script that handles our register requests.
We will call this
register.php

PHP Code 


<?php
include 'db.php';
//We "Import/Include" this so we can access our DB credentials.

    $user = $_GET['user'];
    $pass = $_GET['pass'];
    //We declare two GET variables that we can use to query against our database.

    //We ensure the variables are not empty, if they're not we then proceed into the if statement
    if (!empty($user) && !empty($pass))
    {
        if (registerSuccess($user, $pass))
        {
            echo "success";
        }
        else
        {
            echo "fail";
        }
    } 
    else
    {
        echo "jog on kid";
    }

    function registerSuccess($username, $password)
    {    
        global $con;
        $salt = substr(str_shuffle("!£$%^&*abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"), 0, 25);
        $hash = crypt($password, $salt);

        //We generate a random salt, and we then use the crypt function to generate us a random has.

        $stmt = $con->prepare("INSERT INTO tblLogin(Username, Password, DateRegistered) VALUES(:user, :pass, :ts)");
        //This insert query will insert the two values from our declared GET variables into our SQL tables.

        $stmt->bindValue(':user', $username, PDO::PARAM_STR);
        $stmt->bindValue(':pass', $hash, PDO::PARAM_STR);
        $stmt->bindValue(':ts', date('Y-m-d G:i:s'));
        //We bind the values from our sql query to our prepared statement

        //Executes the query, and if it is successful we print out "success"
            if ($stmt->execute())
        {
            return true;
        }
        else
        {
            return false;
        }

        $db = null;    
    }
?>

We will now create our PHP script to login to our system from our vb.net application. We will call this
login.php

PHP Code 


<?php
include 'db.php';
//We "Import/Include" this so we can access our credentials.

    $user = $_GET['user'];
    $pass = $_GET['pass'];
    //We declare two GET variables that we can use to query against our database.

    LoginUser($user, $pass);

    function LoginUser($username, $password)
    {
        global $con;

        $stmt = $con->prepare("SELECT * FROM tblLogin Where Username = :user Limit 1");
        //This insert query will select all the data from our sql server, where the username of the GET variables 
        //match the one in our SQL table. And the Limit 1 means, return only 1/the first result and ignore the rest.

        $stmt->bindValue(':user', $username, PDO::PARAM_STR);

             $stmt->execute();
             //We then bind our username to our query and execute it.

        while($r = $stmt->fetch(PDO::FETCH_ASSOC))
        {
            $arr = array('UserId' => $r["UserID"], 'IsBanned' => $r["IsBanned"], 'TimeSignedIn' => date('Y-m-d G:i:s'));
            $hashedpassword = $r["Password"];
        }    

        // We retrieve the users hashed password     

             //We has their current password, and use their hashed password as the salt, and compare 
             // Hashing the password with its hash as the salt returns the same hash                 
        if ($hashedpassword == crypt($password, $hashedpassword))
        {
         echo json_encode($arr);
        }

        else
        {
        echo "doesn't exist";
        } 

        $con = null;
    }    
?>

This is an image of what the data it echos when you successfully login would look like.

[Image: OLhke4f.png]

This is the script we use to add a message to our database we will call this.
addmessage.php

PHP Code 


<?php
    include 'db.php';
    //when we include, we import/include all our data from our db.php page so we can use them here. without
    //having to copy and paste the same data all the time

    $MessageContents = $_GET["msg"];
    $UserID = $_GET["userId"];

    //we declare two GET variables, which will contain the userId of the user trying to add the message.
    //And msg is the content of the message they're trying to send. We could of used POST which would be better for larger
    //data, but for tutorial sake, let's just use GET.

    //We check to see if the variables are empty or not, if they are not empty then we process.
    if (!empty($MessageContents) && isset($UserID))    
    {    
        //If not empty, then we add the message, we create our own function called addMessage()
        //This function will take in two arguments as the parameters. UserID and MessageContents.
        //This function will then add the message to our sql table, and if it's successfull.
        //It will print "success" else it prints failed

        if (addMessage($UserID, $MessageContents))
        {
            echo "success";
        }
        else
        {
            echo "failed";
        }

        $con = null;
    } 
    else
    {    
        //If the two GET variables are empty, this message will show.
        echo "jog on kid";
    } 

    function addMessage($userId, $msg)
    {
        global $con;
        //by typing global, we reference the variable in our db.php. So we can use that variable to connect to our db

        $stmt = $con->prepare("Insert Into tblMessage(UserID, Message, TimeSent) Values(:userID, :msgID, :ts) ");

        $stmt->bindValue(':userID', $userId, PDO::PARAM_INT);
        $stmt->bindValue(':msgID', $msg, PDO::PARAM_STR);
        $stmt->bindValue(':ts', date('Y-m-d G:i:s'));

        //we create use PDO(PHP Data Objects) to create a prepared SQL statement, which prevents SQL Injection attacks.
        //And we bind the data $userID and $msg from to user to our database. And we execute. If execution is succesful we return tru
        //else we return false

        if ($stmt->execute())
        {
            return true;
        }
        else
        {
            return false;
        }
    }                    
?>

Next we create our script to get the unread messages of each user. --Warning, this bit gets a bit complex if you do not understand SQL very well, the queries are a bit complex, but don't worry.

We will name this script
getunread.php

PHP Code 


<?php
      include 'db.php';      
    //We "Import/Include" this so we can access our DB credentials.

    $Action = $_GET["action"];
    $UserID = $_GET["userId"];
    $TimeSignedIn = $_GET["timestamp"];

    //we create 3 GET variables.
    //We reat the timesignedIn variable, 

    //This validates to check if the variables are not empty. And validates to see if
    //The user requested is a valid user or not. If they're then we proceed.
    if (!empty($Action) && !empty($TimeSignedIn) && validUserID($UserID) == true)
    {    
        //The GetUnreadCount will return an Integer, of the count of the unreadMessages of the user.
        //The GetUnreadMessages will return the conetents of all their unread messages.

        if ($Action == "count")
        {    
        echo getUnreadCount($UserID);
        } 

        elseif ($Action == "msg")
        {
        getUnreadMessages($UserID);
        } 
    }
    else
    {
        echo "jog on kid";
    }

    function getUnreadCount($userId)
    {
        global $con;
        global $TimeSignedIn;

         $stmt = $con->prepare("Select MessageID, Username, Message, TimeSent  
         From tblLogin, tblMessage
         Where tblMessage.TimeSent >= :timesignedin
         And tblLogin.UserID = tblMessage.UserID
         And Not tblLogin.UserID = :ID
         And tblMessage.MessageID Not In(Select MessageID From tblMessageLog Where UserID = :ID)");

        //This query is quiet complex, but it does is. It Selects all the infromation from the message table,
        //Where the time the message was sent is after the user has logged in, and where the message ID is not found in the MessageLog
        //table(because thats where all read messages go) and where the message is not a message sent by the user himself.

        $stmt->bindValue(':ID', $userId, PDO::PARAM_INT);
        $stmt->bindValue(':timesignedin', $TimeSignedIn);

        $stmt->execute();

        //We then bind the values off the userid and timesigned in to the query, and execute it and return the row count
        //The row count signifies the amount of results it found from then query.

        return $stmt->rowCount();
    }

    function getUnreadMessages($userId)
    {
        global $con;
        global $TimeSignedIn;

        $stmt = $con->prepare("Select MessageID, Username, Message, TimeSent
         From tblLogin, tblMessage
         Where tblMessage.TimeSent >= :timesignedin
         And tblLogin.UserID = tblMessage.UserID
         And Not tblLogin.UserID = :ID
         And tblMessage.MessageID Not In(Select MessageID From tblMessageLog Where UserID = :ID)");

        $stmt->bindValue(':ID', $userId, PDO::PARAM_INT);
        $stmt->bindValue(':timesignedin', $TimeSignedIn);

        $stmt->execute();

        //This query is exactly the same as the previous one, but this time we're not returning the count
        //We're returnig the actual contents of the message.

        $result = array();    
        $msgIDs = array();

        while($r = $stmt->fetch(PDO::FETCH_ASSOC))
        {    
            $result[] = $r;
            $msgIDs[] =  $r["MessageID"];            
          }

          //So while PDO its still reading the results, we add each result it reads into an array
          //We also add the MessageIDs of the data it reads to an array. So After that we can then add the 
          //Message ID to the MessageLog table, to show it has been read.

        echo json_encode($result);

        //We then encode the result, and echo it out as JSON data.    

        for($i = 0; $i < sizeof($msgIDs) ; $i++)
            addMessageLog($userId, $msgIDs[$i]);

        //We then loop through each msgID we have, and add the message ID and userID to the messagelog table
        //this then lets the system know the message has been read, so it will never pull it up again
    }

        function addMessageLog($userId, $messageId)
    {
        global $con;

        $stmt = $con->prepare("Insert Into tblMessageLog(MessageID, UserID) Values(:msgID, :userID)");

        //We could of used a mass Insert statement, but for tutorial purposes, let's keep it nice and simple

        $stmt->bindValue(':userID', $userId, PDO::PARAM_INT);
        $stmt->bindValue(':msgID', $messageId, PDO::PARAM_STR);

        //It adds the userid and messageid and executes. simple.

        $stmt->execute();
    }

        function validUserID($userId)
    {
        global $con;

        $stmt = $con->prepare("Select * From tblLogin Where UserID = :ID"); 

        $stmt->bindValue(':ID', $userId, PDO::PARAM_INT);
        $stmt->execute();

        //We submit the query, and we check the row count. if the row count = 1 that means the user
        //exists else we return false

        if ($stmt->rowcount() == 1)
        {
            return true;
        }
        else
        {
            return false;
        }
    }        
?>

I thought to mention, the JSON data that is being outputted for each message would look like this. And our vb.net application would read that and convert it into an object.

[Image: mhInAoy.png]

But in our case, there will be a lot of those.

okay so now we have finished our server side scripting/coding. And we now go to our vb.net application to finish off.