Pages

Friday, March 27, 2020

Get CREATE TABLE for all tables using CodeIgniter


I have an app on my phone that I use as a pocket reference when I need field names, relationships in GroceryCrud. It has an Import from SQL feature.
I ran my code. Copied the output to keep.google.com and from there pasted the SQL in the mobile app.
This was a quick thing I needed so I just placed it in a controller.
Code follows:

 
public function tableSQLCreate() {
  try {
   $pdo = $this->db;

  }
  catch(PDOException $e) {
   die("Could not connect to the database\n");
  }



  echo '<#pre>';
  try{
   $stmt1 = $pdo->query('SHOW TABLES' );
    try {
     foreach($stmt1->result() as $result) {

       foreach ($result as $key => $value) {
        $stmt2 = $pdo->query("SHOW CREATE TABLE `". $value ."`" );
        $table = $stmt2->result();
        foreach ($table[0] as $key => $value) {
         if ($key=="Create Table") {
         echo "$value;\n\n";
         }
        }
       }

     }
    } catch(PDOException $e) {
      die("Could not get result nested");
    }


  }
  catch(PDOException $e) {
   die("Could not get result");
  }
  
  echo '<#/pre>';

 }
Be sure to remove the # symbol from the pre tags. It was a dirty way around the Blogger formatter.


It outputs

CREATE TABLE `login_attempts` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `ip_address` varchar(15) NOT NULL,
  `login` varchar(100) NOT NULL,
  `time` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


No comments:

Post a Comment