Default value on insert PHP and SQL [duplicate]

Question

I want to make that when someone inserts a post into my database that if they don't supply their name it defaults to anonymous. How would I do this? My code here

$query =  "INSERT INTO cinemaPost (name, text, likes, reply_to) VALUES (?, ?, 0, NULL)";

$stmt = $conn->prepare($query);
$stmt->bindValue(1, $post_data['name']);
$stmt->bindValue(2, $post_data['post']);

$stmt->execute();

I have tried doing a default in the create tables for the db but when the user triggers the insert it overides it as a blank field.

CREATE TABLE CinemaPost(
  id INT AUTO_INCREMENT NOT NULL,
  name varchar(255) NOT NULL DEFAULT 'anonymous',
  text varchar(100) NOT NULL,
  post_date timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  likes INT NOT NULL,
  reply_to INT,
  Primary KEY (id)
);

What's the exact problem? Why not check whether the field containing the name is empty (by whatever terms you define)? — Nico Haase
– Nico Haase, Commented Jun 3, 2020 at 7:26
stackoverflow.com/a/44717227/2943403 , stackoverflow.com/a/6459214/2943403 — mickmackusa
– mickmackusa ♦, Commented Jun 3, 2020 at 8:14

Nick · Accepted Answer · 2020-06-03 08:11:16Z

2

The default value will only be used if you don't actually insert to the column i.e. you would need to remove name from the INSERT column list. For example:

if (empty($post_data['name'])) {
    $query =  "INSERT INTO cinemaPost (text, likes, reply_to) VALUES (?, 0, NULL)";
    $stmt = $conn->prepare($query);
    $stmt->bindValue(1, $post_data['post']);
}
else {
    $query =  "INSERT INTO cinemaPost (name, text, likes, reply_to) VALUES (?, ?, 0, NULL)";
    $stmt = $conn->prepare($query);
    $stmt->bindValue(1, $post_data['name']);
    $stmt->bindValue(2, $post_data['post']);
}

It's probably simplest just to check the input value and replace it with "anonymous" if it's empty instead:

$stmt->bindValue(1, empty($post_data['name']) ? 'anonymous' : $post_data['name']);

edited Jun 3, 2020 at 8:11

answered Jun 3, 2020 at 3:21

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mickmackusa Over a year ago

Is this question perhaps a duplicate on Stack Overflow, Nick? You don't need rep anymore. Please help the team by closing close-able question instead of answering.

Nick Over a year ago

@mickmackusa the only one of those which could be considered to be a duplicate - because it relates to setting a default value in the database - is the first and the answer to that has 2 (of the 3) parts of it which are incorrect (using isset or ??). Additionally that question puts values directly into the query where this one uses a prepared statement. And the answer to that question does not explain why the default value in the table does not get used.

mickmackusa Over a year ago

Adjusted the duplicates to show your two techniques demonstrated by the highest voted answer on respective pages. empty() might not be a good call for silly-fringe cases. 3v4l.org/rKSTW

Niet the Dark Absol · Accepted Answer · 2020-06-03 07:34:42Z

0

Using DEFAULT on the SQL side is absolutely correct. However, you need to ensure that value is actually used.

The default value is only used if you pass NULL as the parameter from PHP. An empty string ("") is not NULL.

$stmt->bindValue(1, empty($post_data['name']) ? NULL : $post_data['name']);

answered Jun 3, 2020 at 7:34

Niet the Dark Absol

326k86 gold badges480 silver badges604 bronze badges

Collectives™ on Stack Overflow

Default value on insert PHP and SQL [duplicate]

2 Answers 2

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Linked

Related