Advertisement
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Arrays meets MySQL

Comments in 'Plugin Development' started by JackboyPlay, May 10, 2016.

  1. JackboyPlay
    Offline

    JackboyPlay Active Member

    Joined:
    Apr 25, 2015
    Posts:
    507
    Minecraft User:
    JackboyPlay
    How can I puts an array into a mysql table aka. row?
    And how can I set the values from the array in mysql into the array?
  2. Primus
    Offline

    Primus Notable Member

    Joined:
    Apr 7, 2015
    Posts:
    1,470
    Minecraft User:
    PrimusLV
    applqpak likes this.
  3. JackboyPlay
    Offline

    JackboyPlay Active Member

    Joined:
    Apr 25, 2015
    Posts:
    507
    Minecraft User:
    JackboyPlay
    I want to store an array in one row
  4. JackboyPlay
    Offline

    JackboyPlay Active Member

    Joined:
    Apr 25, 2015
    Posts:
    507
    Minecraft User:
    JackboyPlay
    PHP:
    $this->mysqli->query("CREATE TABLE IF NOT EXISTS `test` (
    `name` VARCHAR(255) PRIMARY KEY,
    `players` VARCHAR(255) DEFAULT 0
    )"
    );
    And in players I want to store an array
  5. JackboyPlay
    Offline

    JackboyPlay Active Member

    Joined:
    Apr 25, 2015
    Posts:
    507
    Minecraft User:
    JackboyPlay
    @Primus did you know what I mean?
  6. Primus
    Offline

    Primus Notable Member

    Joined:
    Apr 7, 2015
    Posts:
    1,470
    Minecraft User:
    PrimusLV
    Exclusive code from my server core plugin
    PHP:
    public function addPlayer($player, array $data)
      {
      
    $name trim(strtolower($player->getName()));
      
    $values "";
      
    $keys "";
      
    $i 1;
      
    $m count($data);
      foreach (
    $data as $k => $v) {
        if (
    is_bool($v)) {
            
    $v = ($v 0);
        }
         
    $v "'$v'";
         if (
    $k == 'name'$v trim(strtolower($v));
         
    $keys .= "`$k`";
         
    $values .= $v;
         if (
    $i $m) {
           
    $values .= ", ";
           
    $keys .= ", ";
         }
           
    $i++;
        }
        
    $keys .= ", `name`";
        
    $values .= ", '$name'";
        
    $query "INSERT INTO `players` ($keys) VALUES ($values);";
        
    $this->database->query($query);

        if (
    $e $this->database->error) {
          
    $this->getPlugin()->getLogger()->warning("MySQL Error: (" $e ") " __CLASS__ "::" __METHOD__ " at line " __LINE__);
        }
      }
  7. JackboyPlay
    Offline

    JackboyPlay Active Member

    Joined:
    Apr 25, 2015
    Posts:
    507
    Minecraft User:
    JackboyPlay
    I am fame because I have a part from your core xD
  8. JackboyPlay
    Offline

    JackboyPlay Active Member

    Joined:
    Apr 25, 2015
    Posts:
    507
    Minecraft User:
    JackboyPlay
    How can I remove the player?
  9. Primus
    Offline

    Primus Notable Member

    Joined:
    Apr 7, 2015
    Posts:
    1,470
    Minecraft User:
    PrimusLV
    Wait,whut? :D What plugin exactly?

    Anyway, here is the full class code
    PHP:
    <?php
    namespace sc\Data;

    use 
    sc\SupremeCore;
    use 
    sc\Utils\Session;
    use 
    sc\Task\MySQLPingTask;

    use 
    pocketmine\Player;

    class 
    MySQLDataProvider implements DataProvider
    {

      
    /** @var SupremeCore */
      
    protected $plugin;
      
    /** @var msqli */
      
    protected $database;

      public function 
    __construct(SupremeCore $plugin$host 'localhost'$user 'root'$password ''$database 'default'$port 3306)
      {
      
    $start microtime(true);
      
    $this->plugin $plugin;
      
    $plugin->getLogger()->info("Initializing MySQL...");

      
    $this->database = new \mysqli($host$user$password$database);
      if (
    $this->database->connect_error) {
      
    $plugin->getLogger()->warning("Couldn't connect to MySQL: " $this->database->connect_error);
      return;
      }

      
    /** ==================================== PLAYER ====================================  **/
      
    $this->database->query("CREATE TABLE IF NOT EXISTS players (
                      name VARCHAR(16) PRIMARY KEY,
                      rank CHAR(128) not null default '',
                      lastOnline INT not null default 0,
                      kills SMALLINT not null default 0,
                      deaths SMALLINT not null default 0,
                      coins INT not null default 0,
                      xp INT not null default 0,
                      level SMALLINT not null default 1,
                      online BOOLEAN not null default 0,
                      alive BOOLEAN not null default 1,
                      clan VARCHAR(16) not null default '',
                      isAuthenticated BOOLEAN not null default 0,
                      isRegistered BOOLEAN not null default 0,
                      password VARCHAR(55) not null default '',
                      ip VARCHAR(16),
                      uniqid VARCHAR(128) not null default '',
                      status TINYINT not null default 0
                      # What mini-game
                      # Rank
                     );"
      
    );

      if (
    $e $this->database->error) {
      
    $plugin->getLogger()->warning("MySQL Error: " $e);
      }

      
    $plugin->getServer()->getScheduler()->scheduleRepeatingTask(new MySQLPingTask($this->plugin$this->database), 600);
      
    $plugin->getLogger()->info("Data provider: MySQL (" round((microtime(true) - $start), 3) . "s)");
      }

      public function 
    getPlayer($player): array
      {
      
    $name trim(strtolower($player->getName()));
      
    $r $this->database->query("SELECT * FROM `players` WHERE `name` LIKE '{$name}'");
      if (
    $r->num_rows) {
      
    var_dump($r);
      return 
    $r->fetch_assoc();
      } else {
      
    $this->addPlayer($playerSession::$defaultSessionData);
      
    $r $this->database->query("SELECT * FROM `players` WHERE `name` LIKE '{$name}'");
      if (
    $r->num_rows) {
      return 
    $r->fetch_assoc();
      } else {
      return [];
      }
      }
      }

      public function 
    addPlayer($player, array $data)
      {
      
    $name trim(strtolower($player->getName()));
      
    $values "";
      
    $keys "";
      
    $i 1;
      
    $m count($data);
      foreach (
    $data as $k => $v) {
      if (
    is_bool($v)) {
      
    $v = ($v 0);
      }
      
    $v "'$v'";
      if (
    $k == 'name'$v trim(strtolower($v));
      
    $keys .= "`$k`";
      
    $values .= $v;
      if (
    $i $m) {
      
    $values .= ", ";
      
    $keys .= ", ";
      }
      
    $i++;
      }
      
    $keys .= ", `name`";
      
    $values .= ", '$name'";
      
    $query "INSERT INTO `players` ($keys) VALUES ($values);";
      
    $this->database->query($query);

      if (
    $e $this->database->error) {
      
    $this->getPlugin()->getLogger()->warning("MySQL Error: (" $e ") " __CLASS__ "::" __METHOD__ " at line " __LINE__);
      }
      }

      public function 
    setData($player, array $data)
      {
      
    $name trim(strtolower($player->getName()));
      
    $sets "";
      
    $i 1;
      
    $m count($data);
      foreach (
    $data as $key => $v) {
      if (
    is_bool($v)) {
      
    $v = ($v 0);
      }
      
    $v "'$v'";
      
    $sets .= "`$key` = $v";
      if (
    $i $m$sets .= ", ";
      
    $i++;
      }
      
    $query "UPDATE `players` SET $sets WHERE `name` LIKE '$name';";
      
    $this->database->query($query);

      if (
    $e $this->database->error) {
      
    $this->getPlugin()->getLogger()->warning("MySQL Error: (" $e ") " __CLASS__ "::" __METHOD__ " at line " __LINE__);
      }
      }

      public function 
    removePlayer($player)
      {
      
    $name trim(strtolower($player->getName()));
      
    $this->database->query("DELETE FROM `players` WHERE `name` = \'{$name}\';");
      if (
    $e $this->database->error) {
      
    $this->getPlugin()->getLogger()->warning("MySQL Error: (" $e ") " __CLASS__ "::" __METHOD__ " at line " __LINE__);
      }
      }

      
    /**
      * @param $key
      * @param $dataType
      * @param $default
      * @param bool $null
      */
      
    public function addRow($key$dataType$default$null false)
      {
      
    # TODO
      
    }


      public function 
    close()
      {
      
    $this->database->close();
      
    $this->getPlugin()->getLogger()->info("Closed MySQL Connection.");
      }

      public function 
    save()
      {
      }

      public function 
    getPlugin() : SupremeCore
      
    {
      return 
    $this->plugin;
      }
    }
    Tim // robske Büba likes this.
  10. JackboyPlay
    Offline

    JackboyPlay Active Member

    Joined:
    Apr 25, 2015
    Posts:
    507
    Minecraft User:
    JackboyPlay
    @Primus
    And I can input the player like this ?
    PHP:
    $this->addPlayer($player$data);
  11. Primus
    Offline

    Primus Notable Member

    Joined:
    Apr 7, 2015
    Posts:
    1,470
    Minecraft User:
    PrimusLV
    Yeah if you are in context of class which I gave you.
  12. JackboyPlay
    Offline

    JackboyPlay Active Member

    Joined:
    Apr 25, 2015
    Posts:
    507
    Minecraft User:
    JackboyPlay
    I do it on PlayerJoin must I define $data?
  13. Tim // robske Büba
    Offline

    Tim // robske Büba Notable Member

    Joined:
    Feb 26, 2014
    Posts:
    606
    Minecraft User:
    robske_110
    How about just use serialize and then unserialize it
  14. Primus
    Offline

    Primus Notable Member

    Joined:
    Apr 7, 2015
    Posts:
    1,470
    Minecraft User:
    PrimusLV
    Yes, it depends on what keys player's row on MySQL holds. In example below, let's imagine that we have only one key - 'name'
    PHP:
    $this->getPlugin()->getDataProvider()->addPlayer($e->getPlayer(), ['name' => $e->getPlayer()->getName()]);
    JackboyPlay likes this.
  15. JackboyPlay
    Offline

    JackboyPlay Active Member

    Joined:
    Apr 25, 2015
    Posts:
    507
    Minecraft User:
    JackboyPlay
    @Primus I have an last question to you!
    I have an LoginPlugin with MySQL and so on and after a few hours [without restart] say the plugin that I must register my account but my account is registered and before the few hours are gone it works with the login
  16. Primus
    Offline

    Primus Notable Member

    Joined:
    Apr 7, 2015
    Posts:
    1,470
    Minecraft User:
    PrimusLV
    You want to prevent double register?
    PHP:
    $data $dataProvider->getPlayer($player->getName());
    if(
    $data['password'] !== ""){
        
    # Player is already registered.
    } else {
        
    # Player must register.
    }
  17. JackboyPlay
    Offline

    JackboyPlay Active Member

    Joined:
    Apr 25, 2015
    Posts:
    507
    Minecraft User:
    JackboyPlay
    Yes the register and login and so on works but after a few hours the plugin "times out" from the database
  18. Primus
    Offline

    Primus Notable Member

    Joined:
    Apr 7, 2015
    Posts:
    1,470
    Minecraft User:
    PrimusLV
    PHP:
    $plugin->getServer()->getScheduler()->scheduleRepeatingTask(new MySQLPingTask($this->plugin$this->database), 600);
    In the task
    PHP:
    $this->database->ping();
    JackboyPlay likes this.
  19. JackboyPlay
    Offline

    JackboyPlay Active Member

    Joined:
    Apr 25, 2015
    Posts:
    507
    Minecraft User:
    JackboyPlay
    Oh thanks :D

Share This Page

Advertisement