Try to create aka system with mysql

Try to create aka system with mysql - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+--- Thread: Try to create aka system with mysql (/showthread.php?tid=579324)

Try to create aka system with mysql - AroseKhanNiazi - 26.06.2015

I had the aka system with y_ini but now i need to do it with mysql.

pawn Код:

stock LoadPlayerAka(playerid)
{
    new mysqlquery[124];
    mysql_format(g_SQL, mysqlquery, sizeof(mysqlquery), "SELECT ip,username FROM aka LIMIT 1");
    print(mysqlquery);
    mysql_pquery(g_SQL, mysqlquery,"OnAkaLoad", "d", playerid);
}
forward OnAkaLoad(playerid);
public OnAkaLoad(playerid)
{
    new mysqlquery[124],name[MAX_PLAYER_NAME],bool:namefounded=false;
    for(new i, j = cache_get_row_count(g_SQL); i < j; i++) // loop through all the rows that were found
    {
        cache_get_field_content(i, "username", name, g_SQL, MAX_PLAYER_NAME);
        printf("Got row %d with name %s",i,name);
        if(strcmp(name,PlayerInfo[playerid][pUserName]) == 0)
        {
            printf("Name Matched in row %d with name %s",i,name);
            namefounded=true;
            break;
        }
    }
    if(namefounded=false)
    {
        mysql_format(g_SQL, mysqlquery, sizeof(mysqlquery), "INSERT INTO aka SET ip='%e',username='%e';",PlayerInfo[playerid][pIP],PlayerInfo[playerid][pUserName]);
        print(mysqlquery);
    }
    return 1;
}

I tried this code it's half as i couldn't really think

what to do next with the script.

I want is that it should check all rows of mysql in aka database for the playername and ip, if name exists it should check it with the ip of player. IF both exists it should goto next step where it should be checking if playername and ip are in same column like this
[ip] [name]
127.0.0.1 | Sasuke_Uchiha
127.0.0.1 | AroseKhanNiazi
81.111.111.222 | Random_user
81.122.222.222 | Sasuke_Uchiha

this is what i am trying to create, and more over how would i be able to get all the names that are stored for a specific id example i want
/aka ip 127.0.0.1
this will show me name
"Sasuke_Uchiha"
"AroseKhanNiazi"

if i do
/aka name Sasuke_Uchiha
should show me
127.0.0.1
81.122.222.222

I am trying from few days but can't figure it out

with my current code i get these errors

Код:

Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);

and this is my database code

PHP код:



-- phpMyAdmin SQL Dump

-- version 4.3.11

-- http://www.phpmyadmin.net

--

-- Host: 127.0.0.1

-- Generation Time: Jun 26, 2015 at 12:46 PM

-- Server version: 5.6.24

-- PHP Version: 5.6.8


SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

SET time_zone = "+00:00";



/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;


--

-- Database: `database`

--


-- --------------------------------------------------------


--

-- Table structure for table `aka`

--


CREATE TABLE IF NOT EXISTS `aka` (

  `ip` varchar(16) NOT NULL,

  `username` varchar(24) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;


/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Re: Try to create aka system with mysql - mamorunl - 26.06.2015

"Loop through all the rows that are found" 'LIMIT 1'.. You have a loop for 0 or 1 rows.

I'd recommend switching your statements. The conditional should be in the SQL: select * from `aka` where ip='%s' OR `username` = '%s'

You will have to fill the fields though. After this, you can just loop through all rows

Re: Try to create aka system with mysql - X337 - 26.06.2015

You have problem in your query

Код:

mysql_format(g_SQL, mysqlquery, sizeof(mysqlquery), "SELECT ip,username FROM aka LIMIT 1");

1. You limited the row
2. You need to use where statement to filter the data

So you can't do this thing with that query
[ip] [name]
127.0.0.1 | Sasuke_Uchiha
127.0.0.1 | AroseKhanNiazi
81.111.111.222 | Random_user
81.122.222.222 | Sasuke_Uchiha

Here is some example syntax:

Код:

mysql_format(g_SQL, mysqlquery, sizeof(mysqlquery), "SELECT ip,username FROM aka WHERE username = '%e' OR ip = '%e'", PlayerInfo[playerid][pUserName], PlayerInfo[playerid][pIP]);

So every data on that table with same IP or same Name as that player will be loaded.

And for this error:

Код:

Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);
Log("15:29:02","cache_get_field_content_int",2,"no active cache",0);

This error isn't from that code, it's from some script with "cache_get_field_content_int" syntax.

I hope you understand with this. And sorry about my english

Re: Try to create aka system with mysql - AroseKhanNiazi - 26.06.2015

thanks for the first part now second part

pawn Код:

