CodeIgniter Forums
help me write this mysql query in codeigniter - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forum-5.html)
+--- Forum: General Help (https://forum.codeigniter.com/forum-24.html)
+--- Thread: help me write this mysql query in codeigniter (/thread-74815.html)



help me write this mysql query in codeigniter - acebay - 11-11-2019

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


RE: help me write this mysql query in codeigniter - php_rocs - 11-11-2019

@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/database/queries.html?highlight=query#query-bindings )?


RE: help me write this mysql query in codeigniter - acebay - 11-11-2019

(11-11-2019, 07:48 PM)php_rocs Wrote: @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/database/queries.html?highlight=query#query-bindings )?

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


RE: help me write this mysql query in codeigniter - mboufos - 11-12-2019

hello check here https://codeigniter.com/user_guide/database/results.html?highlight=query#result-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");


RE: help me write this mysql query in codeigniter - Chivinsdev - 11-12-2019

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


RE: help me write this mysql query in codeigniter - acebay - 11-12-2019

(11-12-2019, 03:12 AM)Chivinsdev Wrote: 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


RE: help me write this mysql query in codeigniter - Chivinsdev - 11-12-2019

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


RE: help me write this mysql query in codeigniter - Piotr - 11-12-2019

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



RE: help me write this mysql query in codeigniter - acebay - 11-12-2019

(11-12-2019, 09:27 AM)Piotr Wrote:
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.


RE: help me write this mysql query in codeigniter - Chivinsdev - 11-18-2019

(11-12-2019, 03:49 PM)acebay Wrote:
(11-12-2019, 09:27 AM)Piotr Wrote:
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