php - 解析MySQL准备语句

Question

我正在尝试创建一个通用插入/更新函数，但我在解析语句上遇到错误。

首先我创建更新和插入字符串

$update = '$wpdb->prepare("UPDATE '. $dbTable. ' SET Distance = %d WHERE Date = %s AND UserID = %d", '. $distance. ', '. $today. ', '. $userID. ')';
$insert = '$wpdb->prepare("INSERT INTO '. $dbTable. ' (Distance, Date, UserID) VALUES (%d,%s,%d)", '. $distance. ', '. $today. ', '. $userID. ')';

当我回显这一点时，它看起来不错:

$wpdb->prepare("INSERT INTO hfwp_Balance_Arm_Reach (Distance, Date, UserID) VALUES (%d,%s,%d)", 50, 2018-01-13, 29)

但是当我将它传递给我的函数时，我收到语法错误。

function hfwp_SaveFormData($update, $insert, $userID, $today, $dbTable)
{
  global $wpdb;
  $wpdb->show_errors();

  $sql = $wpdb->prepare("SELECT COUNT(*) UserID FROM $dbTable WHERE Date = %s AND UserID = %d", $today, $userID );
  $count = $wpdb->get_var($sql);

  if ($count > 0)
  {
    if (strpos($update, '$wpdb->prepare(' ) === 0) {

        $wpdb->query($update);

        if ($wpdb->last_error != "") {
            $error = $wpdb->last_error;
            hfwp_Log_Errors($update, $userID, $today, $error);
        }
    }

  }else{
    if (strpos($insert, '$wpdb->prepare(' ) === 0) {

        $wpdb->query($insert);

        if ($wpdb->last_error != "") {
            $error = $wpdb->last_error;
            hfwp_Log_Errors($insert, $userID, $today, $error);
        }
    }
}

}

错误:

<div id="error"><p class="wpdberror"><strong>WordPress databasefejl:</strong> [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 

&#039;$wpdb-&gt;prepare(&quot;INSERT INTO hfwp_Balance_Arm_Reach (Distance, Date, UserID) VALU&#039; at line 1]<br /><code>$wpdb-&gt;prepare(&quot;INSERT INTO hfwp_Balance_Arm_Reach (Distance, Date, UserID) VALUES (%d,%s,%d)&quot;, 50, 2018-01-13, 29)</code></p></div>

<div id="error"><p class="wpdberror"><strong>WordPress databasefejl:</strong> [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &#039;$wpdb-&gt;prepare(&quot;INSERT INTO hfwp_Balance_Arm_Reach (Distance, Date, UserID) VALU&#039; at line 1]<br /><code></code></p></div>

是否无法解析包含准备“$wpdb->prepare”的 SQL 语句，或者我在语句中遗漏了某些内容？？？

Answer 1

在更新和插入中删除''

$update = $wpdb->prepare("UPDATE '. $dbTable. ' SET Distance = %d WHERE Date = %s AND UserID = %d, '. $distance. ', '. $today. ', '. $userID. '");
$insert =$wpdb->prepare("INSERT INTO '. $dbTable. ' (Distance, Date, UserID) VALUES (%d,%s,%d)", '. $distance. ', '. $today. ', '. $userID.'");

并像这样执行$update->execute()

您收到语法错误，因为 $update 包含字符串而不是查询在您的代码中

$wpdb->query($update);

$update contains'$wpdb->prepare("UPDATE '.$dbTable.' SET Distance = %d WHERE Date = %s AND UserID = %d", '.$distance.', '. $today.','.$userID.')';\

echen 你打印 $update 它将打印你的准备语句的 update 值。

例如 $R='priot()'; 你认为这会执行吗，它不会 $R 包含值，即 priot() 不是 priot() 的返回值

学习here