Query only doing half of what I want

Question

I have a function that is supposed to delete a product and, with it, delete its stock as well. But it keeps just deleting the stock. SSometimes I change the order of the querys and it shows an error that I can't delete idproduto because its an FK, so I put the delete stock query first. It deletes the stock and there's no error, but it doesn't delete the product.

Database:

TABLE produtos(idproduto, descr) TABLE stock(idstock, idproduto, stock)

Code:

function removeProduto($idproduto){

        global $conn;
        $res="";
        $sql = "DELETE FROM stock WHERE idproduto= ".$idproduto.";";
    
        if($conn -> query($sql) === TRUE){

          $sql = "DELETE FROM produtos WHERE idproduto= ".$idproduto.";";
          
          $res = "Produto removido com sucesso";
    
        } else {

          $res = "Erro: ".$conn->error;
        }
    
        return($res);
    
    
    
    }

I also tried to put the delete produtos query right under the other, I switched them....

[Little Bobby](http://bobby-tables.com/) says [you may be at risk for SQL Injection Attacks](https://stackoverflow.com/q/60174/). Learn about [Prepared Statements](https://en.wikipedia.org/wiki/Prepared_statement) with [parameterized queries](https://stackoverflow.com/a/4712113/5827005). — GrumpyCrouton, Dec 22 '20 at 16:25
You don't do anything with `$sql` after changing it to `DELETE FROM produtos ...` — JeffUK, Dec 22 '20 at 17:00
@JeffUK i do, i ust didn't show it with the code because it wasn't relevant, the error would be in the part of code i showed — curious, Dec 22 '20 at 22:31

score 3 · Answer 1 · answered Dec 22 '20 at 16:24

3

You can use a cascading foreign key constraint instead:

alter table stock add constraint fk_stock_product
    foreign key (idproduto) references produtos (idproduto)
    on delete cascade;

Then when a row is deleted from produtos, all stock is automatically deleted.

answered Dec 22 '20 at 16:24

Gordon Linoff

1,242,037
58
646
786

All stock? Like, the whole table? Or just the stock refering to that product? – curious Dec 22 '20 at 22:31
The cascading delete removes all references to a product that has been deleted in the table where the foreign key is defined. – Gordon Linoff Dec 23 '20 at 01:23

score 3 · Accepted Answer · answered Dec 22 '20 at 16:48

3

You can use left join

DELETE s,p FROM stock s
   LEFT JOIN produtos p ON s.idproduto = p.idproduto
      WHERE idproduto = idproduto

answered Dec 22 '20 at 16:48

Thank you, it worked! At the beginning I had an error saying idproduto was ambiguous, because the program didn't know what idproduto to pick up, but i changed one of them and it works nicely, thanks! – curious Dec 22 '20 at 22:48
Glad it helped. – Dec 22 '20 at 23:30
I have another question. I need to make an update on "descr" from produtos. The stock table also has that value as "description". The query should update the description in both the produtos table and the stock one. This is what I have "$sql = "UPDATE t FROM stock t LEFT JOIN produtos t2 ON t2.idproduto = t.idprod SET t.stock = '".$descricao."' WHERE idproduto = idprod;";". I took it off some different versios in stack overflow but none quite works. – curious Dec 22 '20 at 23:38
1

This should work, you need to correct your column names `$sql = "UPDATE stock LEFT JOIN produtos ON produtos.idproduto = stock.idprod SET stock.column = '".$descricao."', produtos.column = ".$descricao.", WHERE stock.idproduto = idprod;";"` you beter use prepared statements you will be wide open to sql injections. – Dec 22 '20 at 23:47
1

worked like a charm! yes, i'm going to try and pass it onto prepared statements later, i just need to have the basics working first :) – curious Dec 23 '20 at 00:06

score 1 · Answer 3 · answered Dec 22 '20 at 16:30

1

Presuming $conn is a PDO object... PDO::query() returns a PDOStatement and not a boolean. Comparing if the object is equal to true will always be false.

answered Dec 22 '20 at 16:30

Dicebar

21
2

Query only doing half of what I want

3 Answers3