CODEWITHSUNDEEP
phpmysql
Face Code
Face Code

Reputation: 203

How to fix this mysql query?

I have the following MYSQL query: 

if(isset($_GET["keywords"])) {
  $keywords = $db->escape_string($_GET["keywords"]);
  $query = $db->query("SELECT * 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
            $scriptures_link = $r->scriptures_link;
            $books_link = $r->books_link;

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

}

I have two separate categories: scriptures and books. The LIKE statement targets both of these groups. So, if one row of information has info for both of these items. Therefore, if one item matches the LIKE statement, but the other does not, both items show up in the search results. How do I separate up the query, to only do one at a time?

Upvotes: 2

Views: 73

Answers (2)

darkangel
darkangel

Reputation: 120

I think you miss something in this line..

echo "<a href='$scriptures_link'> Hi </a> <br>";
      echo "<a href='$books_link'> Bye </a> <br>";

Try this one..

echo "<a href='".$scriptures_link."'> Hi </a> <br>";
      echo "<a href='".$books_link."'> Bye </a> <br>";

Hope this help... :D

Upvotes: 0

SilicaGel
SilicaGel

Reputation: 459

You can try using a Case statement.

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}%'"

Then echo out $r->the_link

Let me know if that works. (I haven't tested the query, but I think its right.)

Upvotes: 2

Related Questions