Vice City: Multiplayer

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.

Quotefunction 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 + "')");
        }
    }
}

(https://i.postimg.cc/MKx55xK2/Untitled.png)

do it like this: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 + "')");
    }
}


Quote from: habi on Jul 04, 2023, 04:36 PMdo 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 ?

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

1. drop the table called Alias
2. CREATE TABLE Alias(
    name ...,
    ip ...,
    uid ...,
    Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

Quote from: habi on Jul 06, 2023, 07:30 AM1. 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.

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

and function

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 +"')")
    }
}
}
}
(https://i.postimg.cc/2j0zm6SW-/Untitled.png)

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

Quote from: habi on Jul 06, 2023, 10:01 AMThat 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  ??? 

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 + "]");
}
}
}


Because it is searching for a column.
Timestamp DESC LIMIT 1

Quote from: habi on Jul 06, 2023, 10:24 AMBecause it is searching for a column.

Code Select Expand

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

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

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


Quote from: habi on Jul 06, 2023, 10:58 AM

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

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...,

I have good alias system(AdvancedBannedSystem)