CODEWITHSUNDEEP
phpmysqlexport-to-excel
R9102
R9102

Reputation: 727

Before exporting i need to insert data from one table to another table-php

I have created temporary table -temporary_ticket

I want to insert data from main table to temporary table ticket

Before exporting data i need to insert data from main table to temporary table and then exclude unwanted columns

Here is my sql code.

$sql = "SELECT id,subject,created_by,type,status,cat_id,DATE_FORMAT(create_time, '%m/%d/%Y 00:00:00')as create_time ,DATE_FORMAT(update_time, '%m/%d/%Y 00:00:00')as update_time,priority,updated_by,assigned_to,ticket_type,agent_created,cust4 FROM  `$DB_TBLName` ORDER BY id ASC";

I tried 

Insert* INTO temporary_ticket  FROM ticket /* Drop the cloumns that are not needed */
ALTER TABLE temporary_ticket  DROP COLUMN guest_name,guest_email /* Get results and drop temp table */
SELECT * FROM temporary_ticket DROP TABLE temporary_ticket

but its not coming

Anything wrong in this query?

Here is my complete code

<?php
$DB_Server = "localhost"; //MySQL Server    
$DB_Username = "*****"; //MySQL Username     
$DB_Password = "******";             //MySQL Password     
$DB_DBName = "i******";         //MySQL Database Name  
$DB_TBLName = "wp3_wpsp_ticket"; //MySQL Table Name   
$filename = "Support Ticket_Export";         //File Name


$sql="insert into wp3_wpsp_temporary_ticket  
      SELECT *   FROM wp3_wpsp_ticket 
      commit;" 


$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());

//select database   
$Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());   
//execute query 
$result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());    
$file_ending = "xls";
//header info for browser
header("Content-Type: application/xls");    
header("Content-Disposition: attachment; filename=$filename.xls");  
header("Pragma: no-cache"); 
header("Expires: 0");


 $sep = "\t"; //tabbed character
 //start of printing column names as names of MySQL fields
 for ($i = 0; $i < mysql_num_fields($result); $i++) {
 echo mysql_field_name($result,$i) . "\t";
 }
 print("\n");   

 //end of printing column names  
 //start while loop to get data
 while($row = mysql_fetch_row($result))
 {
  $schema_insert = "";
  for($j=0; $j<mysql_num_fields($result);$j++)
    {
   if(!isset($row[$j]))
    $schema_insert .= "NULL".$sep;
    elseif ($row[$j] != "")
    $schema_insert .= "$row[$j]".$sep;
    else
    $schema_insert .= "".$sep;
    }
    $schema_insert = str_replace($sep."$", "", $schema_insert);
    $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
    $schema_insert .= "\t";
    print(trim($schema_insert));
    print "\n";
    }   
    ?>

Upvotes: 0

Views: 178

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133380

You should use insert select 

insert into temporary_ticket  
SELECT *   FROM ticket ;

and be sure your columns between the two table match for type and number (same sequence too)

Upvotes: 1

Shadow
Shadow

Reputation: 34254

In MySQL select ... into ... is not used for populating tables based another table, this statement is used for assigning values to variables or exporting data into external files.

You need to use insert ... select ... statement instead:

insert into temporary_ticket
select * from ticket

However, the series of statements that you described in your question can be simply replaced by a

select <list of fieldnames excluding guest_name,guest_email) from ticket

statement. No need for temporary table creation and all the other complicated stuff.

Upvotes: 1

Related Questions