Connection failed: SQLSTATE[HY000]: General error: 1366 Incorrect integer value

Basically I’m trying to get an ID from a url and insert it into a database, initially I had an undefined variable error, so I looked around and I saw a few threads suggesting $id = ''; to remove this problem, from what I can determine it did, however I noticed it wasn’t inserting into my database so I wrapped it in a try to see if it gave me an error which it did, the error in full was

  Connection failed: SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'reviewId' at row 1

My code

require ("classes/Stats.php");
require ("classes/Database.php");

        if ($_SERVER['REQUEST_METHOD'] == 'POST')
        {
            $id = '';
            $socValidation = new Stats();
            if(isset($_GET['id']) && $_GET['id'] !== '')
            {
                $id = $_GET['id'];
            }
            $post = filter_input_array(INPUT_POST, FILTER_SANITIZE_STRING); 

            $userId =  $_SESSION["userId"];
            $messages = $post['addComment'];
            $errors = array();

            $fields = array(
            'messages' => array(        
                'validate' => 'socMessage',
                'message' => 'Please enter a minimum of three characters',
                'value' => $messages
            )
            );

            foreach($fields as $key => $value) 
            {
                $validation_result = $socValidation->{$value['validate']}($value['value']);

                if(!$validation_result) 
                {
                    $errors[] = ['name' => $key, 'error' => $value['message']];
                }
            }

            if(empty($errors))  
            {  
                try 
            {
                $db = new Database();
                $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                $success = ["response" => "Your notes have been saved"];
                $process = $db->prepare('INSERT INTO reviewReplies (reviewComment, reviewId, userId, dateAdd) VALUES (:addComment, :id, :userId, NOW())');
                $process->bindValue(':addComment', $messages);
                $process->bindValue(':id', $id);
                $process->bindValue(':userId', $userId);
                $process->execute();
                }
                catch (PDOException $e) {
                echo 'Connection failed: ' . $e->getMessage();
            }


            }

        }       

header('Content-Type: application/json');
if (empty($errors))
{
    echo json_encode($success);
}
else
{
    echo json_encode(["errors" => $errors]);
}

Answer

By default the bindValue() assumes a string datatype. If you want to use something like INT you need to add that to the call.

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

You may also need to do this with :userId if that is also an INT

$process->bindValue(':userId', $userId, PDO::PARAM_INT);

You should also check that the $id and $userId variables contain a value and it is an integer

I also note that you are inside a if ($_SERVER['REQUEST_METHOD'] == 'POST') block but are getting your $id from $_GET['id'] with a line that sets your $id to an empty string $id = ''; as a default? This could also be part of your problems!

Advertisement

Answer