mysql-php, error on create temporary table

Question

I've finally gotten my queries ready to insert into code but now I'm getting an error when running the whole query. I believe it has to do with the drop table function. I originally had them inline and then read that I should remove it and add at the beginning of the query like so:

    $query = $this->db->query("DROP TABLE IF EXISTS resultx;");
    $query = $this->db->query("DROP TABLE IF EXISTS resulty;");

$query = $this->db->query("
 CREATE TEMPORARY TABLE resultx AS
 select *, CONCAT(Credit,'_',OrderStat) as consol from (..........

I am creating two temp tables and then joining them in the last query. I am not sure how to put that second DROP temp table back into the full query or if that's even the right way to go. The error that I'm getting is:

A Database Error Occurred
Error Number: 1064
You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right syntax to use 
near 'CREATE TEMPORARY TABLE resulty AS select packetDeet,Sales,SaleDate, UserID,Lead' at line 15

Query:

CREATE TEMPORARY TABLE resultx AS 
    select 
        *,
        CONCAT(Credit,'_',OrderStat) as consol 
    FROM 
        ( select 
              packetDetailsId, GROUP_CONCAT(Credit) AS Credit,
              GROUP_CONCAT(AccountNum) AS AccountNum,
              GROUP_CONCAT(OrderStat) AS OrderStat 
          FROM 
             ( SELECT 
                   pd_extrafields.packetDetailsId, 
                   CASE WHEN 
                       pd_extrafields.ex_title LIKE ('%Credit%') 
                       THEN pd_extrafields.ex_value 
                       ELSE NULL 
                   END as Credit, 
                  CASE WHEN 
                       pd_extrafields.ex_title LIKE ('%Account%') 
                       THEN pd_extrafields.ex_value 
                       ELSE NULL 
                  END as AccountNum, 
                 CASE WHEN 
                       pd_extrafields.ex_title LIKE ('%Existing%') 
                       THEN pd_extrafields.ex_value 
                       ELSE NULL 
                 END as OrderStat 
               FROM pd_extrafields 
             ) AS myalias 
          GROUP BY packetDetailsId 
        )as TempTab; 
CREATE TEMPORARY TABLE resulty AS select packetDeet,Sales,SaleDate, .........

Please let me know if this makes sense or I need to update question with more information.

Thank you for cleaning up UnholyRanger and for the advice Barmar, I will look into that suggestion. — alex
– alex, Commented Mar 13, 2013 at 20:41

jeroen · Accepted Answer · 2013-03-13 21:14:01Z

2

If you are trying to execute both queries in one call to $this->db->query() the problem is probably that your database library does not permit multiple queries.

To see if that is the problem, you should split them up in two separate queries.

answered Mar 13, 2013 at 21:14

jeroen

91.8k22 gold badges118 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql-php, error on create temporary table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related