CODEWITHSUNDEEP
phpmysqlcreate-tabletemp-tables
alex
alex

Reputation: 113

mysql-php, error on create temporary table

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.

Upvotes: 1

Views: 1104

Answers (1)

jeroen
jeroen

Reputation: 91734

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.

Upvotes: 2

Related Questions