CODEWITHSUNDEEP
mysqlsql
John
John

Reputation: 17

Mysql query INSERT two tables

I have two tables:table1 and rating

table1(id,name,category) 
rating (cid,rating,total_rating,total_rates,photoID)

and now when i insert data into table1 i want to set all data in table rating at zero for that specific photoID from table1, but i dont know how..can someone help me?

Upvotes: 0

Views: 2560

Answers (4)

Luuk
Luuk

Reputation: 1969

You can use MySql triggers http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html:

CREATE TRIGGER ins_rating AFTER INSERT ON table1
FOR EACH ROW
BEGIN
    INSERT INTO rating (cid,rating,total_rating,total_rates,photoID)
         VALUES( NEW.ID ,0,0,0,null)
END;

Upvotes: 1

John Woo
John Woo

Reputation: 263703

I'd rather create a STORED PROCEDURE to make a single call from application. Assuming that you want to INSERT a record on table rating for every insert on table1 and that ID on table1 is set as AUTO_INCREMENT.

DELIMITER $$
CREATE PROCEDURE procedureName
(
    IN _name VARCHAR(25),
    IN _category VARCHAR(25)
)
BEGIN
    INSERT INTO table1 (name, category)
    VALUES (_name, _category);

    SET @last_ID := LAST_INSERT_ID();

    INSERT INTO rating (cid, rating, total_rating, total_rates, photoID)
    VALUES (@last_ID, 0,0,0,0);
END $$
DELIMITER ;

and call the procedure,

CALL procedureName('nameHere','categoryHere')

Upvotes: 1

Andomar
Andomar

Reputation: 238078

You can use LAST_INSERT_ID() to retrieve the ID you just inserted. For example, assuming PhotoID is the relation between table1 and rating:

insert table1 (name,category) values ('waterfall 2', 'nature');
insert rating (rating,total_rating,total_rates,photoID) values
    (0, 0, 0, last_insert_id());

Upvotes: 1

Muzafar Ali
Muzafar Ali

Reputation: 1452

If you want to insert data into TABLE1 and delete it from TABLE2, you can write below listed query:

mysql_query("DELETE * FROM table2");

Upvotes: 0

Related Questions