This content originally appeared on Level Up Coding - Medium and was authored by Besarion Turmanauli
An in-depth CodeIgniter 3 + SQLite step-by-step tutorial
CodeIgniter has one of the most easy-to-use and thorough documentation among all PHP frameworks and is my personal framework of choice for a variety of other reasons as well.
Today we’re going to create a fully working SQLite database connection in CodeIgniter and do complex interactions with it.
First, open Notepad or any text editor and save a blank file as db.sqlite in [codeigniter_root_directory]/application/databases/ folder. We’ve got our “database” ready.
We now need to open [codeigniter_toor_directory]/application/config/database.php file and modify connection data array, which is by default stored in $db[‘default’] variable:
You only need to modify ‘database’ and ‘dbdriver’ entries as displayed above for simple local connections.
You’ll also need to connect to the database and manage it, creating new tables and columns, modifying them, I’m usually using the built-in PHPStorm IDE tool for these purposes, any other built-in or standalone database management software would also work fine.
Let’s create a new controller in [codeigniter_root_folder]/application/controlers:
We’re going to work with financial deals and transactions in this tutorial, I have a controller named Full_trans_data.php (short for full transaction data).
This is the starting template, I’m loading database (a class that is responsible for database connections but you can also autoload them globally from [codeigniter_root_folder]/application/config/autoload.php like this:
$autoload['libraries'] = array('database','session',’…’); and so on…
You can load multiple helpers or libraries (from a controller) this way:
If you don’t know how libraries and helpers differ in CodeIgniter, a simple way to look at them would be:
- loading a helper is like loading another php file with include statement and then using the functions and variables inside it as if the content of an imported file was directly inside the host file;
- loading a library is like loading a class instance and then accessing its methods, and properties;
For more information, you can check CodeIgniter documentation on helpers and libraries.
If you’re wondering, why haven’t we loaded the database library using $this->load->library(‘database’) and what’s that $this->load->database() all about, it’s because CodeIgniter supports multiple database connections, $this->load->database() is first of all, shorter to write and can take an argument to load another database connection: $another_db_connection = $this->load->database(‘another_db’,TRUE);
What we do with $this->db we can also do with $another_db_connection.
If you prefer using $this->db , you can just switch the connection to a different database using $this->db->db_select(‘another_db’) when you need to do so.
We haven’t created any function yet in our Full_trans_data class, before we do it’s worth noting that CodeIgniter controllers can take data in different ways:
- A standard POST method (preferable for a large quantity of information)
- GET method using CodeIgniter-specific URL format: /base_url/controller/function/variable1/variable2/variable3/… which are then interpreted by the server as get variables based on instructions in default .htaccess file
- A standard GET method with a standard ?variable1=value1&variable2=value2 in the URL, which is not recommended because of SQL injection and other security vulnerabilities associated with it, unless we’re applying security measures that are already included in CodeIgniter, in fact, CodeIgniter is considered to be the most secure PHP framework in the world.
So, for handling a large amount of data with a high level of security, we’re going to use the POST method and then access the data in CodeIgniter using a built-in method$this->input->post(“variable”);
Let’s now create a new function inside Full_trans_data class, and call it add_entry, this function will take data using a POST method, process it, and then add an entry in the database table, we are going to use $this->db->insert('table_name', $associative_array), a built-in method allowing us to use PHP associative arrays with column names and values inside it: array( ‘column_name1’=>’value1’, ’column_name2’=>’value2’). For more information, you can read CodeIgniter documentation about $this->db->insert() feature here.
It’s worth noting that, once you configure any type of database connection (one or more) in [codeigniter_root_folder]/application/config/database.php, all built-in methods for database interaction will work seamlessly, even if you’re using a mix of different database types (MySQL, SQLite, MSSQL, and so on…). The only difference is $this->db->query(‘SQL QUERY STATEMENT’), which will have to be adjusted for a particular SQL dialect, SQLite in this case.
Here’s a simplified validation and insertion process of the data we receive using the POST method (inside add_entry function):
NOTE: for SQLite, you don’t really need to insert float values where column types are float, or integer, where column types are integer, simple text is fine, SQLite is a text-based database format: values are stored as text, this is why we use whatever we get from POST directly, as long as data is provided, without additional validation, casting, or conversion.
Let’s now create another function called update_values for doing just that, in this scenario, we might need to adapt to a particular situation, maybe we need to update the whole row, one column, or multiple columns at once, we have a lot of columns already, so how do we make one function for any possible combination?
In the example below, we first check if row id is provided so that we know which row we’re working with:
Then we go column by column and add its value to $data_to_update array, only if the relevant value is provided.
We can use a loop to go through all provided values, there are different ways to do that but first, we have to get POST values as an array, for additional security, we can only allow predefined column names using $post_values = $this->input->post(array(‘column1’, ‘column2’));
or go through them directly: $post_values = $this->input->post(NULL);
Simpler and cleaner:
Now in order to retrieve the data, there are built-in functions for frequently used requests but robust application often needs advanced filtering, search, sorting or even more complex operations, as SQL language already has those, I think it’s still better to use the power of SQL by sending whatever requests you have directly: $this->db->query(‘SQL Statement’)
In order to delete a specific row, we can use $this->db->delete(‘table_name’,$array):
Thanks for reading!
Would you like to see some magic? Click and hold that clap button and see what happens :)
As a thank you here’s some free stuff:
$140 from FBS: regulated by IFSC, this broker is one of the oldest and most established institutions, operating since 2009.
Requirements:
- Register a new account with $140 on it
- Use the leverage of 1:500 to maximize your profits
- You can withdraw all profits
Available Markets: Cryptocurrencies, Stocks, CFDs, Metals, Commodities, Foreign Exchange
$30 from Tickmill: regulated by FSA, this broker operates since 2015.
Requirements:
- Register a new account with $30 on it
- Use the leverage of up to 1:500 to maximize your profits
- Withdraw the profits after 5 lots are traded
- The maximum withdrawal amount is $300
Available Markets: Stock Indices, Oil, Precious Metals, Bonds, Foreign Exchange.
$30 from Roboforex: regulated by CySEC and IFSC, Roboforex is operating since 2009 and is one of the most popular and trusted brokers among traders today.
Requirements:
- Open an account and deposit $10 to verify your payment method (can be withdrawn at any time) and get $30 as a gift
- Profits are withdrawable without limitations
- If you trade the necessary number of lots, you can withdraw the $30 too
Available Markets: Stocks (all NYSE, NASDAQ, and AMEX shares + German and Chinese listed companies), Stock CFDs (on all stocks, $1.5 per trade fee on US-listed shares), Indices, ETFs, Commodities, Metals, Energy Commodities, Cryptocurrencies, Cryptoindices, Foreign Exchange.
10% Lifetime Discount on all trades from Binance: Binance is a cryptocurrency exchange that has the lowest fees in the world supporting by far the largest variety of ways you can trade or invest in crypto:
- Spot Trading;
- Peer to Peer (P2P) Trading;
- Margin (up to 10x leverage) Trading;
- Crypto Futures Trading;
- Crypto Conversion and more…
When you invest in any cryptocurrency, you can make an additional risk-free passive income by allowing lending for margin (leveraged) trading, it comes with a powerful Online (web) platform, Windows, Mac, and Linux software, as well as Android and iOS apps + Binance API for software developers.
Binance doesn’t only have the lowest fees, but is one of the few platforms that support trading Dogecoin, and is the largest crypto exchange in terms of traded volume and cryptocurrencies allowed for trading in the world.
Deposit options include:
- Direct crypto deposit of any listed cryptocurrency in your Binance wallet;
- Buying cryptocurrency using your Credit/Debit card;
- Deposit 35 different fiat currencies via Bank Transfer (SWIFT or SEN).
And you can now get an additional 10% discount on every trade you make when you register from this link.
Have an amazing day!
How to connect and use SQLite in CodeIgniter 3 was originally published in Level Up Coding on Medium, where people are continuing the conversation by highlighting and responding to this story.
This content originally appeared on Level Up Coding - Medium and was authored by Besarion Turmanauli
Besarion Turmanauli | Sciencx (2021-05-23T22:18:48+00:00) How to connect and use SQLite in CodeIgniter 3. Retrieved from https://www.scien.cx/2021/05/23/how-to-connect-and-use-sqlite-in-codeigniter-3/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.