Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

PDO Updates diferent columns same query

Could you tell me what I am doing wrong?

I want to update 02 different tables in one query using PDO inside MySQL DB

This is my code:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

            $dados = [
                'senha' => password_hash($_POST['senha'], PASSWORD_BCRYPT),
                'ativo' => '0',
                'email' => $_POST['email'],
            ];

            $sql = "
            UPDATE administradores, solicitacoes_reseta_senhas
            SET    senha = :senha, ativo = :ativo
            WHERE  email = :email and email = :email
            ";

            $stmt= $pdo->prepare($sql);
            $stmt->execute($dados);

And this the error I am getting from MySQL:

Fatal error: Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'email' in where clause is ambiguous

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'email' in where clause is ambiguous

Below the structures of the tables:

CREATE TABLE IF NOT EXISTS `solicitacoes_reseta_senhas` (
  `id` int NOT NULL AUTO_INCREMENT,
  `email` varchar(250) NOT NULL,
  `token` varchar(250) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `data_expiracao` datetime NOT NULL,
  `ativo` int NOT NULL,
  `data_criacao` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `administradores` (
  `id` int NOT NULL AUTO_INCREMENT,
  `nome` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `sobrenome` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `email` varchar(120) DEFAULT NULL,
  `senha` varchar(150) NOT NULL,
  `apelido` varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `sexo` varchar(15) DEFAULT NULL,
  `telefone_fixo` varchar(25) NOT NULL,
  `telefone_celular` varchar(25) NOT NULL,
  `data_nascimento` date DEFAULT NULL,
  `observacao` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `data_criacao` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `data_atualizacao` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `adm_atualizacao` int NOT NULL DEFAULT '0',
  `ativo` tinyint DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`,`ativo`)
) ;

Thank you for your time

>Solution :

Use aliases

  $dados = [
            'senha' => password_hash($_POST['senha'], PASSWORD_BCRYPT),
            'ativo' => '0',
            'email' => $_POST['email'],
        ];

        $sql = "
        UPDATE administradores a, solicitacoes_reseta_senhas s
        SET    senha = :senha, ativo = :ativo
        WHERE  a.email = :email and s.email = :email
        ";

        $stmt= $pdo->prepare($sql);
        $stmt->execute($dados);
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading