Finding New record insertion

[yasmine]

Hi friends,

I'm creating a table which extract 2 fields from another 2 tables. It's working well.
Whenever a new row inserted into the existing 2 tables it should be updated into the new table. How can I do it?

My create table code is:

[CODE=mysql]create table exam(select a.username,b.qu alification from users a, skill b where a.username=b.us ername);[/CODE]

If any new row inserted into the skill or users table, it should be reflected on the exam table.

For this i'm writing php script and my logic is,

i fetch the max(ID) from the skill table.
when the ID should be increased, then the new row will be inserted into exam table.

But My question is how do i know when the new row is inserted in the skill table or when will be the ID is increased?

Is my logic right? or any other ideas to do it?

Please show me the proper code.
Help me out......

[satas]

But My question is how do i know when the new row is inserted in the skill table or when will be the ID is increased?

You mean, that records in skill/user appear not through PHP? Someone manually inserts them using, for example phpMyAdmin? If no, why can't you write another php-function to insert new record in exam table after skill/user update?

It could be done also via DB triggers, without any php code. Even MySQL has them since 5.X version.
Using them you are able to perform some operations BEFORE or AFTER some ivents. E.g. you can insert new row in exam table AFTER insertion row into skill/user table. Check this to find out more information:
http://dev.mysql.com/doc/refman/5.0/en/triggers.html

[yasmine]

You mean, that records in skill/user appear not through PHP? Someone manually inserts them using, for example phpMyAdmin? If no, why can't you write another php-function to insert new record in exam table after skill/user update?

It could be done also via DB triggers, without any php code. Even MySQL has them since 5.X version.
Using them you are able to perform some operations BEFORE or AFTER some ivents. E.g. you can insert new row in exam table AFTER insertion row into skill/user table. Check this to find out more information:
http://dev.mysql.com/doc/refman/5.0/en/triggers.html

hi
Thanks 4 ur reply.
I didn't understand clearly what u r saying.
Is there anything wrong with my logic?
How can i create trigger for inserting the same values which inserted on another table? Is it possible?
Can you show me those lines?

[CODE=mysql]create trigger ins_trig after insert on users for each row
begin
insert into exam values(select username,qualif ication from users where exam.username!= users.username) ;
End;[/CODE]

It shows me error.

Thanx n regards
Yas

[satas]

How can i create trigger for inserting the same values which inserted on another table? Is it possible?

Yes it is:
[CODE=mysql]create trigger ins_trig after insert on users for each row
begin
insert into exam values(NEW.user name,NEW.qualif ication);
End;[/CODE]
This should work, but i have not tried.

By the way, do you use php in your logic? Or it is only about database?

[yasmine]

Yes it is:
[CODE=mysql]create trigger ins_trig after insert on users for each row
begin
insert into exam values(NEW.user name,NEW.qualif ication);
End;[/CODE]
This should work, but i have not tried.

By the way, do you use php in your logic? Or it is only about database?

Thanx again,
Can u tell me what is this 'NEW' keyword refers?
This code doesn't show the fields on users table na?
I want what ever values will be inserted into the users table should be reflect on the exam table.

Help me out.......

[satas]

Thanx again,
Can u tell me what is this 'NEW' keyword refers?
This code doesn't show the fields on users table na?
I want what ever values will be inserted into the users table should be reflect on the exam table.

Help me out.......

In a few words NEW is the last inserted row from table `user`

Read this carefully :
http://dev.mysql.com/doc/refman/5.0/en/using-triggers.html

And after that ask questions.

[ronverdonk]

This is a MySQL question and does not belong in the PHP forum.
Will be moved.

moderator

[yasmine]

In a few words NEW is the last inserted row from table `user`

Read this carefully :
http://dev.mysql.com/doc/refman/5.0/en/using-triggers.html

And after that ask questions.

Thanks a lot yaar........... ..

Now I understood.