nuBuilder Forum


https://forums.nubuilder.cloud/

Display object do not show query results using temp querys results

https://forums.nubuilder.cloud/viewtopic.php?t=12290

Page 1 of 2

Display object do not show query results using temp querys results

Posted: Tue Jun 04, 2024 11:59 am
by Costa
Good morning everyone,

I have a display object that needs to show the percentage of project's progress.
Given the multiple tables involved, I opted for a query setting temporary values ​​and then a select that gives me the result as below:

Code: Select all

SET @variable1 = (select SUM(totale) FROM

(
select (preventivo.pre_subtotale) AS totale from 
      preventivo
        LEFT JOIN progetto ON progetto.progetto_id = preventivo.pre_progetto
        LEFT JOIN cdc ON cdc.cdc_id = preventivo.pre_cdc
        LEFT JOIN fasi_lav ON fasi_lav.fasi_lav_id = preventivo.pre_fase_lav
        LEFT JOIN materiale ON materiale.materiale_id = preventivo.pre_materiale
        left join consuntivo ON preventivo.preventivo_id = consuntivo.con_preventivo
        left join fattura ON consuntivo.con_fattura = fattura.fattura_id
        left join preventivo_n ON consuntivo.con_preventivo = preventivo_n.preventivo_n_id

group by preventivo.preventivo_id

)a);

SET @variable2 = (select SUM(totale1) FROM

(
select (preventivo.con_somma_subtotale) AS totale1 from 
      preventivo
        LEFT JOIN progetto ON progetto.progetto_id = preventivo.pre_progetto
        LEFT JOIN cdc ON cdc.cdc_id = preventivo.pre_cdc
        LEFT JOIN fasi_lav ON fasi_lav.fasi_lav_id = preventivo.pre_fase_lav
        LEFT JOIN materiale ON materiale.materiale_id = preventivo.pre_materiale
        LEFT JOIN consuntivo ON preventivo.preventivo_id = consuntivo.con_preventivo


group by preventivo.preventivo_id

)b);

SELECT CONCAT(COALESCE(FORMAT (@variable2 / @variable1 *100, 2), "0"),' %') AS percento;



but, although it works perfectly in PHPMyAdmin, the display object is empty.

any suggestions??

Thanks for any suggestions

Re: Display object do not show query results using temp querys results

Posted: Thu Jun 06, 2024 11:19 pm
by kev1n
PDO does not support executing multiple queries/statements in a single `PDO::query()` call by default. This limitation is why you see no output when attempting to run multiple queries at once. Each query needs to be executed separately.

To address this, we've introduced a new option: **Run a Procedure**. By updating to the latest of nuBuilder, you will gain access to this feature.


display_procedure.png


Here is an example of how the Procedure would look (its output will be displayed in the Display object):

Code: Select all

global $nuDB;    

