CODEWITHSUNDEEP
phpsqlcodeigniterpdo
Ambrus Tóth
Ambrus Tóth

Reputation: 662

How can I do this database query in Codeigniter?

I want to create a browse method for my CodeIgniter model. I wrote one, but it generates a bad SQL. Here is my Code:

public function browse($keyword = null, $category_id = null, $order = "ASC", $order_by = "expiration_date", $latitude = null, $longitude = null, $maxDistance = null)
{
    $this->db->select(" id,
        round(
            (
                6371 * acos(
                    cos(radians(10)) * cos(radians(latitude)) * cos(radians(20) - radians(longitude)) + sin(radians(10)) * sin(radians(latitude))
                )
            ),
            2
        ) AS distance", false);

    // Order:
    if (!in_array($order, array("ASC", "DESC", "RANDOM"))) {
        $order = "ASC";
    }
    if (!in_array($order_by, array("distance", "expiration_date", "id", "offer_datetime", "food_name", "food_category"))) {
        $order_by = "expiration_date";
    }

    if ($keyword != null && is_string($keyword)) {
        $this->db->group_start();
        $this->db->like("food_name", $keyword);
        $this->db->or_like("details", $keyword);
        $this->db->group_end();
    }

    if ($category_id != null && is_numeric($category_id)) {
        $this->db->where("food_category", $category_id);
    }

    if($maxDistance != null && is_numeric($maxDistance)){
        $this->db->having("distance < $maxDistance", true);
    }
    $this->db->order_by($order_by, $order);


    return $this->db->get(TABLE_NAME);
}

I want to generate an sql like this:

SELECT id, round( ( 6371 * acos( cos(radians($latitude)) * cos(radians(latitude)) * 
cos(radians($longitude) - radians(longitude)) + sin(radians($latitude)) * 
sin(radians(latitude)) ) ), 2 ) AS distance FROM `TABLE_NAME` WHERE ( 
`food_name` LIKE '%keyword%' ESCAPE '!' OR `details` LIKE '%keyword%' ESCAPE '!' ) AND 
`food_category` = '1' HAVING `distance` < 20 ORDER BY `offer_datetime` DESC

But it seems CodeIgniter adds an extra " 1" after `distance` < 20. Why is this happening?

This is the output of my page: enter image description here

Upvotes: 0

Views: 95

Answers (1)

naturaljoin
naturaljoin

Reputation: 475

The "1" is from the true that you provide as the second argument $value where you set your having part of the query.

The documentation states the arguments as:

having($key[, $value = NULL[, $escape = NULL]])

If you are intending to say true to $escape, you may do:

$this->db->having("distance <", $maxDistance, true);

Upvotes: 1

Related Questions