将PHP脚本更改为PDO导致MySQL更新查询期间出现语法错误

I have a php script to update details in a MySQL table. It all worked fine but now I have changed the db connection method to PDO:

我有一个PHP脚本来更新MySQL表中的详细信息。一切正常但现在我已将数据库连接方法更改为PDO:

$pdo = new PDO('mysql:host=localhost;dbname=****', '****', '*****');

I made various changes to the script to accommodate this so it continues to work, The only place that fails is right at the end after the mysql table has been updated. I get this error:

我对脚本进行了各种更改以适应这种情况,因此它继续工作,唯一失败的地方是在mysql表更新后的最后。我收到此错误:

Fatal error: 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 'and park_id=31' at line 1' in /home3/danville/public_html/test2/index.php:29 Stack trace: #0 /home3/danville/public_html/test2/index.php(29): PDO->query('update tpf_ride...') #1 {main} thrown in /home3/danville/public_html/test2/index.php on line 29

致命错误:带有消息'SQLSTATE [42000]的未捕获异常'PDOException':语法错误或访问冲突:1064 SQL语法中有错误;检查与您的MySQL服务器版本对应的手册,以便在/home3/danville/public_html/test2/index.php:29的第1行'和'park_id = 31'附近使用正确的语法。堆栈跟踪:#0 / home3 / danville / public_html / test2 / index.php(29):PDO-> query('update tpf_ride ...')#1 {main}在第29行的/home3/danville/public_html/test2/index.php中引发

This is the piece of code causing the error:

这是导致错误的一段代码:

$query = "update tpf_rides set name='$name',type='$type'";
        if($topride!=""){$query .= ",top_ride=$topride";}
        if($info!=""){$query .= ",info='$info'";}
        if($height!=""){$query .= ",height=$height";}
        if($length!=""){$query .= ",length=$length";}
        if($speed!=""){$query .= ",speed=$speed";}
        if($inversions!=""){$query .= ",inversions=$inversions";}
    $query .= " where ride_id=".$ride_id." and park_id=".$park_id;  
            $pdo->query($query);
        }

line 29 is this on Notepad++ $pdo->query($query); although the error message seems to reference the line above that $query .= " where ride_id=".$ride_id." and park_id=".$park_id;

第29行是Notepad ++ $ pdo-> query($ query);虽然错误消息似乎引用了$ query之前的行。=“where ride_id =”。$ ride_id。“和park_id =”。$ park_id;

Any ideas what I ned to change to stop the error? Additional details - I connect to the db with a require_once include. The updates do take effect despite the error.

我想改变什么想法来阻止错误?其他详细信息 - 我使用require_once include连接到db。尽管出现错误,更新仍会生效。

1 个解决方案

#1

If you're going to switch to PDO, you might as well take advantage of prepared statements and parameter binding. It actually makes your queries much safer from SQL injection and also makes your code more readable. Your query builder approach does complicate things a little but it's still possible. I'd also highly recommend enabling error reporting during development. For example

如果您要切换到PDO,您也可以利用预准备语句和参数绑定。它实际上使您的查询从SQL注入更安全,也使您的代码更具可读性。您的查询构建器方法确实使事情变得复杂但仍然可能。我还强烈建议在开发过程中启用错误报告。例如

error_reporting(E_ALL);
ini_set('display_errors', 'On');

$upd = array('name = :name', 'type = :type');
$values = array(
    'name' => $name,
    'type' => $type,
    'ride_id' => $ride_id,
    'park_id' => $park_id
);

if (!empty($topride)) {
    $upd[] = 'top_ride = :topride'; // :topride is the named parameter placeholder
    $values['topride'] = $topride; // the array key matches the named placeholder above
}
if (!empty($info)) {
    $upd[] = 'info = :info';
    $values['info'] = $info;
}
// and so on

$query = sprintf('UPDATE tpf_rides SET %s WHERE ride_id = :ride_id AND park_id = :park_id',
    implode(', ', $upd));
$stmt = $pdo->prepare($query);
$stmt->execute($values);