how to insert form values to mysql with PDO

Question

I'm trying to insert values to mysql database, but for some reason this is not working. I can get it to work with normal PHP but I have been told that PDO would be safer to use. This is the code I use, the values are posted to the php file, but not updated to mysql. What could be the reason for that?

<?php
include 'config.php';

// database connection
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);

// new data

$value1 = $_POST["value1"];
$value2 = $_POST["value2"];
$value3 = $_POST["value3"];
$value4 = $_POST["value4"];
// query
$sql = "INSERT INTO `database`.`table` 
(`id`, `value1`, `value2`, `value3`, `value4`, `timeStamp`) 
VALUES (NULL, ?, ?, ?, ?, CURRENT_TIMESTAMP)"; 
$q = $conn->prepare($sql);
$q->execute(array($value1, $value2, $value3, $value4));


?>

[PDO query fails but I can't see any errors. How to get an error message from PDO?](http://stackoverflow.com/a/15990858/285587) — Your Common Sense, Jun 27 '13 at 06:41
Always check for errors and return values. And always mention those in your question. "It doesn't work" is not a question. — GolezTrol, Jun 27 '13 at 06:44

Robert · Accepted Answer · 2013-06-27T08:31:46.177

-2

Change this line

$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);

to this

$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass, array(PDO::ATTR_ERRMODE  => PDO::ERRMODE_EXCEPTION));

and use try catch on

try
{
    $q->execute(array($value1, $value2, $value3, $value4));
}
catch(Exception $e)
{
   echo $e->getMessage();
   //other code to handle exception for example logging, rollback transaction if exists etc.
}

When you make these changes then PDO Exceptions will be thrown and you will see what problem is. With PDO exception you can do more to handle error, for example if you use transactions then you can in catch block rollback it.

You can use error_reporting(E_ALL); and display_errors with ini_set().
You can also use PDO::errorInfo

From manual:

PDO::errorInfo — Fetch extended error information associated with the last operation on the database handle

To see examples check this link

Check these links:

edited Jun 27 '13 at 08:31

answered Jun 27 '13 at 06:43

Robert

19,800
5
55
85

If you're just going to catch the exception immediately anyway, may as well just use the good old `if (!$q->execute()) echo $q->errorCode()`. – deceze Jun 27 '13 at 06:46
@deceze sure it'll work but I prefer try/catch :) – Robert Jun 27 '13 at 06:47
The point of exceptions is that they *bubble up* and automatically prevent execution of the following code. If you don't let exceptions bubble up and immediately continue with code in the same scope, you're just doing a more complicated version of `return false` checking. – deceze Jun 27 '13 at 06:49
if something goes wrong then it should be handled maybe it's because I write software in JAVA/C# and in JAVA I'm forced to use exceptions so I got used to it. With exceptions code is less readable but I like using them anyway. – Robert Jun 27 '13 at 06:52
An exception is an *exceptional situation* which should not happen normally. If you get a lot of exceptions and your standard response is to always catch them immediately, they're pointless. You should let them bubble up a bit to a higher scope where you handle *exceptional failures* for an entire sub-process. Where exactly exceptions are caught is an important consideration; just reflexively catching them immediately negates their usefulness. This goes for Java just as much as PHP. – deceze Jun 27 '13 at 08:14
In Java you are forced to catch all exceptions in PHP you are not that's the diffrence. I agree with you I don't write code to throw exception everywhere because then it'll look like goto application. I use it very often with PDO to rollback transactions. Someone can explain downvotes? – Robert Jun 27 '13 at 08:28

how to insert form values to mysql with PDO

1 Answers1