CODEWITHSUNDEEP
phpmysql
Carlos Santiago
Carlos Santiago

Reputation: 73

UPDATE with multiple WHERE and SELECT condition - MySql

So, I have two tables ativos and colaboradores and they are linked by id_colaborador (FK) on my update page, I'm able to change the id_colaborador but instead of changing the ID I want to write the name that match to that ID but I think I need multiple WHERE conditions and SELECT, may anyone help me out? Thanks! I have the following code to update

$sql = "UPDATE ativos  SET ativo = ?, comentario = ?, data_aquisicao = ?, localizacao = ?, fabricante = ?, modelo = ?, imei = ?, 
    numero_serie = ?, ativo_sap = ?, anexo_a = ?, evento = ?, data_evento = ?, SELECT id_colaborador FROM colaboradores WHERE nome = ? AND WHERE id_ativo = ?";

UPDATE
I've already try to separete the two statements like this:

$sql = "UPDATE ativos  SET ativo = ?, comentario = ?, data_aquisicao = ?, localizacao = ?, fabricante = ?, modelo = ?, imei = ?, 
        numero_serie = ?, ativo_sap = ?, anexo_a = ?, evento = ?, data_evento = ?, id_colaborador = ? WHERE id_ativo = ?";
        $sql2 = "SELECT id_colaborador FROM colaboradores WHERE nome = ?";
        $q = $pdo->prepare($sql,$sql2);

But it gives me the following error:

Warning: PDO::prepare() expects parameter 2 to be array, string given in C:\xampp\htdocs\gestao\Colaboradores\ativo_update.php on line 120

Fatal error: Uncaught Error: Call to a member function execute() on boolean in C:\xampp\htdocs\gestao\Colaboradores\ativo_update.php:121 Stack trace: #0 {main} thrown in C:\xampp\htdocs\gestao\Colaboradores\ativo_update.php on line 121

Upvotes: 1

Views: 280

Answers (2)

ADyson
ADyson

Reputation: 61914

This query will allow you to update the id_colabarador in the ativos table based on a name being input from the form:

$sql = "UPDATE ativos SET ativo = ?, comentario = ?, data_aquisicao = ?, localizacao = ?, fabricante = ?, modelo = ?, imei = ?, numero_serie = ?, ativo_sap = ?, anexo_a = ?, evento = ?, data_evento = ?, id_colabarador = (SELECT id_colaborador FROM colaboradores WHERE nome = ? LIMIT 1) WHERE id_ativo = ?";

Note though that if the names are not unique in the colabarador table, there's a chance it may return the wrong ID by accident.

As mentioned in the comments though, I highly recommend that instead you re-design the GUI so that the user can select using the name, but that in the background this stores the ID related to the selected record, so that the ID can be passed directly from the form to the PHP, and then be used in the SQL in place of the sub-query. This will be more robust and doesn't leave you open to accidental mis-identification of the record.

Upvotes: 0

HamzaNig
HamzaNig

Reputation: 1029

You can use sub-query as i understand that you want to update by name which name not in the same table ok you can check query bellow :

$sql = "UPDATE ativos  SET ativo = ?, comentario = ?, data_aquisicao = ?, localizacao = ?, fabricante = ?, modelo = ?, imei = ?, 
    numero_serie = ?, ativo_sap = ?, anexo_a = ?, evento = ?, data_evento = ?  WHERE id_colaborador in (SELECT id_colaborador FROM colaboradores WHERE nome = ?) and  id_ativo = ?";

@ADyson have solution in comment which update the id_colaborador too by name :

$sql = "UPDATE ativos SET ativo = ?, comentario = ?, data_aquisicao = ?, localizacao = ?, fabricante = ?, modelo = ?, imei = ?, numero_serie = ?, ativo_sap = ?, anexo_a = ?, evento = ?, data_evento = ?, id_colabarador = (SELECT id_colaborador FROM colaboradores WHERE nome = ? LIMIT 1) WHERE id_ativo = ?";

Upvotes: 1

Related Questions