Welcome Guest, Not a member yet? Register   Sign In
help me write this mysql query in codeigniter
#1

(This post was last modified: 11-11-2019, 08:00 PM by acebay.)

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)
       
Reply
#2

@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 )?
Reply
#3

(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/datab...y-bindings )?

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

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");
Reply
#5

(This post was last modified: 11-12-2019, 03:49 AM by Chivinsdev.)

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
Reply
#6

(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
Reply
#7

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

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()); 
Reply
#9

(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.
Reply
#10

(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
Reply




Theme © iAndrew 2016 - Forum software by © MyBB