Using SQL String Functions in OrderBy call.

[eluser]porangi[/eluser]
Hi,

To any admins watching - I posted this last night but it didn't seem to save/publish?

I'm trying to use some built in MYSQL string operations inside the $this->db->orderby() function and it fails. Is this possible? What am I doing wrong?

Code:

function findBy($criteria)
    {
        $this->db->where($criteria);
        $this->db->order_by("LEFT(cLabel, '1') desc");
        $this->db->order_by("RIGHT(cLabel, '1') asc");
        $query = $this->db->get($this->table);
        return $query;
     }

cLabel is a string that look slike P1, M4, D1 etc.. so I'm trying to order them by letter and then number.

I have another function which works fine -

Code:

{
        $sql = "SELECT `course`.`title` as course_title, `unit`.`uName` as unit_title, `criteria`.* FROM (`criteria`)
            LEFT JOIN `units` as unit ON `unit`.`id` = `criteria`.`unit`
            LEFT JOIN `courses` as course ON `course`.`id` = `unit`.`course`";
        if($this->session->userdata("criteriafilter")!=0) { $sql .= " WHERE `criteria`.`unit` = ".$this->session->userdata("criteriafilter")." "; }
        $sql .= "ORDER BY `course_title` desc, `unit_title` desc, LEFT(cLabel, 1) desc, RIGHT(cLabel, 1) asc
            LIMIT ".$offset.", ".$count;
        $query = $this->db->query($sql);
        return $query;
    }

and I could do it this way but I don't want to break form and stop using the

Code:

$this->db->where($criteria);

call which takes an associated array as this has wider consquences.

Any help would be appreciated

Chris

PS: still lovin CI and about to get company decals done for the van. Thinking CI logo may have to be there somewhere.

[eluser]toopay[/eluser]
Should be just like

Code:

function findBy($criteria)
    {
        $this->db->where($criteria);
        $this->db->order_by("LEFT(cLabel, 1)", "desc");
        $this->db->order_by("RIGHT(cLabel, 1)", "asc");
        $query = $this->db->get($this->table);
        return $query;
     }

[eluser]porangi[/eluser]
Hi,

Sorry for the delay in thanking you but I've only just got back to it after focusing on other projects. Sadly I'm still getting errors.

Code:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc, RIGHT(cLabel, `1)` asc' at line 4

SELECT * FROM (`criteria`) WHERE `unit` = '10' ORDER BY LEFT(cLabel, `1)` desc, RIGHT(cLabel, `1)` asc

It appears that the , in the function call escapes the two parameters of the LEFT and RIGHT functions.

Edit:-

Yeah, I just double checked my sql by trying

Code:

$this->db->order_by("cLabel", "desc");
        $this->db->order_by("cLabel", "asc");

and it works fine so its the addition of the LEFT, RIGHT mysql functions. If this is a bug its the first time I've ever found one rather than it just being my fault. Now, where is my prize!

Regards

Chris

[eluser]porangi[/eluser]
So, I've got a solution that allows me to keep by criteria array although I think it will fail if my array grows beyond size 1 and this is not as elegant.

Code:

$value = each($criteria);
$sql = "SELECT `criteria`.* FROM (`criteria`)";
$sql .= " WHERE ".$value[0]." = ".$value[1];
$sql .= " ORDER BY LEFT(cLabel, 1) desc, RIGHT(cLabel, 1) asc";
$query = $this->db->query($sql);

[eluser]porangi[/eluser]
Evening all,

Ok, this problem has reared its ugly head again. I have three nested queries and I'm trying to get the count of those starting with P, M and D. This just isn't working

Code:

$query = "SELECT count(*) FROM `criteria` WHERE id = ".$unit_id." ";
$pQuery = $query . "AND LEFT (cLabel,1) = 'P'";
$mQuery = $query . "AND LEFT (cLabel,1) = 'M'";
$dQuery = $query . "AND LEFT (cLabel,1) = 'D'";

becoming

Code:

count(*) FROM `criteria` WHERE id = 63 AND LEFT (cLabel, [b]`1)`[/b] = 'P')

once CI has done its thing.. I'm going to try wildcards but it would be nice if this would work.

Happy CI Days

Chris

[eluser]jmadsen[/eluser]
activerecord has an additional parameter that tells it whether or not to use the backticks - this is what you need

[eluser]porangi[/eluser]
SOLVED - Thanks Jeff.