How to query temporary table with PDO in PHP project?

Time:2021-2-19

SQL1

CREATE TEMPORARY TABLE r_js SELECT
                    b.agenname30 AS agenname,
                    fhsname30 AS fhsname,
                    b.loc_id,
                    b.locname50 AS locname,
                    a.customter,
                    SPACE( 100 ) AS js_desc,
                    SPACE( 20 ) AS js_date1,
                    SPACE( 20 ) AS js_date2,
                    SPACE( 20 ) AS js_amt,
                    SPACE( 20 ) AS js_rate 
                FROM
                    v_saleonline a,
                    v_locat b 
                WHERE
                    a.loc_id = b.loc_id 
                    AND a.inv_date BETWEEN '2020-08-01' 
                    AND '2020-08-31';

SQL2

UPDATE r_js a,
activitygen c,
activityitem d,
activityloc e 
SET a.js_desc = CONCAT( c.id, c.act_desc ),
a.js_date1 = c.act_date1,
a.js_date2 = c.act_date2,
a.js_amt = d.jsprice 
WHERE
    a.inv_date BETWEEN c.act_date1 
    AND c.act_date2 
    AND a.goodssn = d.goodssn 
    AND a.loc_id = e.loc_id 
    AND c.id = d.id 
    AND c.id = e.id;

SQL3

UPDATE r_js a,
activitysettlement c,
itemgen d,
season e 
SET a.js_desc = CONCAT( c.yea_id, e.name20 ),
a.js_date1 = c.sett_date1,
a.js_date2 = c.sett_date2,
a.js_amt = FLOOR( d.price * c.sett_rate ),
a.js_rate = CONCAT( d.price, '  X  ', c.sett_rate ) 
WHERE
    a.inv_date BETWEEN c.sett_date1 
    AND c.sett_date2 
    AND a.goodssn = d.item 
    AND c.yea_id = d.yea_id 
    AND c.sea_id = d.sea_id 
    AND d.sea_id = e.sea_id 
    AND TRIM( a.js_desc ) = '';

SQL4

UPDATE r_js a,
itemgen d 
SET a.js_desc = 'selling price x 0.894 ',
a.js_amt = FLOOR( a.salcost / 0.894 ) 
WHERE
    TRIM( a.js_desc ) = '' 
    AND a.goodssn = d.item;

SQL5

SELECT
    * 
FROM
    r_js 
ORDER BY
    statusdesc,
    agenname,
    js_desc,
    inv_date,
    loc_id;

The results of the above SQL queries can be found directly in the database, but in PHP projects, each SQL is executed once with PDO. In fact, only the first SQL can be executed successfully, and the last four failed. Then I tried to execute only the first SQL, and then execute the fifth SQL, but there was still no result. Why?

In PHP project, can you generate temporary table, update and query the data of temporary table?

This work adoptsCC agreementReprint must indicate the author and the link of this article