Welcome Guest, Not a member yet? Register   Sign In
CI 1.7.1: No escaping of table name
#1

[eluser]davidbehler[/eluser]
I do the following:
Code:
$this->db->select('*');
$this->db->select('(SELECT COUNT(*) FROM user_group WHERE user_group_group_id = group_id) AS user_count', FALSE);
$this->db->order_by('group_name', 'asc');
$result = $this->db->get('group');
The generated query is
Code:
SELECT *, (SELECT COUNT(*) FROM user_group WHERE user_group_group_id = group_id) AS user_count FROM (group) ORDER BY group_name asc

That produces an error caused by the not escaped table name 'group'.

BUT as soon as I leave out the custom select part like this:
Code:
$this->db->select('*');
$this->db->order_by('group_name', 'asc');
$result = $this->db->get('group');
It works fine and the query looks like this:
Code:
SELECT * FROM (`group`) ORDER BY `group_name` asc

As you can see in my second example the table name is escaped just as it should be.

Am I missing something or is this a bug caused by the 'do not escape' parameter of the select method?
#2

[eluser]Tim Brownlaw[/eluser]
I was thinking that the FALSE in the Select might be getting remembered.
I've not looked into the code on this, I was just intrigued by your problem.

So I tried an experiment and found that you needed to have the last select statement with 'TRUE' after
any preceding FALSE settings.
I've only tried this with the code provided in this post.

Like this...
Code:
$this->db->select('(SELECT COUNT(*) FROM user_group WHERE user_group_group_id = group_id) AS user_count', FALSE);
$this->db->select('*',TRUE);
$this->db->order_by('group_name', 'asc');
$result = $this->db->get('group');
I have swapped the two select statements around and set the last one to TRUE.
Leaving TRUE out results in the same 'bad' output already seen.So it's not switching back by default.

So this produces
Code:
SELECT (SELECT COUNT(*) FROM user_group WHERE user_group_group_id = group_id) AS user_count, * FROM (`group`) ORDER BY `group_name` asc

As you can see, group_name is now 'ticked'. So next step is this...
Code:
$this->db->select('(SELECT COUNT(*) FROM user_group WHERE user_group_group_id = group_id) AS user_count', FALSE);
$this->db->order_by('group_name', 'asc');
$this->db->select('*',TRUE);
$result = $this->db->get('group');
Shuffling everything above the $this->db->select('*',TRUE); that we don't require backticks for.

The output becomes
Code:
SELECT (SELECT COUNT(*) FROM user_group WHERE user_group_group_id = group_id) AS user_count, * FROM (`group`) ORDER BY group_name asc

Which is what you were after.

So there is a 'quirk' as you noted.

Disabling backticks in the select $this->db->select turns them off for all statements afterwards and you need to force them back on in another $this->db->select but then everything after that gets backticked.

I'd be curious to know if you get any further as the SQL itself has errors in it.

The reason you need the backticks on the tablename group is because it is a keyword, so MySQL let's
you get away with `group` so it's ignored. That's a clue as to why the SQL statement itself won't work.



Cheers
Tim
#3

[eluser]Tim Brownlaw[/eluser]
I'm probably way off track here but I can't resist.

Code:
SELECT g.*,count(u.id) as user_count FROM `group` g JOIN user_group u ON u.group_id=g.id GROUP BY u.group_id;

Using the above on the following tables...
Code:
CREATE TABLE  `group` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(16) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

Code:
CREATE TABLE  `user_group` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(16) NOT NULL,
  `group_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
With the data...
Code:
INSERT INTO `group` (name) values ('Hockey'),('Soccer'),('IceSkating');
INSERT INTO user_group (name,group_id) values ('Nigel',1),('Fred',1),('George',2),('Sam',3);

Results in
Code:
id  name          user_count
============================
1  Hockey             2
2  Soccer             1
3  IceSkating         1
All tested and working...

Now I can get to bed Smile
#4

[eluser]davidbehler[/eluser]
Haha, yeah. That's pretty much how I solved the problem.

But the reason for this post is the bug that you and I described and even though one can rewrite the query to not use the sub select anymore, the bug stays Wink




Theme © iAndrew 2016 - Forum software by © MyBB