YouTube Video "Low Code Corner #9" - Is this query correct?

[Celson Aquino]

Hello,

I am new in DaDaBIK, just bought my license less thank 1 week ago (this is my first post on forum by the way), and I was learning to code an operational hook to update the master table with the sum of values of a detail table.

I found the video below on YouTube DaDaBIK channel:
https://www.youtube.com/watch?v=e1TwYii0Qrc

When I tried the query on PhpMyAdmin I found it is updating all invoices, then I added a where clause in order to fix it. Could you tell me if I am doing something wrong, or if it was really lacking the WHERE clause?

Thank you!
Celson

System info
PHP Version: 7.1.24
mysql version: 5.6.41-84.1
Web server: Apache
Client: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.132 Safari/537.36

 

[Celson Aquino]

Here is the PHP Function I coded on hook for Insert and Update operations (according to my database tables):

[pre]
function dadabik_atualiza_valor_total_planilha($id_lancamento) {

global $conn;

$sql = "UPDATE planilhas SET valor_total_planilha = ( SELECT sum(lancamentos.valor_lancamento) FROM lancamentos WHERE lancamentos.planilhas_id_planilha = ( SELECT lancamentos.planilhas_id_planilha FROM lancamentos WHERE lancamentos.id_lancamento = :id_lancamento ) ) WHERE planilhas.id_planilha = ( SELECT lancamentos.planilhas_id_planilha FROM lancamentos WHERE lancamentos.id_lancamento = :id_lancamento )";

$res_prepare = prepare_db($conn, $sql);

$values_to_bind = array();

$values_to_bind['id_lancamento'] = $id_lancamento;

foreach( $values_to_bind as $key => $value ) {
$res_bind = bind_param_db($res_prepare, ":".$key, $value);
}

$res_ex = execute_prepared_db($res_prepare, 0);

}
[/pre]

Below is the function for the delete operation:

[pre]
function dadabik_atualiza_valor_total_planilha_delete_item($id_lancamento) {

global $conn;

$sql = "UPDATE planilhas SET valor_total_planilha = ( SELECT sum(lancamentos.valor_lancamento) FROM lancamentos WHERE lancamentos.planilhas_id_planilha = ( SELECT lancamentos.planilhas_id_planilha FROM lancamentos WHERE lancamentos.id_lancamento = :id_lancamento ) AND lancamentos.id_lancamento <> :id_lancamento ) WHERE planilhas.id_planilha = ( SELECT lancamentos.planilhas_id_planilha FROM lancamentos WHERE lancamentos.id_lancamento = :id_lancamento )";

$res_prepare = prepare_db($conn, $sql);

$values_to_bind = array();

$values_to_bind['id_lancamento'] = $id_lancamento;

foreach( $values_to_bind as $key => $value ) {
$res_bind = bind_param_db($res_prepare, ":".$key, $value);
}

$res_ex = execute_prepared_db($res_prepare, 0);

}
[/pre]

My array $hooks is below:

[pre]
$hooks['lancamentos']['insert']['after'] = 'dadabik_atualiza_valor_total_planilha';
$hooks['lancamentos']['update']['after'] = 'dadabik_atualiza_valor_total_planilha';
$hooks['lancamentos']['delete']['before'] = 'dadabik_atualiza_valor_total_planilha_delete_item';
[/pre]

 

[eugenio]

Hello Celson,
I don't know which query you are referring to, the one at minute 5:42?
Those queries have a where clause actually, but they can't work if you just copy and paste in phpmyadmin because they are prepared statement with placeholders (like :id_invoice_item) that are replaced when the query is executed.

Best,

 

[Celson Aquino]

Hi, Eugenio,

The query I ran in phpMyAdmin was not that one with the placeholders of prepared statements. It was a valid query. I will try to explain here what happened:

a) query that updates all records in the master table planilhas, and not only the record i want in master table (I refer to record 15 of detail table lancamentos)
[pre]
UPDATE planilhas SET valor_total_planilha = ( SELECT sum(lancamentos.valor_lancamento) FROM lancamentos WHERE lancamentos.planilhas_id_planilha = ( SELECT lancamentos.planilhas_id_planilha FROM lancamentos WHERE lancamentos.id_lancamento = 15 ) )
[/pre]

b) query that updates only the correct record on master table
[pre]
UPDATE planilhas SET valor_total_planilha = ( SELECT sum(lancamentos.valor_lancamento) FROM lancamentos WHERE lancamentos.planilhas_id_planilha = ( SELECT lancamentos.planilhas_id_planilha FROM lancamentos WHERE lancamentos.id_lancamento = 15 ) ) WHERE planilhas.id_planilha = ( SELECT lancamentos.planilhas_id_planilha FROM lancamentos WHERE lancamentos.id_lancamento = 15 )
[/pre]

 

[eugenio]

You are right for some reason the where clause is missing in the video, thanks for having spotted it!
I will try to edit it and add an alert.

Best,

 

[Celson Aquino]

I'm glad I can help you.