Every time inserting new entry ? (Alias)

[Nihongo^]

Hi, I made some modifications to inserting alias in the database.

I want a new entry whenever the player's IP or UID changes, but I know when the script reads the first entry ( where IP is changed), it kept entering the new try whenever the player joined.

function AddAlias(player) {
    local q = QuerySQL(db, "SELECT * FROM Alias WHERE Name='" + player.Name + "'");
    if (!GetSQLColumnData(q, 0)) {
        QuerySQL(db, "INSERT INTO Alias (Name, IP, UID) VALUES ('" + player.Name + "', '" + player.IP + "', '" + player.UniqueID + "')");
    } else {
        local currentIP = GetSQLColumnData(q, 1);
        local currentUID = GetSQLColumnData(q, 2);
       
        if (currentUID != player.UniqueID) {
            QuerySQL(db, "INSERT INTO Alias (Name, IP, UID) VALUES ('" + player.Name + "', '" + player.IP + "', '" + player.UniqueID + "')");
        } else if (currentIP != player.IP) {
            QuerySQL(db, "INSERT INTO Alias (Name, IP, UID) VALUES ('" + player.Name + "', '" + player.IP + "', '" + currentUID + "')");
        }
    }
}

[habi]

do it like this:

Code Select Expand

local q = QuerySQL(db, "SELECT * FROM Alias WHERE Name='" + player.Name + "' AND IP='" + player.IP + "'");
    if (!GetSQLColumnData(q, 0)) {
        QuerySQL(db, "INSERT INTO Alias (Name, IP, UID) VALUES ('" + player.Name + "', '" + player.IP + "', '" + player.UniqueID + "')");
    }else {
q=QuerySQL(db, "SELECT * FROM Alias WHERE Name='"+player.Name+"' AND UID='"+player.UniqueID+"'");
if (!GetSQLColumnData(q, 0)) {
        QuerySQL(db, "INSERT INTO Alias (Name, IP, UID) VALUES ('" + player.Name + "', '" + player.IP + "', '" + player.UniqueID + "')");
    }
}


[Nihongo^]

do it like this:

Code Select Expand

local q = QuerySQL(db, "SELECT * FROM Alias WHERE Name='" + player.Name + "' AND IP='" + player.IP + "'");
    if (!GetSQLColumnData(q, 0)) {
        QuerySQL(db, "INSERT INTO Alias (Name, IP, UID) VALUES ('" + player.Name + "', '" + player.IP + "', '" + player.UniqueID + "')");
    }else {
q=QuerySQL(db, "SELECT * FROM Alias WHERE Name='"+player.Name+"' AND UID='"+player.UniqueID+"'");
if (!GetSQLColumnData(q, 0)) {
        QuerySQL(db, "INSERT INTO Alias (Name, IP, UID) VALUES ('" + player.Name + "', '" + player.IP + "', '" + player.UniqueID + "')");
    }
}


Its work Thank you so much

Just last question i want to grab the latest IP used by the player can you please guide me on it ?

Code Select Expand

case "ogetip":
if (highestRole.GetLevel() < 2) sendMessage(channels["staffchat"], "You do not have permission to use this command.");
else if (!text) sendMessage(channels["staffchat"], "**USAGE**: !"+ cmd +" <Full Nick>");
else
{
   local plr = GetTok( text, " ", 1 );
  local query = ::QuerySQL(db, "SELECT IP FROM Alias WHERE Name = '" + plr + "' ORDER BY Timestamp DESC LIMIT 1");
local latestIP = ::GetSQLColumnData(query, 0);
if (!query) {
sendMessage(channels["staffchat"], "[Error] There's no such name existing in the database.");
} else {
sendMessage(channels["staffchat"], "Player [" + plr + "] is using the latest IP: [" + latestIP + "]");
}
}
break;
I tried but not work it said  There's no such name existing in the database when i remove "ORDER BY Timestamp DESC LIMIT 1" it give me the ip but not the latest

[habi]

1. drop the table called Alias
2.

Code Select Expand

