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?
Row already acts like an array. Follow this answer: http://stackoverflow.com/questions/17371639/how-to-store-arrays-in-mysql
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
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 ? 1 : 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__); } }
Wait,whut? What plugin exactly? Anyway, here is the full class code PHP: <?phpnamespace 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($player, Session::$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 ? 1 : 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 ? 1 : 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; }}
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()]);
@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
You want to prevent double register? PHP: $data = $dataProvider->getPlayer($player->getName());if($data['password'] !== ""){ # Player is already registered.} else { # Player must register.}
Yes the register and login and so on works but after a few hours the plugin "times out" from the database
PHP: $plugin->getServer()->getScheduler()->scheduleRepeatingTask(new MySQLPingTask($this->plugin, $this->database), 600); In the task PHP: $this->database->ping();