PDO Insert query not working

Question

I'm rebuilding my current script to support PDO instead of MySQL queries. I am stuck on this insert query though, it's not executing and I have no clue why.

Google'd around a bit but couldn't find anything.

                try 
                {
                    $sql = "INSERT INTO
                                news (news_name,
                                    news_description,
                                    news_author,
                                    news_date,
                                    news_link,
                                    news_category)
                            VALUES ('" . trim($_POST['news_name']) . "',
                                '" . trim($_POST['news_description']) . "',
                                " . $_SESSION['admin_id'] . ",
                                NOW(),
                                '" . trim($_POST['news_link']) . "',
                                '" . trim($_POST['news_category']) . "'
                                )";
                    $results = $db->exec($sql);
                    $id = $db->lastInsertId();

                    if($results)
                    {
                        echo $id;
                        echo '<p>News item added succesfully</p>';
                        echo '<a href="admin.php">Click here to return to the admin panel</a>';                     
                    }
                } 
                catch(PDOException $e)
                {
                    echo $e->getMessage();
                }
            } 
            ?>

Don't concatenate values into your queries. Read the doco and examples for [`PDO::prepare`](http://php.net/manual/pdo.prepare.php), [`PDOStatement::bindParam`](http://php.net/manual/pdostatement.bindparam.php) / [`PDOStatement::bindValue`](http://php.net/manual/pdostatement.bindvalue.php) and [`PDOStatement::execute`](http://php.net/manual/pdostatement.execute.php) — Phil, Dec 23 '13 at 23:31
Also, you'll need to set the `PDO::ATTR_ERRMODE` attribute to `PDO::ERRMODE_EXCEPTION` if you want it to throw exceptions. See [`PDO::setAttribute`](http://php.net/manual/pdo.setattribute.php) — Phil, Dec 23 '13 at 23:35

score 1 · Answer 1 · answered Dec 23 '13 at 23:41

First, set PDO to throw exceptions when it encounters an error if you haven't already...

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Now, prepare an INSERT statement with parameters

$sql = <<<_SQL
INSERT INTO `news` (news_name, news_description, news_author,
                    news_date, news_link, news_category)
VALUES (:name, :description, :author_id, NOW(), :link, :category)
_SQL;
$stmt = $db->prepare($sql);

Then execute it with your values

$stmt->execute([ // PHP 5.4 short array syntax, use array(...) if not available
    ':name'        => trim($_POST['news_name']),
    ':description' => trim($_POST['news_description']),
    ':author_id'   => $_SESSION['admin_id'],
    ':link'        => trim($_POST['news_link']),
    ':category'    => trim($_POST['news_category'])
]);

And finally...

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

Never do this. Not only does it expose internal information to end users but it lets your program continue on as if nothing happened.

When developing, let the exception go unhandled, thus terminating execution. In production, implement a high-level exception handler that can log and / or notify you of the problem while presenting users with a friendly error message.

PDO Insert query not working

1 Answers1