HTML Table class duplicates data cells from SQLite2 query results

[eluser]jpwilksch[/eluser]
Problem Summary
The HTML Table class library (CI_Table) in CodeIgniter 1.7.2 duplicates each data cell when generating a table from SQLite2 results. Header cells are not duplicated.

Example HTML table:

Code:

<table border="1" cellpadding="2" cellspacing="1">
<tr>
<th>fruit_id</th><th>name</th><th>price</th><th>description</th></tr>
<tr>
<td>1</td><td>1</td><td>Apple</td><td>Apple</td><td>4.95</td><td>4.95</td><td>Red or green in colour</td><td>Red or green in colour</td></tr>
<tr>
<td>2</td><td>2</td><td>Orange</td><td>Orange</td><td>3.95</td><td>3.95</td><td>A citrus fruit</td><td>A citrus fruit</td></tr>
<tr>
<td>3</td><td>3</td><td>Banana</td><td>Banana</td><td>5.95</td><td>5.95</td><td>Easily peeled skin</td><td>Easily peeled skin</td></tr>
</table>

Reason
By default, PHP function sqlite_query() returns a results array with both numerical- and associative- indices. The CI_DB_sqlite_driver class uses this default behaviour, but the HTML Table class does not fully allow for it.

Table header cells are generated correctly, because:
CI_Table function _set_from_object() uses the
CI_DB_sqlite_result function list_fields() which uses
PHP function sqlite_num_fields() to determine the number of columns in the result.

Table data cells are duplicated, because CI_Table function _set_from_object() assumes the query results to be indexed numerically OR associatively, but not both. It uses a foreach statement to iterate the results:

Code:

foreach ($query->result_array() as $row)

Workaround
I'm uncertain whether it's better to fix the CI_Table class or the CI_DB_sqlite_driver class, but the following workaround is successful for me:

In sqlite_driver.php, alter the query to return ONLY associatively indexed results:

Code:

function _execute($sql)
    {
        $sql = $this->_prep_query($sql);
        //return @sqlite_query($this->conn_id, $sql);
        return @sqlite_query($this->conn_id, $sql, SQLITE_ASSOC);
    }

Reproducing the problem
Make the following settings in database.php:

Code:

$active_group = "sqlite2_testcase";
$active_record = TRUE;

$db['sqlite2_testcase']['hostname'] = "localhost";
$db['sqlite2_testcase']['username'] = "";
$db['sqlite2_testcase']['password'] = "";
$db['sqlite2_testcase']['database'] = "../dbdir/sqlite2_testcase.db";
$db['sqlite2_testcase']['dbdriver'] = "sqlite";
$db['sqlite2_testcase']['dbprefix'] = "";
$db['sqlite2_testcase']['pconnect'] = TRUE;
$db['sqlite2_testcase']['db_debug'] = TRUE;
$db['sqlite2_testcase']['cache_on'] = FALSE;
$db['sqlite2_testcase']['cachedir'] = "";
$db['sqlite2_testcase']['char_set'] = "utf8";
$db['sqlite2_testcase']['dbcollat'] = "utf8_general_ci";

Create the controller sqlite2_testcase.php with contents:

Code:

<?php
class Sqlite2_testcase extends Controller {

    function createdb()
    {
        # create sqlite2 database
        $db = sqlite_open('../dbdir/sqlite2_testcase.db', 0666, $sqliteerror);

        # create a table
        sqlite_query($db, 'DROP TABLE fruit');
        sqlite_query($db, 'CREATE TABLE fruit (
                            fruit_id Integer PRIMARY KEY NOT NULL,
                            name VarChar NOT NULL,
                            price Currency NOT NULL,
                            description VarChar NULL)');
            
        # add some data
        sqlite_query($db, "INSERT INTO fruit VALUES ('1', 'Apple',  '4.95', 'Red or green in colour')");
        sqlite_query($db, "INSERT INTO fruit VALUES ('2', 'Orange', '3.95', 'A citrus fruit')");
        sqlite_query($db, "INSERT INTO fruit VALUES ('3', 'Banana', '5.95', 'Easily peeled skin')");
        sqlite_close($db);
        echo "Database created\n";    
    }    
        
    function index()
    {
        $this->load->library('table');    
        $this->load->database();
        $query = $this->db->get('fruit');
        $tmpl = array('table_open'  => '<table border="1" cellpadding="2" cellspacing="1">');
        $this->table->set_template($tmpl);
        echo $this->table->generate($query);
    }
}
?&gt;

Create the test database by browsing to:
/index.php/sqlite2_testcase/createdb

Then reproduce the problem by browsing to:
/index.php/sqlite2_testcase/

You should see duplicated HTML table cells as shown in the Problem Summary.

[eluser]jpwilksch[/eluser]
I haven't had any feedback on this issue - should I go ahead and report a bug?

[eluser]sunaj[/eluser]
[quote author="jpwilksch" date="1264529105"]I haven't had any feedback on this issue - should I go ahead and report a bug?[/quote]

I can confirm this bug, showed up in my own implementation and as well when running your demo code above. Go ahead and report if this has not already been done I say.

Thanks for the workaround.

[eluser]BigBad[/eluser]
I am experiencing the same thing. I just began working with codeIgniter and I mocked up a very simple MVC with a small sqlite db. It is in fact duplicating my results and not the headers.

I wonder if it would be best for me to run my project on an earlier version of CI.

[eluser]BigBad[/eluser]
I went back all the way to version 1.6.0 and it has the same bug. I didnt go any further because at that point active record isnt implemented. I went ahead and applied the 'work around' from above.

Thanks.

[eluser]jpwilksch[/eluser]
Reported under the following bug:
http://bitbucket.org/ellislab/codeignite...ite2-query

[eluser]Renea077[/eluser]
I discovered this bug a while back. I have been trying to figure out a way around it for some time now, quite unsuccessfully. I tried your work around, and it works perfectly. I greatly appreciate this help. Thank you very much for posting this.