How the search field work.

[kev1n]

I'm not sure if you can solve this by using brackets. An alternative way is to create a temporary table in Before Browse (PHP)

[nub74]

Hi Kev1n,
in fact I also tried this solution following one of the examples posted in the forum
(https://forums.nubuilder.cloud/viewtopic. ... t=table_id)
but it does not create the temporary table for me (should it be visible in phpmyadmin?).
Looking at the debug I see that no errors are even generated. However, this seems to be the right way to proceed.
What is wrong with the example of the post mentioned above?
Thank you.

[kev1n]

Your PHP Before Browse code should look like this:

Code: Select all

$s =  " CREATE TABLE #TABLE_ID#
		SELECT
			registro_task.*,
			tasks.codice,
			zzzzsys_user.sus_name,
			commesse.commessa
		FROM
			registro_task
			LEFT JOIN tasks ON registro_task.id_task = tasks.id_task
			LEFT JOIN zzzzsys_user ON registro_task.id_operatore = zzzzsys_user.zzzzsys_user_id
			LEFT JOIN commesse ON registro_task.id_commessa = commesse.commessa
			LEFT JOIN livelli_view ON registro_task.id_operatore = livelli_view.id_operatore
		WHERE
		(
			tasks.id_reparto = (SELECT reparti.id_reparto
			FROM reparti
			LEFT JOIN operatori ON reparti.id_reparto = operatori.id_reparto
			WHERE
			(operatori.id_nub = '#USER_ID#'))
			AND
			(livelli_view.livello < (SELECT livelli_view.livello 
			FROM livelli_view 
			LEFT JOIN registro_task ON livelli_view.id_operatore = registro_task.id_operatore
			WHERE
			((registro_task.id_operatore = '#USER_ID#')) GROUP BY livelli_view.livello )) 
			OR
			(registro_task.id_operatore='#USER_ID#')
		)
		";

nuRunQuery($s);

And the Browse SQL:

Code: Select all

SELECT * FROM #TABLE_ID#

[nub74]

Hi Kev1n,
the code above does not work for me as I get the "Unknown column 'task' in field list" error,
but there is no a field with this name in fact using the query as SQL source has always worked.
However the temporary table cannot be a solution for me as I don't have to limit myself to filtering data
but I also have to write new records in the table and in this way I would write them in the temporary table
instead of in my work table. What other solution can we find to filter data correctly?
I really need a solution because I only need this step to finish my project.
Thank you very much.

[admin]

nub74,

SELECT id_riga,commessa,sus_name,task,note,ore,data
FROM registro_task LEFT JOIN tasks ON registro_task.id_task = tasks.id_task LEFT JOIN zzzzsys_user ON registro_task.id_operatore = zzzzsys_user.zzzzsys_user_id LEFT JOIN commesse ON registro_task.id_commessa = commesse.commessa LEFT JOIN livelli_view ON registro_task.id_operatore = livelli_view.id_operatore
WHERE tasks.id_reparto = (SELECT reparti.id_reparto FROM reparti LEFT JOIN operatori ON reparti.id_reparto = operatori.id_reparto WHERE operatori.id_nub = '5d3578bad8ecfe0') AND (livelli_view.livello < (SELECT livelli_view.livello FROM livelli_view LEFT JOIN registro_task ON livelli_view.id_operatore = registro_task.id_operatore WHERE ((registro_task.id_operatore = '5d3578bad8ecfe0')) AND ( (CONVERT(commessa USING utf8) LIKE "%1185%" OR CONVERT(sus_name USING utf8) LIKE "%1185%" OR CONVERT(task USING utf8) LIKE "%1185%" OR CONVERT(note USING utf8) LIKE "%1185%" OR CONVERT(ore USING utf8) LIKE "%1185%" OR CONVERT(data USING utf8) LIKE "%1185%") )
GROUP BY livelli_view.livello )) OR (registro_task.id_operatore='5d3578bad8ecfe0')

I believe the double brackets in the middle of your GROUP BY clause is the problem.


Steven

[kev1n]

However the temporary table cannot be a solution for me as I don't have to limit myself to filtering data
but I also have to write new records in the table and in this way I would write them in the temporary table
.

The temporary table is just used for the Browse View, not the Edit Screen.

[nub74]

Hi kev1n,
somehow I managed to make the temporary table work.
Now I would understand how nuBuilder works.
You wrote:

The temporary table is used only for the Browse view, not for the Edit screen.

I added some records to do some tests but apparently they reside only in the temporary table
because they don't exist in the original table from which I created the temporary one.
Is it correct? Should not the new records and their edits appear in the original table
based on what you wrote?
Thank you.

[nub74]

And one last question:
is it possible to use the search field to search for multiple data or I need to write custom code for it?
Thank you.

[kev1n]

I added some records to do some tests but apparently they reside only in the temporary table
because they don't exist in the original table from which I created the temporary one.
Is it correct? Should not the new records and their edits appear in the original table
based on what you wrote?
Thank you.

Yes, newly created records should be saved in the original table (the one you entered in the settings, Table Name field). The other temp. table is only used for Browse, has no impact on how/where the records are saved.

[kev1n]

And one last question:
is it possible to use the search field to search for multiple data or I need to write custom code for it?
Thank you.

It's better to create a new topic for that.