CODEWITHSUNDEEP
sqldatabasesql-update
ofmiceandmoon
ofmiceandmoon

Reputation: 596

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.

Upvotes: 2

Views: 112

Answers (2)

DDS
DDS

Reputation: 2479

On the database: 

alter table alter <column_name> set default <default_value>;

assuming you're on mysql/mariadb so for each new comment you have the fields with default value.

This code will update the data already in the db with the new value.

update <table_name> set <column_name> = <default_value> where <column_name> is null

just insert the column name you need

Upvotes: 1

Thomas Brok
Thomas Brok

Reputation: 409

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

Upvotes: 2

Related Questions