Search
搜尋資料
2019/10/29 (更新內容)
2019/11/04 (補充fetch)
Search with Associative Array
加個搜尋功能,先在query.php裡,新增一個form,讓使用者可以輸入要搜尋的字串
<form action="query.php" method="post">
<p style='text-align:center'>
<input type=text name="searchtxt">
<input type=submit value="搜尋廠商與內容"></p>
</form>
query.php
<?php
require 'db.php';
$sql="select * from job";
$stmt = $conn->prepare($sql);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
<a href="insert.html">新增求才資訊</a>
<form action="query.php" method="post">
<p style='text-align:center'>
<input type=text name="searchtxt">
<input type=submit value="搜尋廠商與內容"></p>
</form>
<table width='85%' style='float:right'>
<tr style='text-align:center'>
<td>求才廠商</td>
<td>求才內容</td>
</tr>
<?php
foreach($rows as $job){
echo "<tr style='text-align:center'><td>".$job["company"]."</td><td>".$job["content"]."</td></tr>";
}
$conn = null;
?>
</table>
sql的部分要加where,表示尋找公司名稱等於輸入的字串
select * from job where company = 'apple';
將搜尋內容以變數取代
$sql="select * from job where company = :searchtxt";
利用前面用過的bindParam
$stmt->bindParam(':searchtxt', $_POST["searchtxt"]);
或者利用execute
$stmt->execute($_POST);
完整的query.php
<?php
require 'db.php';
$sql="select * from job where company = :searchtxt";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':searchtxt', $_POST["searchtxt"]);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
<a href="insert.html">新增求才資訊</a>
<form action="query.php" method="post">
<p style='text-align:center'>
<input type=text name="searchtxt">
<input type=submit value="搜尋廠商與內容"></p>
</form>
<table width='85%' style='float:right'>
<tr style='text-align:center'><td>求才廠商</td><td>求才內容</td></tr>
<?php
foreach($rows as $job){
echo "<tr style='text-align:center'><td>".$job["company"]."</td><td>".$job["content"]."</td></tr>";
}
$conn = null;
?>
</table>
我們會發現看不到任何內容,是因為目前並沒有任何公司的名稱是空白的。所以,如果當沒有輸入任何字串時,要看到所有的資料:
<?php
require 'db.php';
if ($_POST["searchtxt"]==""){
$sql="select * from job";
$stmt = $conn->prepare($sql);
}
else
{
$sql="select * from job where company = :searchtxt";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':searchtxt', $_POST["searchtxt"]);
}
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
<a href="insert.html">新增求才資訊</a>
<form action="query.php" method="post">
<p style='text-align:center'>
<input type="text" name="searchtxt">
<input type=submit value="搜尋廠商與內容"></p>
</form>
<table width='85%' style='float:right'>
<tr style='text-align:center'><td>求才廠商</td><td>求才內容</td></tr>
<?php
foreach($rows as $job){
echo "<tr style='text-align:center'><td>".$job["company"]."</td><td>".$job["content"]."</td></tr>";
}
$conn = null;
?>
</table>
但是,會有個小小的問題,第一次使用這個網頁的時候會有錯誤訊息,因為找不到$_POST["searchtxt"]
<?php
require 'db.php';
$searchtxt = isset($_POST["searchtxt"])? $_POST["searchtxt"] : "";
if ($searchtxt==""){
$sql="select * from job";
$stmt = $conn->prepare($sql);
}
else
{
$sql="select * from job where company = :searchtxt";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':searchtxt', $searchtxt);
}
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
<a href="insert.html">新增求才資訊</a>
<form action="query.php" method="post">
<p style='text-align:center'>
<input type="text" name="searchtxt">
<input type="submit" value="搜尋廠商與內容"></p>
</form>
<table width='85%' style='float:right'>
<tr style='text-align:center'><td>求才廠商</td><td>求才內容</td></tr>
<?php
foreach($rows as $job){
echo "<tr style='text-align:center'><td>".$job["company"]."</td><td>".$job["content"]."</td></tr>";
}
$conn = null;
?>
</table>
如果搜尋時,只要部分字串符合 ,首先,sql要改成
$sql="select * from job where company like :searchtxt";
另外,要在字串前後加"%",因為bindParam的參數一定要是一個變數,所以會得到:
Cannot pass parameter 2 by reference
這時候,就不能使用bindParam,要改用bindValue。(詳參: 比較 PDO bindParam 和 bindValue 的不同 、PDO – bindParam 和 bindValue 的使用時機、Difference between bindParam and bindValue in PHP)
$stmt->bindValue(':searchtxt', "%".$searchtxt."%");
完整的query.php
<?php
require 'db.php';
$searchtxt = isset($_POST["searchtxt"])? $_POST["searchtxt"] : "";
if ($searchtxt==""){
$sql="select * from job";
$stmt = $conn->prepare($sql);
}
else
{
$sql="select * from job where company like :searchtxt";
$stmt = $conn->prepare($sql);
$stmt->bindValue(':searchtxt', "%".$searchtxt."%");
}
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
<a href="insert.html">新增求才資訊</a>
<form action="query.php" method="post">
<p style='text-align:center'>
<input type="text" name="searchtxt">
<input type="submit" value="搜尋廠商與內容"></p>
</form>
<table width='85%' style='float:right'>
<tr style='text-align:center'>
<td>求才廠商</td>
<td>求才內容</td>
</tr>
<?php
foreach($rows as $job){ ?>
<tr style='text-align:center'>
<td><?=$job["company"]?></td>
<td><?=$job["content"]?></td>
</tr>
<?php
}
$conn = null;
?>
</table>
如果也要搜尋求才內容,可以將sql修改為:
$sql="select * from job where company like :searchtxt or content like :searchtxt";
如果要記得上一次的搜尋字串:
<input type=text name="searchtxt" value="<?=$searchtxt?>">
Search with Class
在搜尋的部分,使用class與使用associative array的差異不大。
加個搜尋功能,先在query.php裡,先新增一個form,讓使用者可以輸入要搜尋的字串
<form action="query.php" method="post">
<p style='text-align:center'>
<input type="text" name="searchtxt">
<input type="submit" value="搜尋廠商與內容"></p>
</form>
query.php
<?php
require 'job.php';
require 'db.php';
$sql="select * from job";
$stmt = $conn->prepare($sql);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_CLASS, 'Job');
?>
<a href="insert.html">新增求才資訊</a>
<form action="query.php" method="post">
<p style='text-align:center'>
<input type="text" name="searchtxt">
<input type="submit" value="搜尋廠商與內容"></p>
</form>
<table width='85%' style='float:right'>
<tr style='text-align:center'><td>求才廠商</td><td>求才內容</td></tr>
<?php
foreach($rows as $job){
echo "<tr style='text-align:center'><td>".$job->company."</td><td>".$job->content."</td></tr>";
}
$conn = null;
?>
</table>
sql的部分要加where,表示尋找公司名稱等於輸入的字串
select * from job where company = 'apple';
將搜尋內容以變數取代
$sql="select * from job where company = :searchtxt";
利用前面用過的bindParam
$stmt->bindParam(':searchtxt', $_POST["searchtxt"]);
完整的query.php
<?php
require 'job.php';
require 'db.php';
$sql="select * from job where company = :searchtxt";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':searchtxt', $_POST["searchtxt"]);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_CLASS, 'Job');
?>
<a href="insert.html">新增求才資訊</a>
<form action="query.php" method="post">
<p style='text-align:center'>
<input type="text" name="searchtxt">
<input type="submit" value="搜尋廠商與內容"></p>
</form>
<table width='85%' style='float:right'>
<tr style='text-align:center'><td>求才廠商</td><td>求才內容</td></tr>
<?php
foreach($rows as $job){
echo "<tr style='text-align:center'><td>".$job->company."</td><td>".$job->content."</td></tr>";
}
$conn = null;
?>
</table>
我們會發現看不到任何內容,是因為目前並沒有任何公司的名稱是空白的。所以,如果當沒有輸入任何字串時,要看到所有的資料:
<?php
require 'job.php';
require 'db.php';
if ($_POST["searchtxt"]==""){
$sql="select * from job";
$stmt = $conn->prepare($sql);
}
else
{
$sql="select * from job where company = :searchtxt";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':searchtxt', $_POST["searchtxt"]);
}
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_CLASS, 'Job');
?>
<a href="insert.html">新增求才資訊</a>
<form action="query.php" method="post">
<p style='text-align:center'>
<input type="text" name="searchtxt">
<input type="submit" value="搜尋廠商與內容"></p>
</form>
<table width='85%' style='float:right'>
<tr style='text-align:center'><td>求才廠商</td><td>求才內容</td></tr>
<?php
foreach($rows as $job){
echo "<tr style='text-align:center'><td>".$job->company."</td><td>".$job->content."</td></tr>";
}
$conn = null;
?>
</table>
但是,會有個小小的問題,第一次使用這個網頁的時候會有錯誤訊息,因為找不到$_POST["searchtxt"]
<?php
require 'job.php';
require 'db.php';
$searchtxt = isset($_POST["searchtxt"])? $_POST["searchtxt"] : "";
if ($searchtxt==""){
$sql="select * from job";
$stmt = $conn->prepare($sql);
}
else
{
$sql="select * from job where company = :searchtxt";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':searchtxt', $searchtxt);
}
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_CLASS, 'Job');
?>
<a href="insert.html">新增求才資訊</a>
<form action="query.php" method="post">
<p style='text-align:center'>
<input type="text" name="searchtxt">
<input type="submit" value="搜尋廠商與內容"></p>
</form>
<table width='85%' style='float:right'>
<tr style='text-align:center'><td>求才廠商</td><td>求才內容</td></tr>
<?php
foreach($rows as $job){
echo "<tr style='text-align:center'><td>".$job->company."</td><td>".$job->content."</td></tr>";
}
$conn = null;
?>
</table>
如果搜尋時,只要部分字串符合 ,首先,sql要改成
$sql="select * from job where company like :searchtxt";
另外,要在字串前後加"%",因為bindParam的參數一定要是一個變數,所以會得到:
Cannot pass parameter 2 by reference
這時候,就不能使用bindParam,要改用bindValue。(詳參: 比較 PDO bindParam 和 bindValue 的不同 、PDO – bindParam 和 bindValue 的使用時機、Difference between bindParam and bindValue in PHP)
$stmt->bindValue(':searchtxt', "%".$searchtxt."%");
完整的query.php
<?php
require 'job.php';
require 'db.php';
$searchtxt = isset($_POST["searchtxt"])? $_POST["searchtxt"] : "";
if ($searchtxt==""){
$sql="select * from job";
$stmt = $conn->prepare($sql);
}
else
{
$sql="select * from job where company like :searchtxt";
$stmt = $conn->prepare($sql);
$stmt->bindValue(':searchtxt', "%".$searchtxt."%");
}
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_CLASS, 'Job');
?>
<a href="insert.html">新增求才資訊</a>
<form action="query.php" method="post">
<p style='text-align:center'>
<input type="text" name="searchtxt">
<input type="submit" value="搜尋廠商與內容"></p>
</form>
<table width='85%' style='float:right'>
<tr style='text-align:center'><td>求才廠商</td><td>求才內容</td></tr>
<?php
foreach($rows as $job){
echo "<tr style='text-align:center'><td>".$job->company."</td><td>".$job->content."</td></tr>";
}
$conn = null;
?>
</table>
如果也要搜尋求才內容,可以將sql修改為:
$sql="select * from job where company like :searchtxt or content like :searchtxt";
如果要記得上一次的搜尋字串:
<input type=text name="searchtxt" value="<?=$searchtxt?>">
Fetch
with Associative Array
$sql="select * from job where postid = :postid";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':postid', $postid);
$stmt->execute();
$job = $stmt->fetch(PDO::FETCH_ASSOC);
with Class
$sql="select * from job where postid = :postid";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':postid', $postid);
$stmt->execute();
$job = $stmt->fetchObject('Job');
** 作業 **
請修改上週作業,在資料表中新增一個欄位role,當role為"M"時,該帳號為管理者,當role為"C"時,該帳號為消費者,管理者的帳號直接寫在資料庫中。
管理者登入成功後可以看到所有的註冊資料,未登入者或消費者不可以看到所有人的註冊資料,管理者也可以搜尋消費者資料。
讓註冊成功之後,要求消費者利用註冊時提供的帳號密碼登入,登入成功就可以從資料庫取得自己的註冊資料。
修改註冊程式,讓註冊的消費者的role設定為"C"。
參考資料
Read
Create & Read
Update & Delete