Using SQL String Functions in OrderBy call.

Using SQL String Functions in OrderBy call. - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Using SQL String Functions in OrderBy call. (/showthread.php?tid=43756)

Using SQL String Functions in OrderBy call. - El Forum - 07-22-2011

[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.

Using SQL String Functions in OrderBy call. - El Forum - 07-22-2011

[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;

     }

Using SQL String Functions in OrderBy call. - El Forum - 08-05-2011

[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

Using SQL String Functions in OrderBy call. - El Forum - 08-05-2011

[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);

Using SQL String Functions in OrderBy call. - El Forum - 08-10-2011

[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

Using SQL String Functions in OrderBy call. - El Forum - 08-10-2011

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

Using SQL String Functions in OrderBy call. - El Forum - 08-15-2011

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