Basic SQL Communication

The following tutorial is made by Hope. It teaches you how to perform basic communication tasks to and from a server using the Structured Query Language (SQL) from either your java application, rsps server, or website. Some things this might be useful for: displaying number of players online on your website, sending important player data to an sql server for safe keeping, updating player’s ranks on your forum from your server, and even a start to creating your own vote, highscores, or donation system.

Pre-Requisites to Reading this thread:

  • Know how to create an SQL database on your website or computer.
  • Know how to add a new table to your database along with adding columns to that table.
  • Here is a link incase you don’t know how to do either of those.

The two languages we’ll be working with today are Java and PHP. Both have internal libraries that handle the execution of SQL related processes.

PHP Section

As said above, php offers SQL libraries to help communicate with your chosen database. This is very commonly used for systems such as forums, blogs, keeping user information, ect.

Sending Information using PHP

In the below example, we create a php variable named con and apply an sql connection to it using the mysql_connect method provided by PHP. The parameters for this function is (connection IP address, the database username, the database password), which are all strings. Most leave the connection IP as localhost since their database is located on the same server as what they’re running the script off of, but some might change it to a different IP depending if their database is external or not.

<?php

    $con = mysql_connect("localhost", "DATABASE_USERNAME", "DATABASE_PASSWORD");
    if (!$con) {
        die("Could not connect to database: " . mysql_error());
    }

    mysql_select_db("DATABASE_NAME", $con);
       
    mysql_query("INSERT INTO people (name, age)  VALUES ('Tommy ', 33)");
    
    mysql_close($con);
    
?>

After applying the connection, we check to see if everything is alright. If the connection contains errors, we simply call the die() method, which in other terms ‘exists’. By calling the mysql_error() method when calling an exit to the page, we can grab whatever the error was in the connection. Usually, it’s because you didn’t type the username or password correctly, so make sure all your credientials are correct!

Next, we select a database to use from our server. The method mysql_select_db(your database name, the connection variable) selects the chosen database.

If all went well, you’re 100% connected to your database using SQL.

Next, let’s say you want to add something to your table in your database. The mysql_query function can be used for many different edits to your database, like editing, retrieving, adding, deleting, ect, but for this tutorial, we’re going to cover just inserting and retrieving.

mysql_query("INSERT INTO people (name, age)  VALUES ('Tommy ', 33)");

The above code is basic syntax for inserting information into your database table. INSERT INTO is followed by your table name inside your database. Keep in mind that everything is case sensitive. Following your table name is a parameter container for the columns that your table owns. In this example, my table ‘people’ has 2 columns, which are name and age. The name in my database is a varchar and the age is an integer. Followed by the column names is the keyword VALUES, then a container for the values. Also keep in mind that the values are in order the same as (name, age).

Finally, we call mysql_close to exit the connection (although it’s not really needed for this small of a script, since the connection automatically ends at the end of a script).

Recieving Information using PHP

<?php

    $con = mysql_connect("localhost", "DATABASE_USERNAME", "DATABASE_PASSWORD");
    if (!$con) {
        die("Could not connect to database: " . mysql_error());
    }

     mysql_select_db("DATABASE_NAME", $con);
       
     $data = mysql_query("SELECT * FROM people") or die(mysql_error());
    
    while ($person = mysql_fetch_array($data)) {
        echo $person['name'];
        echo $person['age'];
    }
    mysql_close($con);
    
?>

The above code connects to the database just like before, but this time we’re using the mysql_query in a different way. Following the SELECT keyword, we put a * to indicate grab all. This is then followed by the FROM keyword, then the name of our table. What this does is select all the rows from our table called ‘people’. The variable $data now holds all the rows listed in our table (if any).

Next, we loop through our data using the mysql_fetch_array($data) method. While inside the loop, we can obtain certain columns from each row recieved. For example, if you just wanted to get the persons age from the row, you would take the iterated variable, which is $person followed by ['column name'];
You might be wondering, well what if I don’t want to get all the rows, what if I want to search for someone’s name?

    $data = mysql_query("SELECT * FROM people WHERE name='George'") or die (mysql_error());

Using the above query will select all rows from the table where the column name is equal to “George”. It could potentially return n results depending on how many “George” there are in the table.

Java Section

As stated before, Java also contains a set of useful utilities for sending and recieving information to a database using SQL. Below is an example of a Java class that uses SQL functions.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SQL {

    private static Connection con = null;
    private static Statement stmt;
    
    public static void createConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            String IP="WEB HOST IP";
            String DB="DATABASE NAME";
            String User="DATABASE USERNAME";
            String Pass="DATABASE PASSWORD"; 
            con = DriverManager.getConnection("jdbc:mysql://"+IP+"/"+DB, User, Pass);
            stmt = con.createStatement();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    public static void process() {
        createConnection();
        ResultSet results = statement.executeQuery("SELECT * FROM people WHERE name='George'");
        while(results.next()) {
                String name = results.get("name");
        }
        statement.executeQuery("DELETE FROM `people` WHERE name='GEORGE'");        
        destroyConnection();
    }

    private static void destroyConnection() {
        try {
            stmt.close();
            con.close();
        } catch (Exception e) {}
    }
}

The above code is very similiar to what I showed you in the PHP section, there’s essentially just different names for the functions. Executing a query, as you can see, uses the same concepts from the PHP section. Since they’re almost the same, I’m going to let you explore the functions Java has to offer. If you use an IDE (such as eclipse), you can view all the functions in the sql package. Mostly all of them that I know of are well documented on the oracle website if you want to take a look.

Some sample scripts

The below script recieves the amount of players online stored in a database. It also checks to see if the server is online using fsockopen();

  $query = mysql_query ("SELECT * FROM status WHERE world = '1'") or die(mysql_error());    
    $status = false;
    $players = 0;
    while($row = mysql_fetch_array($query)) {
        $players = $row['players'];
        $timestamp = $row['timestamp']; //will use in future
        $host = "SERVER IP";  //only handles world 1 right now, need handle for future worlds.
        $port = "43594";
        $connection = @fsockopen($host, $port);
        if (is_resource($connection)) {
            $status = true; //server online
            fclose($connection);
        } else {
            $status = false; //server offline
        }
    }

 

Again, this is just a basic coverage of SQL communication, seening as there’s a lot more content. I’ve done a basic version, because I feel like the RSPS community doesn’t nessisarily need such advanced SQL material. Most of what you’re doing is simply related to easy executions for voting, donations, and keeping small data. If you want to learn more, feel free to check out documentation, or send me a message.