How to make a search field that searches from two different tables with mysqli?

Question

I have a search field on my website where a SELECT statement is made, and it needs to return data from two different tables.

I've tried joining the two tables, but the results repeats themselves.

The code for search.php is:

<?php include 'header.php'; include 'db.php'; ?>

<p>Results:</p>
<table>

<?php

    if (isset($_POST['button_search'])) {
        $search = mysqli_real_escape_string($conn, $_POST['search']);
        $sql = "
        SELECT * FROM tb_article , tb_series

        WHERE 
        title_article LIKE '%$search%' OR
        subtitle_article LIKE '%$search%' OR
        author_article LIKE '%$search%' OR
        content_article LIKE '%$search%' OR 

        title_series LIKE '%$search%' OR
        cast_series LIKE '%$search%' OR
        year_series LIKE '%$search%' OR
        description_series LIKE '%$search%' OR 
        ";
        $result = mysqli_query($conn, $sql);
        $queryResult = mysqli_num_rows($result);

        if ($queryResult > 0) {

            while ($line = mysqli_fetch_array($result)) {
        echo '<tr><td class="td_tit"><a class="index_link_title" href="example_article.php?id=' . $line["id_tb_article"] . '">'.$line["title_article"].'</a></td></tr>';
        echo '<tr><td class="td_subt"><a  class="index_link_subtitle" href="example_article.php?id=' . $line["id_tb_article"] . '">'.$line["subtitle_article"].'</a></td></tr>';

            while ($line = mysqli_fetch_array($result)) {
        echo '<tr><td class="td_tit"><a class="index_link_title" href="series.php?id=' . $line["id_tb_series"] . '">'.$line["title_series"].'</a></td></tr>';
        }

        }else{
            echo "No results";
        }

    }

?>

</table>

<?php include 'footer.php'; ?>

Why the nested `while` loops? Also - your query ends with a stray `OR` which would seem incorrect — Professor Abronsius, Sep 20 '19 at 16:14
Possible duplicate: https://stackoverflow.com/questions/28385145/correct-way-to-use-like-var-with-prepared-statements-mysqli — Dharman, Sep 20 '19 at 16:32

score -1 · Answer 1 · edited Sep 20 '19 at 16:52

-1

https://www.w3schools.com/sql/sql_join_inner.asp

ex

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

edited Sep 20 '19 at 16:52

Paul Spiegel

30,925
5
44
53

answered Sep 20 '19 at 16:43

Marcelo Henrique

1

score -2 · Accepted Answer · edited Sep 20 '19 at 16:52

One way is split in two querys and two results:

$queryArticles = "
SELECT * FROM tb_article 
        WHERE 
        title_article LIKE '%$search%' OR
        subtitle_article LIKE '%$search%' OR
        author_article LIKE '%$search%' OR
        content_article LIKE '%$search%'";

while ($resultArticles) ...

$querySeries = "
SELECT * FROM tb_series
        WHERE 
        title_series LIKE '%$search%' OR
        cast_series LIKE '%$search%' OR
        year_series LIKE '%$search%' OR
        description_series LIKE '%$search%'
";

while ($resultSeries) ...

How to make a search field that searches from two different tables with mysqli?

2 Answers2