Backup of the database in CodeIgniter (includes CSV)

- Andrés Cruz

En español
Backup of the database in CodeIgniter (includes CSV)

Backing up the project database is essential to ensure data integrity and availability in case of loss or damage. When it comes to web applications, the database is the core and the most important element of the system, since it is where the critical data and information of the users and the project in general are stored and protected. An unplanned loss of data can result in the loss of the entire application data structure, which can be disastrous for both the business or end user, and the users of the system. Therefore, it is critical to back up your web application database on a regular basis to prevent data loss and ensure business continuity. Additionally, it is important to verify and validate backups to ensure that they are effective and to avoid unpleasant surprises in the event of a system failure.

In CodeIgniter, as the PHP framework that it is, we can perform most of the operations we need in the most common web applications; as it is to fully export your database (or part of it), we can do it easily and organizedly in CodeIgniter; for that we are going to rely on the official documentation to do this task.

Database Utility Class.

The first thing we need is a utility class that does the backup for us:

$this->load->dbutil();

As you can see, it's that simple, we already have a functionality that is in charge of making the backup of our application's database, of the database that we configure in the database file of the conf folder; now, we make the copy of our database:

$backup = $this->dbutil->backup();

Once we have the backup, the raw file as such is of little use to us; we have to write it somewhere we write this data in something understandable by us, and we save it in a file:

$this->load->helper('file');
write_file('/path/to/mybackup.gz', $backup);

As you can see, we use CodeIgniter's helper file, which accepts the block of data we generated earlier, and writes it to some file in some location with some extension.

Now we use the helper to download files directly in our browser and force the download:

$this->load->helper('download');
force_download('mybackup.gz', $backup);

As you can see, there is also a helper for this purpose; with this helper, our CodeIgniter gives the file to be downloaded from our database and with this the process is finished.

Backup a particular table or query from the database: Export it to Excel/CSV

We can also do a partial export of the database, of a table or of a query in general; for that we have to build the query and later; for example, a table called users:

$this->load->dbutil();
$query = $this->db->query("SELECT * FROM usuarios");
$config = array (
        'root'          => 'root',
        'element'       => 'element',
        'newline'       => "\n",
        'tab'           => "\t"
);
echo $this->dbutil->xml_from_result($query, $config);

As you can see, we simply need to create our query to load the library for the export and then use the csv_from_result method to generate said file which receives a second optional parameter to indicate the format of the data.

Andrés Cruz

Desarrollo con Laravel, Django, Flask, CodeIgniter, HTML5, CSS3, MySQL, JavaScript, Vue, Android, iOS, Flutter

Andrés Cruz en Udemy

Acepto recibir anuncios de interes sobre este Blog.