update a table where id is inside a string [duplicate]

This question already has an answer here:

Can I bind an array to an IN() condition?

21 answers

Mysql search with comma delimited string

3 answers

I want to update a table where id is inside a comma separated string.

$str = "1,2,3,4,5,6,7,8,9,10,11";

$sql = "update abc set status = 'home' where id in '" . $str . "'";

$st = $db->prepare($sql);

$st->execute();

Also tried:

$sql = "update abc set status = 'home' where id in " . $str;

In both cases I'm getting a syntax error.

Any help?

asked Nov 21 '18 at 6:42

puerto

968511

marked as duplicate by Shadow mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.

StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 21 '18 at 6:53

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

str is not an array, it's a string.

– puerto
Nov 21 '18 at 6:55

Try $sql = "update abc set status = 'home' where instr(',". $str.",', ','+CAST(id AS VARCHAR(10))+',') > 0";. The idea is to search a string like :1:11:111:1111: for a substring like :11:. By wrapping at the front and at the end with the separator, you are guarantee to not find false positive.

– Dominique Fortin
Nov 21 '18 at 7:14

1

@Shadow The two question might look similar, but they have different type of data. It might be possible to transform a string into an array, but it very inefficient and therefore requires a different solution. It's like fitting a circle into a square.

– Dominique Fortin
Nov 21 '18 at 7:21

@DominiqueFortin in this particular case using a string as an input means that only string interpolation can be used, which would open up the code to sql injection.

– Shadow
Nov 21 '18 at 8:10

@Shadow this question is a better dupe reference instead: stackoverflow.com/questions/6897176/…

– Madhur Bhaiya
Nov 21 '18 at 10:31

add a comment |

This question already has an answer here:

Can I bind an array to an IN() condition?

21 answers

Mysql search with comma delimited string

3 answers

I want to update a table where id is inside a comma separated string.

$str = "1,2,3,4,5,6,7,8,9,10,11";

$sql = "update abc set status = 'home' where id in '" . $str . "'";

$st = $db->prepare($sql);

$st->execute();

Also tried:

$sql = "update abc set status = 'home' where id in " . $str;

In both cases I'm getting a syntax error.

Any help?

asked Nov 21 '18 at 6:42

puerto

968511

marked as duplicate by Shadow mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.

StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 21 '18 at 6:53

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

str is not an array, it's a string.

– puerto
Nov 21 '18 at 6:55

Try $sql = "update abc set status = 'home' where instr(',". $str.",', ','+CAST(id AS VARCHAR(10))+',') > 0";. The idea is to search a string like :1:11:111:1111: for a substring like :11:. By wrapping at the front and at the end with the separator, you are guarantee to not find false positive.

– Dominique Fortin
Nov 21 '18 at 7:14

1

@Shadow The two question might look similar, but they have different type of data. It might be possible to transform a string into an array, but it very inefficient and therefore requires a different solution. It's like fitting a circle into a square.

– Dominique Fortin
Nov 21 '18 at 7:21

@DominiqueFortin in this particular case using a string as an input means that only string interpolation can be used, which would open up the code to sql injection.

– Shadow
Nov 21 '18 at 8:10

@Shadow this question is a better dupe reference instead: stackoverflow.com/questions/6897176/…

– Madhur Bhaiya
Nov 21 '18 at 10:31

add a comment |

This question already has an answer here:

Can I bind an array to an IN() condition?

21 answers

Mysql search with comma delimited string

3 answers

I want to update a table where id is inside a comma separated string.

$str = "1,2,3,4,5,6,7,8,9,10,11";

$sql = "update abc set status = 'home' where id in '" . $str . "'";

$st = $db->prepare($sql);

$st->execute();

Also tried:

$sql = "update abc set status = 'home' where id in " . $str;

In both cases I'm getting a syntax error.

Any help?

asked Nov 21 '18 at 6:42

puerto

968511

This question already has an answer here:

Can I bind an array to an IN() condition?

21 answers

Mysql search with comma delimited string

3 answers

I want to update a table where id is inside a comma separated string.

$str = "1,2,3,4,5,6,7,8,9,10,11";

$sql = "update abc set status = 'home' where id in '" . $str . "'";

$st = $db->prepare($sql);

