Multiple ???

JakesSA

can someone please explain to me in SQL scripting for mysql, how to update or insert to mulitiple tables using a comman ID.

eg $sql = "UPDATE user u, detalis d, SET u.username = "Joe",
u.password = "Soap",
d.firstname = "Joey"
d.lastname = "soap"
Where u.user_id = d.det_id AND
u.user_id ="13" ";

Im lost, everything looks cool....but it dont want to work.

tha_mink

Besides the missing comma after the
d.firstname = "Joey"
?

JakesSA

ok, I forgot the comma when I posted....but it still does'nt work. Is there anything else that you can see wrong with the script?

xblue

which mysql version are you running? according to the manual, updates that involve multiple tables only works from 4.0.4, and this is still marked as gamma release.

JakesSA

Im running 3.23.49. So how would I go about insert/update into multiple tables from a multiple page form.

I basically have a login area and customer details in two separate tables....and I use the comman ID to link the two together.

Thanks

Midloman

I ran into this one myself!
It's the quotes...

Try this, see if it doesn't improve things.
note: only first and last double quotes remain,
others have been changed to single quotes.
Commas inserted after 'Joey', and 'soap'.

eg $sql = "UPDATE user u, detalis d, SET u.username = 'Joe',
u.password = 'Soap',
d.firstname = 'Joey',
d.lastname = 'soap',
Where u.user_id = d.det_id AND
u.user_id ='13' ";

JakesSA

Are you sure it was a update or insert sql statement? The select statement works fine.

Is there another way to update/insert two tables at once?

Thanks

JakesSA

With the question above, how do I post into two tables with to seperate queries where the are linked by the same comman id.

I need to grab that last id, so that I can link it to the other table

eg $sql1 = " insert "----------> set id
$sql2 = " insert "----------> grab id from sql1 and link together

xblue

your update question: I don't think it is possible until mysql 4, so you'd probably have to use multiple statements.

your insert question: if the id in the first table is autoincrementing, you can use mysql_insert_id() to get its current value.

JakesSA

Do you have a script that can show me how to do this, because Ive tried every single possible way, but just cant seem to get it right.

🙁

thanks

daedlus

ummm, well, I'm not sure if this is relevant, but if it is a typo then it will SURELY affect how the code behaves: are you certain it's detalis and not details? In the mysql statement, right after users. Anyways, those were my thoughts.

JakesSA

Nope that not it!

JakesSA

Nope that not it!