SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'image_url' cannot be null

Question

I have been trying to save image along with some inputs into the database but I kept on receiving this message 'SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'image_url' cannot be null'.

here is the source code:

Php code for processing the inputs:

<?php
if($_POST){

        //$ef = $_POST["exp_file"];
         if($_POST["heading"] == '' && $_POST["myimage"] =='' && $_POST["text"] =='')
        {
            echo '<div style="padding:7px; margin-top:5px; margin-bottom:5px;" id="good" class="alert-warning">Check inputs for empty values.</div>';
        }else{

                try {


        $head = $mysqli->real_escape_string($_POST["heading"]);
        $txt = $mysqli->real_escape_string($_POST["text"]);
        $time = time();
        $upload_image=$_FILES["myimage"]["name"];

        $folder="uploads/";

        move_uploaded_file($_FILES["myimage"]["tmp_name"], "$folder".$_FILES["myimage"]["name"]);


            $stmt = $db->prepare("insert into posts(

          heading,
          post_desc,
          image_url,
         user,
         post_date
          ) VALUES(:head,:pd,:iu,:user,:dat)");
         $stmt->execute(array(
        ':head'=>$head,
        ':pd'=>$txt,
        ':iu'=>$upload_image,
        ':user'=>$user,
        ':dat'=>$time


          ));
                //Saved
                echo '<div style="padding:7px; margin-top:5px; margin-bottom:5px;" id="good" class="alert-success">Published Successfully.</div>';
                //$_POST = array();
            } catch(PDOException $e) {
                echo $e->getMessage();
            }

            }
        }
?>

Jquery part of it:

 <script>
/* must apply only after HTML has loaded */
$(document).ready(function () {
    $("#contact_form").on("submit", function(e) {
        var postData = $(this).serializeArray();
        var formURL = $(this).attr("action");
        $.ajax({
            url: formURL,
            type: "POST",
            data: postData,
            success: function(data, textStatus, jqXHR) {
                $('#contact_dialog .modal-header .modal-title').html("Result");
                $('#contact_dialog .modal-body').html(data);
                $("#submitForm").remove();
            },
            error: function(jqXHR, status, error) {
                console.log(status + ": " + error);
            }
        });
        e.preventDefault();
    });

    $("#submitForm").on('click', function() {
        $("#contact_form").submit();
    });
});
</script>

Lastly, the html

<div class="modal fade" id="contact_dialog" role="dialog">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <button type="button" class="close" data-dismiss="modal">&times;</button>
                <h4 class="modal-title"><span class="glyphicon glyphicon-file"></span> Create an Article</h4>
            </div>
                <div class="modal-body">
                <form id="contact_form" class="form col-md-12 center-block form-signin" action="article/publish.php" method="post" autocomplete="off" enctype="multipart/form-data">

                     <div class="row">     
                         <div class="col-lg-12">
                            <div class="form-group">
                           <input type="text" class="form-control input-lg" placeholder="Heading" name="heading"  id="heading" value="">                               
                            </div>
                         </div>
                     </div>

                                          <div class="row">     
                         <div class="col-lg-12">
                            <div class="form-group">
                           <input type="file" class="form-control file_image" placeholder="Upload a file" name="myimage"  id="files" value="">                               
                            </div>
                         </div>
                     </div>


                      <div class="row">     
                         <div class="col-lg-12">
                            <div class="form-group">
                           <textarea class="form-control input-lg textarea" name="text" id="text" cols="120"  rows="9" wrap="virtual"></textarea>
                            </div>
                         </div>
                     </div>


                     </form>

                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
                    <button type="button" id="submitForm" class="btn btn-default">Publish</button>
                </div>
            </div>
        </div>
    </div>

What does the error mean? And what exactly am I doing wrong?

score 0 · Answer 1 · answered May 23 '17 at 02:39

You are submitting multipart/form-data with ajax. That is why $_FILES veritable is not available in your script and set null data in $upload_image. There is also a possibility that your move_uploaded_file() will not uploading file in your server. Though I have not tested this but you can test the below change in your ajax code:

$.ajax({
            url: formURL,
            type: "POST",
            data: new FormData( this ),
            processData: false,
            contentType: false,
            success: function(data, textStatus, jqXHR) {
                $('#contact_dialog .modal-header .modal-title').html("Result");
                $('#contact_dialog .modal-body').html(data);
                $("#submitForm").remove();
            },
            error: function(jqXHR, status, error) {
                console.log(status + ": " + error);
            }
        });

Thanks it worked, Could you please tell one reason why image path is saved to db but does not move to root folder? — Fagbemi Ayodele, May 23 '17 at 03:00

score 0 · Accepted Answer · answered May 23 '17 at 03:01

That's a little hard to exactly tell where the problem lies without testing all the code... But it seems that you are permanently trying to insert a NULL as value for image_url (and not just).

First of all, you should beforehand decide if you want to accept NULL values in database. Define the fields correspondingly in the database (NOT NULL field).

Second, you should validate on NULL values - not just on empty values (... == '') - too and use the OR operator - instead of AND - on received $_POST's, like:

if ($_POST) {
    if (
            $_POST["heading"] == '' || !isset($_POST["heading"]) ||
            $_POST["myimage"] == '' || !isset($_POST["myimage"]) ||
            $_POST["text"] == '' || !isset($_POST["text"])
    ) {
        echo '...';
    } else {
        //...
    }
}

Third, ensure that you really send valid form values, e.g. not empty and unequal NULL.

I also would recommend you to use bindValue() or bindParam() when you are preparing the sql statement. This way, you can validate each binding parameter on data type. This is an important step, because the data type of the values to insert must correspond to the data type defined for the corresponding field in database. Like:

$sql = 'insert into posts(heading, post_desc, image_url, user, post_date) VALUES(:head, :pd, :iu, :user, :dat)';
$stmt = $db->prepare($sql);
$stmt->bindValue(':head', $head, getInputParameterDataType($head));
$stmt->bindValue(':pd', $txt, getInputParameterDataType($txt));
$stmt->bindValue(':iu', $upload_image, getInputParameterDataType($upload_image));
//...
$stmt->execute();

function getInputParameterDataType($value) {
    $dataType = PDO::PARAM_STR;
    if (is_int($value)) {
        $dataType = PDO::PARAM_INT;
    } elseif (is_bool($value)) {
        $dataType = PDO::PARAM_BOOL;
    }
    return $dataType;
}

And also, the PDOStatement::prepare() throws not always an exception. So you should also handle the case when prepare() equals FALSE separately. The execute() function throws also no exception. So handle it like prepare() too. Here is an example, maybe it helps:

try {
    $connection = getConnection();

    $sql = "INSERT INTO...";
    $statement = $connection->prepare($sql);

    if (!$statement) {
        throw new Exception('The SQL statement can not be prepared!');
    }

    $statement->bindValue(':id', $id, PDO::PARAM_INT);

    if (!$statement->execute()) {
        throw new Exception('The PDO statement can not be executed!');
    }

    return $statement->rowCount() > 0 ? TRUE : FALSE;
} catch (PDOException $pdoException) {
    echo '<pre>' . print_r($pdoException, true) . '</pre>';
    exit();
} catch (Exception $exception) {
    echo '<pre>' . print_r($exception, true) . '</pre>';
    exit();
}

thank you so much...I'll re-modify my codes now. Thanks again for your precious time. — Fagbemi Ayodele, May 23 '17 at 03:11
@FagbemiAyodele I wrote an answer some time ago about the workflow of exception handling in a (php) app (defining exceptions, rethrowing them, exception-coding, ordering exceptions, etc). It's big :-)) but easy-to-follow. I showed especially the PDO exception handling. So, if you are interested: [Exception handling for PDO::prepare() and PDOStatement::execute() + A generalized exception handling scheme](https://stackoverflow.com/questions/43691356/cant-update-or-delete-dynamically-with-twig-and-php-oop/43704962#43704962) — , May 23 '17 at 03:24
just checked it out. Got it printed - will go through it when done with the work at hand. thanks again. — Fagbemi Ayodele, May 23 '17 at 03:35
With pleasure. The principle is relative simple... Also, thanks for accepting my answer. But I want to be fair-play too: you said to @Chayan, that your code worked based on his solution. But you accepted my answer. Unless my code was the real solution for you, you should accept his answer instead of mine ;-) It is important to choose the right answer for you, so that other people around will also get to read the right answer. But, if my answer was the one, then I'm glad to have helped you! — , May 23 '17 at 03:58
yes, both answers worked for me but on different scales to my application. his was on jquery while yours was on mysql procedural usage. So I have to accept both of your solutions based on the applications to my development. So, I couldn't thank you both enough. — Fagbemi Ayodele, May 23 '17 at 09:46
@FagbemiAyodele As already said: with pleasure. Yes, SO should actually provide a way to accept multiple good answers. That would be cool. Good luck with your projects. — , May 23 '17 at 10:08

SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'image_url' cannot be null

2 Answers2