php - 对两个表进行选择，结果允许访问这两个选定表的字段

Question

我在做一个搜索系统，但遇到了一些问题。

我需要在两个表(新闻和页面)中进行搜索，我的搜索系统已经成功地只搜索了一个表，但是要搜索两个表就不容易了。

我已经对两个使用 UNION 的表使用了 select 语句，因为我想显示搜索结果的数量，即我的第一个 sql 语句返回的行数。

但现在我需要做一个选择语句，允许我访问我的新闻表的所有字段和我的页面表的所有字段。

我需要在我的新闻表中访问这些字段:id、标题、内容、链接、日期、nViews

我需要在我的页面表中访问这些字段:id、标题、内容、链接

我也尝试使用 UNION 来执行此操作，但在这种情况下，我没有返回任何行。

你看到我的代码有什么问题了吗？

<?php
//first I get my $search keyword
$search = $url[1];

$pdo = connecting();
//then I want to show number of returned rows for keyword searched
$readALL = $pdo->prepare("SELECT title,content FROM news WHERE title LIKE ? OR content LIKE ? 
                          UNION SELECT title,content FROM pages WHERE title LIKE ? OR content like ?");
$readALL->bindValue(1,"%$search%", PDO::PARAM_STR);
$readALL->bindValue(2,"%$search%", PDO::PARAM_STR);
$readALL->bindValue(3,"%$search%", PDO::PARAM_STR);
$readALL->bindValue(4,"%$search%", PDO::PARAM_STR);
$readALL->execute();
//I show number of returned rows
echo '<p>Your search keyword returned <strong>'.$readALL->rowCount().'</strong> results!</p>';
//If dont return any rows I show a error message
if($readALL->rowCount() <=0){
    echo 'Sorry but we didnt found any result for your keyword search.';
}
else{
    //If return rows I want to show, if it is a page result I want to show title and link that I have in my page table
    //if it is a news result I want to show title and link that I have in my news table and also date of news
    echo '<ul class="searchlist">';
            $readALL2 = $pdo->prepare("SELECT * FROM news WHERE status = ? AND title LIKE ? OR content LIKE ? LIMIT 0,4
                                      UNION SELECT * FROM pages where title LIKE ? OR content LIKE ? LIMIT 0,4"); 
            $readALL2->bindValue(1, '1');
            $readALL2->bindValue(2, "%$search%", PDO::PARAM_STR);
            $readALL2->bindValue(3, "%$search%", PDO::PARAM_STR);
            $readALL2->bindValue(4, "%$search%", PDO::PARAM_STR);
            $readALL2->execute();   

            while ($result = $readALL2->fetch(PDO::FETCH_ASSOC)){
                echo '<li>';
                    echo '<img src="'.BASE.'/uploads/news/'.$result['thumb'].'"/>';
                    echo '<a href="'.BASE.'/news/'.$result['id_news'].'">'.$result['title'].'</a>';
                    //if it is a news result I also want to show data on my list
                    //echo '<span id="date">'.$result['data'].'</span>';
                echo '</li>';
            }
    echo ' </ul>';
    //but how can I do my select statement to have access to my news table fields and my page table fields??
}
?>

这是我的新闻表:

这是我的页面表:

当我在我的表单中搜索关键字“doc”时，我得到了这个:

您正在搜索关键字:“doc”

您的搜索返回了 2 个结果!

Array ( [id_news] => 472 [thumb] => 2014/07/title-of-news-11405372264.png [title] => Documents [content] => Link 1, Link 2 [ofte] => 2014-07-14 23:11:04 [views] => 0 [author] => 1 [category] ​​=> 116 [status] => 1 [id] => 1 [link] => documents ) 新闻图片文档

Array ( [id_news] => 473 [thumb] => 2014/07/title-of-news-21405372282.png [title] => Documents [content] => Link 1, Link 2 [ofte] => 2014-07-14 23:11:22 [views] => 0 [author] => 1 [category] ​​=> 115 [status] => 1 [id] => 1 [link] => documents ) 新闻图片文档

Array ( [id_news] => 472 [thumb] => 2014/07/title-of-news-11405372264.png [title] => 关于 [content] => 我们是一家公司... [ofte] => 2014-07-14 23:11:04 [views] => 0 [author] => 1 [category] ​​=> 116 [status] => 1 [id] => 2 [link] => about ) 图片新闻关于

Array ( [id_news] => 473 [thumb] => 2014/07/title-of-news-21405372282.png [title] => 关于 [content] => 我们是一家公司... [ofte] => 2014-07-14 23:11:22 [views] => 0 [author] => 1 [category] ​​=> 115 [status] => 1 [id] => 2 [link] => about ) 图片新闻关于

数组 ( [id_news] => 472 [thumb] => 2014/07/title-of-news-11405372264.png [title] => Contacts [content] => Email: test@email.com [ofte] => 2014-07-14 23:11:04 [views] => 0 [author] => 1 [category] ​​=> 116 [status] => 1 [id] => 3 [link] => contacts ) 图片新闻联系人

数组 ( [id_news] => 473 [thumb] => 2014/07/title-of-news-21405372282.png [title] => Contacts [content] => Email: test@email.com [ofte] => 2014-07-14 23:11:22 [views] => 0 [author] => 1 [category] ​​=> 115 [status] => 1 [id] => 3 [link] => contacts ) 图片新闻联系人

Answer 1

要查询 2 个表，并使结果集包含两个表中的列:

SELECT n.id, n.status, n.views,  p.id, p.title
FROM news n, pages p
WHERE n.status = ? 
AND p.title = ?
...

为了简化我的回答，我省略了大部分您需要的列，但您只需在 select 语句中添加更多列即可。当然，你可以随时使用

SELECT n.*, p.*

从两个表中选择所有列。

更新:

对于您的特定场景，请尝试:

SELECT n.*, p.* 
FROM news n, pages p
WHERE n.title LIKE ?
  OR n.content LIKE ?
  OR p.title LIKE ?
  OR p.content LIKE ?