Update Row To Another Table

Yii 1.1 General Discussion
1

I have the following 2 tables(places & Offer (comapny) in mysql database named COUPON1.81

CREATE TABLE IF NOT EXISTS places

( id int(60) NOT NULL AUTO_INCREMENT, namevarchar(255) NOT NULL,

Description text NOT NULL,

‘Company_id’ int(60) NOT NULL,

Active int(60) NOT NULL,

CREATE TABLE IF NOT EXISTS offer_company

( id int(60) NOT NULL AUTO_INCREMENT,

company_name varchar(150) NOT NULL,

Address text NOT NULL,

‘places_id’ int(60) NOT NULL,

I need to Update the rows from table company_id to table places when insert a new row on table company

Example, if i insert a new row to Offer_company values as (‘1’, CSB(pvt)limited, srilanka,123) then the the trigger should to UPDATE the rows from table company_id to table places

[color="#FF0000"]Places_id select form places table(using drop down list)[/color]

places table

±---------±------------±-----±----±--------±------±------±------+

| id | name | Description | Company_id |

±---------±------------±-----±----±--------±------±------±------+

| 123 | eiffel tower | CSB(pvt)limited | 0 |

| 124 | sigiriya | sigiriya Rock | 0 |

±---------±------------±-----±----±--------±------±------±------+

Offer _comapny table

±---------±------------±-----±----±--------±------+

| id | company_name | Address | places_id|

±---------±------------±-----±----±--------±------+

| 1 | CSB(pvt)limited | 89/A,Srilanka | 123 |

±---------±------------±-----±----±--------±------+

After select places _id in company table

In Places table, Company_id should be update

places table

±---------±------------±-----±----±--------±------±------±------+

| id | name | Description | Company_id |

±---------±------------±-----±----±--------±------±------±------+

| 123 | eiffel tower | is an iron lattice tower | [color="#FF0000"]| 1 | [/color]

| 124 | sigiriya | sigiriya Rock | 0 |

±---------±------------±-----±----±--------±------±------±------+

–

– Triggers offer_company

–

DROP TRIGGER IF EXISTS add;

DELIMITER //

CREATE TRIGGER add AFTER INSERT ON offer_company

FOR EACH ROW INSERT INTO places (Company_id)

SELECT new.id 


        FROM offer_company


        WHERE offer_company.places_id = new.places_id

//

DELIMITER ;

How do this using trigger or php option please solve me thank you

2

Hi

So if I understand this correctly, what you are trying to do is:

When you insert a new row in offer_company, you want the trigger to update the company_id in the ‘places’ table where places.id = offer_company.places_id.

Triggers are not a Yii question ;)




DROP TRIGGER IF EXISTS 'update_places_table_after_insert';

DELIMITER //

CREATE TRIGGER `update_places_table_after_insert` 

 AFTER INSERT ON `offer_company` FOR EACH ROW  

    BEGIN  

       UPDATE places

       SET Company_id = NEW.company_id  

       WHERE id = NEW.places_id; 

    END//


DELIMITER ;

This NetTuts tutorial provides an introduction to triggers.

Hope that helps

3

if offer company save the recorded then after you want to write a update query using offer company id

for e.g




$model= new Offer Company ()

$model->save(false){

   //update query

    $update= Places::model()->updateByPk($model->id,array('username'=>'hai'));

  

}

check the update query