phpmyadmin not using DEFAULT value when input is left empty

Question

I have this problem where if I leave my input for 'Title' blank, then it won't set the default value: "Untitled" when sent to the database. I've looked online and have made sure that my settings were correct in phpmyadmin but it still won't set the default value. Any piece of advice is appreciated!

Here are my PHPmyadmin settings for the "Title" column:

These are my files:

addart.php

<form method="post" action="addtodb.php">

  <label for="Title">
     <h4>Title</h4>
  </label>

  <input class="u-full-width" 
         type="text" 
         placeholder="Title of art" 
         id="Title"
         name="Title">

</form>

addtodb.php

<?php

if($_SERVER['REQUEST_METHOD'] == "POST") {

$host = 'localhost';
$user = 'root';
$pass = '';
$db = 'testdb';

$dbConnection = new mysqli($host, $user, $pass, $db);

if (mysqli_connect_errno()) {
    printf("Could not connect to the mySQL database: %s\n", mysqli_connect_error());
    exit();
}

if($_POST) {

    $artwork = $_POST["Artwork"];
    $medium = $_POST["Medium"];
    $artist = $_POST["Artist"];
    $title = $_POST["Title"];

    $results = $dbConnection->query("INSERT INTO art 
                                   (Artwork, Title, Artist, Medium) VALUES      
                               ('$artwork','$title','$artist','$medium');");

    if (!$results) {
        echo 'Unable to insert into database.';
        exit();
    } else {
        echo 'Successfully added!';
    }

    mysqli_close($dbConnection);
    header("Location: galleryonly.php"); /* Redirect browser */
    exit();
}
?>

This happens when you add the column or when you perform insert operation? — Wolverine
– Wolverine, Commented Dec 2, 2016 at 23:13
@Perumal93 It happens when I perform the insert operation. When I check the database table in phpmyadmin after leaving the input for Title blank, it is also blank. — Xirol
– Xirol, Commented Dec 2, 2016 at 23:15
to get the default value, do not add the Title to the list of columns nor list of values. don't insert anything and the default will be inserted. — WEBjuju
– WEBjuju, Commented Dec 2, 2016 at 23:31
@WEBjuju Ahh I see what you're saying. Seems like I'm going have to make multiple if statements then? — Xirol
– Xirol, Commented Dec 2, 2016 at 23:33

Wolverine · Accepted Answer · 2016-12-02 23:35:17Z

3

$artwork = $_POST["Artwork"];
$medium = $_POST["Medium"];
$artist = $_POST["Artist"];
$title = $_POST["Title"];

if(!empty($title)) {
   $sql = "INSERT INTO art (Artwork, Title, Artist, Medium) VALUES ('$artwork', '$title', '$artist', '$medium')";
} else {
   $sql = "INSERT INTO art (Artwork, Artist, Medium) VALUES ('$artwork', '$artist', '$medium')";
}

$results = $dbConnection->query($sql);

You can try out this code.

If you're omitting the column, the default value will be set.

Because you have only one column with default value, you can stick with this code.

If you have more than one column with default value, you will need to make changes according to your requirements.

answered Dec 2, 2016 at 23:35

Wolverine

1,7001 gold badge15 silver badges18 bronze badges

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

Comments

WEBjuju · Accepted Answer · 2016-12-02 23:47:28Z

1

You have a bit of trick ahead of you, because you won't be able to use the Title column if you need the Default value.

// assuming use of proper method of sanitizing
// these values so we don't get SQL INJECTED!!
$artwork = 'artwork';
$title = 'title';
$artist = 'artist';
$medium = 'medium';

// make an array with the columns
$cols = explode(',', 'Artwork,Title,Artist,Medium');

// make an array with the values (that you sanitized properly!)
$vars = explode(',', 'artwork,title,artist,medium');

foreach ($cols as $i=>&$col) {
  $var = ${$vars[$i]};
  if ($col == 'Title') {
    if (empty($var)) {
      // don't add this column if empty
      continue;
    }
  }
  // otherwise (if not Title)
  // add it to a column = "value" insert string
  $pcs[] = "`$col` = '$var'";
}

// fortunately, we can insert with update syntax, too!
$query = 'insert into art set ';
$query .= implode(', ', $pcs);

edited Dec 2, 2016 at 23:47

answered Dec 2, 2016 at 23:15

WEBjuju

6,6814 gold badges31 silver badges37 bronze badges

3 Comments

Xirol Over a year ago

I tried this out and it didn't quite work sadly. I even tried taking off the '' from NULL but that just crashed my site. I'm fiddling around with your solution right now. I appreciate you helping me so much.

Wolverine Over a year ago

He wants to set Untitled if the title field in the form is empty. Your code will set NULL in case it is empty.

WEBjuju Over a year ago

single query version with easy to edit list of columns so you don't have to edit two queries when your data structure changes ;)

Naveen Kumar · Accepted Answer · 2016-12-02 23:22:09Z

0

use always small letters in

<input class="u-full-width" 
         type="text" 
         placeholder="Title of art" 
         id="Title"
         name="title">

answered Dec 2, 2016 at 23:22

Naveen Kumar

16 bronze badges

Collectives™ on Stack Overflow

phpmyadmin not using DEFAULT value when input is left empty

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related