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, May 27 '18 at 07:56
Sorry, Deleted my comment as It wasn't accurate, https://stackoverflow.com/questions/39139831 looks interesting — Scuzzy, May 27 '18 at 07: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, May 27 '18 at 07:58
Yeah, I've tried to run them one by one and both works perfect but not combined. — Nicklas Olofsson, May 27 '18 at 08:21

score 1 · Accepted Answer · answered May 27 '18 at 08:42

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 '18 at 08:42

u_mulder

54,101
5
48
64

Thanks, went for two querys and executed as you wrote with my statements. All good for now :) – Nicklas Olofsson May 27 '18 at 08:53

Update multiple tables in one SQL query

1 Answers1