Using str_replace in table query in MYSQL [duplicate]

Question

There are a lot of examples on SO of using str_replace to modify a query that uses variables in MYSQL but I can't find one that solves my problem.

I have the following legacy query that I'm debugging written in PHP and MySQL.

Somewhat simplified it is:

$sql = "SELECT * from MOVIES WHERE cat = '$cat'";

In the event that cat has a certain value, say, "action" I want to check for "adventure";

Let's say you start with query:

$cat = "action";
$sql = "SELECT * FROM MOVIES WHERE cat='$cat'";

I'm trying to modify the query with:

$needle = "cat=".$cat;
$altcat = "adventure";
$altwhere = "cat=".altcat;
$sql = str_replace($needle,$altwhere,$sql); //NOTHING GETS REPLACED...NOT MATCHING Needle

How can I do this? I'm thinking the problem has something to do with use of spaces or apostrophes in the sql string but can't get it to work.

Thanks for any suggestions.

How many replacements are you intending to do? Action/Adventure = 1 replacement. — Mm-Art-In
– Mm-Art-In, Commented Dec 25, 2020 at 21:26
Replace the value, not the whole condition. if ($cat === 'action') { $cat = 'adventure'; } I'd rather you use a prepared statement anyhow. — mickmackusa
– mickmackusa ♦, Commented Dec 25, 2020 at 21:34

ysth · Accepted Answer · 2020-12-25 21:27:32Z

1

You want to replace "cat='".$cat."'" with "cat='adventure'", not "cat=".$cat with "cat=adventure".

(Though you are inconsistent in saying if there are spaces around the =.)

But you should not do this and should use a placeholder instead.

edited Dec 25, 2020 at 21:27

answered Dec 25, 2020 at 21:26

ysth

99.1k6 gold badges126 silver badges220 bronze badges

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

2 Comments

user1904273 Over a year ago

How would I use a placeholder?

ysth Over a year ago

see Bill's answer.

Bill Karwin · Accepted Answer · 2020-12-25 21:29:21Z

1

I would not try to do string substitution on the SQL query. Instead, just use query parameters.

$cat = 'action'; // suppose this is the input to your program

$sql = "SELECT * from MOVIES WHERE cat = ?";

if ($cat == 'action') {
  $cat = 'adventure';
}

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

$stmt->execute( [ $cat ] );

edited Dec 25, 2020 at 21:29

answered Dec 25, 2020 at 21:28

Bill Karwin

567k87 gold badges711 silver badges872 bronze badges

4 Comments

user1904273 Over a year ago

yes this is probably the best way to do it. I marked ysth as accepted because he suggested this approach and upvoted you as showing how to do so.

mickmackusa Over a year ago

@user this IS the professional way to do this. By accepting the less professional answer, you may unintentionally mislead researchers about which answer should be used.

user1904273 Over a year ago

Correct me if I'm wrong but answering first is supposed to count for something too. Ysth suggested the PDO approach in addition to the literal answer to the question (2 approaches) first before Bill gave the full PDO answer. If Ysth' answer was incorrect that would be a different story but it was correct and he also suggested the placeholder approach for which Bill provided the full answer. I had to choose one so I chose the first one and upvoted the second one. No reason to downvote and close question. after it was answered.

Bill Karwin Over a year ago

I'm not concerned about the points. The point is that you got your problem solved. Anyone who has a similar problem in the future and sees this thread will get the benefit of both answers.

Collectives™ on Stack Overflow

Using str_replace in table query in MYSQL [duplicate]

2 Answers 2

2 Comments

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Linked

Related