Integrity constraint violation

carljr

hello, i think i'm in the right place. having a bit of an issue with my php script passing data.
here is the error message:

"Connected to database
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'item' cannot be null' in /home/ewff/public_html/prac0.php:66 Stack trace: #0 /home/ewff/public_html/prac0.php(66): PDOStatement->execute() #1 {main} thrown in /home/ewff/public_html/prac0.php on line 66"

there are only 4 values to pass. here is the script that is giving me the problem:

<?php


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

{
$errors = [];
}

if(empty($errors)) {




// prepare sql and bind parameters
    $query = "INSERT INTO details (item, temple, quantity, price)
            VALUES (:item, :temple, :quantity, :price)";
}
    $stmt = $dbh->prepare($query);
        $stmt->bindParam(':item', $_POST['item']);
        $stmt->bindParam(':temple', $_POST['temple']);
        $stmt->bindParam(':quantity', $_POST['quantity']);
        $stmt->bindParam(':price', $_POST['price']);
$stmt->execute();

/*** close the database connection ***/
$dbh = null;

?>

if you could please, explain why this is happening so i may learn how to prevent/catch it myself in the future. thank you all much.

dalecosp

Integrity constraint violation: 1048 Column 'item' cannot be null' in /home/ewff/public_html/prac0.php:66

For some reason that cannot be determined from the information you've provided, you are feeding the DB server a null value. I'll assume line 66 is this one:

$stmt->bindParam(':item', $_POST['item']);

If not that, then it's the execute statement.

The challenge, then, is to figure out why $_POST['item'] isn't populated.

carljr

hello all. still no luck. i've been at it for two days and still no relief. i'm thinking that i'm putting the execute statement in a way that's not working with my pdo. any other thoughts?
here again is the code:

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
/*** echo a message saying we have connected ***/
echo 'Connected to database<br />';

}catch(PDOException $e) {
     echo $e->getMessage();
}
?>

<body>


<?php


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

{
$errors = [];
}

if(empty($errors)) {

// prepare sql and bind parameters
    $query = "INSERT INTO details (description, temple, quantity, price)
            VALUES (:description, :temple, :quantity, :price)";
}
    $stmt = $dbh->prepare($query);
        $stmt->bindParam(':description', $_POST['description']);
        $stmt->bindParam(':temple', $_POST['temple']);
        $stmt->bindParam(':quantity', $_POST['quantity']);
        $stmt->bindParam(':price', $_POST['price']);
       $stmt->execute();                                                                            /***line 66***/

/*** close the database connection ***/
$dbh = null;

i'm thinking that maybe i should be using $dbh instead of $stmt for the handler that binds the parameters. but it seem like that may not be the issue because the script breaks at line 66. i'm still working at it. thanks all. any ideas are greatly appreciated. i'll try almost anything at this point.

oh yeah, here is the error message again:
"Connected to database
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'description' cannot be null' in /home/ewff/public_html/prac0.php:66 Stack trace: #0 /home/ewff/public_html/prac0.php(66): PDOStatement->execute() #1 {main} thrown in /home/ewff/public_html/prac0.php on line 66". peace.

carljr

i attempted to change the handler from $stmt to $dbh because that's what all the other PDO handlers use. i still get a fatal error message but it's different this time:
"Connected to database

Fatal error: Call to undefined method PDO::bindParam() in /home/ewff/public_html/php.php on line 62"
is that better than the PDOXception messages i was getting with regard to integrity constraint violation(see above error message)?

dalecosp

carljr;11053113 wrote:
i attempted to change the handler from $stmt to $dbh because that's what all the other PDO handlers use. i still get a fatal error message but it's different this time:
"Connected to database

Fatal error: Call to undefined method PDO::bindParam() in /home/ewff/public_html/php.php on line 62"
is that better than the PDOXception messages i was getting with regard to integrity constraint violation(see above error message)?

No. Put it back the other way for now.

Go back to your script and do this:

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

{ 
$errors = []; 
} 

print_r($_POST);
exit;

If this script is a webpage/web script, wrap your print_r in an HTML "pre" tag:

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

{ 
$errors = []; 
} 

echo "<pre>";
print_r($_POST);
echo "</pre>";
exit;

Derokorian

$dbh represents your connection to the database
$stmt represents the prepared query, this is where you bindParams to

You are not even checking for POST data to exist and be valid before attempting to prepare, bind and execute. You probably want to check that things are actually set and contain reasonable values.

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

{ 
    $errors = []; 

if (empty($_POST['description'])) {
    $errors[] = 'Description is required';
} 
// or similar, and etc for other fields...