PDO ERROR: invalid input syntax for type boolean and integer - if empty input

Question

I am using PDO for a dynamical update query for PostgreSQL. The problem is, all the values are transmitted to the database as strings, which throws an error for the supposed boolean (edit: and integer) values:

PHP Fatal error: Uncaught PDOException: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type boolean: Â»Â«

Edit: The problem appears whenever the boolean or integer fields get an empty input. What can I do to make the PDO transfer the inputs as "null" to the database instead of empty strings that throw the data type errors?

Below is my update method, I have tried to do the binding inside a loop and add an explicit type casting for the two supposed boolean values:

public function updateMod($array)
{
    $keys = array_keys($array);
    $vals = array_values($array);

    $setStr = "";
    foreach ($keys as $key) {
        $setStr .= "$key = :$key, ";
    }
    $setStr = rtrim($setStr, ", ");

    $pdoConn = DbConnection::openConnection();
    $stmt = $pdoConn->prepare("UPDATE mods SET $setStr WHERE id = :id;");

    $i = 0;
    foreach ($keys as $key) {
        // type casting the two supposed booleans:
        if($key === 'g_exists' || $key === 'w_exists') {
            $stmt->bindValue($key, $vals[$i], PDO::PARAM_BOOL);
        } else {
            $stmt->bindValue($key, $vals[$i]);
        }
        $i++;
    }

    $stmt->bindValue('id', $this->Id());

    if ($stmt->execute()) {
        return true;
    } else {
        return false;
    }
}

But I keep getting the same error. Does anyone have an idea how to solve this?

helvete · Accepted Answer · 2021-08-04 14:48:25Z

1

You are creating "the setting part" of the query in a loop irregardless of whether there are any values to write or not.

So the solution would be to check whether there is a value for given key.

A simple solution might me to call array_filter on the array containing the data - this way the empty values and their keys are being removed before processing

Try to start the method like this:

public function updateMod($array)
{
    $array = array_filter($array); # This line is important
    $keys = array_keys($array);
    $vals = array_values($array);

    ...

array_filter documentations says:

If no callback is supplied, all empty entries of array will be removed

answered Aug 4, 2021 at 14:48

helvete

2,73915 gold badges38 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nana Over a year ago

Thanks for your answer! But I don't want to remove these fields from the array, I do want them to be processed. The user of the application should be able to delete the last input entry of a field and then leave that field empty. Therefore, I need the update method to be able to enter null entries into the database. But filtering the array before and changing the empty inputs to 'null' didn't help either, because (I suppose) through the pdo binding these nulls got translated into 'null' strings again and were then not recognized correctly by Postgres.

Nana Over a year ago

While writing that comment, the answer got clear to me: I should try to type cast to null, but not before the binding process but within the process. And it worked! (See my answer on that)

helvete Over a year ago

@Nana Good to hear that

Nana · Accepted Answer · 2021-08-05 08:04:38Z

1

Ok, I just found the answer:

My mistake was that I was trying to typecast to booleans/integers which didn't work on empty strings. So I had to filter the empty strings themselves as well and cast them directly to null values. So, the above binding foreach loop should look like this instead:

foreach ($keys as $key) {
        if($key === 'g_exists' && $vals[$i] === '') { // filter key AND empty value
            $stmt->bindValue($key, $vals[$i], PDO::PARAM_NULL); // cast to null
        } else {
            $stmt->bindValue($key, $vals[$i]);
        }
        $i++;
    }

edited Aug 5, 2021 at 8:04

answered Aug 5, 2021 at 7:13

Nana

716 bronze badges

Collectives™ on Stack Overflow

PDO ERROR: invalid input syntax for type boolean and integer - if empty input

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related