使用PDO搜寻表格 [英] Search form with PDO
问题描述
下面的代码现在可以工作，但是我如何才能做到这一点，如果找不到结果，它将回显一条消息，而不是空白.
我认为我已经设法为数据库创建搜索查询.它只是一个非常基本的搜索，但由于某种原因似乎无法正常工作.对pdo来说，任何建议对我来说都是新手(非常新！请客气！).
I think I've managed to create a search query for my database. Its only a very basic search but it doesn't seem to work for some reason. Any advice would be appreciated im still new to pdo (very new! be kind!).
也没有用户提交的数据插入数据库，所以我想我可以排除xss，前提是它的SQL注入是免费的?根据我的理解，PDO是哪一个?加上我使用了没有写访问权限的独立数据库用户.
Also no user submitted data is inserted into the database so I think i can rule out xss assuming its SQL inject free? Which from what I understand PDO is? plus im using a stand alone DB user with no write access.
为安全起见，必须用xxx替换数据
Have replace data with xxx for security
文件称为search.php
file is called search.php
*已更新以反映建议的更改 *第二次更新以反映所提供的帮助 *第三次更新
*updated to reflect changes suggested *2nd update to reflect help provided *3rd update
<html>
<head>
</head>
<body>
<form name="frmSearch" method="post" action="search.php">
<table width="599" border="1">
<tr>
<th>Keyword
<input name="var1" type="text" id="var1">
<input type="submit" value="Search"></th>
</tr>
</table>
</form>
<?php
$nameofdb = 'xxxxxx';
$dbusername = 'xxxxxxxxxxxxxx';
$dbpassword = 'xxxxxxxxxxxxx';
// Connect to MySQL via PDO
try {
$dbh = new PDO("mysql:dbname=$nameofdb;host=localhost", $dbusername, $dbpassword);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
$var1 = str_replace(array('%','_'),'',$_POST['var1']);
if (!$var1)
{
exit('Invalid form value: '.$var1);
}
$query = "SELECT * FROM xxxxx WHERE xxxxxx LIKE :search OR xxxxx LIKE :search";
$stmt = $dbh->prepare($query);
$stmt->bindValue(':search', '%' . $var1 . '%', PDO::PARAM_INT);
$stmt->execute();
/* Fetch all of the remaining rows in the result set */
print("Fetch all of the remaining rows in the result set:\n");
$result = $stmt->fetchAll();
foreach( $result as $row ) {
echo $row["id"];
echo $row["title"];
}
?>
</body>
</html>
推荐答案
问题出在形式上.方法是GET，但是在您的php中，您期望$_POST
The problem is in the form. the method is GET but in your php you expect $_POST
所以这行:
<form name="frmSearch" method="get" action="search.php">
应为:
<form name="frmSearch" method="post" action="search.php">
更新
更改此:
// Connect to MySQL via PDO
try {
$dbh = new PDO("mysql:dbname=$nameofdb;host=localhost", $dbusername, $dbpassword);
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
$var1 = $_POST['var1'];
$query = "SELECT * FROM xxxxx WHERE xxxx LIKE ? OR xxxxx LIKE ?";
$params = array("%$var1%");
$stmt = $handle->prepare($query);
$stmt->execute($params);
对此:
// Connect to MySQL via PDO
try {
$dbh = new PDO("mysql:dbname=$nameofdb;host=localhost", $dbusername, $dbpassword);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
$var1 = $_POST['var1'];
$query = "SELECT * FROM xxxxx WHERE xxxx LIKE :search OR xxxxx LIKE :search";
$stmt = $dbh->prepare($query);
$stmt->bindValue(':search', '%' . $var1 . '%', PDO::PARAM_INT);
$stmt->execute();
更新2
我只是看到您使用$handle进行连接，但是您定义了$dbh.我想如果您将$handle更改为$dbh，它将起作用
I just see you use $handle for the connection but you define $dbh. I guess if you change $handle to $dbh it will work
更新3
更改此行:
$result = $sth->fetchAll();
对此:
$result = $stmt->fetchAll();
更新4
要检查是否没有行并给出消息，您可以这样操作:
To check if there are no line and give a message you can do it like this:
if ($stmt->rowCount() > 0) {
$result = $stmt->fetchAll();
foreach( $result as $row ) {
echo $row["id"];
echo $row["title"];
}
} else {
echo 'There is nothing to show';
}
这篇关于使用PDO搜寻表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
