我正在尝试根据用户输入更新我的sql表。因此,用户从选择列表中选择要更新的列。然后,他们输入行中的数据,然后输入要更新的数据。比如说pick列teamname,然后通过文本框输入他们想要更改的团队名称,然后通过另一个文本框输入新名称。不管怎样,这就是我想要实现的。
我试图从用户删除数据并修改的地方复制和粘贴我的代码。我尝试了一些不同的事情,我尝试的第一件事给了我这个错误:
UPDATE teamset rockets=:value1 WHERE teamname=rocketsSQLSTATE[42S22]:未找到列:1054"where clause"中的未知列"rockets"
我的sql语句如下所示:
$sql = "UPDATE teams SET $selectData = :value1 WHERE $columnSelect = $selectData";
然后,我试着做这样的事情:
下面的整个代码,如上所述,我已经尝试了几个不同的$sql
变量:
php
<?php
if ($_SERVER["REQUEST_METHOD"] == "POST"){
$servername = "localhost";
$username = "";
$password = "";
$dbname = "";
$columnSelect = $_POST['selectColumn2'];
$selectData = $_POST['selectData'];
$updateData = $_POST['updateData'];
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// sql to delete a record
$sql = "UPDATE teams SET :value1 = :value2 WHERE $columnSelect = :value1";
// use exec() because no results are returned
$stmt = $conn->prepare($sql);
$stmt->execute(array(':value1'=>$selectData, ':value2'=>$updateData));
if ($stmt->rowCount() > 0) {
echo 'Updated '.$stmt->rowCount().' rows';
} else {
echo 'No rows updated';
}
}
catch(PDOException $e)
{
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
}
?>
html
<form method='post' action='addData.php'>
Select a column name, then enter which data to update.
<br>
<br>
<label for='option2'>
<select name='selectColumn2'>
<option value='teamname' id='team2'>teamname</option>
<option value='city' id='city2'>city</option>
<option value='bestplayer' id='best2'>bestplayer</option>
<option value='yearformed' id='year2'>year</option>
<option value='website' id='website2'>website</option>
</select>
</label>
<label for='option2'>
select data to change: <input type='text' name='selectData'>
enter new data: <input type='text' name='updateData'>
</label>
<br><br>
<input type='submit' value='Submit New Entry'>
</form>
基本上,我要做的是取$columnSelect
,在该列中找到$selectData
,并使用PDO方法将其替换为$updateData
。我做错了什么?
这应该可以工作:UPDATE teams SET $columnSelect = :value1 WHERE $columnSelect = $selectData
像这样更改您的查询,
$sql = "UPDATE teams SET `$selectData` = ':value1' WHERE `$columnSelect` = '$selectData'";
插入或更新时,为字符串值添加单引号。并添加了表名或列名的反方括号。
编辑
$sql = "UPDATE teams SET `$columnSelect` = ':value1' WHERE `$columnSelect` = '$selectData'";