[eluser]sudesh[/eluser]
Hi all,
Below is my code that i m using to get some records, but i m getting database error.
Code:
$this->db->select('store_prefix.*');
$this->db->select('store.store_name');
$this->db->select("(select GROUP_CONCAT(CAST(CONCAT(st.tot_start, ' - ', st.tot_end) AS CHAR ) ORDER BY st.tot_start SEPARATOR ', ') from ".$this->db->dbprefix('store_tot')." st where st.tot_store = ".$this->db->dbprefix('store_prefix').".store_id) ranges", false);
$this->db->from('store_prefix');
$this->db->join('store_tot', 'store_prefix.store_id = store_tot.tot_store', 'left');
$this->db->join('store', 'store_prefix.store_id = store.store_id', 'left');
$this->db->group_by('store_prefix.prefix_id');
Its Produces query
Code:
SELECT bkv_store_prefix.*, bkv_store.store_name, (select GROUP_CONCAT(CAST(CONCAT(st.tot_start, ' - ', [b][u]bkv_[/u][/b]st.tot_end) AS CHAR ) ORDER BY st.tot_start SEPARATOR ', ') from bkv_store_tot st where st.tot_store = bkv_store_prefix.store_id) ranges FROM (bkv_store_prefix) LEFT JOIN bkv_store_tot ON bkv_store_prefix.store_id = bkv_store_tot.tot_store LEFT JOIN bkv_store ON bkv_store_prefix.store_id = bkv_store.store_id GROUP BY bkv_store_prefix.prefix_id
rather than
Code:
SELECT bkv_store_prefix.*, bkv_store.store_name, (select GROUP_CONCAT(CAST(CONCAT(st.tot_start, ' - ', st.tot_end) AS CHAR ) ORDER BY st.tot_start SEPARATOR ', ') from bkv_store_tot st where st.tot_store = bkv_store_prefix.store_id) ranges FROM (bkv_store_prefix) LEFT JOIN bkv_store_tot ON bkv_store_prefix.store_id = bkv_store_tot.tot_store LEFT JOIN bkv_store ON bkv_store_prefix.store_id = bkv_store.store_id GROUP BY bkv_store_prefix.prefix_id
Can't understand.
What wrong am i doing here.
Below is my table structure
CREATE TABLE IF NOT EXISTS `bkv_store_prefix` (
`prefix_id` int(11) NOT NULL auto_increment,
`prefix` varchar(100) NOT NULL,
`store_id` int(11) NOT NULL,
PRIMARY KEY (`prefix_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
CREATE TABLE IF NOT EXISTS `bkv_store_tot` (
`tot_id` int(11) NOT NULL auto_increment,
`tot_start` int(11) NOT NULL,
`tot_end` int(11) NOT NULL,
`tot_used` int(11) NOT NULL default '0',
`tot_available` int(11) NOT NULL,
`tot_store` int(11) NOT NULL,
`tot_time` datetime NOT NULL,
PRIMARY KEY (`tot_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
Can somebody help.