Using PHP classes when adding users to database

Question

Originally I used concatenated queries created by for loops to add large numbers of players/users to a mysql DB. Today, I wanted to experiment with classes because I didn't really understand their use case.

In the class below the new user(+data) is added to the DB and returns the unique ID number for the last inserted row.

I would have assumed that doing this insert as one query would be fastest way of doing it, rather than each time I instantiate a new player, but to be honest I'm still at the beginner phase so would welcome some advice, or suggestions on how best to optimise this.

Ideally as one query, Unless I'm wrong in assuming this is faster?

Is it possible to return the Unique ID's for multiple players as an array?

class player {
    var $player;
    var $PlayerID;
    var $email;
    function create_player($new_player, $email, $password, $db) {
        $this->player = $new_player;
        $this->email = $email;

        $salt = "xxx"; //simplified for this example            
        $passwordhash = $password; //simplified for this example
        $query = "INSERT INTO userstest (name, email, hash, salt ) values(:name, :email, :hash, :salt)";
        $params = array (':name' => $new_player, ':email' => $email, ':hash' => $passwordhash, ':salt' => $salt);

        $stmt = $db->prepare($query); 
        $stmt->execute($params); 
        $id = $db->lastInsertId();
        $this->PlayerID = $id;
    }
}

It would be awesome if instead of just down voting you would take the time to tell me why it is you deem this a poor question. While it's fairly apparent to you, it's unclear to me. Hence the question. At least with with reason I have some direction to go research and understand further. — denski
– denski, Commented Feb 28, 2017 at 22:47
When inserting multiple rows it maybe worth using 'batched insert with transactions. i.e.keep calling the ` $stmt->execute($params);` with different parameters. see details about it: : PDO - Insert large array to MySQL database. It can be surprisingly efficient and is simple to code. — Ryan Vincent
– Ryan Vincent, Commented Mar 1, 2017 at 1:07

Wajih · Accepted Answer · 2017-03-01 05:42:01Z

I'm using PDO as it's preferred solution to deal with Database from PHP, You can try this structure:

Class Player {
    public $playerName;
    public $playerID;
    public $email;
    public $pass;
    public $salt;
}

Class PlayerDAO {
    public function add(Player $player) {
        $sql = "INSERT INTO userstest (name, email, hash, salt) values(?, ?, ?, ?)";
        $params = [$player->playerName, $player->email, $player->pass, $player->salt];

        $this->db_add($sql, $params, false);
    }

    public function add_multi($players) {
        $sql = "INSERT INTO userstest (name, email, hash, salt) values(?, ?, ?, ?)";
        $params_arr = [];
        foreach($players as $player) {
            $params = [$player->playerName, $player->email, $player->pass, $player->salt];
            $params_arr[] = $params;
        }

        $this->db_add($sql, $params, true);
    }


    private function db_add($sql, $params, $is_multi) {
        $stmt = $this->link->prepare($sql);

        $res = [];
        if ($is_multi) {
            foreach ($params as $params_1) {
                $res[] = $stmt->execute($params_1);
            }
        } else {
            $res[] = $stmt->execute($params);
        }
        if (!$res) {
            print_r($stmt->errorInfo());
            //$this->errorCode = $stmt->errorCode();
        }
        //$this->rowsAffected = $stmt->rowCount();
        //$this->lastInsertedId = $this->link->lastInsertId();

        return $res;
}

Make sure to prepare $link var as the db connector.

Also it you have multi classes you can make a parent class for them,

M31 · Accepted Answer · 2017-03-01 05:16:40Z

Its not necessarily better to run it as a single query. It depends on your use case and goals. I generally do individual queries as my use case is normally to add things to the database from a web form. My classes generally look something like:

Class Player{
  protected $id;
  protected $uname;
  protected $email;

  public function __construct($id){
    $this->id = $id;
  }
  public function initialize(inputArray){
    if(isset(inputArray['id'])){$this->id = inputArray['id'];}
    if(isset(inputArray['uname'])){$this->uname = inputArray['uname'];}
    if(isset(inputArray['email'])){$this->email = inputArray['email'];}
  }
  public function mysql_insert($con){
    $sql = "INSERT INTO TABLE (Uname,email) Values(".$this->uname.",".$this->email.")";
    mysqli_query($con,$sql);
    $this->id = mysqli_insert_id($con);
    return $this->id;
  }
}

This way you can take your object data, where ever that comes from and then generically add it to the database. Your class is functional, but you're working a bit to hard by creating the database connection in your class, just pass the open connection object. This also would allow you to iterate through your objects making individual queries against the same database connection:

$con = mysqli_connect("127.0.0.1", "my_user", "my_password", "my_db");
$data = array_of_player_arrays[];
$ids = array();

for($i = 0; $i < count($data); i++){
  $p = new Player();
  $p->initialize($data[$i]);
  $ids[] = $p->mysql_insert($con);
}

By making mysql_insert() return the id value you can capture the IDs added in that run. The goal should be to continue to add functions to the class to make handling the Player data easily repeatable. Try adding mysql_select() and mysql_update() functions or a toArray() function to help complete the loop of behaviors needed to handle the data without needing to script the interaction beyond the use of these classes. If you really wanted to run it as one query you could return the insert sql in the loop rather than the ID to concatenate the same way you have been, but if I'm not mistaken thats pretty much the same as running them individualy. Hope this helps.

Doc999tor · Accepted Answer · 2017-03-01 00:03:04Z

0

You have a few options:
You can create another class that will have a loop with new Players creation. But you will have multiple queries.
Another way is to create a multiquery: one query with multiple inserts.
One player class will create its own query string and return it to PDO.
After the multiquery returns you will have all the id of your rows.
You can check the docs about the syntax.

answered Mar 1, 2017 at 0:03

Doc999tor

3004 silver badges15 bronze badges

Collectives™ on Stack Overflow

Using PHP classes when adding users to database

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related