CREATE TABLE Alias(
    name ...,
    ip ...,
    uid ...,
    Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

[Nihongo^]

1. drop the table called Alias
2.

Code Select Expand

CREATE TABLE Alias(
    name ...,
    ip ...,
    uid ...,
    Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

I tried but it said CURRENT_TIMESTAM doesn't not exist.

Code Select Expand

QuerySQL(db, "CREATE TABLE IF NOT EXISTS Alias (Name TEXT, IP VARCHAR(20), UID TEXT, CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP)");

and function

Code Select Expand

function AddAlias(player) {
    local q = QuerySQL(db, "SELECT * FROM Alias WHERE Name='" + player.Name + "'");
    if (!GetSQLColumnData(q, 0)) {
 QuerySQL(db, "INSERT INTO Alias (Name, IP, UID, CreatedAt) VALUES ('" + player.Name + "', '" + player.IP + "', '" + player.UniqueID + "', '" + CURRENT_TIMESTAMP +"')")
    } else {
local q = QuerySQL(db, "SELECT * FROM Alias WHERE Name='" + player.Name + "' AND UID='" + player.UniqueID + "'");
    if (!GetSQLColumnData(q, 0)) {
QuerySQL(db, "INSERT INTO Alias (Name, IP, UID, CreatedAt) VALUES ('" + player.Name + "', '" + player.IP + "', '" + player.UniqueID + "', '" + CURRENT_TIMESTAMP +"')")
    }else {
q=QuerySQL(db, "SELECT * FROM Alias WHERE Name='"+player.Name+"' AND IP='"+player.IP+"'");
if (!GetSQLColumnData(q, 0)) {
QuerySQL(db, "INSERT INTO Alias (Name, IP, UID, CreatedAt) VALUES ('" + player.Name + "', '" + player.IP + "', '" + player.UniqueID + "', '" + CURRENT_TIMESTAMP +"')")
    }
}
}
}

[habi]

That field will automatically inserted when a new row is supplied.
Revert code of function back.

[Nihongo^]

That field will automatically inserted when a new row is supplied.
Revert code of function back.

Thanks it works

 but when i try to retrieve the latest IP it said There's no such name existing in the database   

Code Select Expand

case "ogetip":
if (highestRole.GetLevel() < 2) sendMessage(channels["staffchat"], "You do not have permission to use this command.");
else if (!text) sendMessage(channels["staffchat"], "**USAGE**: !"+ cmd +" <Full Nick>");
else {
local plr = GetTok(text, " ", 1);
local query = ::QuerySQL(db, "SELECT IP FROM Alias WHERE Name = '" + plr + "' ORDER BY Timestamp DESC LIMIT 1");
local latestIP = ::GetSQLColumnData(query, 0);
if (!query) {
sendMessage(channels["staffchat"], "[Error] There's no such name existing in the database.");
} else {
sendMessage(channels["staffchat"], "Player [" + plr + "] is using the latest IP: [" + latestIP + "]");
}
}
}


[habi]

Because it is searching for a column.

Code Select Expand

Timestamp DESC LIMIT 1

[Nihongo^]

Because it is searching for a column.

Code Select Expand

I did not understand you ? the name is already in the column?

Code Select Expand

local query = ::QuerySQL(db, "SELECT IP FROM Alias WHERE Name = '" + plr + "'Timestamp DESC LIMIT 1");
It still saying There's no such name existing in the database

[habi]

Code Select Expand

local query = ::QuerySQL(db, "SELECT IP FROM Alias WHERE Name = '" + plr + "' ORDER BY CreatedAt DESC LIMIT 1");


[Nihongo^]

Code Select Expand

local query = ::QuerySQL(db, "SELECT IP FROM Alias WHERE Name = '" + plr + "' ORDER BY CreatedAt DESC LIMIT 1");


Thank you so much habi <3

[2b2ttianxiu]

these is wrong
record data must have these: ip, UID1, UID2
and these cannot delete. If you delete. it couldnt query.
best way is use 'array' to record
belike: IP: 127.0.0.1, 192.168.1.2
UID1: 7aef..., 76ad...,
UID2: 6953..., 4fad...,

[2b2ttianxiu]

I have good alias system(AdvancedBannedSystem)