str_replace与变量生成的字符串不能与PHP一起使用

Question

I am sure this has something to do with using variables to populate a string, but I am not sure how to fix the problem.

I have a sql statement that gets populated through variable which may or may not contain data which is why I have to do it this way.

Example

if ($data1 != '') {
    $col1 = "col1 = '".$data1."', "
}
if ($data2 != '') {
    $col2 = "col2 = '".$data2."', "
}
if ($data3 != '') {
    $col3 = "col3 = '".$data3."', "
}

Not every $data may contain something so some variables will be empty and not part of my SQL statement, but lets assume all three have data.

$sql = "UPDATE table SET $col1$col2$col3 WHERE ID = '$ID'";

which then produces

$sql = "UPDATE table SET col1 = 'data1', col2 = 'data2', col3 = 'data3', WHERE ID = '$ID'";

The problem is the last comma in the sql statement before the , WHERE. That needs to be removed.

So I tried

$sql = "UPDATE table SET $col1$col2$col3 WHERE ID = '$ID'";
$sql = str_replace(", WHERE", " WHERE", $sql);

but that seems to be ignoring it. I thought maybe I needed to do something like strval($sql) first, but again didn't work.

Is there a way to get this to work? Am I making this harder than I need too?

Answer 1

Instead of:

if ($data1 != '') {
    $col1 = "col1 = '".$data1."', "
}
if ($data2 != '') {
    $col2 = "col2 = '".$data2."', "
}
if ($data3 != '') {
    $col3 = "col3 = '".$data3."', "
}

use something like:

$update_arr = array();
if ($data1 != '') {
    $update_arr[] = "col1 = '".$data1."'";
}
if ($data2 != '') {
    $update_arr[] = "col2 = '".$data2."'";
}
if ($data3 != '') {
    $update_arr[] = "col3 = '".$data3."'";
}

and then create update part of query like this:

if ($update_arr) {
    $sql = "UPDATE table SET ".implode(", ", $update_arr)." WHERE ID = '$ID'";
}