$st->execute();

Also tried:

$sql = "update abc set status = 'home' where id in " . $str;

In both cases I'm getting a syntax error.

Any help?

This question already has an answer here:

Can I bind an array to an IN() condition?

21 answers

Mysql search with comma delimited string

3 answers

php mysql

asked Nov 21 '18 at 6:42

puerto

968511

asked Nov 21 '18 at 6:42

puerto

968511

asked Nov 21 '18 at 6:42

puerto

968511

asked Nov 21 '18 at 6:42

puerto

968511

asked Nov 21 '18 at 6:42

puerto

968511

marked as duplicate by Shadow mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.

StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 21 '18 at 6:53

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

marked as duplicate by Shadow mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.

StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 21 '18 at 6:53

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

str is not an array, it's a string.

– puerto
Nov 21 '18 at 6:55

Try $sql = "update abc set status = 'home' where instr(',". $str.",', ','+CAST(id AS VARCHAR(10))+',') > 0";. The idea is to search a string like :1:11:111:1111: for a substring like :11:. By wrapping at the front and at the end with the separator, you are guarantee to not find false positive.

– Dominique Fortin
Nov 21 '18 at 7:14

1

@Shadow The two question might look similar, but they have different type of data. It might be possible to transform a string into an array, but it very inefficient and therefore requires a different solution. It's like fitting a circle into a square.

– Dominique Fortin
Nov 21 '18 at 7:21

@DominiqueFortin in this particular case using a string as an input means that only string interpolation can be used, which would open up the code to sql injection.

– Shadow
Nov 21 '18 at 8:10

@Shadow this question is a better dupe reference instead: stackoverflow.com/questions/6897176/…

– Madhur Bhaiya
Nov 21 '18 at 10:31

add a comment |

str is not an array, it's a string.

– puerto
Nov 21 '18 at 6:55

Try $sql = "update abc set status = 'home' where instr(',". $str.",', ','+CAST(id AS VARCHAR(10))+',') > 0";. The idea is to search a string like :1:11:111:1111: for a substring like :11:. By wrapping at the front and at the end with the separator, you are guarantee to not find false positive.

– Dominique Fortin
Nov 21 '18 at 7:14

1

@Shadow The two question might look similar, but they have different type of data. It might be possible to transform a string into an array, but it very inefficient and therefore requires a different solution. It's like fitting a circle into a square.

– Dominique Fortin
Nov 21 '18 at 7:21

@DominiqueFortin in this particular case using a string as an input means that only string interpolation can be used, which would open up the code to sql injection.

– Shadow
Nov 21 '18 at 8:10

@Shadow this question is a better dupe reference instead: stackoverflow.com/questions/6897176/…

– Madhur Bhaiya
Nov 21 '18 at 10:31

str is not an array, it's a string.

– puerto
Nov 21 '18 at 6:55

Try $sql = "update abc set status = 'home' where instr(',". $str.",', ','+CAST(id AS VARCHAR(10))+',') > 0";. The idea is to search a string like :1:11:111:1111: for a substring like :11:. By wrapping at the front and at the end with the separator, you are guarantee to not find false positive.

– Dominique Fortin
Nov 21 '18 at 7:14

@Shadow The two question might look similar, but they have different type of data. It might be possible to transform a string into an array, but it very inefficient and therefore requires a different solution. It's like fitting a circle into a square.

– Dominique Fortin
Nov 21 '18 at 7:21

@DominiqueFortin in this particular case using a string as an input means that only string interpolation can be used, which would open up the code to sql injection.

– Shadow
Nov 21 '18 at 8:10

@Shadow this question is a better dupe reference instead: stackoverflow.com/questions/6897176/…

– Madhur Bhaiya
Nov 21 '18 at 10:31

add a comment |

2 Answers
2

active

oldest

votes

You need parentheses when using IN (...):

$str = "1,2,3,4,5,6,7,8,9,10,11";

$sql = "update abc set status = 'home' where id in (" . $str . ")";

$st = $db->prepare($sql);

$st->execute();

Also, this is not the way you effectively use Parametrized queries. You need to use placeholders properly.

$str = "1,2,3,4,5,6,7,8,9,10,11";

$placeholder_str = "?,?,?,?,?,?,?,?,?,?,?";

