php - SQLSTATE[42000] : Syntax error or access violation: 1064 Error in setting Variable in bindparam

Question

我想从数据库获取数据并将字符串添加到主题并将它们放入另一个查询中。当我运行代码时出现此错误:

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 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 ''goldhyipPID', 'goldhyipPayStatus', programName,'goldhyipLastPayout') VALUES ('1' at line 1 in C:\wamp64\www\allmonitors\test.php on line 69

<?php
set_time_limit(3600);
require_once 'fetchdetails/func.php';
require_once 'config.php';
$stmt = $conn->prepare("SELECT * FROM `monitors`");
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $monitor) {
    $monitorName = $monitor['monitorName'];
    $monitorNamePID = $monitorName . 'PID';
    $monitorNameLastPayout = $monitorName . 'LastPayout';
    $monitorNamePayStatus = $monitorName . 'PayStatus';
    $siteURL = $monitor['monitorurl'];
    $pattern1GetPID = $monitor['monitorPatternGetPID'];
    $patternLastPayOut = $monitor['monitorPatternLastPayout'];
    $patternPStatus = $monitor['monitorPatternPayStatus'];
    $patterndetailsurl = $monitor['monitorDetailsLink'];
    $patterngotositesurl = $monitor['monitorPatternGoSite'];
    $content = getPageContent($siteURL);
    preg_match_all($pattern1GetPID, $content, $matches, PREG_SET_ORDER, 0);
    foreach ($matches as $pid) {
        $id = $pid[1];
        $detailsurl = $patterndetailsurl . $id;
        $gositesurl = $patterngotositesurl . $id;
        $details = getPageContent($detailsurl);
        preg_match_all($patternPStatus, $details, $status);
        $payingStatusNumber = $status[1][0];
        if ($payingStatusNumber == 4) {
            $payingStatus = 'Not Paying';
        } elseif ($payingStatusNumber == 3) {
            $payingStatus = 'Problem';
        } elseif ($payingStatusNumber == 2) {
            $payingStatus = 'Waiting';
        } elseif ($payingStatusNumber == 1) {
            $payingStatus = 'Paying';
        }
        preg_match_all($patternLastPayOut, $details, $payout);
        if (isset($payout[1][0])) {
            $payoutdate = $payout[1][0];
        } else {
            $payoutdate = ' Not Set';
        };
        $stmt2 = $conn->prepare('SELECT * FROM programs where :monitorNamePID=:id');
        $stmt2->bindParam('monitorNamePID', $monitorNamePID);
        $stmt2->bindParam('id', $id);
        $stmt2->execute();
        $numofupdates = $stmt2->rowCount();
        if ($numofupdates >= 1) {
            $stmt3 = $conn->prepare("UPDATE programs SET :monitorNamePayStatus=:payingstatus , :monitorNameLastPayout=:goldhyiplastpayout WHERE :monitorNamePID=:goldhyippid  ");
            $stmt3->bindParam('monitorNamePayStatus', $monitorNamePayStatus);
            $stmt3->bindParam('monitorNameLastPayout', $monitorNameLastPayout);
            $stmt3->bindParam('monitorNamePID', $monitorNamePID);
            $stmt3->bindParam('payingstatus', $payingStatus);
            $stmt3->bindParam('goldhyiplastpayout', $payoutdate);
            $stmt3->bindParam('goldhyippid', $id);
            $stmt3->execute();
            echo 'P ID Updated : ' . $id . '<br>';
        } else {
            $siteAddress = get_redirect_final_host_url($gositesurl);
            echo $siteAddress;
            $stmt3 = $conn->prepare('INSERT INTO programs (:monitorNamePID, :monitorNamePayStatus, programName,:monitorNameLastPayout) VALUES (:goldhyippid, :payingstatus, :progname, :goldhyiplastpayout)');
            $stmt3->bindParam('monitorNamePID', $monitorNamePID);
            $stmt3->bindParam('monitorNamePayStatus', $monitorNamePayStatus);
            $stmt3->bindParam('monitorNameLastPayout', $monitorNameLastPayout);
            $stmt3->bindParam('goldhyippid', $id);
            $stmt3->bindParam('payingstatus', $payingStatus);
            $stmt3->bindParam('progname', $siteAddress);
            $stmt3->bindParam('goldhyiplastpayout', $payoutdate);
            $stmt3->execute();
            echo 'P ID inserted : ' . $id . '<br>';
        }
    }
    echo "Fetching $siteURL Done <br>";
}

我从 sql 中获取详细信息，并向其中添加字符串，在 PDO 查询中使用。当我写列名时没问题，但是当我使用变量时出现错误。

Answer 1

列名不应以冒号开头。在 UPDATE 和 INSERT 查询中从列名称中删除冒号 :。绑定(bind)表(或列)名称不起作用。

$stmt3 = $conn->prepare("UPDATE programs SET monitorNamePayStatus=:payingstatus , monitorNameLastPayout=:goldhyiplastpayout WHERE monitorNamePID=:goldhyippid  ");

$stmt3 = $conn->prepare('INSERT INTO programs (monitorNamePID, monitorNamePayStatus, programName, monitorNameLastPayout) VALUES (:goldhyippid, :payingstatus, :progname, :goldhyiplastpayout)');