CODEWITHSUNDEEP
phpmysql
CyberFla
CyberFla

Reputation: 71

Drop columns from a table

I have a .csv file data being imported into mysql table. What I like to do everytime I run the code I would like to DROP all of the column and the data and REPLACE it with the updated .csv content. 

   $affectedRows = $pdo->exec("
     //HERE I do need additional code to drop column data from $databasetable similar to following
  /*------------------------------------------*/
    DROP TABLE IF EXISTS '$databasetable'

    CREATE TABLE IF NOT EXISTS `$databasetable` (
          Column names1
          Column name 2
          .
     ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  /*------------------------------------------*/


     LOAD DATA LOCAL INFILE ".$pdo->quote($csvfile)." INTO TABLE `$databasetable`
     FIELDS TERMINATED BY ".$pdo->quote($fieldseparator)."
     LINES TERMINATED BY ".$pdo->quote($lineseparator));

Upvotes: 0

Views: 336

Answers (2)

CyberFla
CyberFla

Reputation: 71

 $affectedRows = $pdo->exec("
 //HERE I do need additional code to drop column data from $databasetable similar to following
 /*------------------------------------------*/
/* I REMOVED THE SINGLE QUOTESFROM $databasetable and I was able to drop the table. Once the table was dropped then I was able to create the $databasetable which solved the problem. 
DROP TABLE IF EXISTS $databasetable;
CREATE TABLE IF NOT EXISTS `$databasetable` (
      Column names1
      Column name 2
      .
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*------------------------------------------*/


 LOAD DATA LOCAL INFILE ".$pdo->quote($csvfile)." INTO TABLE `$databasetable`
 FIELDS TERMINATED BY ".$pdo->quote($fieldseparator)."
 LINES TERMINATED BY ".$pdo->quote($lineseparator));

Upvotes: 0

Poiz
Poiz

Reputation: 7617

I think what you want to do is TRUNCATE the Table in Question. That is to remove all the Data inside the Table but not Delete the Table itself, right? So, here is 1 Tip:

<?php 
    //FIRST TRUNCATE (REMOVE OLD DATA IN THE TABLE $databasetable
    $stmt      = $db->prepare("TRUNCATE TABLE {$databasetable}");
    $tblEmpty  = $stmt->execute();

    //NOW, ONCE THE TABLE'S BEEN TRUNCATED; YOU MAY CONTINUE WITH THE NEW "INSERTS"...
    if($tblEmpty){
        // INSERT THE DATA FROM THE CSV...
    }

    //...
    //...
    // MORE CODE HERE...

Upvotes: 1

Related Questions