PHP PDO prepared insert - does not insert data and show no errors

Question

This problem is driving me crazy, i tried everything. Is does not give me any error, but it does not insert anything to the database either. Database connection is good, and there should be no typos. Please take a look, and see if you can find the problem:

$err = array();

if (isset($_POST['submit'])) {

    $ip = gethostbyname($_SERVER['REMOTE_ADDR']);
    $date = "2012-02-02 02:02:02"; //Example
    $uploader_name = $_POST['uploader_name'];

    // Validation happens here...


    if (empty($err)) {

        $host = "host";
        $dbname = "db";
        $user = "user";
        $pass = "pass";

        try {
            $dbh = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);  

            $sql = "INSERT INTO `table` (`ip`, `date`, `uploader_name`) 
                    VALUES (:ip, :date, :uploader_name)";
            $stmt = $dbh->prepare($sql);

            # the data we want to insert  
            $params = array(
                ':ip' => $ip,
                ':date' => $date,
                ':uploader_name' => $uploader_name
            );


            $stmt->execute($params);

            $dbh = null;

        } catch(PDOException $pe) {
            die('SQL Error');
        }


        if (empty($err)) {
            $err[] = "Success!";
        }   
    }

}

Also, Im sure it gets to the insert part, because i get the 'Success' message.

score 10 · Accepted Answer · answered Apr 11 '12 at 21:59

10

Use this code to execute your statement. If there is a non-fatal error, it will display it.

$stmt->execute($params) or die(print_r($stmt->errorInfo(), true));

Almost certainly your db user does not have permissions to execute the statement you're asking against the table you're trying to execute against.

answered Apr 11 '12 at 21:59

Nathaniel Ford

20,545
20
91
102

Thank you!! the problem was that some of the values was NULL. But its only some of the fields that is "required", how do i insert blank values to these fields? – 2by Apr 11 '12 at 22:10
You'd have to provide validation code for each input that you're adding to the prepared statement. if ($myVar === nulll) { $myVar = '';} or something. – Nathaniel Ford Apr 11 '12 at 22:13
2 years later you da bomb – bryan Jun 06 '14 at 23:07

score 2 · Answer 2 · answered Apr 11 '12 at 22:00

Are you using auto-commit? If not you may need to wrap your query in a transaction.

try
{
    $dbh->beginTransaction();
    // your code.
    $dbh->commit();
}
catch(PDOException $pe)
{
    $dbh->rollback();
    die($pe->getMessage());
}

http://php.net/manual/en/pdo.transactions.php

PHP PDO prepared insert - does not insert data and show no errors

2 Answers2