Reading from database

Hello,

I am currently working on my own custom Hunger Games plugin, and I want to store some information in a database.
This is the first time I'm using a MySQL database and I was wondering how I can check if a user (row) already exists in the database, and if not, write the row to the database.

This is what my database looks like:

Code:

CREATE TABLE IF NOT EXISTS `leaderboard` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `username` text COLLATE latin1_general_ci NOT NULL,
  `diedamount` int(4) NOT NULL,
  `wonamount` int(4) NOT NULL,
  `firstlogin` text COLLATE latin1_general_ci NOT NULL,
  `lastlogin` text COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`id`)
)

And this is what my code looks like:

Code:

public void WriteJoindb(String username){
   DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
   Date today = new Date();
   String currentdate = dateFormat.format(today);
   try{
   Class.forName(driver);
   Connection con = DriverManager.getConnection(url+db, user, pass);
   Statement st = con.createStatement();
   ResultSet rs=st.executeQuery("SELECT username FROM leaderboard WHERE " + username.toLowerCase());
   if(rs.next()){
   ResultSet joinamount = st.executeQuery("SELECT `joinamount`, `username` FROM `leaderboard` WHERE username = " + username.toLowerCase());
       String joinamountstr = "";
       joinamountstr = joinamount.getString("joinamount");
       int joinamountint = Integer.parseInt(joinamountstr);
       joinamountint = joinamountint + 1;
       int i= st.executeUpdate("UPDATE `leaderboard` (`id`, `username`, `lastlogin`) VALUES (NULL, '" + username.toLowerCase() + "', '"+ currentdate.toString() +"')");
       st.close();
       con.close();
   }else{
   ResultSet joinamount = st.executeQuery("SELECT `joinamount`, `username` FROM `leaderboard` WHERE username` = " + username.toLowerCase() + "`");
   String joinamountstr = "";
   joinamountstr = joinamount.getString("joinamount");
   int joinamountint = Integer.parseInt(joinamountstr);
   joinamountint = 1;
   int i= st.executeUpdate("INSERT INTO `leaderboard` (`id`, `username`, `diedamount`, `wonamount`, `firstlogin`, `lastlogin`) VALUES (NULL, '"+ username.toLowerCase() +"', '0', '0', '"+ currentdate.toString() +"', '"+ currentdate.toString() +"')");
   st.close();
   con.close();
   }
   }catch(Exception e){
   System.out.println(e);
   }
    }

 

insert into leaderboard on duplicate key update

 

So, that means my code should be like this:

Code:

public void WriteJoindb(String username){
DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
Date today = new Date();
String currentdate = dateFormat.format(today);
try{
Class.forName(driver);
Connection con = DriverManager.getConnection(url+db, user, pass);
Statement st = con.createStatement();
String joinamountstr = "";
joinamountstr = joinamount.getString("joinamount");
int joinamountint = Integer.parseInt(joinamountstr);
joinamountint = 1;
int i= st.executeUpdate("INSERT INTO `leaderboard` ON DUPLICATE KEY UPDATE (`id`, `username`, `diedamount`, `wonamount`, `firstlogin`, `lastlogin`) VALUES (NULL, '"+ username.toLowerCase() +"', '0', '0', '"+ currentdate.toString() +"', '"+ currentdate.toString() +"')");
st.close();
con.close();
}catch(Exception e){
System.out.println(e);
 }
}
{

 

No. You insert into the leaderboard using

Code:

INSERT INTO `leaderboard` (`field1`, `field2`) VALUES ('VALUE1', 'VALUE2') ON DUPLICATE KEY UPDATE `field1` = 'stuff', `field2` = 'otherstuff'

Think of it as a insert and update query combined.

 

I have it like this right now:

Code:

public void WriteJoindb(String username){
   DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
   Date today = new Date();
   String currentdate = dateFormat.format(today);
   try{
   Class.forName(driver);
   Connection con = DriverManager.getConnection(url+db, user, pass);
   Statement st = con.createStatement();
   //int i= st.executeUpdate("INSERT INTO `leaderboard` ON DUPLICATE KEY UPDATE (`id`, `username`, `diedamount`, `wonamount`, `firstlogin`, `lastlogin`) VALUES (NULL, '"+ username.toLowerCase() +"', '0', '0', '"+ currentdate.toString() +"', '"+ currentdate.toString() +"')");
   int i = st.executeUpdate("INSERT INTO `leaderboard` (`id`, `username`, `diedamount`, `winamount`, `firstlogin`, `lastlogin`) VALUES (NULL, '"+ username.toLowerCase() +"', '0', '0', '"+ currentdate.toString() +"', '"+ currentdate.toString() +"') ON DUPLICATE KEY UPDATE `lastlogin` = '" + currentdate.toString() + "'");
   st.close();
   con.close();
   }catch(Exception e){
   System.out.println(e);
        }
    }

But it seems to create a new row every time I log in :S

 

Bump

 

your 'username' column needs to be declared UNIQUE

 

Okay, and how do I do that in phpMyAdmin?

 

Google : mysql unique column
Answer on : http://www.w3schools.com/sql/sql_unique.asp

Create :

Code:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

Modify existing :

Code:

ALTER TABLE Persons
ADD UNIQUE (P_Id)

 

Code:

ALTER TABLE `leaderboard`
  ADD UNIQUE(`username`)

 

So, it should be like this:

Code:

CREATE TABLE IF NOT EXISTS `leaderboard` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `username` text COLLATE latin1_general_ci NOT NULL,
  `diedamount` int(4) NOT NULL,
  `wonamount` int(4) NOT NULL,
  `firstlogin` text COLLATE latin1_general_ci NOT NULL,
  `lastlogin` text COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`id`)
  ALTER TABLE `leaderboard`
  ADD UNIQUE ( `username` )
)
 

 

You didn't read my post

 

I did,
I used
ALTER TABLE Persons
ADD UNIQUE (P_Id)


Looks like I succesfully executed SQL query

Code:

ALTER TABLE leaderboard
ADD UNIQUE (username)

after changing the column type from text to varchar. Testing it right now

EDIT:
It works!
Thank you so much guys

EDIT by Moderator: merged posts, please use the edit button instead of double posting.

 

this in your create table :

,
CONSTRAINT uc_Username UNIQUE (Username)