Welcome Guest, Not a member yet? Register   Sign In
Delete with Join or Where_in not working
#1

(This post was last modified: 05-05-2020, 03:18 AM by nicolas33770.)

Hello, 

I would like to know the best way to delete with Query builder and join (not working).

This select with join working good with Query Builder and I would like to use the same way to delete but is not working. How I can do that ?

PHP Code:
//WORKS FINE    
public function get_services($profile_id$location_id)
    {
        $this->db->select('s.*,l.location_id,l.profile_id');
        $this->db->from('service s');
        $this->db->join('location l''s.location_id = l.location_id''left');
        $this->db->where('s.location_id'$location_id);
        $this->db->where('l.profile_id'$profile_id);

        $this->db->order_by("s.service_title""ASC");

        $query $this->db->get();
        return $query->result();
    


PHP Code:
// NOT WORKING    
public function delete_service($location_id,$service_id,$profile_id)
    {
        $this->db->from('service s');
        $this->db->join('location l''s.location_id = l.location_id''left');
        $this->db->where('s.service_id'$service_id);
        $this->db->where('s.location_id'$location_id);
        $this->db->where('l.profile_id'$profile_id);

        return $this->db->delete('service s');
    


PHP Code:
// NOT WORKING    
public function delete_service($location_id,$service_id,$profile_id)
    {
        $this->db->select('s.*,l.location_id,l.profile_id');
        $this->db->from('service s');
        $this->db->join('location l''s.location_id = l.location_id''left');
        $this->db->where('l.profile_id'$profile_id);
        $this->db->where('s.location_id'$location_id);
        $this->db->where('s.service_id'$service_id);

        $where_clause $this->db->get_compiled_select();
        
        $this
->db->where_in('s.location_id'$where_clause);

        return $this->db->delete('service s');
    
Reply
#2

@nicolas33770,

Have you tried using query binding (https://codeigniter.com/userguide3/datab...y-bindings)? It may work better for you.
Reply
#3

Hi,

I don't know if its a copy & paste failure but you provide your not working functions two times with $service_id.
Did you changed one of them to $location_id?
Reply
#4

(This post was last modified: 05-05-2020, 03:44 AM by nicolas33770.)

(05-05-2020, 02:04 AM)niklas Wrote: Hi,

I don't know if its a copy & paste failure but you provide your not working functions two times with $service_id.
Did you changed one of them to $location_id?

Thanks, yes its copy & paste failure, I fixed that

(05-04-2020, 08:22 PM)php_rocs Wrote: @nicolas33770,

Have you tried using query binding (https://codeigniter.com/userguide3/datab...y-bindings)?  It may work better for you.

Thanks for your reply but I would like use join with Query builder on DELETE. I don't understand how this feature could help me? But if I understand correctly it is not possible to use Join queries with query Builder on DELETE. so I have to make my request myself?

I use query binding with my own Query and it's working but is not even simple than Query builder. :-)

Thanks for your help
Reply
#5

(This post was last modified: 05-05-2020, 09:36 AM by neuron.)

1. You should do inner join in this case instead of left join (unless $profile_id can be null).
2. In MySQL if you specifying alias for table name you should use alias in delete statement not a table name.
So try this: 
PHP Code:
public function delete_service($location_id,$service_id,$profile_id)
{
    $this->db->from('service s');
    $this->db->join('location l''s.location_id = l.location_id');
    $this->db->where('s.service_id'$service_id);
    $this->db->where('s.location_id'$location_id);
    $this->db->where('l.profile_id'$profile_id);

    return $this->db->delete('s');

Reply
#6

@nicolas33770,

With query binding you can do any query and it will work. I prefer it because it more or less goes directly to the db. I have very strong db skills so I prefer it over having the framework build it for me.
Reply
#7

(This post was last modified: 05-05-2020, 02:17 PM by nicolas33770.)

(05-05-2020, 09:35 AM)neuron Wrote: 1. You should do inner join in this case instead of left join (unless $profile_id can be null).
2. In MySQL if you specifying alias for table name you should use alias in delete statement not a table name.
So try this: 
PHP Code:
public function delete_service($location_id,$service_id,$profile_id)
{
    $this->db->from('service s');
    $this->db->join('location l''s.location_id = l.location_id');
    $this->db->where('s.service_id'$service_id);
    $this->db->where('s.location_id'$location_id);
    $this->db->where('l.profile_id'$profile_id);

    return $this->db->delete('s');


Thanks for your replay, I tried but seems join is ignored in final query. Don't working.

(05-05-2020, 12:42 PM)php_rocs Wrote: @nicolas33770,

With query binding you can do any query and it will work.  I prefer it because it more or less goes directly to the db.  I have very strong db skills so I prefer it over having the framework build it for me.


Yes of course. Thank you.  Wink
Reply




Theme © iAndrew 2016 - Forum software by © MyBB