$sql = "update abc set status = 'home' where id in (" . $placeholder_str . ")";

$st = $db->prepare($sql);

$st->execute(explode(',', $str)); // assuming you are using PDO

edited Nov 21 '18 at 7:06

answered Nov 21 '18 at 6:44

Madhur Bhaiya

19.6k62236

about the query - I don't know in advance what the string will be like, so can't write a certain number of ?.

– puerto
Nov 21 '18 at 6:50

@puerto you can dynamically generate ? in a loop, using the count of values dynamically

– Madhur Bhaiya
Nov 21 '18 at 6:51

could you show me, pls

– puerto
Nov 21 '18 at 6:52

1

The first solution is completely wrong. And again, this question has been asked before...

– Shadow
Nov 21 '18 at 6:53

@puerto see the solutions in the duplicate topic for dynamically generating the placeholders.

– Shadow
Nov 21 '18 at 6:54

|
show 3 more comments

-1

Try this
$sql = " update abc set status = 'home' where instr($str, id) > 0 ";

answered Nov 21 '18 at 6:51

Krishna

402518

add a comment |

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

You need parentheses when using IN (...):

$str = "1,2,3,4,5,6,7,8,9,10,11";

$sql = "update abc set status = 'home' where id in (" . $str . ")";

$st = $db->prepare($sql);

$st->execute();

Also, this is not the way you effectively use Parametrized queries. You need to use placeholders properly.

$str = "1,2,3,4,5,6,7,8,9,10,11";

$placeholder_str = "?,?,?,?,?,?,?,?,?,?,?";

$sql = "update abc set status = 'home' where id in (" . $placeholder_str . ")";

$st = $db->prepare($sql);

$st->execute(explode(',', $str)); // assuming you are using PDO

edited Nov 21 '18 at 7:06

answered Nov 21 '18 at 6:44

Madhur Bhaiya

19.6k62236

about the query - I don't know in advance what the string will be like, so can't write a certain number of ?.

– puerto
Nov 21 '18 at 6:50

@puerto you can dynamically generate ? in a loop, using the count of values dynamically

– Madhur Bhaiya
Nov 21 '18 at 6:51

could you show me, pls

– puerto
Nov 21 '18 at 6:52

1

The first solution is completely wrong. And again, this question has been asked before...

– Shadow
Nov 21 '18 at 6:53

@puerto see the solutions in the duplicate topic for dynamically generating the placeholders.

– Shadow
Nov 21 '18 at 6:54

|
show 3 more comments

You need parentheses when using IN (...):

$str = "1,2,3,4,5,6,7,8,9,10,11";

$sql = "update abc set status = 'home' where id in (" . $str . ")";

$st = $db->prepare($sql);

$st->execute();

Also, this is not the way you effectively use Parametrized queries. You need to use placeholders properly.

$str = "1,2,3,4,5,6,7,8,9,10,11";

$placeholder_str = "?,?,?,?,?,?,?,?,?,?,?";

$sql = "update abc set status = 'home' where id in (" . $placeholder_str . ")";

$st = $db->prepare($sql);

$st->execute(explode(',', $str)); // assuming you are using PDO

edited Nov 21 '18 at 7:06

answered Nov 21 '18 at 6:44

Madhur Bhaiya

19.6k62236

about the query - I don't know in advance what the string will be like, so can't write a certain number of ?.

– puerto
Nov 21 '18 at 6:50

@puerto you can dynamically generate ? in a loop, using the count of values dynamically

– Madhur Bhaiya
Nov 21 '18 at 6:51

could you show me, pls

– puerto
Nov 21 '18 at 6:52

1

The first solution is completely wrong. And again, this question has been asked before...

– Shadow
Nov 21 '18 at 6:53

@puerto see the solutions in the duplicate topic for dynamically generating the placeholders.

– Shadow
Nov 21 '18 at 6:54

|
show 3 more comments

You need parentheses when using IN (...):

$str = "1,2,3,4,5,6,7,8,9,10,11";

$sql = "update abc set status = 'home' where id in (" . $str . ")";

$st = $db->prepare($sql);

$st->execute();

Also, this is not the way you effectively use Parametrized queries. You need to use placeholders properly.

