How to use Try Catch on an Insert

Question

How do I use a Try catch to return an error from an Insert that was not inserted into the database. For example

$sql = $pdo->prepare("INSERT INTO usuarios (nome,email,senha,telefone) VALUES (:nome,:email,:senha,:telefone)");
   $sql->bindValue(":nome",$nome);
   $sql->bindValue(":email",$email);
   $sql->bindValue(":senha",md5($senha));
   $sql->bindValue(":telefone",$telefone);
   $sql->execute();

How can I check to know if it was really inserted and if not, return me the error.

Answer 1

Instead of using Exception use Pdoexception

Answer 2

In case you would have to work with the exception class of php the class (Exception), I put a basic example here following the line of your code, but there is a more correct way to work with it.

http://php.net/manual/en/language.exceptions.php

try
{

   $sql = $pdo->prepare("INSERT INTO usuarios (nome,email,senha,telefone) VALUES (:nome,:email,:senha,:telefone)");
   $sql->bindValue(":nome",$nome);
   $sql->bindValue(":email",$email);
   $sql->bindValue(":senha",md5($senha));
   $sql->bindValue(":telefone",$telefone);
   $sql->execute();

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

Answer 3

Good afternoon!

The PDO execute() method returns a boolean that indicates whether there has been success or failure in SQL execution (http://php.net/manual/en/pdostatement.execute.php).

In your case, you can do so:

public function insereDados($nome, $email, $senha, $telefone) {
    if (!$nome || !$email || !$senha || !$telefone) {
        throw new DadosNaoPreenchidosException("Dados incompletos!");
    }

    $sql = $pdo->prepare("INSERT INTO usuarios (nome,email,senha,telefone) VALUES (:nome,:email,:senha,:telefone)");
    $sql->bindValue(":nome",$nome);
    $sql->bindValue(":email",$email);
    $sql->bindValue(":senha",md5($senha));
    $sql->bindValue(":telefone",$telefone);
    $sucesso = $sql->execute();
    if (!$sucesso) {
        throw new SqlException("Mensagem de erro");
    }
}

You can create exceptions to treat them individually outside the method that calls it, thus: https://secure.php.net/manual/en/language.exceptions.extending.php

class SqlException extends SystemException {
    public __construct($mensagem) {
        parent::__construct($mensagem, 0);
    }
}

And then, you treat your exceptions in the call of methods:

//faz alguma coisa
try {
    $obj->insereDados("João da Silva", "[email protected]", "123", "(11) 1111-1111");
} catch (DadosIncompletosException $e) {
    echo "Dados não preenchidos";
} catch (SqlException $e) {
    echo "Erro ao executar o SQL";
}

Answer 4

By default PDO emits errors in unsuccessful query, since errors and exceptions are distinct things especially in php.

The simplest way to treat a failure is to catch the result of execute()

$sql = $pdo->prepare("INSERT INTO usuarios (nome, email) VALUES (:nome,:email,:senha)");
$sql->bindValue(":nome",$nome);
$sql->bindValue(":email",$email);

if(!$sql->execute()){
    print_r($sql->errorInfo());
}

If you really need exceptions you should flag this in the constructor by informing the fourth argument with the options array or via setAttribute(). The setting that must be modified is PDO::ATTR_ERRMODE and its value is PDO::ERRMODE_EXCEPTION

Example in the constructor:

$opcoes = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
$pdo = new PDO('mysql:host=localhost;dbname=catalogo', 'root', 'root', $opcoes);

Example via setAttribute()

$pdo = new PDO('mysql:host=localhost;dbname=catalogo', 'root', 'root');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

When working with exceptions always remember which problem you want to solve or capture only the most specific Exception (which will give a different treatment) in the case Pdoexception.

$opcoes = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
$pdo = new PDO('mysql:host=localhost;dbname=catalogo', 'root', 'root', $opcoes);

try{
    $sql = $pdo->prepare("INSERT INTO erro de sintaxe");
    $sql->bindValue(":nome",$nome);
    $sql->bindValue(":email",$email);

    $sql->execute();
} catch(PDOException $e){
  echo 'pokemon capturado com sucesso!11';
}

Answer 5

In my opinion the best way to use a block of exception related to the database is using PDO::beginTransaction.

Take a look at the item Applying business rules when an exception occurs of reply: What Try/Catch Blocks are for and when they should be used?