How to update a database table letting two fields be empty/not filled/null

On a blog I’m coding I want admins to be able to edit a comment. This works just fine, but there’s a problem with writing a new comment because of two fields in the table that should be empty then.

The Db-table comments is build up like this:

comments(id, content, post_id, comment_author, date, editedBy, editedAt)

When a new comment is written the fields editedBy and editedAt should be empty/just not filled/null, since it hasn’t been updated obviously.

I can’t just leave the fields empty, can I put an empty string in them or mark them as NULL?

public function insertForPost($content, $post_id, $comment_author, $date, $editedBy, $editedAt)
{
  $table = $this->getTableName();

  $stmt = $this->pdo->prepare(
    "INSERT INTO `{$table}`(`content`, `post_id`, `comment_author`, `date`,
    `editedBy`, `editedAt`) VALUES (:content, :post_id, :comment_author, :date,
    :editedBy, :editedAt)"
  );

  $stmt->execute([
    'content' => $content,
    'post_id' => $post_id,
    'comment_author' => $comment_author,
    'date' => $date,
    'editedBy' => $editedBy,
    'editedAt' => $editedAt
  ]);
}

All parameters have the right data in them, I just don’t know what to do with editedBy and editedAt.

When the comment gets edited(which works just fine) editedBy will be filled with the username of the one editing the comment and editedAt will be filled with the date when the comment has been edited, so both are Strings.

I want to know if I need to put an empty string in these two fields or set them null and how to do it.

Answer

As long as editedAt and editedBy are nullable (so the columns don’t include a NOT NULL statement during the creation of the table), you can leave them from the set of columns inbetween the tablename and the VALUES clause with an insert or update statement. Hence, the query results in:

INSERT INTO `{$table}`(`content`, `post_id`, `comment_author`, `date`) VALUES (:content, :post_id, :comment_author, :date);

Advertisement

Answer