possible DB glitch in 1.7

[eluser]Jevedor[/eluser]
I just recently tried updating my CI 1.6.3 to 1.7 and ran into an issue that was not documented in the update guide.

I have a good number of sql scripts that run on my site and any of them that used "AS" became broken after installing the the 1.7 database library.

Here is an example of some code that worked in 1.6.3 but no longer does in 1.7


$this->CI->db->select('a.*, b.page_name AS parent_name');
$this->CI->db->from('pages AS a');
$this->CI->db->join('pages AS b', 'a.parent_id = b.page_id', 'left');
$this->CI->db->where('a.display_status', 'Y');
$this->CI->db->orderby('a.parent_id, a.sort_order');

$this->navigation = $this->CI->db->get();

Has something been deprecated that I am not aware of or Am I doing something incorrectly?

For now i will continue to use 1.6.3 as it still works but I would like to be able to use all of 1.7. Any help would be appreciated.

[eluser]ray73864[/eluser]
for the 'from' and 'join' clause, just drop the 'AS', not even MySQL requires it to be there, you can just do:

Code:

$this->CI->db->from('pages a')

and

Code:

$this->CI->db->join('pages b','a.parent_id=b.page_id','left')

.

that might be all that you have to change to get it to work again.

[eluser]Jevedor[/eluser]
removing the "AS" form the clauses helped but then i got an error in my order by. It seems like the function will no longer except a comma delimited list for multiple orders.

I had to call the function multiple times to get it to work.

Where you used to be able to do this


$this->CI->db->orderby('a.parent_id, a.sort_order');

You now must do it like this.

$this->CI->db->orderby('a.parent_id');
$this->CI->db->orderby('a.sort_order');

Kinda devolving if you ask me.

Any reason why this may have happened.

[eluser]izzyGuy72[/eluser]
I used the lowercase for "AS" and it worked just fine on my end. I'm currently using CI 1.7. But removing it works better as Ray said.

As for the orderby, I have no clue why it's like. I also would like to know why it is.

[eluser]Jevedor[/eluser]
Okay i have found a solution to the above issue with orderby, however i dont think it is ideal. Apparently what i have found is happening is that if you use the comma delimited list when the protect_identifiers function from the db_driver gets called on the value it adds the comma to the field name and then doesn't look at the rest. If you make sure to put a space behind every comma in the list so it looks like this:


$this->db->orderby('a.region , a.country , a.province , a.state , a.city');


instead of this:


$this->db->orderby('a.region, a.country, a.province, a.state, a.city');


It seems to work properly. I think this is a bug in the protect_identifiers function but i havent really had a chance to really look into it.

Any thoughts?

[eluser]xwero[/eluser]
The orderby problem can be solved by adding the sort keyword, ASC or DESC.

[eluser]Jevedor[/eluser]
how so?

like this?

$this->db->orderby(‘a.region, a.country, a.province, a.state, a.city’, 'ASC');

I just tried it and it does not solve the problem. You still have to add space between the fields and the commas.

It used to work in 1.6.3 this wasn't even necessary and you could even do complicated orderings like this

$this->db->orderby(‘a.region ASC, a.country DESC');

This also no longer works.