[Complete] Sql

Discussion in 'Systems Administration' started by Achieving, May 25, 2017.

  1. So the server i just started work for is storing all of their player data in essentials user info. I want to transfer every user's name and ip in 2 separate columns in a MySQL Database. How do i do this quickly and without having to enter them manually?

    Code (Text):
    ** EXAMPLE **

    lastAccountName: Test
    timestamps:
      login: 1490492639675
      logout: 1490492732126
    ipAddress: 127.0.0.1
    lastlocation:
      world: world
      x: 0.5
      y: 79.0
      z: 0.5
      yaw: 0.0
      pitch: 0.0
    logoutlocation:
      world: world
      x: -22.081976360348545
      y: 77.0
      z: -3.0827260384386888
      yaw: -12.082925
      pitch: 15.198275
     
    Each user's data is in different files.
     
  2. #2 blackflagprogram, May 25, 2017
    Last edited: May 25, 2017
  3. Strahan

    Benefactor

    Not really complex. Took me about three minutes and a function lifted from Googling how to convert UUID to name hehe. Assumes you run it in the userdata folder. I also took no time to add error detection/handling or anything of that nature but should work.. did for me when I tested.

    Code (Text):
    <?php

      $pdo = new PDO("mysql:host=localhost; dbname=whatever", "username", "password");
      foreach (glob(getcwd() . "/*.yml") AS $file) {
        foreach (explode("\n", file_get_contents($file)) AS $line) {
          $tmp = explode(": ", $line);
          if ($tmp[0] == "ipAddress") {
            $uuid = pathinfo($file, PATHINFO_FILENAME);
            $username = uuid_to_username($uuid);
            $sql = $pdo->prepare("INSERT INTO sometable (username, uuid, ip) VALUES (?, ?, ?)");
            $sql->execute(array($username, $uuid, $tmp[1]));
          }
        }
      }
      $pdo = null;

      function uuid_to_username($uuid) {
          $uuid = minify_uuid($uuid);
          if (is_string($uuid)) {
              $json = file_get_contents('https://api.mojang.com/user/profiles/' . $uuid . '/names');
              if (!empty($json)) {
                  $data = json_decode($json, true);
                  if (!empty($data) and is_array($data)) {
                      $last = array_pop($data);
                      if (is_array($last) and isset($last['name'])) {
                          return $last['name'];
                      }
                  }
              }
          }
          return false;
      }
      function minify_uuid($uuid) {
          if (is_string($uuid)) {
              $minified = str_replace('-', '', $uuid);
              if (strlen($minified) === 32) {
                  return $minified;
              }
          }
          return false;
      }

    ?>
     
    • Winner Winner x 1