2

hi i have two tables namely 

sms(Message,sms_index...columns) 
c_paid_bribe(c_addi_info,....)

what i want to do is to insert the values of Message column of sms table into c_addi_info column of c_paid_bribe table automatically whenever a new value is inserted into sms table. I tried this 

$query=mysql_query("insert into bd_paid_bribe(c_addi_info) select Message from sms");

But when a new value is inserted and i run the .php file the already existed values are also inserting into the table again.....

juergen d
  • 201,996
  • 37
  • 293
  • 362
sree
  • 23
  • 4

2 Answers2

1
Q: what i want to do is to insert the values of Message column of sms table into c_addi_info column of c_paid_bribe table automatically whenever a new value is inserted into sms table.

A: If you want to update one table when another is changed (and, for whatever reason, you can't simply do this in your application), then use "triggers":

http://net.tutsplus.com/tutorials/databases/introduction-to-mysql-triggers/

Q: But when a new value is inserted and i run the .php file the already existed values are also inserting into the table again.....

A: You want an "upsert". For example:

How do I update if exists, insert if not (AKA "upsert" or "merge") in MySQL?

Community
  • 1
  • 1
paulsm4
  • 114,292
  • 17
  • 138
  • 190
  • can u show me how to use the trigger for the above said query as i'm a newbie to this mysql..i'm little confused on how to use that.... – sree Apr 14 '12 at 06:07
0

What you want to do is simply to make two queries that are comitted in one transaction. This is what transactions are for. See here for examples on how to do it: PHP + MySQL transactions examples

Community
  • 1
  • 1
barsju
  • 4,408
  • 1
  • 19
  • 24
  • i want a query to write when i insert a record in one table then that record automatically should inserted in another table....help me out.... – sree Apr 14 '12 at 06:15
  • Then look at triggers. But I would still think twice about that, since you will be hiding your functionality away in MySQL. To me that smells like bugs and unexpected behaviors waiting to happen. I would rather design my php code to keep these insert(s) together so it is clear to anyone reading the code what is going on. But in your case the only sensible thing might be triggers, just think about the consequences when it comes to readability and transparency of your code. – barsju Apr 14 '12 at 06:17
  • can u please show me the better way to get out this problem....i have sms table in that message column is there and i want to get all the things in this message column into the c_addi_info column of c_paid_bribe table and whenever new value is inserted into that message column it should be updated into my c_addi_info column....show me the better way for this even in php it's fine...i'm breaking my head fr the possible solution... – sree Apr 14 '12 at 06:54
  • which link u r asking me to refer? – sree Apr 14 '12 at 07:56