内爆以修复更新 SQL - implode to fix update sql

implode to fix update sql

本文关键字：更新 SQL | 更新日期: 2023-09-27

我有这个函数

function updateDbRecord($db, $table, $carry, $carryUrl) {   
    mysql_select_db($db) or die("Could not select database. " . mysql_error());
    $resultInsert = mysql_query("SHOW COLUMNS FROM " . $table . " WHERE Field NOT IN ('id')");
    $fieldnames=array();
      if (mysql_num_rows($resultInsert) > 0) {
        while ($row = mysql_fetch_array($resultInsert)) {
            $fieldnames[] = $row['Field'];
            $arr = array_intersect_key( $_POST, array_flip($fieldnames) ); #check if value is null otherwise do not INSERT
        }
      }
      $set = "";
      foreach($arr as $key => $v) {
        $val = is_numeric($v) ? $v : "'" . $v . "'";
        $set .= $key . '=' . $val . ', ';
      }
      $sql = sprintf("UPDATE %s SET %s WHERE id='%s'", $table, $set, $_POST['id']);
      mysql_query($sql);
      if ($carry == 'yes') {
        redirect($carryUrl.'?id='.$_REQUEST['id']);
      } else { echo "Done!"; }
      echo $sql;
}

例如，它输出：更新项目集 project_name='123'， project_bold='123'， project_content='123'，其中 id='12'

where 之前的最后一个逗号阻止它工作。有没有办法避免这种情况？我知道该功能会崩溃，但是我不确定在这种情况下如何使用它。

是的，

$sql = substr($sql,'',-1);

我会使用

$sql = rtrim($sql, ',');

要么，要么不是附加到字符串，而是附加到数组并使用 implode .

function updateDbRecord($db, $table, $carry, $carryUrl) {   
    mysql_select_db($db) or die("Could not select database. " . mysql_error());
    $resultInsert = mysql_query("SHOW COLUMNS FROM " . $table . " WHERE Field NOT IN ('id')");
    $fieldnames=array();
      if (mysql_num_rows($resultInsert) > 0) {
        while ($row = mysql_fetch_array($resultInsert)) {
            $fieldnames[] = $row['Field'];
            $array = array_intersect_key( $_POST, array_flip($fieldnames) ); #check if value is null otherwise do not INSERT
        }
      }
      foreach ($array as $key => $value) {
                $value = mysql_real_escape_string($value); // this is dedicated to @Jon
                $value = "'$value'";
                $updates[] = "$key = $value";
            }
      $implodeArray = implode(', ', $updates);
      $sql = sprintf("UPDATE %s SET %s WHERE id='%s'", $table, $implodeArray, $_POST['id']);
      mysql_query($sql);