How can I create a CRUD with Codeigniter using SQLite?

Question

I'm starting a new project where I want to use Codeigniter with SQLite. I searched information about this and I managed to conect and get data from my SQLite database but I have no idea of how can I create a CRUD for a table with this, I'm trying to do it the same way I did in the past with MySQL buy it is not working.

Here is what I did:

config/database.php

$db['default'] = array(
    'dsn'   => '',
    'hostname' => '',
    'username' => '',
    'password' => '',
    'database' => APPPATH.'/database/Pasapalabra.sqlite',
    'dbdriver' => 'sqlite3',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => (ENVIRONMENT !== 'production'),
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_general_ci',
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE
);

config/autoload.php

$autoload['libraries'] = array('database');

models/modelo_prueba.php

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class modelo_prueba extends CI_Model  {

    public function getTestData() {
        return $this->db->get('preguntas')->result();
    }
}

?>

controllers/Welcome.php

public function __construct() {
    parent::__construct();
    $this->load->library("grocery_CRUD");
}

public function prueba() {

        $this->load->model('modelo_prueba');
        $dataSet = $this->modelo_prueba->getTestData();
        $this->load->view('data_view', [
            'dataSet' => $dataSet
        ]);

    }

views/data_view.php

<h4>Lets test the data</h4>
<?php
    if($dataSet) {
        foreach($dataSet as $dataItem) {
            echo $dataItem->definicion . "<hr>";
        }
    }
?>
<h5>done!</h5>

This worked for me, it shows correctly all data in 'preguntas' database, but now I'm trying to create a CRUD just like I usually do with MySQL and it's working, it shows a database error.

The only thing that I have changed from the code of above is Welcome.php

public function prueba() {

    $crud = new Grocery_CRUD();
    $crud->set_table('preguntas');

    $output = $crud->render();
    $this->load->view("example.php", $output);

}

The error that is shown in the screen is the following:

A PHP Error was encountered

Severity: Warning

Message: SQLite3::query(): Unable to prepare statement: 1, near "SHOW": syntax error

Filename: sqlite3/sqlite3_driver.php

Line Number: 129

Backtrace:

File: C:\xampp\htdocs\Pasapalabra_Isidro\application\models\Grocery_crud_model.php
Line: 436
Function: query

File: C:\xampp\htdocs\Pasapalabra_Isidro\application\libraries\Grocery_CRUD.php
Line: 48
Function: get_field_types_basic_table

File: C:\xampp\htdocs\Pasapalabra_Isidro\application\libraries\Grocery_CRUD.php
Line: 1567
Function: get_field_types

File: C:\xampp\htdocs\Pasapalabra_Isidro\application\libraries\Grocery_CRUD.php
Line: 4507
Function: showList

File: C:\xampp\htdocs\Pasapalabra_Isidro\application\controllers\Welcome.php
Line: 38
Function: render

File: C:\xampp\htdocs\Pasapalabra_Isidro\index.php
Line: 315
Function: require_once


A Database Error Occurred

Error Number: 0

not an error

SHOW COLUMNS FROM `preguntas`

Filename: C:/xampp/htdocs/Pasapalabra_Isidro/system/database/DB_driver.php

Line Number: 691

Anyone knows what is happening? There isn't any error with the database connection because the previous code worked, but it's like Grocery CRUD can't create a CRUD of an SQLite.

I'm working with Codeigniter 3.1.4 and I created the SQLite database with SQLite Manager (Firefox) 0.8.3.1

Thank you for your answers.

in grocery_crud model they are using raw sql queries to fetch column names from table, which is not compatible with sqlite3 you have to rewrite few lines of code to make it sqlite3 compatible. check in grocery_crud_model SHOW COLUMNS FROM — umefarooq, Apr 15 '17 at 04:44
I see... well, since I have just started this project I can change the database, I will use MySQL instead to save these headaches. Thank you for you answer. — Ise92, Apr 15 '17 at 18:39
there is solution for grocery crud with sqlite3 also available that works fine with sqlite3 i have test it check my answer below — umefarooq, Apr 16 '17 at 05:53

score 2 · Answer 1 · answered Apr 16 '17 at 05:58

2

Hi I have found solution from grocery crud form post with works with sqlite3 you have to modify Grocery curd library first copy sqlite3 model from this link

http://www.grocerycrud.com/forums/topic/651-suport-for-sqlite3/
this model extending grocery_crud_model, just include grocery_crud_model in your sqlite model.
in grocery crud library find grocery_crud_model, where it is loading this model just replace with grocery_crud_sqlite3, and you are done. it will work perfect.

just remember you have to modify these lines manually on each update in future.

answered Apr 16 '17 at 05:58

umefarooq

4,540
1
29
38

It didn't worked to me, It shows exactly the same error, I don't know if I did something wrong either. I created a controller called Grocery_crud_sqlite3 and copied there all the code from that page, I included the code from the Grocery_crud_model and then in GROCERY CRUD library I replaced this: – Ise92 Apr 16 '17 at 14:49
It didn't worked to me, It shows exactly the same error, I don't know if I did something wrong either. I created a controller called Grocery_crud_sqlite3 and copied there all the code from that page, I included the code from the Grocery_crud_model and then in GROCERY CRUD library I replaced this: $ci->load->model('Grocery_crud_model'); for this: $ci->load->model('Grocery_crud_sqlite3'); But it still isn't working... I'll use MySQL instead, so it doesn't matter, Thank you for you answer. – Ise92 Apr 16 '17 at 14:57

How can I create a CRUD with Codeigniter using SQLite?

1 Answers1

Linked