MySQL is not setting the DEFAULT value when a NULL is sent

Question

I have the following form on my website where it adds a supplier to the database. The database suppliers table has

id(AI),
name(varchar),
company(varchar),
phone(int),
avatar(varchar)

Nothing is allowed to be null, the avatar column is a path to an image that the admin uploads, I have a DEFAULT path to an image called "def.jpg", now if I upload an image it inserts to the database successfully, but whenever I send it null, MySQL does not use the DEFAULT path set and hence why it also fails to insert the record at all, it is 100% correct path.

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$uploadError = '';
if (isset($_POST['submit'])) {
    include 'db.php';
    $sName = $_POST['sName'];
    $company = $_POST['sCmp'];
    $phone = $_POST['sNumber'];
    $type = isset($_FILES['sPhoto']) ? (explode('.', $_FILES['sPhoto']['name'])) : null;
    if (!empty($type)) { //a file was upload
        $type = end($type); //get the extension
        $allowed = ['png', 'jpg'];
        if (in_array($type, $allowed)) { //type is allowed
            $tmp_name = $_FILES['sPhoto']['tmp_name'];
            $name = $_FILES['sPhoto']['name'];
            $target_dir = 'assets/images/suppliers/';

            //move the file to the target location
            $target_file = $target_dir . basename($_FILES["sPhoto"]["name"]);
            move_uploaded_file($tmp_name, $target_file);

        } else { //not allowed so we set error message
            $uploadError = 'This type isnt allowed!';
            echo($uploadError);
        }

    } else { //no file was passed in so we let it continue to insert
        $target_file = null;
    }

    if (empty($uploadError)) { //no errors, so we insert
        $sql = "INSERT INTO suppliers (name,company,phone,avatar) VALUES (?,?,?,COALESCE(?, avatar, DEFAULT(avatar)));";
        $stmt = mysqli_stmt_init($conn);
        mysqli_stmt_prepare($stmt, $sql);
        mysqli_stmt_bind_param($stmt, "ssis", $sName, $company, $phone, $target_file);
        mysqli_stmt_execute($stmt);
    }

} else {
    if (isset($_POST['delSubmit'])) {
        include 'db.php';

        $name = $_POST['name'];
        $sql = "DELETE FROM suppliers WHERE name = ?;";
        $stmt = mysqli_stmt_init($conn);
        mysqli_stmt_prepare($stmt, $sql);
        mysqli_stmt_bind_param($stmt, "s", $name);
        mysqli_stmt_execute($stmt);
    }
}
?>
    <!-- ***** Preloader Start ***** -->
    <div id="preloader">
        <div class="jumper">
            <div></div>
            <div></div>
            <div></div>
        </div>
    </div>
    <!-- ***** Preloader End ***** -->
    <!-- Header -->
    <header class="">
<?php
session_start();
if (isset($_SESSION['email'])) {
    if ($_SESSION['email'] === 'admin@allday.com') {
        include 'navadmin.php';
    } else {
        include 'navuser.php';
    }
} else {
    include 'nav.php';
}

?>

After I've read some comments below, I've checked that whenever I do not upload an image , it goes into " this type is not allowed " block, instead of using MySQL's DEFAULT value.

I'm confused. You start about asking about PHP MyAdmin and then switch to some custom PHP code that you wrote. Which isn't behaving the way you expect? PHP MyAdmin or your code? — Quentin, Aug 09 '21 at 20:34
I am not sure why PhPMyAdmin is not setting the default value when I send a NULL, I provided all of the information I could because I couldn't locate it myself. — Ameen Assadi, Aug 09 '21 at 20:36
phpMyAdmin doesn't do anything related to your PHP code. It's just a tool (one of several available) for managing a mySQL database. Maybe you're really trying to say that mysql isn't doing what you expected. — ADyson, Aug 09 '21 at 20:47
Provide your PHP code as well. Don't fill the `avatar` column in your code and it should work. — Scripter, Aug 09 '21 at 20:55
I was just told that its not related to my code above hehe, but no worries I will add it again — Ameen Assadi, Aug 09 '21 at 20:58
I removed phpMyAdmin as this question doesn't look related to phpMyAdmin in any way. — Dharman, Aug 09 '21 at 23:09
NULL is a value. So when you provide NULL, then the default is not used. However, your column does not allow NULL values, so it results in error. Simple as that. — Dharman, Aug 09 '21 at 23:19
phpmyadmin is not your database, it is just a client you may be using to access your database. please show the output of `select version();` so we can see what version your database is? — ysth, Aug 09 '21 at 23:20

score 1 · Answer 1 · answered Aug 09 '21 at 23:22

1

Your question has:

INSERT INTO suppliers (name,company,phone,avatar) VALUES (?,?,?,COALESCE(?, avatar, DEFAULT(avatar)));

There's an extra "avatar" there that doesn't belong; presumably you mean:

INSERT INTO suppliers (name,company,phone,avatar) VALUES (?,?,?,COALESCE(?, DEFAULT(avatar)));

answered Aug 09 '21 at 23:22

ysth

96,171
6
121
214

Good morning, thanks for your reply but I did not solve the problem, still goes into " Type not allowed " whenever I send the avatar null in the form. – Ameen Assadi Aug 10 '21 at 07:05
Do some debugging; is `isset($_FILES['sPhoto'])` true? If not, why not? If so, what are `$_FILES['sPhoto']['name']` and `explode('.', $_FILES['sPhoto']['name'])`? – ysth Aug 10 '21 at 14:42

MySQL is not setting the DEFAULT value when a NULL is sent

1 Answers1