Create MySQL temporary table and select from it within PHP

Question

I need an advice with a PHP/MySQL code I am writing. What I need to do is to create a temp table and then select from it. Problem is that the temp table only lasts during the query that created it so the second query in PHP is empty.

Here are both queries, I assume the solution would be to merge them into one singe query but so far nothing I tired works.

The queries below work in phpMyAdmin if executed one after another.

The temporary table query:

$sql_temp_table = "CREATE TEMPORARY TABLE IF NOT EXISTS 
                   tbl_temp (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY) AS 
                  (SELECT 
                        SUM(number_active_ads) as active_ads, 
                        MONTH(create_date) as month, 
                        YEAR(create_date) as year, 
                        dealer_id as dealer_id 
                  FROM tbl_active_ads
                  WHERE dealer_id = '".$rs->fields['id']."' 
                  GROUP BY year, month 
                  ORDER BY id)";
$rs_temp_table  =   $db -> Execute($sql_temp_table);

The second SELECT query:

$qry_chk_active_ads = "SELECT 
                            a.*, 
                            t.user_dealer_id, 
                            t.dealer_payment_for_month, 
                            t.dealer_payment_for_year
                       FROM tbl_temp as a
                       LEFT OUTER JOIN tbl_transaction as t
                       ON (a.dealer_id = t.user_dealer_id 
                           AND a.month = t.dealer_payment_for_month 
                           AND a.year = t.dealer_payment_for_year) 
                       ORDER BY id DESC 
                       LIMIT 3";
$rs_chk_active_ads  =   $db -> Execute($qry_chk_active_ads);

UPDATE: When I do this: var_dump($rs_temp_table); I get the following output from PHP:

object(ADORecordSet_empty)#6 (6) { ["dataProvider"]=> string(5) "empty" ["databaseType"]=> bool(false) ["EOF"]=> bool(true) ["_numOfRows"]=> int(0) ["fields"]=> bool(false) ["connection"]=> bool(false) }

Maybe this is explaining why the temp_table seems to be empty in PHP?

A temporary table lasts until the connection is closed. If either you or your database class is closing the connection after each query your temporary table will be lost. — user1864610
– user1864610, Commented Jun 7, 2015 at 9:47
@Hobo Sapiens, I am not closing the connection and the database class is definitely not doing it either. If what you say is correct, why the second query is empty in PHP but executes successfully in phpMyAdmin? — user3132858
– user3132858, Commented Jun 7, 2015 at 9:50
If you can run these queries successfully in PHPMyAdmin the you should be able to run them in your own PHP code. If you can't that suggests a problem with your PHP, but you haven't posted that so we can't help you. — user1864610
– user1864610, Commented Jun 7, 2015 at 10:01
Well, the code in PHP is pretty much those two queries. What I do after them is: var_dump($rs_chk_active_ads->fields['active_ads']);` And it is NULL, while in phpMYAdmin, values are existing. — user3132858
– user3132858, Commented Jun 7, 2015 at 10:08
Does it work when you execute the queries at the same time? So: $db->Execute($sql_temp_table . ';' . $qry_chk_active_ads); — Richard Deurwaarder
– Richard Deurwaarder, Commented Jun 7, 2015 at 10:16

Strawberry · Accepted Answer · 2015-06-07 11:06:08Z

3

Why not simply use...

SELECT a.*
     , t.user_dealer_id
     , t.dealer_payment_for_month
     , t.dealer_payment_for_year
  FROM 
     ( SELECT SUM(number_active_ads) active_ads
            , DATE_FORMAT(create_date,'%Y-%m') yearmonth
            , dealer_id 
         FROM tbl_active_ads
        WHERE dealer_id = '".$rs->fields['id']."' 
        GROUP 
           BY DATE_FORMAT(create_date,'%Y-%m') 
     ) a
  LEFT 
  JOIN tbl_transaction t
    ON a.dealer_id = t.user_dealer_id 
   AND a.yearmonth = MONTH(t.dealer_payment_for_month)
   AND a.yearmonth = YEAR(t.dealer_payment_for_year) 
 ORDER 
    BY id DESC 
 LIMIT 3;

answered Jun 7, 2015 at 11:06

Strawberry

34k14 gold badges43 silver badges58 bronze badges

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

1 Comment

user3132858 Over a year ago

Thank you, Strawberry! This is what I was trying to achieve as alternative. I modified your code a bit to output the desired results and all works now. It seems that there is some issue with ADOdb and the temp table I was trying to create, not sure why especially when similar code works on similar pages.

Collectives™ on Stack Overflow

Create MySQL temporary table and select from it within PHP

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related