PHP PDO查询SQLSTATE[42000]错误 - PHP PDO query SQLSTATE[42000] error

我不得不构建快速的pdo查询（这是一个非常旧的网站，不可能使用框架），当我输入多个参数进行搜索时，我遇到了问题。

（对于单个参数，它运行良好，但组合两个或多个参数会产生SQLSTATE[42000]错误）。

完全错误：

<br />
<b>Fatal error</b>:  Uncaught exception 'PDOException' with message '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 'WHERE CUSTPHON.PHONE = '1230001343' 
        GROUP BY CUSTOMER.CUST_ID
        ORDER BY CUST' at line 7' in /home/larrysii/public_html/yaclib/lib/db.php:104

数据库中的第104行用于执行查询：

public function execute(){
        return $this->stmt->execute();
    }

这里怎么了？

我正在从这样的表单中获取数据（我不知道将使用哪个参数）：

$searchParams = [];
    if(strlen($_POST['customerName']) > 2)
        $searchParams['customerName'] = strtolower(strip_tags(trim($_POST['customerName'])));
    if(strlen($_POST['cardNumber']) == 7)
        $searchParams['cardNumber'] = strtolower(strip_tags(trim($_POST['cardNumber'])));
    if(strlen($_POST['phoneNumber']) > 3)
        $searchParams['cardNumber'] = strtolower(strip_tags(trim($_POST['phoneNumber'])));
    if(strlen($_POST['customerAddress']) > 3)
        $searchParams['customerAddress'] = strtolower(strip_tags(trim($_POST['customerAddress'])));
    if(strlen($_POST['customerEmail']) > 2)
        $searchParams['customerEmail'] = strtolower(strip_tags(trim($_POST['customerEmail'])));

这就是我查询的方式：

$queryArray = [];
    foreach($searchParams as $key => $value):
        switch ($key):
            case 'customerName':
                $queryArray['customerName'] = " WHERE LOWER(CUSTOMER.LAST_NAME) LIKE ? ";
                break;
            case 'cardNumber':
                $queryArray['cardNumber'] = " WHERE CUSTPHON.PHONE = ? ";
                break;
            case 'phoneNumber':
                $queryArray['cardNumber'] = " WHERE CUSTPHON.PHONE = ? ";
                break;
            case 'customerAddress':
                $queryArray['customerAddress'] = " WHERE LOWER(CUSTADDR.ADDRESS) LIKE ? ";
                break;
            case 'customerEmail':
                $queryArray['customerEmail'] = " WHERE LOWER(CUSTOMER.EMAIL) LIKE ? ";
                break;
        endswitch;
    endforeach;

获取查询：

$queryString = implode(' OR ', $queryArray);
    $query1 =  "SELECT CUSTOMER.LAST_NAME,CUSTOMER.DOB,CUSTOMER.EMAIL,CUSTPHON.PHONE,CUSTOMER.CUST_ID,CUSTOMER.DATE_LAST,CUSTOMER.ORD_COUNT
        FROM CUSTOMER
        RIGHT JOIN CUSTPHON
        ON CUSTOMER.CUST_ID=CUSTPHON.CUST_ID
        RIGHT JOIN CUSTADDR
        ON CUSTOMER.CUST_ID=CUSTADDR.CUST_ID
        {$queryString}
        GROUP BY CUSTOMER.CUST_ID
        ORDER BY CUSTOMER.LAST_NAME
        ";
    $pdo->prepareQuery($query1);
    $i = 1;
    foreach($searchParams as $key => $value):
        switch ($key):
            case 'cardNumber':
                $cardNumber = $value;
                if(strlen($value) == 7)
                    $cardNumber = '123' . (string)$value;
                $pdo->bind($i, $cardNumber);
                break;
            case 'customerName':
            case 'customerEmail':
            case 'customerAddress':
                $pdo->bind($i, "%$value%");
            break;
        endswitch;
        $i++;
    endforeach;

错误似乎是您有多个WHERE子句。

试试这个来构建你的WHERE子句：

$queryArray = [];
    foreach($searchParams as $key => $value):
        switch ($key):
            case 'customerName':
                $queryArray['customerName'] = " LOWER(CUSTOMER.LAST_NAME) LIKE ? ";
                break;
            case 'cardNumber':
                $queryArray['cardNumber'] = " CUSTPHON.PHONE = ? ";
                break;
            case 'phoneNumber':
                $queryArray['cardNumber'] = " CUSTPHON.PHONE = ? ";
                break;
            case 'customerAddress':
                $queryArray['customerAddress'] = " LOWER(CUSTADDR.ADDRESS) LIKE ? ";
                break;
            case 'customerEmail':
                $queryArray['customerEmail'] = " LOWER(CUSTOMER.EMAIL) LIKE ? ";
                break;
        endswitch;
    endforeach;

这是为了构建您的查询：

$queryString = implode(' AND ', $queryArray); // Or you can use ' OR ', depending on what results you want
$query1 =  'SELECT CUSTOMER.LAST_NAME,CUSTOMER.DOB,CUSTOMER.EMAIL,CUSTPHON.PHONE,CUSTOMER.CUST_ID,CUSTOMER.DATE_LAST,CUSTOMER.ORD_COUNT
    FROM CUSTOMER
    RIGHT JOIN CUSTPHON
    ON CUSTOMER.CUST_ID=CUSTPHON.CUST_ID
    RIGHT JOIN CUSTADDR
    ON CUSTOMER.CUST_ID=CUSTADDR.CUST_ID
    ' . ($queryString != '' ? ' WHERE ' . $queryString : '') . '
    GROUP BY CUSTOMER.CUST_ID
    ORDER BY CUSTOMER.LAST_NAME
    ';