$str = "1,2,3,4,5,6,7,8,9,10,11";

$placeholder_str = "?,?,?,?,?,?,?,?,?,?,?";

$sql = "update abc set status = 'home' where id in (" . $placeholder_str . ")";

$st = $db->prepare($sql);

$st->execute(explode(',', $str)); // assuming you are using PDO

edited Nov 21 '18 at 7:06

answered Nov 21 '18 at 6:44

Madhur Bhaiya

19.6k62236

You need parentheses when using IN (...):

$str = "1,2,3,4,5,6,7,8,9,10,11";

$sql = "update abc set status = 'home' where id in (" . $str . ")";

$st = $db->prepare($sql);

$st->execute();

Also, this is not the way you effectively use Parametrized queries. You need to use placeholders properly.

$str = "1,2,3,4,5,6,7,8,9,10,11";

$placeholder_str = "?,?,?,?,?,?,?,?,?,?,?";

$sql = "update abc set status = 'home' where id in (" . $placeholder_str . ")";

$st = $db->prepare($sql);

$st->execute(explode(',', $str)); // assuming you are using PDO

edited Nov 21 '18 at 7:06

answered Nov 21 '18 at 6:44

Madhur Bhaiya

19.6k62236

edited Nov 21 '18 at 7:06

answered Nov 21 '18 at 6:44

Madhur Bhaiya

19.6k62236

answered Nov 21 '18 at 6:44

Madhur Bhaiya

19.6k62236

answered Nov 21 '18 at 6:44

Madhur Bhaiya

19.6k62236

about the query - I don't know in advance what the string will be like, so can't write a certain number of ?.

– puerto
Nov 21 '18 at 6:50

@puerto you can dynamically generate ? in a loop, using the count of values dynamically

– Madhur Bhaiya
Nov 21 '18 at 6:51

could you show me, pls

– puerto
Nov 21 '18 at 6:52

1

The first solution is completely wrong. And again, this question has been asked before...

– Shadow
Nov 21 '18 at 6:53

@puerto see the solutions in the duplicate topic for dynamically generating the placeholders.

– Shadow
Nov 21 '18 at 6:54

|
show 3 more comments

about the query - I don't know in advance what the string will be like, so can't write a certain number of ?.

– puerto
Nov 21 '18 at 6:50

@puerto you can dynamically generate ? in a loop, using the count of values dynamically

– Madhur Bhaiya
Nov 21 '18 at 6:51

could you show me, pls

– puerto
Nov 21 '18 at 6:52

1

The first solution is completely wrong. And again, this question has been asked before...

– Shadow
Nov 21 '18 at 6:53

@puerto see the solutions in the duplicate topic for dynamically generating the placeholders.

– Shadow
Nov 21 '18 at 6:54

about the query - I don't know in advance what the string will be like, so can't write a certain number of ?.

– puerto
Nov 21 '18 at 6:50

@puerto you can dynamically generate ? in a loop, using the count of values dynamically

– Madhur Bhaiya
Nov 21 '18 at 6:51

could you show me, pls

– puerto
Nov 21 '18 at 6:52

The first solution is completely wrong. And again, this question has been asked before...

– Shadow
Nov 21 '18 at 6:53

@puerto see the solutions in the duplicate topic for dynamically generating the placeholders.

– Shadow
Nov 21 '18 at 6:54

|
show 3 more comments

-1

Try this
$sql = " update abc set status = 'home' where instr($str, id) > 0 ";

answered Nov 21 '18 at 6:51

Krishna

402518

add a comment |

-1

Try this
$sql = " update abc set status = 'home' where instr($str, id) > 0 ";

answered Nov 21 '18 at 6:51

Krishna

402518

add a comment |

-1

Try this
$sql = " update abc set status = 'home' where instr($str, id) > 0 ";

answered Nov 21 '18 at 6:51

Krishna

402518

Try this
$sql = " update abc set status = 'home' where instr($str, id) > 0 ";

answered Nov 21 '18 at 6:51

Krishna

402518

answered Nov 21 '18 at 6:51

Krishna

402518

answered Nov 21 '18 at 6:51

Krishna

402518

answered Nov 21 '18 at 6:51

Krishna

402518

add a comment |

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu