Fagbemi Ayodele Fagbemi Ayodele - 1 year ago 153
jQuery Question

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

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?

Answer Source

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();
}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download