-1

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.

mickmackusa
  • 43,625
  • 12
  • 83
  • 136
user1904273
  • 4,562
  • 11
  • 45
  • 96
  • How many replacements are you intending to do? Action/Adventure = 1 replacement. – Martin Dec 25 '20 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 Dec 25 '20 at 21:34

2 Answers2

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.

ysth
  • 96,171
  • 6
  • 121
  • 214
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 ] );
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 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. – user1904273 Dec 25 '20 at 21:37
  • @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. – mickmackusa Dec 25 '20 at 21:45
  • 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. – user1904273 Dec 27 '20 at 02:43
  • 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. – Bill Karwin Dec 27 '20 at 04:40