Print data from mash subform

[massiws]

I need a Print button on a mashed subform to rapidly get a list of all the item displayed in sub-browse-form, but the standard Print button don't work.
Is this true?

img.png

The error message is:

SQL
SELECT req_number, app_date, TIME_FORMAT(app_hour,'%H:%i'), CONCAT(cli_lname, ' ', cli_fname), req_destname, req_destaddr, aps_name, tecnici FROM ___nu15076dcfeaeaa0___ WHERE ( ( (req_number LIKE "%MARCO%") OR (DATE_FORMAT(app_date,"%d-%m-%Y") LIKE "%MARCO%") OR (TIME_FORMAT(app_hour,'%H:%i') LIKE "%MARCO%") OR (CONCAT(cli_lname, ' ', cli_fname) LIKE "%MARCO%") OR (req_destname LIKE "%MARCO%") OR (req_destaddr LIKE "%MARCO%") OR (aps_name LIKE "%MARCO%") OR (tecnici LIKE "%MARCO%") )) ORDER BY app_date DESC, app_hour DESC

Transaction
No Transaction.

Any suggestion?

[admin]

massiws,

I tried it on a mashed Browse I have and it worked ok.

Have you tried pasting that sql into phpmyadmin?

Steven

[massiws]

Steven,

sorry for delay, but I've spent all the day to search a solution.
Pasting the query in phpmyadmin I get error: #1146 - Table 'taichiin_dbbfd.___nu1507884a59df68___' doesn't exist, hence the temporary table is not created.

After spending a couple of hours, I build a new form with the same data and (surprise!) I get the same error, so the problem is not related to the mashed Browse subform.

The form is build with this custom code in BeforeBrowse:

Code: Select all

// Build a temp table
$sql  = <<<SQL
CREATE TABLE #browseTable#
 SELECT * FROM appointment
 INNER JOIN (request, appointment_status, client)
 ON (app_req_id = req_id AND app_aps_id = aps_id AND req_cli_id = cli_id)
 WHERE app_aps_id <> '1504808820c931'
SQL;
nuDebug('1: '.$sql);
nuRunQuery($sql);

// Add a column to temp table
$sql = "ALTER TABLE #browseTable# ADD tecnici VARCHAR(150) NOT NULL ";
nuDebug('2: '.$sql);
nuRunQuery($sql);


$sql = "SELECT * FROM #browseTable# ";
nuDebug('3: '.$sql);
$rs = nuRunQuery($sql);

// For each appointment get related technician
while ($row = db_fetch_object($rs)) {
    $appID = $row->app_id;               // get appointment ID
    $tecnici = getTechnician($appID);    // get technician names from 'appointment_technician' table
    $tecnici = $tecnici[0];              // get only names
    
    // Insert found names in temp table
    $sql = "UPDATE #browseTable# SET tecnici = '".$tecnici."' WHERE app_id = '".$appID."'";
    nuDebug('4: '.$sql);
    nuRunQuery($sql);
}

With nuDebug() function, I captured all the query send to database and paste them in phpmymdmin and (double surprise!) all work fine!
Step by step the code produce right results, but seems that the query are not send to mysql.
I've tried to write the first SQL sentence in a variable, change double/single quotes, but result remain the same.

Any suggestion will be appreciated.

[massiws]

Steven,

I think there is a bug.
The print button don't work if the browse form is build with custom code in BeforeBrowse and #browseTable# hashvariable in SQL.
I tried to work on a simple table and this is the result:

imgok.png

imgko.png

It seems that nuBuilder don't find the temporary table used to build the browse form, maybe it's deleted before print data.

[admin]

massiws,

I think I see your problem.

In Before Browse you need to CREATE the #browseTable# eg.

Code: Select all

nuRunQuery("CREATE TABLE #browseTable# SELECT * FROM customer");

then

Use it in the SQL eg.

Code: Select all

SELECT * FROM #browseTable#

Steven

[massiws]

Steven,
sorry but I made an error uploading the image here.

This is in SQL field:

Code: Select all

SELECT * FROM #browseTable# ORDER BY req_qdate DESC

This is in Before Browse field:

Code: Select all

$sql = "CREATE TABLE #browseTable# SELECT * FROM request ";
nuRunQuery($sql);

