PDO prepared statement insert not working

Question

I absolutely have no clues about why this prepared statement doesn't work returning false but without giving me any error. I've read similar questions here on StackOverflow but I didn't find any tip to solve this unknown problem.

The errors are activated

public function PDOconn() {
    try {
        return new PDO($this->getDsn(), $this->username, $this->password, [PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION]);
    } catch (PDOException $e) {
        echo "Errore di connessione al database";
        throw $e->getCode();
    }
}

This is the PHP file that handles the AJAX request. I inserted the query into a try {} ... catch() but it actually didn't help since it doesn't report any error. execute() just returns false

/* ***** CLASSES AUTOLOAD fn ***** */
spl_autoload_register(function ($class_name) {
    require_once '../classes/' . $class_name . '.php';
});

$db     = new database();
$conn   = $db->PDOconn();


$name   = postGet::post('name');
$email  = postGet::post('email');
$commercial = postGet::post('commercial');


try {
    $stmt = $conn->prepare('INSERT INTO newsletter_subscription(name, email, commercial) VALUES(:name, :email, :commercial)');
    $stmt->execute(array(':name' => $name, ':email' => $email, ':commercial' => $commercial));
} catch (PDOException $e) {
    $return = $e->getCode();
}

header('Content-Type: application/json; charset=utf-8');
echo json_encode($return);

The variables string $name, string $email and int $commercial are correctly passed, I printed them into the php file.

This is the table structure where subscriptorID is PRIMARY AI and email is UNIQUE

[SOLUTION] Shame on me and on my short memory but maybe it can help someone else: I had the flash that that DB user didn't have INSERT priviledges, that's why the query returned just false without any error.

Do you get any error messages? – RiggsFolly Jul 05 '17 at 16:37 — RiggsFolly, Jul 05 '17 at 16:37

score -1 · Answer 1 · answered Jul 05 '17 at 16:42

-1

Ah I see commercial is a boolean.

When you use this method of passing parameter, the parameters are all assumed to be strings.

$stmt->execute(array(':name' => $name, ':email' => $email, ':commercial' => $commercial));

You may have to bind the values like this in the case of a boolean.

$stmt->bindValue(':name', $name, PDO::PARAM_STR);
$stmt->bindValue(':email', $email, PDO::PARAM_STR);
$stmt->bindValue(':commercial', $commercial, PDO::PARAM_BOOL);

$stmt->execute();

answered Jul 05 '17 at 16:42

RiggsFolly

93,638
21
103
149

My fault, `$commercial` is now an `int`. I tried anyway to bind params manually (with `PDO::PARAM_INT`) as you suggested, but it still doesn't work and it still doesn't print any error message – Brigo Jul 05 '17 at 16:48

score -2 · Answer 2 · answered Jul 05 '17 at 16:40

-2

bool $commercial is passed as integer and not boolean. Change it in your sql to boolean which is True or False

answered Jul 05 '17 at 16:40

Grasper

1,293
12
26

It was my fault, it's `int $commercial`. But anyway, as far as I know, in MySQL `INT(1)` is considered `BOOLEAN` – Brigo Jul 05 '17 at 16:54
remove it and see if that's the problem. – Grasper Jul 05 '17 at 17:05

PDO prepared statement insert not working

2 Answers2