Check if value exist in Mysql Database on PHP

Question

When I'm updating an item (ativo) of an employee (colaborador) I need to check if an employee that the user input exists or not in the DB, and if it doesn't - give an error message. I don't know how can I do it, can you guys point me to some tutorial that explains it? Thanks in advance!
This is my query btw:

 $pdo = Database::connect();

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "UPDATE ativos SET ativo = ?, comentario = ?, data_aquisicao = ?,
                localizacao = ?, fabricante = ?, modelo = ?, 
                imei = ?, numero_serie = ?, ativo_sap = ?, 
                anexo_a = ?, evento = ?, data_evento = ?,
                id_colaborador = (SELECT id_colaborador 
                                    FROM colaboradores 
                                    WHERE nome = ? 
                                    LIMIT 1
                                  ) 
        WHERE id_ativo = ?";

$q = $pdo->prepare($sql);
$q->execute(array($ativo,$comentario,$data_aquisicao,$localizacao,
            $fabricante,$modelo,$imei,$numero_serie,$ativo_sap,
            $anexo_a,$evento,$data_evento,$id_colaborador,$id));

And this is the input (i don't know if it's needed to validate if the row exist)

<input autocomplete="off" name="id_colaborador" type="text"  placeholder="Nome do Colaborador" value="<?php echo !empty($nome)?$nome:'';?>" class='auto'>

I guess what you need is AJAX, cf stackoverflow.com/questions/9762478/… — Jules R
– Jules R, Commented Dec 18, 2018 at 9:39
One mistake is is referencing the id_colaborador with #nome# and not with ID. First for all, I'll reference colaborador by ID and pass it to the UPDATE to just do UPDATE bla bla WHERE id = $idColaborador — Roy Bogado
– Roy Bogado, Commented Dec 18, 2018 at 9:40
Simple answer is, first do a select of the colaborator if you get a row back you will have the benefit of being able to use the rows id in the update query and if you dont get a row back you throw an error message — RiggsFolly
– RiggsFolly, Commented Dec 18, 2018 at 9:43
Well then you either have to repaint the page, OR, you have to learn some AJAX so you can do it all without having to round trip the whole page — RiggsFolly
– RiggsFolly, Commented Dec 18, 2018 at 9:48
@CarlosSantiago but you're searching id by nome... and that maybe a problem if colaborador has the same name. — Roy Bogado
– Roy Bogado, Commented Dec 18, 2018 at 10:05

Leandro Finger · Accepted Answer · 2018-12-18 10:07:41Z

1

Your query is not considering the possibility of homonyms. So, you'd better use some auto_complete field to get the 'id_colaborador' based on the name and keep it in a hidden input field and then execute the update with the exact values.

answered Dec 18, 2018 at 10:07

Leandro Finger

864 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Carlos Santiago Over a year ago

I have the autocomplete, but it won't prevent the user to input a employee that doesn't exist..

Collectives™ on Stack Overflow

Check if value exist in Mysql Database on PHP

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related