Thread

Hello all,
    I’m trying to have CI build a sql query to use with jquery jqgrid.  My query string is as follows:

$sql_data = array(
                      $sidx,
                      $sord,
                      $start,
                      $limit
                  );

$sql = "SELECT * FROM livestock, ORDER BY ? ? LIMIT ? , ?";
            
$query = $this->db->query($sql, $sql_data);

CI throws an error:

A Database Error Occurred

Error Number: 1064

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 ‘ORDER BY ‘id’ ‘desc’ LIMIT 0 , ‘10’’ at line 1

SELECT * FROM livestock, ORDER BY ‘id’ ‘desc’ LIMIT 0 , ‘10’

There’s an error with the query.  I’m thinking it has to do with ‘desc’, but I’m not exactly sure.  What is wrong with the sql query and what would be the correct query string?

-Thanks,
    Rich

$sql = "SELECT * FROM livestock ORDER BY ? ? LIMIT ? , ?";

As requested from our IRC chat I hard coded the sql query:

$sql = "SELECT * FROM livestock ORDER BY 'id' DESC LIMIT 0 ,5";
            
            $query = $this->db->query($sql);

            $responce->page = $page; 
            $responce->total = $total_pages; 
            $responce->records = $count;
            $i=0;
            
            foreach ($query->result() as $row)
            {
                echo $row->alias;
                $responce->rows[$i]['id']=$row->id; 
                $responce->rows[$i]['cell']=array($row->id,$row->species_id,$row->alias,$row->gender,$row->birth_date,$row->breeding,$row->parents,$row->location,$row->comment);
                $i++;
            }

Having the sql query hard coded works as expected.

This also works:

$sql = "SELECT * FROM livestock ORDER BY $sidx $sord LIMIT $start, $limit";
            
            $query = $this->db->query($sql);

This does NOT work:

$sql = "SELECT * FROM livestock ORDER BY ? ? LIMIT ? ,?";
            
            $query = $this->db->query($sql, $sql_data);

I’m not sure why that query binding is not working.  Any help with getting that query binding to work is greatly appreciated.

-Thank You,
    Rich

CI doesn’t have any way of knowing that the DESC you’re inserting is part of an ORDER BY statement and shouldn’t be escaped (which query() does automatically when you use query binding). This is a good place to use AR.

$query = $this->db->order_by($sidx, $sord)->limit($limit, $start)->get('livestock');

I wanted to use query bindings as protection from unwanted characters.  Will that automatically escape the unwanted values or am I having to escape them manually?

I ran the query:

$sql = $this->db->order_by($sidx, $sord)->limit($limit, $start)->get('livestock');
            
$query = $this->db->get($sql);

Now I’m receiving an error:
A PHP Error was encountered

Severity: Warning

Message: strpos() expects parameter 1 to be string, object given

Filename: database/DB_active_rec.php

Line Number: 1448

A Database Error Occurred

Error Number: 1064

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 ‘’ at line 2

SELECT * FROM (`Resource` id #30, `Resource` id #34, `Array`, `Array`, `0`, `10`, `)

-Thank You,
    Rich

I was playing around with the code and decided to try:

$sql = $this->db->order_by($sidx, $sord)->limit($limit, $start);
$query = $this->db->get('livestock',$sql);

That worked as expected.  Why does that work?

I was reading the CI User guide about method chaining and it lacks support.  Are there anymore sections on CI’s website that goes more in depth?

-Rich

You’re calling get() twice.

Method chaining doesn’t really ‘lack support’, it just doesn’t work on PHP4.

You’re calling get() twice.

Method chaining doesn’t really ‘lack support’, it just doesn’t work on PHP4.

It’s my ignorance to php that is the problem.  I’ll keep reading and reading.  Thank you for the explanation.

-Rich