WHERE in INSERT MySQl & PHP
up vote
0
down vote
favorite
In MySql is possible to make UPDATE using WHERE in for multiple users like:
$users = ['user1', 'user2', 'user3'];
$amount = 25;
$users = implode(", ",array_keys($users));
"UPDATE balances SET balance = (balance + $amount) WHERE obj_id IN ($users)"
The question is: Is possible to make a WHERE IN insert similar to the example from bellow without using foreach loops before query?
"INSERT INTO balances ('balance', 'user_id') VALUES (($amount), user_id) WHERE user_id IN ($users)"
php mysql
asked Nov 28 at 11:11
Alex Statnii
31
migrated from superuser.com Nov 28 at 17:02
This question came from our site for computer enthusiasts and power users.
add a comment |
up vote
0
down vote
favorite
In MySql is possible to make UPDATE using WHERE in for multiple users like:
$users = ['user1', 'user2', 'user3'];
$amount = 25;
$users = implode(", ",array_keys($users));
"UPDATE balances SET balance = (balance + $amount) WHERE obj_id IN ($users)"
The question is: Is possible to make a WHERE IN insert similar to the example from bellow without using foreach loops before query?
"INSERT INTO balances ('balance', 'user_id') VALUES (($amount), user_id) WHERE user_id IN ($users)"
php mysql
asked Nov 28 at 11:11
Alex Statnii
31
migrated from superuser.com Nov 28 at 17:02
This question came from our site for computer enthusiasts and power users.
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
In MySql is possible to make UPDATE using WHERE in for multiple users like:
$users = ['user1', 'user2', 'user3'];
$amount = 25;
$users = implode(", ",array_keys($users));
"UPDATE balances SET balance = (balance + $amount) WHERE obj_id IN ($users)"
The question is: Is possible to make a WHERE IN insert similar to the example from bellow without using foreach loops before query?
"INSERT INTO balances ('balance', 'user_id') VALUES (($amount), user_id) WHERE user_id IN ($users)"
php mysql
asked Nov 28 at 11:11
Alex Statnii
31
In MySql is possible to make UPDATE using WHERE in for multiple users like:
$users = ['user1', 'user2', 'user3'];
$amount = 25;
$users = implode(", ",array_keys($users));
"UPDATE balances SET balance = (balance + $amount) WHERE obj_id IN ($users)"
The question is: Is possible to make a WHERE IN insert similar to the example from bellow without using foreach loops before query?
"INSERT INTO balances ('balance', 'user_id') VALUES (($amount), user_id) WHERE user_id IN ($users)"
php mysql
php mysql
asked Nov 28 at 11:11
Alex Statnii
31
asked Nov 28 at 11:11
Alex Statnii
31
asked Nov 28 at 11:11
Alex Statnii
31
asked Nov 28 at 11:11
Alex Statnii
31
asked Nov 28 at 11:11
Alex Statnii
31
31
migrated from superuser.com Nov 28 at 17:02
This question came from our site for computer enthusiasts and power users.
migrated from superuser.com Nov 28 at 17:02
This question came from our site for computer enthusiasts and power users.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
MySQL does not support this, but assuming you are using PHP (you did not specify this, but your code looks like php), the workaround is fairly straight forward.
Also, keep in mind, INSERT INTO creates new rows, so WHERE does not apply. It adds to the bottom, and WHERE and ORDER BY can be used when retrieving the rows to sort the data.
You loop each value of the array, iterate it, and execute a query per iteration.
Your code would be something like this:
<?php
//create array
$user_ids = array('User1', 'User2', 'User3');
$amount = 25;
// go through each value in the array and execute the query
foreach( $user_ids as $userid )
{
mysqli_query "INSERT INTO `balances` ('balance', 'user_id')
VALUES (($amount), user_id)";
}
?>
If you want to limit it to just one query, you can do the following:
<?php
//create array
$user_ids = array('User1', 'User2', 'User3');
$amount = 25;
// go through each value in the array and prepare the insert
$values = 'VALUES';
foreach( $user_ids as $userid )
{
$values .= '(' . $amount . ',' . $userid .'),';
}
$values = substr($values,0,-1);
mysqli_query "INSERT INTO `balances` ('balance', 'user_id') " . $values;
?>
answered Nov 28 at 12:13
LPChip
2031618
Wouldn't using theIN ($userid)add unnecessary overhead where MySQL is going to look into a one element array every time? Could the script just beWHERE user_id = '$userid'at the end?
– Kinnectus
Nov 28 at 12:21
@Kinnectus Ah, yeah... I missed that part when copying the query. Fixed. Actually, the entire Where statement is not necessary, given that it is an INSERT. it inserts into the database, so where does not even apply.
– LPChip
Nov 28 at 12:22
Oh yeah, good call! Should re-read the question :D
– Kinnectus
Nov 28 at 12:27
@LPChip Second case is the case I was looking for, thank you.
– Alex Statnii
Nov 28 at 12:42
I thought you were the more I was thinking of this. :) You're welcome. Feel free to mark it solved so others know you no longer need help. :)
– LPChip
Nov 28 at 12:42
|
show 2 more comments
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',
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
});
}
});
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%2f53524585%2fwhere-in-insert-mysql-php%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
MySQL does not support this, but assuming you are using PHP (you did not specify this, but your code looks like php), the workaround is fairly straight forward.
Also, keep in mind, INSERT INTO creates new rows, so WHERE does not apply. It adds to the bottom, and WHERE and ORDER BY can be used when retrieving the rows to sort the data.
You loop each value of the array, iterate it, and execute a query per iteration.
Your code would be something like this:
<?php
//create array
$user_ids = array('User1', 'User2', 'User3');
$amount = 25;
// go through each value in the array and execute the query
foreach( $user_ids as $userid )
{
mysqli_query "INSERT INTO `balances` ('balance', 'user_id')
VALUES (($amount), user_id)";
}
?>
If you want to limit it to just one query, you can do the following:
<?php
//create array
$user_ids = array('User1', 'User2', 'User3');
$amount = 25;
// go through each value in the array and prepare the insert
$values = 'VALUES';
foreach( $user_ids as $userid )
{
$values .= '(' . $amount . ',' . $userid .'),';
}
$values = substr($values,0,-1);
mysqli_query "INSERT INTO `balances` ('balance', 'user_id') " . $values;
?>
answered Nov 28 at 12:13
LPChip
2031618
Wouldn't using theIN ($userid)add unnecessary overhead where MySQL is going to look into a one element array every time? Could the script just beWHERE user_id = '$userid'at the end?
– Kinnectus
Nov 28 at 12:21
@Kinnectus Ah, yeah... I missed that part when copying the query. Fixed. Actually, the entire Where statement is not necessary, given that it is an INSERT. it inserts into the database, so where does not even apply.
– LPChip
Nov 28 at 12:22
Oh yeah, good call! Should re-read the question :D
– Kinnectus
Nov 28 at 12:27
@LPChip Second case is the case I was looking for, thank you.
– Alex Statnii
Nov 28 at 12:42
I thought you were the more I was thinking of this. :) You're welcome. Feel free to mark it solved so others know you no longer need help. :)
– LPChip
Nov 28 at 12:42
|
show 2 more comments
up vote
1
down vote
accepted
MySQL does not support this, but assuming you are using PHP (you did not specify this, but your code looks like php), the workaround is fairly straight forward.
Also, keep in mind, INSERT INTO creates new rows, so WHERE does not apply. It adds to the bottom, and WHERE and ORDER BY can be used when retrieving the rows to sort the data.
You loop each value of the array, iterate it, and execute a query per iteration.
Your code would be something like this:
<?php
//create array
$user_ids = array('User1', 'User2', 'User3');
$amount = 25;
// go through each value in the array and execute the query
foreach( $user_ids as $userid )
{
mysqli_query "INSERT INTO `balances` ('balance', 'user_id')
VALUES (($amount), user_id)";
}
?>
If you want to limit it to just one query, you can do the following:
<?php
//create array
$user_ids = array('User1', 'User2', 'User3');
$amount = 25;
// go through each value in the array and prepare the insert
$values = 'VALUES';
foreach( $user_ids as $userid )
{
$values .= '(' . $amount . ',' . $userid .'),';
}
$values = substr($values,0,-1);
mysqli_query "INSERT INTO `balances` ('balance', 'user_id') " . $values;
?>
answered Nov 28 at 12:13
LPChip
2031618
Wouldn't using theIN ($userid)add unnecessary overhead where MySQL is going to look into a one element array every time? Could the script just beWHERE user_id = '$userid'at the end?
– Kinnectus
Nov 28 at 12:21
@Kinnectus Ah, yeah... I missed that part when copying the query. Fixed. Actually, the entire Where statement is not necessary, given that it is an INSERT. it inserts into the database, so where does not even apply.
– LPChip
Nov 28 at 12:22
Oh yeah, good call! Should re-read the question :D
– Kinnectus
Nov 28 at 12:27
@LPChip Second case is the case I was looking for, thank you.
– Alex Statnii
Nov 28 at 12:42
I thought you were the more I was thinking of this. :) You're welcome. Feel free to mark it solved so others know you no longer need help. :)
– LPChip
Nov 28 at 12:42
|
show 2 more comments
up vote
1
down vote
accepted
up vote
1
down vote
accepted
MySQL does not support this, but assuming you are using PHP (you did not specify this, but your code looks like php), the workaround is fairly straight forward.
Also, keep in mind, INSERT INTO creates new rows, so WHERE does not apply. It adds to the bottom, and WHERE and ORDER BY can be used when retrieving the rows to sort the data.
You loop each value of the array, iterate it, and execute a query per iteration.
Your code would be something like this:
<?php
//create array
$user_ids = array('User1', 'User2', 'User3');
$amount = 25;
// go through each value in the array and execute the query
foreach( $user_ids as $userid )
{
mysqli_query "INSERT INTO `balances` ('balance', 'user_id')
VALUES (($amount), user_id)";
}
?>
If you want to limit it to just one query, you can do the following:
<?php
//create array
$user_ids = array('User1', 'User2', 'User3');
$amount = 25;
// go through each value in the array and prepare the insert
$values = 'VALUES';
foreach( $user_ids as $userid )
{
$values .= '(' . $amount . ',' . $userid .'),';
}
$values = substr($values,0,-1);
mysqli_query "INSERT INTO `balances` ('balance', 'user_id') " . $values;
?>
answered Nov 28 at 12:13
LPChip
2031618
MySQL does not support this, but assuming you are using PHP (you did not specify this, but your code looks like php), the workaround is fairly straight forward.
Also, keep in mind, INSERT INTO creates new rows, so WHERE does not apply. It adds to the bottom, and WHERE and ORDER BY can be used when retrieving the rows to sort the data.
You loop each value of the array, iterate it, and execute a query per iteration.
Your code would be something like this:
<?php
//create array
$user_ids = array('User1', 'User2', 'User3');
$amount = 25;
// go through each value in the array and execute the query
foreach( $user_ids as $userid )
{
mysqli_query "INSERT INTO `balances` ('balance', 'user_id')
VALUES (($amount), user_id)";
}
?>
If you want to limit it to just one query, you can do the following:
<?php
//create array
$user_ids = array('User1', 'User2', 'User3');
$amount = 25;
// go through each value in the array and prepare the insert
$values = 'VALUES';
foreach( $user_ids as $userid )
{
$values .= '(' . $amount . ',' . $userid .'),';
}
$values = substr($values,0,-1);
mysqli_query "INSERT INTO `balances` ('balance', 'user_id') " . $values;
?>
answered Nov 28 at 12:13
LPChip
2031618
answered Nov 28 at 12:13
LPChip
2031618
answered Nov 28 at 12:13
LPChip
2031618
answered Nov 28 at 12:13
LPChip
2031618
2031618
Wouldn't using theIN ($userid)add unnecessary overhead where MySQL is going to look into a one element array every time? Could the script just beWHERE user_id = '$userid'at the end?
– Kinnectus
Nov 28 at 12:21
@Kinnectus Ah, yeah... I missed that part when copying the query. Fixed. Actually, the entire Where statement is not necessary, given that it is an INSERT. it inserts into the database, so where does not even apply.
– LPChip
Nov 28 at 12:22
Oh yeah, good call! Should re-read the question :D
– Kinnectus
Nov 28 at 12:27
@LPChip Second case is the case I was looking for, thank you.
– Alex Statnii
Nov 28 at 12:42
I thought you were the more I was thinking of this. :) You're welcome. Feel free to mark it solved so others know you no longer need help. :)
– LPChip
Nov 28 at 12:42
|
show 2 more comments
Wouldn't using theIN ($userid)add unnecessary overhead where MySQL is going to look into a one element array every time? Could the script just beWHERE user_id = '$userid'at the end?
– Kinnectus
Nov 28 at 12:21
@Kinnectus Ah, yeah... I missed that part when copying the query. Fixed. Actually, the entire Where statement is not necessary, given that it is an INSERT. it inserts into the database, so where does not even apply.
– LPChip
Nov 28 at 12:22
Oh yeah, good call! Should re-read the question :D
– Kinnectus
Nov 28 at 12:27
@LPChip Second case is the case I was looking for, thank you.
– Alex Statnii
Nov 28 at 12:42
I thought you were the more I was thinking of this. :) You're welcome. Feel free to mark it solved so others know you no longer need help. :)
– LPChip
Nov 28 at 12:42
Wouldn't using the
IN ($userid) add unnecessary overhead where MySQL is going to look into a one element array every time? Could the script just be WHERE user_id = '$userid' at the end?– Kinnectus
Nov 28 at 12:21
Wouldn't using the
IN ($userid) add unnecessary overhead where MySQL is going to look into a one element array every time? Could the script just be WHERE user_id = '$userid' at the end?– Kinnectus
Nov 28 at 12:21
@Kinnectus Ah, yeah... I missed that part when copying the query. Fixed. Actually, the entire Where statement is not necessary, given that it is an INSERT. it inserts into the database, so where does not even apply.
– LPChip
Nov 28 at 12:22
@Kinnectus Ah, yeah... I missed that part when copying the query. Fixed. Actually, the entire Where statement is not necessary, given that it is an INSERT. it inserts into the database, so where does not even apply.
– LPChip
Nov 28 at 12:22
Oh yeah, good call! Should re-read the question :D
– Kinnectus
Nov 28 at 12:27
Oh yeah, good call! Should re-read the question :D
– Kinnectus
Nov 28 at 12:27
@LPChip Second case is the case I was looking for, thank you.
– Alex Statnii
Nov 28 at 12:42
@LPChip Second case is the case I was looking for, thank you.
– Alex Statnii
Nov 28 at 12:42
I thought you were the more I was thinking of this. :) You're welcome. Feel free to mark it solved so others know you no longer need help. :)
– LPChip
Nov 28 at 12:42
I thought you were the more I was thinking of this. :) You're welcome. Feel free to mark it solved so others know you no longer need help. :)
– LPChip
Nov 28 at 12:42
|
show 2 more comments
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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.
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%2f53524585%2fwhere-in-insert-mysql-php%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