forward OnAkaLoad(playerid); //forward 
public OnAkaLoad(playerid) // public 
{
    new mysqlquery[124], //storing mysql format
        name[MAX_PLAYER_NAME], // storing the name in row
        ip[16], //storing the ip in row
        namerow=-1, // will store where the name was found
        iprow=-1; // will store where the ip was found
    for(new i, j = cache_get_row_count(g_SQL); i < j; i++) // loop through all the rows that were found
    {
        cache_get_field_content(i, "username", name, g_SQL, MAX_PLAYER_NAME); // this will get the username from the current row
        cache_get_field_content(i, "ip", ip, g_SQL, MAX_PLAYER_NAME); //this will get the ip from the current row
        printf("Got row %d with name %s",i,name); // just for my own testing
        if(strcmp(name,PlayerInfo[playerid][pUserName]) == 0) // the will match if the name from that field matches player name
        {
            printf("Name Matched in row %d with name %s",i,name); // just for my own testing
            namerow=i; // will store player name row
        }
        if(strcmp(ip,PlayerInfo[playerid][pIP]) == 0) //this will match if the ip from player matches the one in row
        {
            printf("IP Matched in row %d with name %s",i,name); // just for my own testing
            iprow=i; // will store player ip row
        }
        if(namerow == iprow) // this will check if the player name row and ip row are same 
        {
            break; // as they are same no need to do anything further
        }
        
    }
    if(namerow != -1) // will check if any row with that name was founded or not
    {
        mysql_format(g_SQL, mysqlquery, sizeof(mysqlquery), "INSERT INTO aka SET ip='%e',username='%e';",PlayerInfo[playerid][pIP],PlayerInfo[playerid][pUserName]);
        print(mysqlquery);
        mysql_pquery(g_SQL,mysqlquery); //no row with that name so inserting the aka
    }
    if(iprow != -1) //will check if any row with that ip was founded or not
    {
        mysql_format(g_SQL, mysqlquery, sizeof(mysqlquery), "INSERT INTO aka SET ip='%e',username='%e';",PlayerInfo[playerid][pIP],PlayerInfo[playerid][pUserName]);
        print(mysqlquery);
        mysql_pquery(g_SQL,mysqlquery); // no row with this ip inserting the aka
    }
    if(namerow != iprow) // will check if this ip was also stored for the name we are checking
    {
        mysql_format(g_SQL, mysqlquery, sizeof(mysqlquery), "INSERT INTO aka SET ip='%e',username='%e';",PlayerInfo[playerid][pIP],PlayerInfo[playerid][pUserName]);
        print(mysqlquery);
        mysql_pquery(g_SQL,mysqlquery); // updating the aka.
    }
    return 1;

but it only printed

Код:

SELECT ip,username FROM aka WHERE username = 'Sasuke_Uchiha' OR ip = '127.0.0.1'

And yes that mysql log was my error some where else not nothing in log about this error too.

And the above code is note even updating the aka

EDIT fixed the above code had a small mistake

pawn Код:

forward OnAkaLoad(playerid); //forward 
public OnAkaLoad(playerid) // public 
{
    new mysqlquery[124], //storing mysql format
        name[MAX_PLAYER_NAME], // storing the name in row
        ip[16], //storing the ip in row
        namerow=-1, // will store where the name was found
        iprow=-1; // will store where the ip was found
    for(new i, j = cache_get_row_count(g_SQL); i < j; i++) // loop through all the rows that were found
    {
        cache_get_field_content(i, "username", name, g_SQL, MAX_PLAYER_NAME); // this will get the username from the current row
        cache_get_field_content(i, "ip", ip, g_SQL, MAX_PLAYER_NAME); //this will get the ip from the current row
        printf("Got row %d with name %s",i,name); // just for my own testing
        if(strcmp(name,PlayerInfo[playerid][pUserName]) == 0) // the will match if the name from that field matches player name
        {
            printf("Name Matched in row %d with name %s",i,name); // just for my own testing
            namerow=i; // will store player name row
        }
        if(strcmp(ip,PlayerInfo[playerid][pIP]) == 0) //this will match if the ip from player matches the one in row
        {
            printf("IP Matched in row %d with name %s",i,name); // just for my own testing
            iprow=i; // will store player ip row
        }
        if(namerow == iprow) // this will check if the player name row and ip row are same 
        {
            break; // as they are same no need to do anything further
        }
        
    }
    if(namerow == -1) // will check if any row with that name was founded or not
    {
        mysql_format(g_SQL, mysqlquery, sizeof(mysqlquery), "INSERT INTO aka SET ip='%e',username='%e';",PlayerInfo[playerid][pIP],PlayerInfo[playerid][pUserName]);
        print(mysqlquery);
        mysql_pquery(g_SQL,mysqlquery); //no row with that name so inserting the aka
    }
    else if(iprow == -1) //will check if any row with that ip was founded or not
    {
        mysql_format(g_SQL, mysqlquery, sizeof(mysqlquery), "INSERT INTO aka SET ip='%e',username='%e';",PlayerInfo[playerid][pIP],PlayerInfo[playerid][pUserName]);
        print(mysqlquery);
        mysql_pquery(g_SQL,mysqlquery); // no row with this ip inserting the aka
    }
    else if(namerow != iprow) // will check if this ip was also stored for the name we are checking
    {
        mysql_format(g_SQL, mysqlquery, sizeof(mysqlquery), "INSERT INTO aka SET ip='%e',username='%e';",PlayerInfo[playerid][pIP],PlayerInfo[playerid][pUserName]);
        print(mysqlquery);
        mysql_pquery(g_SQL,mysqlquery); // updating the aka.
    }
    return 1;
}
public OnQueryError(errorid, error[], callback[], query[], connectionHandle)
{
    switch(errorid)
    {
        case CR_SERVER_GONE_ERROR:
        {
            printf("Lost connection to server, trying reconnect...");
            mysql_reconnect(connectionHandle);
        }
        case ER_SYNTAX_ERROR:
        {
            printf("Something is wrong in your syntax, query: %s",query);
        }
    }
    return 1;
}

fixed version to help anyone else
and thanks for the help +rep