help me write this mysql query in codeigniter

Hi All,

I need help on writing this MySQL query in CodeIgniter

   

Code:

SELECT *, ABS( post_code - 2015 ) AS distance FROM list_data WHERE business_name LIKE '%cash%' ESCAPE '!' ORDER BY distance LIMIT 6

2015 and '%cash%' is coming from input fields

so far I have written it like this but not working

 

PHP Code:

public function searchListing($data, $location) {
    $this->db->select('(SELECT *, ABS(`post_code`-' $location' AS distance FROM `list_data` ORDER BY distance)', FALSE);
    $this->db->like('business_names', $data);
    $query = $this->db->get();
    return $query->result();
    } 


I've attached images of the table, first image is showing all data in the table, the second image is the query that I want to achieve in codeigniter



Can anyone help me? Thanks

Attached Files

Thumbnail(s)
       

@acebay,

When you execute the query what messages are you seeing? What does the generated query look like? Have you tried query binding ( https://codeigniter.com/user_guide/datab...y-bindings )?

@acebay,

When you execute the query what messages are you seeing?  What does the generated query look like?  Have you tried query binding ( https://codeigniter.com/user_guide/datab...y-bindings )?

I've updated my post with an image can you please have a look. Thanks

hello check here https://codeigniter.com/user_guide/datab...ult-arrays

you dont have to translate the mysql query to CI query just do like this

$query = $this->db->query("SELECT *, ABS( post_code - 2015 ) AS distance FROM list_data WHERE business_name LIKE '%cash%' ESCAPE '!' ORDER BY distance LIMIT 6");

Try this one I hope it will help you get what you are looking for.

in your controller.

Either you autoload the database in autoload file or you load it to the controller straight by

PHP Code:

$this->load->database(); // within the controller but I prefer autoload 


in your model just continue with

PHP Code:

public function searchListing($data, $location) {
    $q = $this->db->select('*, ABS( post_code - '.$location.') AS distance')
         ->from('list_data')
         ->like('business_name', $data)
         ->order_by('distance')
         ->limit(6)
         ->get()
         ->result();
    return $q;
} 


I used the chaining method which is the shorter method. So try it I hope it will help you.
If you are facing more problem and confused let me know either you private message me or drop your problem here. I am here to help

Try this one I hope it will help you get what you are looking for.

in your controller.

Either you autoload the database in autoload file or you load it to the controller straight by

PHP Code:

$this->load->database(); // within the controller but I prefer autoload 


in your model just continue with

PHP Code:

public function searchListing($data, $location) {
    $q = $this->db->select('*, ABS( post_code - '.$location.') AS distance')
         ->from('list_data')
         ->like('business_name', $data)
         ->order_by('distance')
         ->limit(6)
         ->get()
         ->result();
    return $q;
} 


I used the chaining method which is the shorter method. So try it I hope it will help you.
If you are facing more problem and confused let me know either you private message me or drop your problem here. I am here to help

Hi @Chivinsdev, your code works, thank you so much, I've been looking for the solution the whole day. Thanks again

I am happy it work. Thanks for your reply. enjoy!!!

PHP Code:

Easiest way:

$q = $this->db->query("SELECT *, ABS( post_code - 2015 ) AS distance FROM list_data WHERE business_name LIKE '%cash%' ESCAPE '!' ORDER BY distance LIMIT 6");

print_r($q->result()); 


PHP Code:

Easiest way:

$q = $this->db->query("SELECT *, ABS( post_code - 2015 ) AS distance FROM list_data WHERE business_name LIKE '%cash%' ESCAPE '!' ORDER BY distance LIMIT 6");

print_r($q->result()); 


Hi, @Piotr thank you for your reply, you forgot that 2015 and '%cash%' is coming from input fields so you can treat it as a string. Please look at the solution from @Chivinsdev, his solution is correct.

PHP Code:

Easiest way:

$q = $this->db->query("SELECT *, ABS( post_code - 2015 ) AS distance FROM list_data WHERE business_name LIKE '%cash%' ESCAPE '!' ORDER BY distance LIMIT 6");

print_r($q->result()); 


Hi, @Piotr thank you for your reply, you forgot that 2015 and '%cash%' is coming from input fields so you can treat it as a string. Please look at the solution from @Chivinsdev, his solution is correct.

Note the code can still work but he forgot to arrange it well for you to understand it.
That is

PHP Code:

$q = $this->db->query("SELECT *, ABS(post_code - ".$location.") AS distance FROM list_data WHERE business_name LIKE '%".$data."%' ESCAPE '!' ORDER BY distance LIMIT 6")->result();

print_r($q); 


I hope it will also work as expected