// Set variable1
$stmt = $nuDB->prepare("
	SET @variable1 = (
		SELECT SUM(totale) FROM (
			SELECT (preventivo.pre_subtotale) AS totale FROM preventivo
			LEFT JOIN progetto ON progetto.progetto_id = preventivo.pre_progetto
			LEFT JOIN cdc ON cdc.cdc_id = preventivo.pre_cdc
			LEFT JOIN fasi_lav ON fasi_lav.fasi_lav_id = preventivo.pre_fase_lav
			LEFT JOIN materiale ON materiale.materiale_id = preventivo.pre_materiale
			LEFT JOIN consuntivo ON preventivo.preventivo_id = consuntivo.con_preventivo
			LEFT JOIN fattura ON consuntivo.con_fattura = fattura.fattura_id
			LEFT JOIN preventivo_n ON consuntivo.con_preventivo = preventivo_n.preventivo_n_id
			GROUP BY preventivo.preventivo_id
		) a
	)
");
$stmt->execute();

// Set variable2
$stmt = $nuDB->prepare("
	SET @variable2 = (
		SELECT SUM(totale1) FROM (
			SELECT (preventivo.con_somma_subtotale) AS totale1 FROM preventivo
			LEFT JOIN progetto ON progetto.progetto_id = preventivo.pre_progetto
			LEFT JOIN cdc ON cdc.cdc_id = preventivo.pre_cdc
			LEFT JOIN fasi_lav ON fasi_lav.fasi_lav_id = preventivo.pre_fase_lav
			LEFT JOIN materiale ON materiale.materiale_id = preventivo.pre_materiale
			LEFT JOIN consuntivo ON preventivo.preventivo_id = consuntivo.con_preventivo
			GROUP BY preventivo.preventivo_id
		) b
	)
");
$stmt->execute();

// Select query
$stmt = $nuDB->prepare("
	SELECT CONCAT(COALESCE(FORMAT(@variable2 / @variable1 * 100, 2), '0'), ' %') AS percento
");
$stmt->execute();

// Fetch the result
$result = $stmt->fetch(PDO::FETCH_NUM);

// Output the result
echo $result[0];

Re: Display object do not show query results using temp querys results

Posted: Fri Jun 07, 2024 12:00 pm
by Costa
Hi Kev1n!

sorry for the late reply but I was travelling.
I had seen from the phone that at first you had given a solution but now that I came back and wanted to implement it, I saw that you modified the master code. Sorry... I didn't mean to give you more work!! :D

A small problem: I loaded the new version of NUBuilder (4.5.8.6) but in my browser (Chrome) the data fields in the input section are overlapped (see attachment) and I don't know if it's a problem with my browser (i also cancelled all cache) or a layout oversight and therefore I wasn't able to experience what you did also because the field for entering the procedure does not appear in the display mask

Anyway, thanks for all your support.
Costa

Re: Display object do not show query results using temp querys results

Posted: Fri Jun 07, 2024 12:18 pm
by kev1n
Did you also run the update within nuBuilder?

The Version Info should then show:
nuBuilder DB: V.4.5-2024.06.06.02
nuBuilder Files: V.4.5-2024.06.06.03

Re: Display object do not show query results using temp querys results

Posted: Fri Jun 07, 2024 12:43 pm
by Costa
Yes, i have done the update (using the database update procedure) and also delete all file/directory and copied the new release but the info are the followings:

Database: MariaDB Server 10.5.8-MariaDB-log
PHP: 7.4.30
nuBuilder DB: V.4.5-2024.05.31.00
nuBuilder Files: V.4.5-2024.05.31.04

in the MYsql error log i found the followings:

Code: Select all

[0] : ===PDO MESSAGE===

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'amministrazione.sys_zzzzsys_user_permission' doesn't exist

===SQL===========

REPLACE INTO zzzzsys_user_permission SELECT * FROM sys_zzzzsys_user_permission

===BACK TRACE====

/share/CACHEDEV1_DATA/Web/amministrazione/core/nusystemupdatelibs.php - line 361 (nuRunQuery)

/share/CACHEDEV1_DATA/Web/amministrazione/core/nusystemupdate.php - line 112 (nuAppendToSystemTables)

/share/CACHEDEV1_DATA/Web/amministrazione/core/nusystemupdate.php - line 23 (nuRunUpdate)



[0] : ===PDO MESSAGE===

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'amministrazione.sys_zzzzsys_permission_item' doesn't exist

===SQL===========

REPLACE INTO zzzzsys_permission_item SELECT * FROM sys_zzzzsys_permission_item

===BACK TRACE====

/share/CACHEDEV1_DATA/Web/amministrazione/core/nusystemupdatelibs.php - line 361 (nuRunQuery)

/share/CACHEDEV1_DATA/Web/amministrazione/core/nusystemupdate.php - line 112 (nuAppendToSystemTables)

/share/CACHEDEV1_DATA/Web/amministrazione/core/nusystemupdate.php - line 23 (nuRunUpdate)



[0] : ===PDO MESSAGE===

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'amministrazione.sys_zzzzsys_email_log' doesn't exist

===SQL===========

REPLACE INTO zzzzsys_email_log SELECT * FROM sys_zzzzsys_email_log

===BACK TRACE====

/share/CACHEDEV1_DATA/Web/amministrazione/core/nusystemupdatelibs.php - line 361 (nuRunQuery)

/share/CACHEDEV1_DATA/Web/amministrazione/core/nusystemupdate.php - line 112 (nuAppendToSystemTables)

/share/CACHEDEV1_DATA/Web/amministrazione/core/nusystemupdate.php - line 23 (nuRunUpdate)

Re: Display object do not show query results using temp querys results

Posted: Fri Jun 07, 2024 1:22 pm
by Costa
Hi Kev1n,

just for information, I completely deleted the db and recreated it by uploading the backup I had made shortly before the update. I deleted all the files and directories and proceeded to update again but nothing changes. Info are still

Database: MariaDB Server 10.5.8-MariaDB-log
PHP: 7.4.30
nuBuilder DB: V.4.5-2024.05.31.00
nuBuilder Files: V.4.5-2024.05.31.04

Re: Display object do not show query results using temp querys results

Posted: Fri Jun 07, 2024 1:26 pm
by kev1n
Did you download this ? https://github.com/nuBuilder/nuBuilder/ ... master.zip

Re: Display object do not show query results using temp querys results

Posted: Fri Jun 07, 2024 1:38 pm
by Costa
My mistake.
I assumed it was the latest github release and downloaded that.

I apologize if I wasted your time

anyway it works perfectly.

Costa

Re: Display object do not show query results using temp querys results

Posted: Fri Jun 07, 2024 2:04 pm
by Costa
Kev1n,

Sorry to bother you but but Is there the possibility of refreshing the display object (working with procedure) following the application of filters in the sql?
The procedure

Code: Select all

function getDisplayValue($obj) {

    $sql = "SELECT sob_display_sql FROM `zzzzsys_object` WHERE sob_all_id = ?";
    $t = nuRunQuery($sql, [$obj]);

    if (db_num_rows($t) == 1) {
        $r = db_fetch_row($t);
        if ($r != false) {

            $disS = nuReplaceHashVariables($r[0]);
            $disT = nuRunQuery($disS);
            $disR = db_fetch_row($disT);

            return ($disR != false) ? $disR[0] : false;
        }
    }

    return false;
}

$obj = '#refreshDisplayObject#';
$value = getDisplayValue($obj);
if ($value == false) {
    $j = "nuMessage([nuTranslate('Failed to refresh the Display Object: ') + '$obj']); ";
} else {
    $j = "$('#$obj').val('$value').change();";
}

nuJavascriptCallback($j);
doesn't work anymore... some tips how to use the javascript on_click??

Thanks
Costa

Re: Display object do not show query results using temp querys results

Posted: Fri Jun 07, 2024 2:30 pm
by kev1n
The nuRefreshDisplayObject() function has not yet been modified to update when a procedure is used instead of SQL. This still needs to be done (if that’s what you mean).
All times are UTC+02:00
Page 1 of 2

Powered by phpBB® Forum Software © phpBB Limited