1

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

Carlos Santiago
  • 73
  • 1
  • 9
  • Are you asking about `WHERE IN` with a `SELECT`? Can you clarify what you mean. – Script47 Dec 05 '18 at 11:33
  • You generally _can't_ do a select and update in the same statement. You should use two separate statements, one for the update, and one for the select. – Tim Biegeleisen Dec 05 '18 at 11:35
  • I mean, I think I need two `WHERE` conditions and one `SELECT` to get the name I want to input from `colaboradores` instead of input the `id_colaborador` that is my `FK`. – Carlos Santiago Dec 05 '18 at 11:35
  • @TimBiegeleisen but i was able to do it with `INSERT INTO` , why can't I do it on `UPDATE` ? – Carlos Santiago Dec 05 '18 at 11:37
  • I think I understand - you want to input the name, and use the name to get the ID based on that, in order to use the ID in your UPDATE. But...if the name column is not the primary key and does not have a "unique" then you run the risk of returning multiple values and/or the wrong ID. This strikes me as a risky plan - unless your names are all guaranteed to always be unique in the colaboradores table? – ADyson Dec 05 '18 at 11:37
  • @ADyson yes it's that!! I know that risk, that's why near the `name input` field it has the `ID` as `readonly` so the user can be sure of what user he wants, but it is possible to do what I want? :/ – Carlos Santiago Dec 05 '18 at 11:40
  • So - an UPDATE with a sub-query then? Does this help: https://stackoverflow.com/questions/11588710/mysql-update-query-with-sub-query ? – CD001 Dec 05 '18 at 11:40
  • @CarlosSantiago When you do `INSERT INTO ... SELECT` it is a single statement. – Tim Biegeleisen Dec 05 '18 at 11:41
  • 1
    instead why don't you give the user a dropdown list or an autocomplete which can return all possible names. Then when the user selects a name it would store the related ID in a hidden field, and that could be used when the form is submitted, so that your SQL is provided with a ready-made ID directly, and doesn't have to go looking for it. Then there is no ambiguity about what the user chose. – ADyson Dec 05 '18 at 11:42
  • @ADyson I've already think about it, but I still need the dropdown to replace the ID with the name that the user will input :/ – Carlos Santiago Dec 05 '18 at 11:46
  • @TimBiegeleisen Iv'e try to separate them 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 an error – Carlos Santiago Dec 05 '18 at 11:48
  • Not sure what you mean. If you have, for example `` then when you submit the form, if the user selected "Carlos" it will send the value `1` to the server, and you can use that directly in your SQL, without needing to know the actual name – ADyson Dec 05 '18 at 11:48
  • @CarlosSantiago is id_colaborador on both table or id_colaborador=id_ativo ? – HamzaNig Dec 05 '18 at 11:50
  • @ADyson but i have more than 300 users... I can't do it one by one on the code, it should get the values from the DB – Carlos Santiago Dec 05 '18 at 11:51
  • if you have too many users for a dropdown, then use an autocomplete which does the same thing - lets the user search by typing, and then lets them select a name from the results, and then stores the associated ID in a hidden field, which can then be sent to the server. Something like jQuery autocomplete can be made to do that very easily. People implement things like that all the time. I bet you could even find [an example](https://stackoverflow.com/questions/4815330/jquery-ui-autocomplete-with-item-and-id) if you look around. – ADyson Dec 05 '18 at 11:52

2 Answers2

1

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 = ?";
HamzaNig
  • 1,019
  • 1
  • 10
  • 33
  • @ADyson Thanks for that yeah i see it but ithink he already know that because even the select will return more than one row he still have id_ativo there and as i think will not make a problem because id_ativo and the returned id_colaborador will make a unique key (AS I THINK) – HamzaNig Dec 05 '18 at 12:05
  • Well, it does not give me any error anymore, but it totaly skips the action and does nothing (neither with the ID now) :/ – Carlos Santiago Dec 05 '18 at 12:05
  • @CarlosSantiago is that mean is not updating ? – HamzaNig Dec 05 '18 at 12:08
  • @HamzaNig yes :/ – Carlos Santiago Dec 05 '18 at 12:09
  • @CarlosSantiago are you really looking to update the value of id_colabarador within the `ativos` table? I realised this query is using it to restrict what rows are returned, but actually you already have `id_ativo` for this, which I guess is PK? I wonder if the query should be `$sql = "UPDATE ativos SET ativo = ?, comentario = ?, data_aquisicao = ?, localizacao = ?, fabricante = ?, modelo = ?, imei = ?, numero_serie = ?, ativo_sap = ?, anexo_a = ?, evento = ?, data_evento = ?, id_colabarador = (SELECT TOP 1 id_colaborador FROM colaboradores WHERE nome = ?) WHERE id_ativo = ?";` – ADyson Dec 05 '18 at 12:09
  • for the input field I think it remain the same, right? ` ` – Carlos Santiago Dec 05 '18 at 12:10
  • @ADyson Yes, both of `id_colaborador` and `id_ativo` are `PK` and `id_colaborador` is the `FK` of the two tables (`ativos.id_colaborador` and `colaboradores.id_colaborador` ) – Carlos Santiago Dec 05 '18 at 12:11
  • @CarlosSantiago if yes you can make a `select * from ativos WHERE id_colaborador in (SELECT id_colaborador FROM colaboradores WHERE nome = ?) and id_ativo = ?` and see if the select return rows if not that mean there is no rows with the condition that you set – HamzaNig Dec 05 '18 at 12:12
  • @CarlosSantiago Ok then well then my query in my last comment (I edited it, you may need to refresh) makes more sense than this suggestion. But like I said in the main comment thread, it would make a lot more sense if you just use some kind of autocomplete to provide the ID directly from the HTML form, then you don't need the subquery at all – ADyson Dec 05 '18 at 12:12
  • @HamzaNig OP is trying change the value of the id_colabarador field in the ativos table, not restrict the results by it. id_ativo is already unique anyway, so if that is in the WHERE clause it can only ever return one row - an extra item in the WHERE clause won't do anything useful. At worst it will cause the table not to update because the new ID won't match the existing one. – ADyson Dec 05 '18 at 12:13
  • @ADyson With that query it now gives me an error `Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1 id_colaborador FROM colaboradores WHERE nome = '9') WHERE id_ativo = '39'' at line 1 in C:\xampp\htdocs\gestao\Colaboradores\ativo_update.php:119 Stack trace: #0 C:\xampp\htdocs\gestao\Colaboradores\ativo_update.php: PDOStatement->execute(Array) #1 {main} thrown inC:\xampp\htdocs\gestao\Colaboradores\ativo_update.php` – Carlos Santiago Dec 05 '18 at 12:15
  • 1
    @CarlosSantiago oh sorry it's mysql isn't it, TOP is SQL Server syntax. Apologies. Try `$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 = ?";` instead. But _really_, I would stop wasting your time on this and look at a proper autocomplete instead. Honestly, it's a far more normal and reliable solution. – ADyson Dec 05 '18 at 12:16
  • @ADyson Omg it worked thank you so much!!! But I will be honest.. I didn't get what `LIMIT 1` does, it's something that important??? – Carlos Santiago Dec 05 '18 at 12:20
  • @ADyson but i needed the query to work with the `name` instead of the `ID` for the autocomplete to work too, now I can! Thanks a lot!!! (Mayby you should post that query as an answer so I can give it the "Correct" !! – Carlos Santiago Dec 05 '18 at 12:21
  • @ADyson yeah now i understand he wont to udapte id_colabarador Too yeah it will make rpoblem if she returned more than one Thanks for explication , yep you can post it as an answer it will be more helpfull than in comment – HamzaNig Dec 05 '18 at 12:23
  • @CarlosSantiago LIMIT 1 stops it from returning more than one row, in case of a non-unique name (otherwise the query will fail). It's a keyword, you can google it easily. And "i needed the query to work with the name instead of the ID for the autocomplete to work too"...no, you don't, that's been my point all along. The autocomplete can return you the **ID** if you want it to. Anyway, glad you solved it for now, I wrote a formal answer for it. – ADyson Dec 05 '18 at 13:47
  • Wow.. thanks! I didn't knew that an autocomplete could return the ID too, guess I'll search and implement it! Thanks a lot! – Carlos Santiago Dec 05 '18 at 14:19
  • @CarlosSantiago look at my last comment on the question comments thread, I already posted a link to an example. You will be able to find other examples too if you search more. – ADyson Dec 05 '18 at 14:28
  • @ADyson Thanks a lot one more time! – Carlos Santiago Dec 05 '18 at 14:35
0

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.

ADyson
  • 57,178
  • 14
  • 51
  • 63