CODEWITHSUNDEEP
phpmysqlclass
denski
denski

Reputation: 2040

Using PHP classes when adding users to database

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;
    }
}

Upvotes: 1

Views: 480

Answers (3)

Wajih
Wajih

Reputation: 4383

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,

Upvotes: 1

M31
M31

Reputation: 1418

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.

Upvotes: 1

Doc999tor
Doc999tor

Reputation: 300

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.

Upvotes: 0

Related Questions