PDO MySQL Can not insert query results into temp table
<p>Why can I not insert query results into temp table, but if I remove TEMPORARY from CREATE TEMPORARY TABLE statement and create an actual table my query runs perfectly.</p>
<p>Logic would leave me to believe that the db session is being lost and temp table is being deleted, but when I test various location in the script the temp table appears to exist and for whatever reason, the data is not being inserted into temp table, but it will insert into normal table.</p>
<p>Create Table Query:</p>
<pre><code> $ttbldrop = $db-&gt;prepare(&quot;DROP TABLE IF EXISTS z123&quot;);
    $ttbldrop-&gt;execute();


     $stmt = $db-&gt;prepare(&quot;CREATE TEMPORARY TABLE z123 (zcity varchar(32), zstate varchar(32), zip_code varchar(5), latitude DECIMAL(9,6), longitude DECIMAL(9,6), miles DECIMAL(4,1))&quot;);
     $temptbl = $stmt-&gt;execute();

    if(!$temptbl) {
        echo &quot;Temp Table Not Created&quot;;
        die;
    }else{
        //echo &quot;Temp Table Created Successfully&quot;;
</code></pre>
<p>Create Function:</p>
<pre><code>  function radiusContent() {
        include 'conn.php';

            global $latN;
            global $latS;
            global $lonE;
            global $lonW;
            global $lat1;
            global $lon1;
            
            $sql = $db-&gt;prepare(&quot;SELECT * FROM tbl_zipcode WHERE (latitude &lt;= $latN AND latitude &gt;= $latS AND longitude &lt;= $lonE AND longitude &gt;= $lonW) AND (latitude != $lat1 AND longitude != $lon1) AND city != '' ORDER BY state, city, latitude, longitude&quot;);
            $results = $sql-&gt;execute();
                if(!$results) {
                    echo &quot;Database Query 2 Error \n&quot;; 
                    die; 
                }
            $rs = $sql-&gt;fetchAll();
            return $rs; 
            
        }
         
</code></pre>
<p>Run Function &amp; Foreach Loop:</p>
<pre><code>$data = radiusContent();
            if(!$data){
                echo &quot;No Additional Zip Codes Found Within Search Radius - Increase Radius Search Distance \n&quot;;
                die;
            }else {   
                     
                 
                foreach($data as $row) {

                $truedistance = acos(sin(deg2rad($lat1)) * sin(deg2rad($row['latitude'])) + cos(deg2rad($lat1)) * cos(deg2rad($row['latitude'])) * cos(deg2rad($row['longitude']) - deg2rad($lon1))) * $r;

                $zcity = $row['city'];  
                $zstate = $row['state_abbrv'];
                $zip_code = $row['zip_code'];
                $latitude = $row['latitude'];
                $longitude = $row['longitude'];
                $miles = $truedistance;

            
</code></pre>
<p>Insert Into Temp Table:</p>
<pre><code>  if($miles &lt;= $distance) {
      $insert = $db-&gt;prepare(&quot;INSERT INTO z123 (zcity, zstate, zip_code, latitude, longitude, miles) 
                             VALUES ('$zcity', '$zstate', '$zip_code', '$latitude', '$longitude', '$miles')&quot;); 
       $result = $insert-&gt;execute();
                       
       if(!$result){
       echo &quot;Data Insert Failed&quot;;
        }
                                   
     }
 }
     
                                
</code></pre>
<p>(FYI above PHP code is all together in line by line sequential order in script)</p>
<p>Thanks</p>
<p><br><br>
NOTE: To clarify any confusion:</p>
<pre><code>$result = $insert-&gt;execute(); 

if(!$result) { 
   echo &quot;Data Insert Failed&quot;; 
} 
</code></pre>
<p>If there is any error in my sql statement whether it be table name does not exist, column name does not exist, bad mysql syntax, etc., etc. I will get &quot;Data Insert Failed&quot; message.</p>
<p>So to test,</p>
<p>If I run code as above using CREATE TEMPORARY TABLE I get no error (just no data is inserted into table).</p>
<p>test 1: if I intentionally change temp table name to a nonexistent table name I get the above error message &quot;Data Insert Failed&quot;, which should prove table existed in original code.</p>
<p>test 2: if I use correct temp table name in the INSERT INTO statement and delete the CREATE TEMPARARY TABLE statement in its entirety (including related code), I now get the above error message &quot;Data Insert Failed&quot;, which again should prove table existed in original code.</p>
<br>
<br>
<p>Update: SOLUTION -FIX</p>
<p>I was using &quot;include 'conn.php';&quot; in each function instead of using &quot;global $db&quot; for my connection ($db is defined in my conn.php file). While this was not CLOSING any existing sessions it was opening additional (multiple) sessions for each function in my script.</p>
<p>Example Of Original Code Causing Multiple Connections In Each Function:</p>
<pre><code>&lt;?php
   include 'conn.php';

   function myFunctionName(){
     include 'conn.php';
      (my funciton code here)
    }
</code></pre>
<p>Solution - Correct Way To Connect To Database Within Function:</p>
<pre><code>&lt;?php
   include 'conn.php';

   function myFunctionName(){
     global $db;
      (my funciton code here)
    }
</code></pre>
<p>Note: &quot;global $db;&quot; is defined within &quot;conn.php&quot; file.</p>