Default value on insert PHP and SQL

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, Jun 03 '20 at 07:26
https://stackoverflow.com/a/44717227/2943403 , https://stackoverflow.com/a/6459214/2943403 — mickmackusa, Jun 03 '20 at 08:14

Nick · Answer 1 · 2020-06-03T08:11:16.753

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 03 '20 at 08:11

answered Jun 03 '20 at 03:21

Nick

138,499
22
57
95

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. – mickmackusa Jun 03 '20 at 07:38
1

@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. – Nick Jun 03 '20 at 07:53
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. https://3v4l.org/rKSTW – mickmackusa Jun 03 '20 at 09:20

score 0 · Answer 2 · answered Jun 03 '20 at 07:34

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

Default value on insert PHP and SQL

2 Answers2