如何从一个表行获取两个结果行[关闭]

I have the following MYSQL query:

<?php

require "gospel_connect.php";


if(isset($_GET["keywords"])) {
  $keywords = $db->escape_string($_GET["keywords"]);
  $query = $db->query("SELECT *,
  CASE
    WHEN scriptures LIKE '%{$keywords}%' THEN scriptures_link
    WHEN books LIKE '%{$keywords}%' THEN books_link
  END as the_link
FROM data WHERE scriptures LIKE '%{$keywords}%' OR books LIKE '%{$keywords}%'");

  ?>

  <div class='result-count'>
    Found <?php echo $query->num_rows; ?> results.
  </div>
  <?php

  if($query->num_rows) {
    while($r = $query->fetch_object()) {
      ?>
        <div class='result'>
          <?php
            $link = $r->the_link;

          echo "<a href='$link'> Hi </a> <br>";
          ?>
        </div>
      <?php
    }
  }

}

Let me explain, the query is searching from the db table data and it has the following columns: id, date, scriptures, books, scriptures_link, books_link.

Now, the query is working perfectly. It returns the respective link. The problem is that if both scriptures and books match with keywords, only one result will show. I want both to appear if both have matches. Otherwise, I only want one set to return information. How can I accomplish this?

The problem is that you want two rows returned from one row in the database table. In your case, you should then use UNION ALL to obtain the desired result:

SELECT *, scriptures_link AS the_link
FROM data WHERE scriptures LIKE '%{$keywords}%'
UNION ALL
SELECT *, books_link AS the_link
FROM data WHERE books LIKE '%{$keywords}%'

This query will also work (after changing table names), if you split your table into two, one for scriptures and one for books. This will probably be a better database design. Another possibility is to use one row for each possible match, and then add a column (e.g. source) to identify the source of the data.