Insert default value

Question

I create a table in php like this through the method createTableUsers().

    function createTableUsers() {
       $db = Database::getInstance(); 
       $query = "CREATE TABLE IF NOT EXISTS users (
                   id INTEGER AUTO_INCREMENT NOT NULL,
                   username VARCHAR(50) NOT NULL UNIQUE KEY,
                   password VARCHAR(100) NOT NULL,
                   email VARCHAR(50) NOT NULL,
                   first_name VARCHAR(50) NOT NULL default 'default_text', 
                   last_name VARCHAR(50) NOT NULL default 'default_text'
                   PRIMARY KEY(id)
                );";      
       $stmt = $db->prepare($query);
       $stmt->execute();
    }  

When I insert a new user do:

   $db = Database::getInstance();               
   $username = "John";
   $email = "john@example.com";
   $password = "john";
   $this->user = new User();
   $this->user->setUsername($username);
   $this->user->setEmail($email);
   $this->user->setPassword($password);
   $this->user->save();

So I use the methods of the User class:

   class User {

    private $id = null;
    private $username = null;   
    private $password = null;
    private $email = null;   
    private $first_name = null;
    private $last_name = null;

    //GET & SET methods...


    public static function load(array $data) {
      $u = new User();
      $u->id = $data['id'];
      $u->setUsername($data['username']);
      $u->password = $data['password'];
      $u->setEmail($data['email']);      
      $u->setFirstName($data['first_name']);
      $u->setLastName($data['last_name']);
      return $u;
    }

    public function save() {
      $new = is_null($this->id);

      $db = Database::getInstance();

      if($new) {
        $sql = "INSERT INTO `users` (`username`, `password`, `email`, `first_name`, `last_name`) "
              . "VALUES (?, ?, ?, ?, ?)";
        $stmt = $db->prepare($sql);
        $stmt->execute(array($this->getUsername(), $this->password, $this->getEmail(), $this->getFirstName(), $this->getLastName()));       
        $this->id = $db->lastInsertedId();
      }
      else {
        $sql = "UPDATE `users` SET `username` = ?, `password` = ?, `email` = ?, `first_name` = ?, `last_name` = ? WHERE `id` = ?";
        $stmt = $db->prepare($sql);
        $stmt->execute(array($this->getUsername(), $this->password, $this->getEmail(), $this->getFirstName(), $this->getLastName(), $this->id));
      }
    }    
  }

However, when I insert a new user, for example in the signup section, I do not know all the fields, but only a few of them (username, password, and email). What I want is a tuple like: John | john | john@example.com | default_value | default_value.

Instead, the tuple inserted is John | john | john@example.com | NULL | NULL.

I have tried both putting single quotation marks', either by putting `. But it is the same, always inserts NULL.

How do you do enter a default value instead of NULL? I have the syntax wrong? Thanks

Answer 1

You don't leverage the default values if you set them from PHP and the variables are initialized with PHP's null (which is translated into SQL's NULL). Use this:

$sql = "INSERT INTO `users` (`username`, `password`, `email`) "
    . "VALUES (?, ?, ?)";

Don't set the other values at all.