Update multiple tables in one SQL query

Question

I've done some research and figured out that it's not possible to do through one query, but instead could for example be done through a transaction. But I can't get the SQL query to work properly. If I use the statements one by one they work all good.

What should I do?

The goal is to update two tables with information from one form.

$query = "

START TRANSACTION;

UPDATE projects

SET

projects.project_name = '".$mysqli->real_escape_string($_POST['project_name'])."',

projects.project_group = '".$mysqli->real_escape_string($_POST['project_group'])."',

projects.project_notes = '".$mysqli->real_escape_string($_POST['project_notes'])."',

projects.project_created = '".$mysqli->real_escape_string($_POST['project_created'])."',

projects.project_start = '".$mysqli->real_escape_string($_POST['project_start'])."',

projects.project_delivery  = '".$mysqli->real_escape_string($_POST['project_delivery'])."',

projects.project_orderdetails = '".$mysqli->real_escape_string($_POST['project_orderdetails'])."',

projects.project_owner = '".$mysqli->real_escape_string($_POST['project_owner'])."'

where projects.project_id = '".$mysqli->real_escape_string($_REQUEST['id'])."';

INSERT INTO hours

hours.userhours_id = '".$mysqli->real_escape_string($_POST['project_owner'])."',

hours.projecthours_id = '".$mysqli->real_escape_string($_REQUEST['id'])."',

hours.user_hours = '".$mysqli->real_escape_string($_POST['user_hours'])."'

where projects.project_id = '".$mysqli->real_escape_string($_REQUEST['id'])."';

COMMIT;

";

@Scuzzy I've done some joins also but since one is update and the other is insert will it still work? — Nicklas Olofsson
– Nicklas Olofsson, Commented May 27, 2018 at 7:56
Sorry, Deleted my comment as It wasn't accurate, stackoverflow.com/questions/39139831 looks interesting — Scuzzy
– Scuzzy, Commented May 27, 2018 at 7:57
No worries :) I've looked at that one before without success but will try to study it again and see what my error might be — Nicklas Olofsson
– Nicklas Olofsson, Commented May 27, 2018 at 7:58
Yeah, I've tried to run them one by one and both works perfect but not combined. — Nicklas Olofsson
– Nicklas Olofsson, Commented May 27, 2018 at 8:21

u_mulder · Accepted Answer · 2018-05-27 08:42:03Z

1

Mysqli has methods for working with transactions, for example http://php.net/manual/en/mysqli.begin-transaction.php.

Code can be:

$mysqli->begin_tansaction();
$mysqli->query('query1');
$mysqli->query('query2');
$mysqli->commit();

answered May 27, 2018 at 8:42

u_mulder

54.8k5 gold badges54 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nicklas Olofsson Over a year ago

Thanks, went for two querys and executed as you wrote with my statements. All good for now :)

Collectives™ on Stack Overflow

Update multiple tables in one SQL query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related