Error when using temp table in browse form

[oli]

Hello,
I added an Iframe with a specific Form to list all Items that are assigned to a project.
The data will be collected by selecting the data from different tables and writing the result in a temp table by following code in the BEFORE BROWSE custom code of the Items Form:

Code: Select all

// Temp Table
$create = "CREATE TABLE #TABLE_ID# ";

// get items from assigned project
$q = 'SELECT pro_items FROM project WHERE project_id = "' . "#tas_project#" . '";';
$x = nuRunQuery($q);
$y = db_fetch_object($x);
$items = $y->pro_items;


$items = str_replace(']', '',str_replace('[', '', $items));     // remove [ and ] from the result 

// create temp TABLE
if ($items != ""){
    $qry = 'SELECT * FROM item WHERE item_id IN (' . $items . ')';  // get data
    nuRunQuery($create.$qry);                                       // create temp table
}

It works great when there are items assigned to a project.
But if there are no results I got following error in the debug log because the temp table will not be created:

Code: Select all

1st Debug entry:
__________________________________________

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

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

SELECT COUNT(*) FROM (SELECT item_id,ite_label,ite_status,ite_manufacturer,ite_model,ite_saler
 FROM ___nu1614ec21204891___ END
WHERE 1) nuTCount

__________________________________________
2nd debug entry:
__________________________________________

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

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

SELECT item_id,ite_label,ite_status,ite_manufacturer,ite_model,ite_saler
 FROM ___nu1614ec21204891___ END
WHERE 1 LIMIT 0, 7

I tried to avoid the error message by using a condition in the SQL statement (Form - Browse) but the error still occurs:

IF (EXISTS (#TABLE_ID#))
BEGIN
SELECT * FROM #TABLE_ID#
END

Are there any solutions how to avoid the error?

BR, Oli

[kev1n]

The Temporary Table must be created under all circumstances. Maybe you could select a "dummy" row when $items is blank?

Code: Select all

// create temp TABLE
if ($items != ""){
    $qry = 'SELECT * FROM item WHERE item_id IN (' . $items . ')';  // get data
    
} else {

	$qry = "SELECT NULL as 'item_id',NULL as 'ite_label',NULL as 'ite_status',NULL as 'ite_manufacturer',NULL as 'ite_model',NULL as 'ite_saler'";
}

nuRunQuery($create.$qry);                                       // create temp table

[oli]

Thanks kev1n ... great support again !!

solved