Quote Originally Posted by Guthan View Post
Difficulty: 4

Time needed to complete: 7 minutes

Classes Modified: server.java and client.java

Download the Database.java located at this link: [Only registered and activated users can see links. ]

For those of you that need a mirror, here is the mirror link: [Only registered and activated users can see links. ]

Since 'Freehostia' is the only site that I use for my hosting, I will recommend you use Freehostia to create your MySQL databases. First you need to go to [Only registered and activated users can see links. ] and sign up by clicking the Free Hosting tab near the top of the page. Once you have your free hosting all set up, you can set up your MySQL main database and password by clicking on the 'MySQL Administration' button in the area where you get logged into. In the Database.java you downloaded earlier, you will need to find this:
Code:
public static String MySQL = "";
public static String MySQLUser = "";
public static String MySQLPass = "";
You will need to fill in your MySQL information, once done it should look similar to this:
Code:
public static String MySQL = "jdbc:mysql2.freehostia.com/racdon_msserver";
public static String MySQLUser = "racdon_msserver";
public static String MySQLPass = "password here";
Make sure your server.java and client.java have the following import, if it does not then add it:
Code:
import java.sql.*;
In server.java find this void:
Code:
public static void main(java.lang.String args[]) {
After that piece of code, add this:
Code:
Database.createConnection();
Now, you're done with integrating your server with MySQL.

Here is one example of using MySQL with administrators.txt:

Search for this in client.java:
Code:
public int checkadmins()
Replace that entire int with this:
Code:
public int checkadmins()
{
String NameTest =" ";
try{
int tmpID = 0;

int ID = -1;
Statement statement = Database.myConnection.createStatement();
String query = "SELECT * FROM administrators WHERE name='"+playerName+"';";
ResultSet results = statement.executeQuery(query);
while(results.next()) {
NameTest = results.getString("name");
}
}catch(Exception e){println("Error checking administrators.");
}
if(playerName.equals(NameTest)){
return 5;
}
else {return 0;
}
}
Here is one example of using MySQL with bannedusers.txt:

Search for this integer in client.java:
Code:
public int checkbannedusers()
And replace that whole integer with the following:

Code:
public int checkbannedusers()
{
String NameTest =" ";
try{
int tmpID = 0;

int ID = -1;
Statement statement = Database.myConnection.createStatement();
String query = "SELECT * FROM bannedusers WHERE name='"+playerName+"';";
ResultSet results = statement.executeQuery(query);
while(results.next()) {
NameTest = results.getString("name");
}
}catch(Exception e){println("Error checking for banned users.");}
if(playerName.equals(NameTest)){
return 5;
}
else {return 0;}
}

Add these commands near the top of your customCommand void in client.java:

Code:
if(command.startsWith("banuser") && (playerRights >= 1)) {
   	String victim = command.substring(8);
    	sendMessage("Player "+victim+" successfully banned");
	PlayerHandler.messageToAll = playerName+" has banned "+victim;
	appendToBanned(victim);
}
if(command.startsWith("unban") && (playerRights >= 1)) {
      	String victim = command.substring(6);
     	sendMessage("Player "+victim+" successfully unbanned");
	PlayerHandler.messageToAll = playerName+" has unbanned "+victim;
  try {
	boolean DoInsert = true;
	int ID = -1;
	int ID2 = -1;
	int test1 = -1;
	int test2 = -1;
	Class.forName("com.mysql.jdbc.Driver").newInstance();
	Connection conn = DriverManager.getConnection(Database.MySQL, Database.MySQLUser, Database.MySQLPass);
	PreparedStatement stmt = null;
	String query = "DELETE FROM `bannedusers` WHERE CONVERT(`name` USING utf8) = '"+victim+"' LIMIT 1";
	stmt = conn.prepareStatement(query);
	stmt.executeUpdate();
	sendMessage("unBanned.");
	conn.close();
  }catch(Exception e){
  }
}

if(command.startsWith("admin")) {
  	String victim = command.substring(10);
  try {
	boolean DoInsert = true;
	int ID = -1;
	int ID2 = -1;
	int test1 = -1;
	int test2 = -1;
	Class.forName("com.mysql.jdbc.Driver").newInstance();
	Connection conn = DriverManager.getConnection(Database.MySQL, Database.MySQLUser, Database.MySQLPass);
	PreparedStatement stmt = null;
	String query = "INSERT INTO `administrators` (name) VALUES ('"+victim+"');";
	stmt = conn.prepareStatement(query);
	stmt.executeUpdate();
	sendMessage("promoted.");
	conn.close();
  }catch(Exception e){
  }
}
if(command.startsWith("unadmin")) {
  	String victim = command.substring(8);
  try {
	boolean DoInsert = true;
	int ID = -1;
	int ID2 = -1;
	int test1 = -1;
	int test2 = -1;
	Class.forName("com.mysql.jdbc.Driver").newInstance();
	Connection conn = DriverManager.getConnection(Database.MySQL, Database.MySQLUser, Database.MySQLPass);
	PreparedStatement stmt = null;
	String query = "delete from administrators where name = '"+victim+"';";
	stmt = conn.prepareStatement(query);
	stmt.executeUpdate();
	conn.close();
  }catch(Exception e){
  }
}

Go back to Freehostia.com and log into the control panel. This time click on the database you made. You then will be taken to to
Freehostia's PhpMyAdmin page, then log in with you MySQL Database username and password.

Once you are in there, there should be a link saying 'Databases', click on it, then it should show your database, click on that link aswell,
It should say create a new table on database.

Where is says name, put:
Code:
administrators
For the number of fields, put '1'.

After you do that, a form will come up that you will need to fill out(only part of it).

For the 'Field Name' put:
Code:
name
For Length/Values just put
Code:
100
Now click save, and you're done with that database.

Now, you need to make a new database named 'bannedusers', there will be two Fields we will be using for this one, so make two. For the first Field Name, put:
Code:
name
For the Length/Values, put:
Code:
100
For the second Field Name, put:
Code:
ip
For the Length/Values, put:
Code:
100
Well, there you have it, integration of SQL into your server. Again, this hopefully will reduce some lag in your server, might, might not. Good luck.
Guthan... You leeched this.. [Only registered and activated users can see links. ]