2 tables INSERT/UPDATE at the same time?

[joep]

Hello,

With the following string you can update/fill a table:
-------------
$sql = "INSERT INTO tableA (name,address,fax,email) VALUES
('$name','$address','$fax','$email')";
-------------
That's easy ands works. But now i want to insert values to another table at the same time. I tried something like this:
-------------
$sql = "INSERT INTO tableA, tableB (name,address,fax,email,valueB) VALUES
('$name','$address','$fax','$email','$valueB')WHERE tableA_id = tableB_id ";
-------------
But that won't work. Who can help me?
Greets,

Joep [sig][/sig]

 

[bigscouse]

Just guessing but have you tried: -
$sql = "INSERT INTO tableA (name,address,fax,email) VALUES
('$name','$address','$fax','$email');
INSERT INTO tableA (name,address,fax,email) VALUES
('$name','$address','$fax','$email');commit;";
[sig]<p>Ged Jones<br><a href=mailto:gedejones@hotmail.com>gedejones@hotmail.com</a><br><a href= > </a><br> [/sig]

 

[joep]

Doesn't work. Sorry.
Greets,

Joep [sig][/sig]

 

[TeaAddictedGeek]

What do you mean when you say that &quot;it won't work&quot;? Does it deliver an error? Is it simply not adding it to the table?

Try doing a response.write on the SQL string and see what it outputs. [sig]<p> <br><a href=mailto:aberman@thebiz.net>aberman@thebiz.net</a><br><a href= > </a><br>Database web programmer and developer, fueled by peach snapple and mochas.[/sig]

 

[TeaAddictedGeek]

Ah... two tables...

If you create a stored procedure it can do this without additional syntax. Otherwise I think that you have to rework it a little. [sig]<p> <br><a href=mailto:aberman@thebiz.net>aberman@thebiz.net</a><br><a href= > </a><br>Database web programmer and developer, fueled by peach snapple and mochas.[/sig]

 

[bigscouse]

Note my obvious error, both inserts were on tablea [sig]<p>Ged Jones<br><a href=mailto:gedejones@hotmail.com>gedejones@hotmail.com</a><br><a href= > </a><br> [/sig]

 

[Gennady]

Just guessing but have you tried: -
$sql = &quot;INSERT INTO tableA (name,address,fax,email) VALUES
('$name','$address','$fax','$email');
INSERT INTO tableA (name,address,fax,email) VALUES
('$name','$address','$fax','$email');commit;&quot;;

Ged Jones

It must work.One note - in the second &quot;INSERT...&quot; must be

INSERT INTO tableB (name,address,fax,email) VALUES
('$name','$address','$fax','$email');commit;&quot;;

Gennady

 

[MikeLacey]

Joep,

What database are you using? [sig]<p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br> [/sig]

 

[joep]

MySQL 3.22.32 with php3 installed.
(I didn't tested the other answers yet because i was out of office all day. Tomorrow i'll work with those. But if you have new information.. Please !!)
Greets,

Joep [sig][/sig]

 

[LordOfCode]

I have never heard about an instruction that can do an insert in two tables at time. When I need to do such a thing I just execute two Insert Into commands. If the second insert must be trigger for the first insert I just create a trigger. But if the second insert is just part of a bigger transaction a just build a store procedure. [sig]<p>Edwin Dalorzo<br><a href=mailto:edalorzo@hotmail.com>edalorzo@hotmail.com</a><br><a href= > </a><br> [/sig]

 

[joep]

Allright, that makes sence. But i don't understand some things (I'm dutch.... ha ha)
Can you give me a concrete answer?
I'll explane the need:
I've to INSERT var1, var2, var3 in tableA and var4 in tableB with the tableA_id = tableB_id. (These id's are not the auto_increment_id and primory but the 'connection_id')
Thanks in advance,

Joep [sig][/sig]

 

[MalcolmW]

It sounds like you need to UPDATE rather than INSERT. Does the record aleady exist (I presume since you have a &quot;connection_id&quot; for it, it must)? [sig][/sig]

 

[joep]

No and yes. It's either UPDATE or INSERT. That depends on what i want. The connection_id is made by INSERT or updated by UPDATE. I just want to be able to write information to two databases at one time.

Joep [sig][/sig]

 

[listener22]

Is TableA_id a table or a column? [sig]<p>Jim<br><a href= > </a><br>oracle, vb, some javascript[/sig]

 

[joep]

Column [sig][/sig]

 

[listener22]

Since it's a column, you shouldn't specify a
relation between the two tables when you do the insert;
just be sure that the same connection_id value is inserted
into both. Two separate inserts.

When you want to do an update on the two tables,
just be sure that you specify a where clause with TableA_id
for the update of table A, and a where clause with TableB_id
for the update of table B. Again, you should not specify
a relation between the two tables. Two separate updates. [sig]<p>Jim<br><a href= > </a><br>oracle, vb, some javascript[/sig]

 

[bigscouse]

joep,

No matter how many tables you can only enter one table in an INSERT or UPDATE command, however no matter how many individual INSERT or UPDATE commands you make they will not be applied until you type COMMIT; At this point the updates will all be done at the same time as they will be posted as one transaction to the database. [sig]<p>Ged Jones<br><a href=mailto:gedejones@hotmail.com>gedejones@hotmail.com</a><br><a href= > </a><br>Top man[/sig]

 

[joep]

Thanks to you all.

Joep [sig][/sig]