This is the form:

img.png

This is the error after print button:

error.png

I've tried with other forms but the result is the same.

It seems that nuBuilder don't find the temp table used to build the form, so it can print nothing.

Next week I have some free days: I will look the code...

[admin]

massiws,

The error that appears in your last post implies a php syntax error in browseprint.php on line 123

if you put

nuDebug($BP_b4);

on line 121 we might see what is wrong with the php that is being created.

Steven

[massiws]

Steven,

this is the nuDebug($BP_b4)

Code: Select all

$sql = <<<SQL CREATE TABLE ___nu15087b2f433462___ SELECT * FROM request SQL; nuRunQuery($sql);

The code in BeforeBrowse is:

Code: Select all

$sql = <<<SQL
CREATE TABLE #browseTable#
 SELECT * FROM request
SQL;
nuRunQuery($sql);

End identifier in heredoc seems not valutated.

I rewrite the code in this way:

Code: Select all

$sql = "CREATE TABLE #browseTable# SELECT * FROM request ";
nuRunQuery($sql);

and now print button works, but this is on a simple form: the problem still remain in a mash subform.


My mash subform has this in SQL field:

Code: Select all

SELECT * FROM #browseTable# 
ORDER BY app_date DESC, app_hour DESC

In Before Browse I have this code:

Code: Select all

// Create a temp table with all appointments NOT-CLOSED and add a column with technician names
$sql  = "CREATE TABLE #browseTable#";
$sql .= " SELECT * FROM appointment INNER JOIN (request, appointment_status, client)";
$sql .= " ON (app_req_id = req_id AND app_aps_id = aps_id AND req_cli_id = cli_id)";
$sql .= " WHERE app_aps_id <> '1504808820c931' AND app_aps_id <> '1504808787a1a9'";
nuDebug('run1-> '.$sql);
nuRunQuery($sql);

// Add a column to temp table
$sql = "ALTER TABLE #browseTable# ADD tecnici VARCHAR(250) NOT NULL";
nuDebug('run2-> '.$sql);
nuRunQuery($sql);

// Work on temp table
$sql = "SELECT * FROM #browseTable#";
nuDebug('run3-> '.$sql);
$rs = nuRunQuery($sql);

// get technicians related to each appointment
while ($row = db_fetch_object($rs)) {
    $appID = $row->app_id;               // get appointment ID
    $tecnici = getTechnician($appID);    // get technician from DB
    $tecnici = $tecnici[0];              // get only names
    
    // Update temp table with technician names
    $sql = "UPDATE #browseTable# SET tecnici = '".$tecnici."' WHERE app_id = '".$appID."'";
    nuDebug('run4-> '.$sql);
    nuRunQuery($sql);
}

This is the subform:

mashSubform.png

Now, if I try to print data I get this error:

error.png

If I paste the SQL in phpmyadmin I get:

Code: Select all

#1146 - Table '<dbname>.___nu15087c0ebcdca7___' doesn't exist

If I paste in phpmyadmin all the query captured by nuDebug() functions all work fine: temp table is created and updated correctly:

Code: Select all

(nuBuilder Before Browse) of sfAppXTecnico : run1-> CREATE TABLE ___nu15087f6c1311ca___ SELECT * FROM appointment INNER JOIN (request, appointment_status, client) ON (app_req_id = req_id AND app_aps_id = aps_id AND req_cli_id = cli_id) WHERE app_aps_id <> '1504808820c931' AND app_aps_id <> '1504808787a1a9'

(nuBuilder Before Browse) of sfAppXTecnico : run2-> ALTER TABLE ___nu15087f6c1311ca___ ADD tecnici VARCHAR(250) NOT NULL

Tell me if I can try other solutions.

[admin]

massiws,

I don't know.

I'm able to do it..

Untitled.png

I'll keep thinking.

Steven

[massiws]

Steven,

some doubts:
- the problem could be that in mashed form edit and browse form aren't the same?
- after browse form is open, before press print button, the code in CustomCode > BeforeBrowse is executed, so the #browseTable# table ___nu* should have been created, but I can't find it in phpmyadmin: it's normal??
- does nuBulder create the ___nu* table as TEMPORARY tables in MySQL?

Sorry for all questions, but I still haven't found a solution.