mysqli_begin_transaction correct usage
0
I am wanting to some transactional mysql in my PHP, I have had a look at the PHP docs and there are a couple of functions I need to use,
mysqli_begin_transaction
mysqli_rollback
mysqli_commit
My code currently looks like this,
mysqli_begin_transaction($db_link, MYSQLI_TRANS_START_READ_WRITE);
$sql = "SELECT * FROM table";
$result = mysqli_query($sql);
if(!$result)
$rollback = true;
$sql = "SELECT * FROM another";
$result = mysqli_query($sql);
if(!$result)
$rollback = true;
$sql = "DELETE FROM table_name WHERE condition;"
mysqli_query($sql);
if(mysqli_affected_rows($db_link) < 0)
$rollback = true;
if($rollback)
mysqli_rollback($db_link)
else
mysqli_commit($db_link)
This is very rough pseudo code, but my question is that the transaction function all return values according to the php documentation so should I be wrapping them in conditional statements and throwing an exception of something similar if they dont return true.
php mysql mysqli transactions
edited Nov 15 '18 at 15:04
Funk Forty Niner
1
asked Nov 15 '18 at 14:23
UddersUdders
2,8291873133
|
show 3 more comments
0
I am wanting to some transactional mysql in my PHP, I have had a look at the PHP docs and there are a couple of functions I need to use,
mysqli_begin_transaction
mysqli_rollback
mysqli_commit
My code currently looks like this,
mysqli_begin_transaction($db_link, MYSQLI_TRANS_START_READ_WRITE);
$sql = "SELECT * FROM table";
$result = mysqli_query($sql);
if(!$result)
$rollback = true;
$sql = "SELECT * FROM another";
$result = mysqli_query($sql);
if(!$result)
$rollback = true;
$sql = "DELETE FROM table_name WHERE condition;"
mysqli_query($sql);
if(mysqli_affected_rows($db_link) < 0)
$rollback = true;
if($rollback)
mysqli_rollback($db_link)
else
mysqli_commit($db_link)
This is very rough pseudo code, but my question is that the transaction function all return values according to the php documentation so should I be wrapping them in conditional statements and throwing an exception of something similar if they dont return true.
php mysql mysqli transactions
edited Nov 15 '18 at 15:04
Funk Forty Niner
1
asked Nov 15 '18 at 14:23
UddersUdders
2,8291873133
2
WHY are you setting$rollback = true;after a failed SELECT, nothing is changed by a select
– RiggsFolly
Nov 15 '18 at 14:26
1
Maybe your actual code is significantly different (in which case, you should really be showing that), but what you have here really isn't the best use-case for transactions.
– Patrick Q
Nov 15 '18 at 14:28
Please also take a look at secure.php.net/manual/en/mysqli.autocommit.php
– Lithilion
Nov 15 '18 at 14:30
3
You would normally use a transaction to maintain database integrity, which normally means you would be updating more than one table, in for example a parent-child relationship. So for example Create Invoice, create 3 invoice line items. You wrap all 4 inserts in a transaction to make sure that if one insert fails, you dont end up with a Invoice and only one line item
– RiggsFolly
Nov 15 '18 at 14:31
2
Basically, this senario is NOT a good example of when, why and how you would use transactions
– RiggsFolly
Nov 15 '18 at 14:45
|
show 3 more comments
0
0
0
I am wanting to some transactional mysql in my PHP, I have had a look at the PHP docs and there are a couple of functions I need to use,
mysqli_begin_transaction
mysqli_rollback
mysqli_commit
My code currently looks like this,
mysqli_begin_transaction($db_link, MYSQLI_TRANS_START_READ_WRITE);
$sql = "SELECT * FROM table";
$result = mysqli_query($sql);
if(!$result)
$rollback = true;
$sql = "SELECT * FROM another";
$result = mysqli_query($sql);
if(!$result)
$rollback = true;
$sql = "DELETE FROM table_name WHERE condition;"
mysqli_query($sql);
if(mysqli_affected_rows($db_link) < 0)
$rollback = true;
if($rollback)
mysqli_rollback($db_link)
else
mysqli_commit($db_link)
This is very rough pseudo code, but my question is that the transaction function all return values according to the php documentation so should I be wrapping them in conditional statements and throwing an exception of something similar if they dont return true.
php mysql mysqli transactions
edited Nov 15 '18 at 15:04
Funk Forty Niner
1
asked Nov 15 '18 at 14:23
UddersUdders
2,8291873133
I am wanting to some transactional mysql in my PHP, I have had a look at the PHP docs and there are a couple of functions I need to use,
mysqli_begin_transaction
mysqli_rollback
mysqli_commit
My code currently looks like this,
mysqli_begin_transaction($db_link, MYSQLI_TRANS_START_READ_WRITE);
$sql = "SELECT * FROM table";
$result = mysqli_query($sql);
if(!$result)
$rollback = true;
$sql = "SELECT * FROM another";
$result = mysqli_query($sql);
if(!$result)
$rollback = true;
$sql = "DELETE FROM table_name WHERE condition;"
mysqli_query($sql);
if(mysqli_affected_rows($db_link) < 0)
$rollback = true;
if($rollback)
mysqli_rollback($db_link)
else
mysqli_commit($db_link)
This is very rough pseudo code, but my question is that the transaction function all return values according to the php documentation so should I be wrapping them in conditional statements and throwing an exception of something similar if they dont return true.
php mysql mysqli transactions
php mysql mysqli transactions
edited Nov 15 '18 at 15:04
Funk Forty Niner
1
asked Nov 15 '18 at 14:23
UddersUdders
2,8291873133
edited Nov 15 '18 at 15:04
Funk Forty Niner
1
asked Nov 15 '18 at 14:23
UddersUdders
2,8291873133
edited Nov 15 '18 at 15:04
Funk Forty Niner
1
edited Nov 15 '18 at 15:04
Funk Forty Niner
1
edited Nov 15 '18 at 15:04
Funk Forty Niner
1
1
asked Nov 15 '18 at 14:23
UddersUdders
2,8291873133
asked Nov 15 '18 at 14:23
UddersUdders
2,8291873133
asked Nov 15 '18 at 14:23
UddersUdders
2,8291873133
2,8291873133
2
WHY are you setting$rollback = true;after a failed SELECT, nothing is changed by a select
– RiggsFolly
Nov 15 '18 at 14:26
1
Maybe your actual code is significantly different (in which case, you should really be showing that), but what you have here really isn't the best use-case for transactions.
– Patrick Q
Nov 15 '18 at 14:28
Please also take a look at secure.php.net/manual/en/mysqli.autocommit.php
– Lithilion
Nov 15 '18 at 14:30
3
You would normally use a transaction to maintain database integrity, which normally means you would be updating more than one table, in for example a parent-child relationship. So for example Create Invoice, create 3 invoice line items. You wrap all 4 inserts in a transaction to make sure that if one insert fails, you dont end up with a Invoice and only one line item
– RiggsFolly
Nov 15 '18 at 14:31
2
Basically, this senario is NOT a good example of when, why and how you would use transactions
– RiggsFolly
Nov 15 '18 at 14:45
|
show 3 more comments
2
WHY are you setting$rollback = true;after a failed SELECT, nothing is changed by a select
– RiggsFolly
Nov 15 '18 at 14:26
1
Maybe your actual code is significantly different (in which case, you should really be showing that), but what you have here really isn't the best use-case for transactions.
– Patrick Q
Nov 15 '18 at 14:28
Please also take a look at secure.php.net/manual/en/mysqli.autocommit.php
– Lithilion
Nov 15 '18 at 14:30
3
You would normally use a transaction to maintain database integrity, which normally means you would be updating more than one table, in for example a parent-child relationship. So for example Create Invoice, create 3 invoice line items. You wrap all 4 inserts in a transaction to make sure that if one insert fails, you dont end up with a Invoice and only one line item
– RiggsFolly
Nov 15 '18 at 14:31
2
Basically, this senario is NOT a good example of when, why and how you would use transactions
– RiggsFolly
Nov 15 '18 at 14:45
2
2
WHY are you setting
$rollback = true; after a failed SELECT, nothing is changed by a select– RiggsFolly
Nov 15 '18 at 14:26
WHY are you setting
$rollback = true; after a failed SELECT, nothing is changed by a select– RiggsFolly
Nov 15 '18 at 14:26
1
1
Maybe your actual code is significantly different (in which case, you should really be showing that), but what you have here really isn't the best use-case for transactions.
– Patrick Q
Nov 15 '18 at 14:28
Maybe your actual code is significantly different (in which case, you should really be showing that), but what you have here really isn't the best use-case for transactions.
– Patrick Q
Nov 15 '18 at 14:28
Please also take a look at secure.php.net/manual/en/mysqli.autocommit.php
– Lithilion
Nov 15 '18 at 14:30
Please also take a look at secure.php.net/manual/en/mysqli.autocommit.php
– Lithilion
Nov 15 '18 at 14:30
3
3
You would normally use a transaction to maintain database integrity, which normally means you would be updating more than one table, in for example a parent-child relationship. So for example Create Invoice, create 3 invoice line items. You wrap all 4 inserts in a transaction to make sure that if one insert fails, you dont end up with a Invoice and only one line item
– RiggsFolly
Nov 15 '18 at 14:31
You would normally use a transaction to maintain database integrity, which normally means you would be updating more than one table, in for example a parent-child relationship. So for example Create Invoice, create 3 invoice line items. You wrap all 4 inserts in a transaction to make sure that if one insert fails, you dont end up with a Invoice and only one line item
– RiggsFolly
Nov 15 '18 at 14:31
2
2
Basically, this senario is NOT a good example of when, why and how you would use transactions
– RiggsFolly
Nov 15 '18 at 14:45
Basically, this senario is NOT a good example of when, why and how you would use transactions
– RiggsFolly
Nov 15 '18 at 14:45
|
show 3 more comments
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53321548%2fmysqli-begin-transaction-correct-usage%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53321548%2fmysqli-begin-transaction-correct-usage%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
WHY are you setting
$rollback = true;after a failed SELECT, nothing is changed by a select– RiggsFolly
Nov 15 '18 at 14:26
1
Maybe your actual code is significantly different (in which case, you should really be showing that), but what you have here really isn't the best use-case for transactions.
– Patrick Q
Nov 15 '18 at 14:28
Please also take a look at secure.php.net/manual/en/mysqli.autocommit.php
– Lithilion
Nov 15 '18 at 14:30
3
You would normally use a transaction to maintain database integrity, which normally means you would be updating more than one table, in for example a parent-child relationship. So for example Create Invoice, create 3 invoice line items. You wrap all 4 inserts in a transaction to make sure that if one insert fails, you dont end up with a Invoice and only one line item
– RiggsFolly
Nov 15 '18 at 14:31
2
Basically, this senario is NOT a good example of when, why and how you would use transactions
– RiggsFolly
Nov 15 '18 at 14:45