phpcodeigniterjoinsql-update

Update Codeigniter's data on Table #2 based Table #1's id

I have 2 tables, reservation and is customer

table #1 : reservation
------------------------
- reservation_id
- reservation_status
- customer_id

table #2 : customer
------------------------
- customer_id
- customer_name
- customer_status

I'm new in codeigniter but how I can update/change customer status (from 0 to 1 or 1 to somevalue) based on reservation id

is there using join and update or somewhat. Thanks

Solution

  • Try this query:

    $sql = 'UPDATE customer AS C JOIN reservation AS R ON R.customer_id=C.customer_id 
SET C.customer_status = (CASE C.customer_status WHEN 1 THEN 0 ELSE 1 END) 
WHERE R.reservation_id="2"';
$result = $this->db->query($sql);
if($result) {
   echo "Success";
} else {
   echo "fail";
}

    And check your query by:

    echo $this->db->last_query();