DEV Community

Cover image for Working with multi-queries in CodeIgniter
Prahlad Yeri
Prahlad Yeri

Posted on • Originally published at prahladyeri.github.io

Working with multi-queries in CodeIgniter

Running multi-queries (a bunch of text containing arbitrary DML/DDL statements) is highly unreliable and not an exact science in CodeIgniter or even PHP for that matter. The Internet is filled with posts like this, this, and this but you can't depend on these solutions in most situations due to the difference between how each database driver handles it.

Most of the query() or exec() statements often don't run at all due to the batch structure (begin and commit trans) not handled properly as per that driver's liking. It may also happen that due to one error in a single SQL statement, the whole text is ignored and yet, there is no error prompt, the folks will think that multi-query executed successfully when, in fact, it didn't. Hence, it is often so tempting to do something like this in CodeIgniter but doesn't always work (especially with SQLITE databases):

$sql = file_get_contents(APPPATH . '/core/init.sql');
$this->db->query($sql);
Enter fullscreen mode Exit fullscreen mode

The only approach that is guaranteed to work reliably here is to break your multi query into individual SQL chunks by splitting the semicolon and then run each individual statement like this:

$sqls = explode(';', $sql);
array_pop($sqls);
foreach($sqls as $statement){
    $statment = $statement . ";";
    //echo $statement;
    $this->db->query($statement);   
}
Enter fullscreen mode Exit fullscreen mode

This will naturally rule out adding of any comments or whitespaces above or below the statements as you would in a script because that might cause an error. However, a simple and clean SQL script such as this one will work flawlessly:

drop table if exists settings;
drop table if exists prices;
drop table if exists quantities;

create table settings (
    id integer primary key,
    key varchar(2000),
    value varchar(2000)
);

create table update_status (
    id integer primary key,
    idx int,
    last_update datetime
);

create table prices (
    id integer primary key,
    price decimal(9,2),
    price_dt datetime
);

create table quantities (
    id integer primary key,
    idx integer,
    qty decimal(9,2),
    qty_dt datetime
);
Enter fullscreen mode Exit fullscreen mode

Image of Datadog

How to Diagram Your Cloud Architecture

Cloud architecture diagrams provide critical visibility into the resources in your environment and how they’re connected. In our latest eBook, AWS Solution Architects Jason Mimick and James Wenzel walk through best practices on how to build effective and professional diagrams.

Download the Free eBook

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more