Nested or chain queries for reports

[JEDV]

I want to do a complex query/report. I've read the nubuilder will not do nested sql select queries. Can I do multiple temporary tables in order to get around that limitation? If so, how do I do this? I keep getting error messages, that the temporary tables do not exist. See process below. If I cannot use temp tables, is there some other solution? Here's the process I envision.

Step 1. Create a first temporary table using a select query from a single existing table based on several criteria in the report form. [Doing this separately speeds the next three steps.]

Step 2. Create a second temporary table using a select query from temporary table 1. [Using a a preg_replace function on one of the columns. The aim is to strip out all the lower case letters from the field.]

Step 3: Create a third temporary table using a select query from temporary table 2. [Concat 2 columns including the one created in step 2.]

Step 4: Create a fourth table (#TABLE_ID#) for the report using a query from temporary table 3. [GROUP_CONCAT on table 3]

[kev1n]

How about creating a view that contains subqueries?

SELECT
....
FROM
(
SELECT ... FROM abc

) AS sub1

[admin]

JDEV,

You wrote...

I want to do a complex query/report

You can make use a Procedure that you can use to construct any complex query - including a nested query.

All you need to do is save the query's result to #TABLE_ID# (this is what nuBuilder will use to create the Report.)

eg.

Code: Select all


nuRunQuery("CREATE TABLE #TABLE_ID# SELECT user_email, user_url FROM wp_users");

$s = "UPDATE #TABLE_ID# SET user_url = SUBSTRING(user_email,INSTR(user_email, '@') + 1)";

nuRunQuery($s);



Steven

[JEDV]

Thanks for the replies.
Kevin: I could find not documentation for creating views in the nubuilder wiki, user guide or forums. So after much experimentation and web searching, I was able to create a view to accomplish the first step. To save time for other newbies like myself here's the php code: $a = "SELECT * FROM databasename WHERE datefield BETWEEN '#reportformdatecriteria1#' and '#reportformdatecriteria2#'; nuRunQuery ("Create VIEW view1 AS $a");
On to step two of my original post; I want to strip all the lower case letters from a field in each record which has one or more full names. Example. John Doe, Alice Doe to become JD,AD
Using mysql string functions to do this become a very complex long command at least as I tried it. My host uses MySQL 5.6 so the new REGEXP_REPLACE is out for me.
I hope to use the php regular function preg_replace to do this. I tried this: $b = "SELECT column1, column2, preg_replace(/a-z\s+/,'',column3) as Initials FROM view1"; nuRunQuery ("Create VIEW view2 AS $b"); Trying this gives an unknown function error refering to preg_replace. So is there a way to using preg_replace or is there a simple mysql process?

Admin: I'll try the idea of nested queries instead of creating multiple views and the dropping them. Can I presume that I can run multiple update queries on #TABLE_ID# each time updating the table until I get what I want for the report?

Can anyone point the way to answer on the question above on how to use the PHP preg_replace or a mysql function? Remember REXEXP_REPLACE is not an option for me.

[admin]

JEDV,

Can I presume that I can run multiple update queries on #TABLE_ID# each time updating the table until I get what I want for the report?

That's correct (the table #TABLE_ID# will be deleted once the Report has run).


Steven

[JEDV]

Success for my step two issue. I found a way around the lack of regex_replace on the Mysql version on the web host server I use. I found a user defined MYSql function regex_replace. Source: https://techras.wordpress.com/2011/06/0 ... for-mysql/ For newbies, like me, to install this using phpMyAdmin I copied and then pasted the source code to the sql tab on the database, hit go and it created the regex_replace function. One issue: to make this case sensitive I had to change the parameters "pattern" and "original" to binary. To do this, again using phpMyAdmin click on the function under the database name which opens an edit routine. At the parameters list on the options dropdown I selected binary for both pattern and original and then hit go to save the changes. At this point I anticipate getting whole process working shortly. I'll document when I do.

[JEDV]

Success for creating the desired report! Thanks again to Kevin and Steven for their help. Here's the goal and then how I reached it. Maybe this will be helpful to another newbie developing a report that requires some complex string functions to prepare for the report. I have a database with a table that several columns, but the relavant ones are: visitee, datevisited, visitor. I want a report in which each line lists a visitee followed by a list of dates he/she was visited along with visitor(s) initials for each visit. Report row example: John Doe 1/8/18:VVW,MVW-1/26/18:HH-1/14/18:TS

I ran into a problem trying Steven's suggestion which was to create #TABLE_ID# with select statement and then alter/update it successively until I had what I wanted for the report. I could not figure out how to run the GROUP_CONCAT function doing an update to #TABLE_ID#. I think I would have the same issue using views. So I went in the direction of creating another table (temp_report), running several updates on it, and finally creating the table #TABLE_ID# for the report using the new table. There may be better ways of doing this, but it works and I learned a lot about nubuilder doing this.

$a = "DROP TABLE IF EXISTS temp_report"; // Note1
nuRunQuery ($a);
$b = "SELECT * FROM databasetable WHERE [several criteria based on form object values]";
nuRunQuery ("CREATE TABLE temp_report AS $b");
$c = "ALTER TABLE temp_report ADD visitorinitials varchar(25), ADD datestromg varchar(25), ADD dateinitials varchar(25)";
nuRunQuery($c);
$d = "UPDATE temp_report SET visitorinitials = regex_replace('[^A-Z\,]','',visitor)"; //Note 2
nuRunQuery($d);
$e = "UPDATE temp_report SET datestring = Date_FORMAT(datevisited,'%c/%e/%y')"; //Note 3
nuRunQuery($e);
$f = "UPDATE temp_report SET dateinitials = CONCAT_WS(':',datestring,visitorinitials)";
nuRunQuery($f);
$g = "Create TABLE #TABLE_ID# AS SELECT visitee, GROUP_CONCAT(dateinitials SEPARATOR '-') as groupeddateinitials FROM temp_report GROUP BY visitee";
nuRunQuery($g);

Note 1: Remove the table so it can be rebuilt for the report. Since I was creating and then updating a table, during development I could easily test each step of the process. (Comment out those steps after the step being tested, run the report, ignore report, but go to the database directly (Builder -> Database) and check the content of the temp_report table after that step.)
Note 2: See previous entry for note on regex_replace. Here it strips out everything but Uppercase letters and commas from a list of names.
Note 3 Convert the date to a sring with a short form (example: 1/1/19) to facilitate the next step.

[admin]

JEDV,

Well done!

Steven