Can not load numerical field names from Database

[ajslaghu]

I have piece of code that pulls weekyears as columnnames from the database.

However, using numerical values does not give those colums back in the results. Perpending the numerical value with a letter , does return the correct results.

Not working code:

SELECT Merk, 201302 AS 201302, 201303 AS 201303, 201304 AS 201304, 201305 AS 201305, 201302 + 201303 + 201304 + 201305 AS som FROM (stats_nebuys_eek_merk) WHERE 201302 + 201303 + 201304 + 201305 > 100 ORDER BY 201302 + 201303 + 201304 + 201305 DESC LIMIT 15

Working:
SELECT Merk, 201302 AS W201302, 201303 AS W201303, 201304 AS W201304, 201305 AS W201305, 201302 + 201303 + 201304 + 201305 AS som FROM (stats_newbuys_week_merk) WHERE 201302 + 201303 + 201304 + 201305 > 100 ORDER BY 201302 + 201303 + 201304 + 201305 DESC LIMIT 15

I use $this->db->query($sql); for inserting those queries.

[narfbg]

Could you show some real code please?

[ajslaghu]

DUMMY DATA:

CREATE TABLE IF NOT EXISTS `stats_newbuys_week_merk` (
  `Merk` varchar(100) DEFAULT NULL,
  `201242` decimal(23,0) DEFAULT NULL,
  `201243` decimal(23,0) DEFAULT NULL,
  `201244` decimal(23,0) DEFAULT NULL,
  `201245` decimal(23,0) DEFAULT NULL,
  `201246` decimal(23,0) DEFAULT NULL,
  `201247` decimal(23,0) DEFAULT NULL,
  `201248` decimal(23,0) DEFAULT NULL,
  `201249` decimal(23,0) DEFAULT NULL,
  `201250` decimal(23,0) DEFAULT NULL,
  `201251` decimal(23,0) DEFAULT NULL,
  `201252` decimal(23,0) DEFAULT NULL,
  `201253` decimal(23,0) DEFAULT NULL,
  `201301` decimal(23,0) DEFAULT NULL,
  `201302` decimal(23,0) DEFAULT NULL,
  `201303` decimal(23,0) DEFAULT NULL,
  `201304` decimal(23,0) DEFAULT NULL,
  `201305` decimal(23,0) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `stats_newbuys_week_merk` (`Merk`, `201242`, `201243`, `201244`, `201245`, `201246`, `201247`, `201248`, `201249`, `201250`, `201251`, `201252`, `201253`, `201301`, `201302`, `201303`, `201304`, `201305`) VALUES
('A.C.', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
('A.C.L.', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);

Not working situation.

CODE:

$columns = $this->db->query('SHOW COLUMNS FROM stats_newbuys_week_merk;');

    foreach ($columns->result() as $result) {
        if ($result->Field == 'Merk')
            continue;
        $fieldlist[] = $result->Field;
    }
    array_splice($fieldlist, 0, -4);

    foreach ($fieldlist as $field)
        $quoted[] = "`$field`";
    $sum = implode(' + ', $quoted);

// Inserting a letter for each coloumn because of Code Igniter
    foreach ($fieldlist as $field)
        $desc[] = "`$field` AS `$field`";
    $select = implode(' , ', ['`Merk`', implode(' , ', $desc), $sum . ' AS som']);

    $query = $this->db->select($select)->from('stats_newbuys_week_merk')
                    ->order_by($sum . ' DESC')->where($sum . ' > ' . "100")
                    ->limit(15)->get();
//echo         $this->db->last_query();
// Removing W again cuz this is a bug in Code Igniter

    print($this->db->last_query());

    $query_result = $query->result();

    var_dump($query_result);

    die();

RESULT:

SELECT `Merk`, `201302` AS `201302`, `201303` AS `201303`, `201304` AS `201304`, `201305` AS `201305`, `201302` + `201303` + `201304` + `201305` AS som FROM (`stats_newbuys_week_merk`) WHERE `201302` + `201303` + `201304` + `201305` > 100 ORDER BY `201302` + `201303` + `201304` + `201305` DESC LIMIT 15

array(15) { [0]=> object(stdClass)#38 (2) { ["Merk"]=> string(10) "VOLKSWAGEN" ["som"]=> string(4) "3617" } [1]=> object(stdClass)#39 (2) { ["Merk"]=> string(4) "FORD" ["som"]=> string(4) "3265" } [2]=> object(stdClass)#40 (2) { ["Merk"]=> string(7) "PEUGEOT" ["som"]=> string(4) "2670" } [3]=> object(stdClass)#41 (2) { ["Merk"]=> string(7) "RENAULT" ["som"]=> string(4) "2184" } [4]=> object(stdClass)#42 (2) { ["Merk"]=> string(3) "KIA" ["som"]=> string(4) "2051" } [5]=> object(stdClass)#43 (2) { ["Merk"]=> string(6) "TOYOTA" ["som"]=> string(4) "1871" } [6]=> object(stdClass)#44 (2) { ["Merk"]=> string(4) "FIAT" ["som"]=> string(4) "1742" } [7]=> object(stdClass)#45 (2) { ["Merk"]=> string(3) "BMW" ["som"]=> string(4) "1729" } [8]=> object(stdClass)#46 (2) { ["Merk"]=> string(7) "CITROEN" ["som"]=> string(4) "1694" } [9]=> object(stdClass)#47 (2) { ["Merk"]=> string(4) "AUDI" ["som"]=> string(4) "1645" } [10]=> object(stdClass)#48 (2) { ["Merk"]=> string(5) "VOLVO" ["som"]=> string(4) "1178" } [11]=> object(stdClass)#49 (2) { ["Merk"]=> string(4) "OPEL" ["som"]=> string(4) "1125" } [12]=> object(stdClass)#50 (2) { ["Merk"]=> string(7) "HYUNDAI" ["som"]=> string(4) "1052" } [13]=> object(stdClass)#51 (2) { ["Merk"]=> string(5) "SKODA" ["som"]=> string(3) "820" } [14]=> object(stdClass)#52 (2) { ["Merk"]=> string(13) "MERCEDES-BENZ" ["som"]=> string(3) "737" } }

// We are missing the numbers

Adjusted (working situation)

CODE

$columns = $this->db->query('SHOW COLUMNS FROM stats_newbuys_week_merk;');

    foreach ($columns->result() as $result) {
        if ($result->Field == 'Merk')
            continue;
        $fieldlist[] = $result->Field;
    }
    array_splice($fieldlist, 0, -4);

    foreach ($fieldlist as $field)
        $quoted[] = "`$field`";
    $sum = implode(' + ', $quoted);

// Inserting a letter for each coloumn because of Code Igniter
    foreach ($fieldlist as $field)
        $desc[] = "`$field` AS `W$field`";// !!! W INSERTED 
    $select = implode(' , ', ['`Merk`', implode(' , ', $desc), $sum . ' AS som']);

    $query = $this->db->select($select)->from('stats_newbuys_week_merk')
                    ->order_by($sum . ' DESC')->where($sum . ' > ' . "100")
                    ->limit(15)->get();
//echo         $this->db->last_query();
// Removing W again cuz this is a bug in Code Igniter

    print($this->db->last_query());

    $query_result = $query->result();

    var_dump($query_result);
die();

RESULt:

SELECT `Merk`, `201302` AS W201302, `201303` AS W201303, `201304` AS W201304, `201305` AS W201305, `201302` + `201303` + `201304` + `201305` AS som FROM (`stats_newbuys_week_merk`) WHERE `201302` + `201303` + `201304` + `201305` > 100 ORDER BY `201302` + `201303` + `201304` + `201305` DESC LIMIT 15

Result set:

array(15) { [0]=> object(stdClass)#38 (6) { ["Merk"]=> string(10) "VOLKSWAGEN" ["W201302"]=> string(4) "1153" ["W201303"]=> string(3) "782" ["W201304"]=> string(3) "830" ["W201305"]=> string(3) "852" ["som"]=> string(4) "3617" } [1]=> object(stdClass)#39 (6) { ["Merk"]=> string(4) "FORD" ["W201302"]=> string(3) "541" ["W201303"]=> string(3) "395" ["W201304"]=> string(4) "1949" ["W201305"]=> string(3) "380" ["som"]=> string(4) "3265" } [2]=> object(stdClass)#40 (6) { ["Merk"]=> string(7) "PEUGEOT" ["W201302"]=> string(3) "750" ["W201303"]=> string(3) "592" ["W201304"]=> string(3) "893" ["W201305"]=> string(3) "435" ["som"]=> string(4) "2670" } [3]=> object(stdClass)#41 (6) { ["Merk"]=> string(7) "RENAULT" ["W201302"]=> string(3) "799" ["W201303"]=> string(3) "585" ["W201304"]=> string(3) "473" ["W201305"]=> string(3) "327" ["som"]=> string(4) "2184" } [4]=> object(stdClass)#42 (6) { ["Merk"]=> string(3) "KIA" ["W201302"]=> string(3) "707" ["W201303"]=> string(3) "567" ["W201304"]=> string(3) "407" ["W201305"]=> string(3) "370" ["som"]=> string(4) "2051" } [5]=> object(stdClass)#43 (6) { ["Merk"]=> string(6) "TOYOTA" ["W201302"]=> string(3) "557" ["W201303"]=> string(3) "483" ["W201304"]=> string(3) "459" ["W201305"]=> string(3) "372" ["som"]=> string(4) "1871" } [6]=> object(stdClass)#44 (6) { ["Merk"]=> string(4) "FIAT" ["W201302"]=> string(3) "163" ["W201303"]=> string(3) "117" ["W201304"]=> string(4) "1372" ["W201305"]=> string(2) "90" ["som"]=> string(4) "1742" } [7]=> object(stdClass)#45 (6) { ["Merk"]=> string(3) "BMW" ["W201302"]=> string(3) "519" ["W201303"]=> string(3) "455" ["W201304"]=> string(3) "531" ["W201305"]=> string(3) "224" ["som"]=> string(4) "1729" } [8]=> object(stdClass)#46 (6) { ["Merk"]=> string(7) "CITROEN" ["W201302"]=> string(3) "319" ["W201303"]=> string(3) "328" ["W201304"]=> string(3) "832" ["W201305"]=> string(3) "215" ["som"]=> string(4) "1694" } [9]=> object(stdClass)#47 (6) { ["Merk"]=> string(4) "AUDI" ["W201302"]=> string(3) "390" ["W201303"]=> string(3) "247" ["W201304"]=> string(3) "803" ["W201305"]=> string(3) "205" ["som"]=> string(4) "1645" } [10]=> object(stdClass)#48 (6) { ["Merk"]=> string(5) "VOLVO" ["W201302"]=> string(3) "272" ["W201303"]=> string(3) "296" ["W201304"]=> string(3) "313" ["W201305"]=> string(3) "297" ["som"]=> string(4) "1178" } [11]=> object(stdClass)#49 (6) { ["Merk"]=> string(4) "OPEL" ["W201302"]=> string(3) "313" ["W201303"]=> string(3) "219" ["W201304"]=> string(3) "421" ["W201305"]=> string(3) "172" ["som"]=> string(4) "1125" } [12]=> object(stdClass)#50 (6) { ["Merk"]=> string(7) "HYUNDAI" ["W201302"]=> string(3) "236" ["W201303"]=> string(3) "157" ["W201304"]=> string(3) "516" ["W201305"]=> string(3) "143" ["som"]=> string(4) "1052" } [13]=> object(stdClass)#51 (6) { ["Merk"]=> string(5) "SKODA" ["W201302"]=> string(3) "264" ["W201303"]=> string(3) "178" ["W201304"]=> string(3) "191" ["W201305"]=> string(3) "187" ["som"]=> string(3) "820" } [14]=> object(stdClass)#52 (6) { ["Merk"]=> string(13) "MERCEDES-BENZ" ["W201302"]=> string(3) "233" ["W201303"]=> string(3) "173" ["W201304"]=> string(3) "199" ["W201305"]=> string(3) "132" ["som"]=> string(3) "737" } }

[narfbg]

This is not a CodeIgniter bug, it's a PHP limitation - property names cannot start with a digit.

$ php -a
Interactive shell

php > $foo = new stdClass();
php > $foo->1 = 2;
PHP Parse error:  syntax error, unexpected T_LNUMBER, expecting T_STRING or T_VARIABLE or '{' or '$' in php shell code on line 1

[ajslaghu]

ah. actually result_array() does provide a work around as well.

[sharazy]

@ajslaghu , thanks for the workaround. I had the same problem as yours.