从不同的数据库表中获取搜索结果并显示它们？

I'm trying to create a search function that will retrieve search results for different posters we're creating on our site. If a person is searching for lets say "dog" then it will show the posters which is related to dogs. The site is going to publish different events in the form of posters.

我正在尝试创建一个搜索功能,它将检索我们在我们网站上创建的不同海报的搜索结果。如果一个人正在寻找让我们说“狗”的东西,那么它将显示与狗有关的海报。该网站将以海报的形式发布不同的活动。

The code looks as following at the moment:

该代码目前看起来如下:

<?php

class Search
{
    public static $con;

    private $search = '';

    function __construct()
    { 
        self::$con = mysqli_connect('localhost', 'guest', 'guestpw', 'db_users');

        $this->search = mysqli_real_escape_string(self::$con, $_POST['search']);
    }

    if(isset($_POST['submit_search']))
    {

    $sql = mysqli_query($con, "SELECT * FROM Event WHERE eventNamn LIKE '%" . $search);

    $sql = mysqli_query($con, "SELECT * FROM Användare WHERE userName LIKE '%" . $search);

    $sql = mysqli_query($con, "SELECT * FROM Poster WHERE Kategori LIKE '%" . $search);

    $sql = mysqli_query($con, "SELECT * FROM EventTyp WHERE EventTyp LIKE '%" . $search);

    $result = mysqli_query($sql);

    }
}

What I want to happen now is to use the search word the user is searching for and then display the events that are associated with that word. All help is much appreciated! Thank you.

我现在想要发生的是使用用户正在搜索的搜索词,然后显示与该词相关联的事件。非常感谢所有帮助!谢谢。

3 个解决方案

#1

The code you're having now is not going to work. You're checking for a $_POST request inside a class? I made some adjustments

你现在拥有的代码不会起作用。您正在检查类中的$ _POST请求?我做了一些调整

<?php

class Search
{
    public static $con;
    private $search = '';

    public function __construct($search)
    { 
        self::$con = mysqli_connect('localhost', 'guest', 'guestpw', 'db_users');

        $this->search = mysqli_real_escape_string(self::$con, $search);
    }

    //Do this for every search

    public function search() {
       $sql = mysqli_query(self::$con, "SELECT [column] FROM Event WHERE eventNamn LIKE '%" .       $search . "'".
"UNION ALL ".
"SELECT [column] FROM Användare WHERE userName LIKE '%" . $search . "'".
"UNION ALL".
"SELECT [column] FROM Poster WHERE Kategori LIKE '%" . $search . "'".
"UNION ALL".
"SELECT [column] FROM EventTyp WHERE EventTyp LIKE'%" . $search . "'");
       return mysqli_query($sql);
    } 
}

if(isset($_POST['search'])) {

  $searchClass = new Search($_POST['search']);
  $result = $searchClass->searchEvent();

}

#2

You might want to use UNION or UNION ALL operator. The SQL UNION operator combines the result of two or more SELECT statements.

您可能希望使用UNION或UNION ALL运算符。 SQL UNION运算符组合了两个或多个SELECT语句的结果。

 SELECT col FROM Event WHERE ...
 UNION ALL
 SELECT col FROM User WHERE ...

The document is here:
MYSQL UNION operator : http://dev.mysql.com/doc/refman/5.0/en/union.html

该文档在这里:MYSQL UNION运算符:http://dev.mysql.com/doc/refman/5.0/en/union.html

Your code could go like this:

你的代码可能是这样的:

$sql  = "SELECT [your column] AS event FROM Event WHERE eventNamn LIKE '%" . $search . "'".
    "UNION ALL ".
    "SELECT [your column] AS event FROM Användare WHERE userName LIKE '%" . $search . "'".
    "UNION ALL".
    "SELECT [your column] AS event FROM Poster WHERE Kategori LIKE '%" . $search . "'".
    "UNION ALL".
    "SELECT [your column] AS event FROM EventTyp WHERE EventTyp LIKE'%" . $search . "'";

$result = mysqli_query($con,$sql);

while($row = mysqli_fetch_array($result)) {
  echo $row['event '];
  echo "<br>";
}
mysqli_close($con);

Hope this helps.

希望这可以帮助。

#3

1.don't rely on mysqli escape string. use a prepared statement. its faster and foolproof.

1.不要依赖mysqli逃脱字符串。使用准备好的声明。它更快,更加万无一失。

2.You can select from multiple tables in one query pretty easily

2.您可以很容易地在一个查询中从多个表中进行选择

public $mysqli; //make sure your connection is available

public $result = NULL;//setup your result variable
public $search = NULL;
public function getresults() //pass your connection to the object
{
    $search = $_POST["search"]; //post is a super global. no need to pass it by reference

    $result = array();//create an array to store the search results in

    $mysqli = $this->mysqli;//define mysqli so you can access the property of your class object. 

    //construct mysqli in your __construct call, and return it.

    $stmt = $mysqli->prepare("SELECT column1,column2,column3,column4 
                                FROM Event,Användare,Poster,Eventyp 
                                WHERE eventNamn LIKE '% ?';");//prepare our query

    $stmt->bind_param('s', $this->search); 

    //bind our search parameters to the question mark in the query. 
    //if you have multiple querystrings, they must be bound in order.
    if($stmt->execute())//execute prepared statment
    {
        $stmt->bind_result($col1,$col2,$col3,$col4);//bind the selected column results to variables. 
        //these also must be bound in order
        if($stmt->num_rows > 0)
        {
            $result[] = array('type'=> 'success','result' => $stmt->num_rows, 'search string' => $search); //success!
            while($row = $stmt->fetch()) //never use get_result to fetch your statement. always use fetch.
            {
                $result[] = array('type' => 'result', 
                                  'column1' => $col1 ,
                                  'column2' => $col2 ,
                                  'column3' => $col3 , 
                                  'column4' => $col4); //store each result row in an array.
            }
            $stmt->close();//close the connection
        }else{
            $result[] = array('type'=> 'emptyresult','result' => 'No Results Found', 'search string' => $search); //No Results!
        }
    }else{
        $result[] = array('type'=> 'error','result' => 'Error with query', 'search string' => $search); //No Results!
    }
    return $result;//finally return our result for further processing
}

#1