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, Dec 02 '16 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, Dec 02 '16 at 23:15
Here is a post that explains how to [avoid SQL Injection](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — WEBjuju, Dec 02 '16 at 23:16
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, Dec 02 '16 at 23:31
@WEBjuju Ahh I see what you're saying. Seems like I'm going have to make multiple if statements then? — Xirol, Dec 02 '16 at 23:33
@xirol - i had a moment there with the NULL...let me fix my answer. long week. — WEBjuju, Dec 02 '16 at 23:34

score 3 · Accepted Answer · answered Dec 02 '16 at 23:35

$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.

WEBjuju · Answer 2 · 2016-12-02T23:47:28.967

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 02 '16 at 23:47

answered Dec 02 '16 at 23:15

WEBjuju

5,797
4
27
36

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. – Xirol Dec 02 '16 at 23:24
He wants to set `Untitled` if the title field in the form is empty. Your code will set `NULL` in case it is empty. – Wolverine Dec 02 '16 at 23:27
single query version with easy to edit list of columns so you don't have to edit two queries when your data structure changes ;) – WEBjuju Dec 02 '16 at 23:48

score 0 · Answer 3 · answered Dec 02 '16 at 23:22

0

use always small letters in

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

answered Dec 02 '16 at 23:22

Naveen Kumar

1
6

phpmyadmin not using DEFAULT value when input is left empty

3 Answers3

Related