Skip to main content

CakePHP: Export / Download Database Code

/**
   * Dumps the MySQL database that this controller's model is attached to.
   * This action will serve the sql file as a download so that the user can save the backup to their local computer.
   *
   * @param string $tables Comma separated list of tables you want to download, or '*' if you want to download them all.
   */
  public function db_mysql_dump($tables = '*') {

      $return = '';
      $modelName = $this->modelClass;

      $dataSource = $this->{$modelName}->getDataSource();
      $databaseName = $dataSource->getSchemaName();


     //  // Do a short header
      $return .= '-- Database: `' . $databaseName . '`' . "\n";
      $return .= '-- Generation time: ' . date('D jS M Y H:i:s') . "\n\n\n";


      if ($tables == '*') {
          $tables = array();
          $result = $this->{$modelName}->query('SHOW TABLES');
          foreach($result as $resultKey => $resultValue){
              $tables[] = current($resultValue['TABLE_NAMES']);
          }
      } else {
          $tables = is_array($tables) ? $tables : explode(',', $tables);
      }

      // Run through all the tables
      foreach ($tables as $table) {
          $tableData = $this->{$modelName}->query('SELECT * FROM ' . $table);

          $return .= 'DROP TABLE IF EXISTS ' . $table . ';';
          $createTableResult = $this->{$modelName}->query('SHOW CREATE TABLE ' . $table);
          $createTableEntry = current(current($createTableResult));
          $return .= "\n\n" . $createTableEntry['Create Table'] . ";\n\n";

          // Output the table data
          foreach($tableData as $tableDataIndex => $tableDataDetails) {

              $return .= 'INSERT INTO ' . $table . ' VALUES(';

              foreach($tableDataDetails[$table] as $dataKey => $dataValue) {

                  if(is_null($dataValue)){
                      $escapedDataValue = 'NULL';
                  }
                  else {
                      // Convert the encoding
                      $escapedDataValue = mb_convert_encoding( $dataValue, "UTF-8", "ISO-8859-1" );

                      // Escape any apostrophes using the datasource of the model.
                      $escapedDataValue = $this->{$modelName}->getDataSource()->value($escapedDataValue);
                  }

                  $tableDataDetails[$table][$dataKey] = $escapedDataValue;
              }
              $return .= implode(',', $tableDataDetails[$table]);

              $return .= ");\n";
          }

          $return .= "\n\n\n";
      }

      // Set the default file name
      $fileName = $databaseName . '-backup-' . date('Y-m-d_H-i-s') . '.sql';

      // Serve the file as a download      
      $this->response->body($return);
      $this->response->type('Content-Type: text/x-sql');
      $this->response->download($fileName);

      return $this->response;
  }

Post a Comment

Popular posts from this blog

CakeAdminLTE is based on AdminLTE Theme for CakePHP 2.4+

CakeAdminLTE v.1.0 CakeAdminLTE is based on Twitter Bootstrap 3.0+ and AdminLTE Theme for CakePHP 2.4+


AdminLTE Dashboard and Control Panel Template  Responsive admin template Free & open source Built with Bootstrap 3 Easy to customize Quick StartDownload the .zip fileExtract the files into your CakePHP folderTo enable your theme add the following to your "app/Controller/AppController.php" class:public$theme="CakeAdminLTE"; IMPORTANT: If you would like to bake your app, please make sure you have enabled the theme before baking. DOWNLOAD: CakeAdminLTE

CakePHP: COUNT data and GROUP BY date

Goal: Count Tip Offs created per day for a month to use for graph
Problem: created field name is in datetime format: Y-m-d H:i:s
Solution: format SQL Query date: DATE_FORMAT(TipOff.created, '%Y-%m-%d')





Inside the function of controller
<?php$tipOffsMonthly=$this->TipOff->find('all', array('conditions'=>array('AND'=>array('TipOff.electric_cooperatives_id'=>AuthComponent::User('electric_cooperatives_id'), 'TipOff.created BETWEEN ? AND ?'=>array($first_day, $last_day))), 'fields'=>array("COUNT('created') as per_day", 'created'), 'group'=>array("DATE_FORMAT(TipOff.created, '%Y-%m-%d')") ));?>

CakePHP: Calling function from other controller

Import Controller of the function you want to use.

Once you imported  the controller you can call any function of this controller.

<?php//Import controller  App::import('Controller','SmsOutgoings');class ReportsController extends AppController {publicfunctionadd(){$message="Notification: New report submitted!";//Instantiation$SmsOutgoings=new SmsOutgoingsController;//Call a method from SmsOutgoingsControllerwith parameter$SmsOutgoings->notify_user($user_id,$message);}}?> Other way is by using the requestAction() function
<?phpclass ReportsController extends AppController {publicfunctionadd(){$message="Notification: New report submitted!";//Call a method from SmsOutgoingsController with parameter$this->requestAction('/sms_outgoings/notify_user',array($user_id,$message))