CODEWITHSUNDEEP
phphtmlmysqldatabase
Anthony Waymouth
Anthony Waymouth

Reputation: 21

How to search for multiple words in database

I cant seem to work out how to search (and display) multiple words that are in my database. This is an image of the database column

enter image description here

There all separate locations: Africa | Wales, UK | Stockholm, Sweden and I want to search for locations that are the same and display them, the following code works if there is one word in the column but not if there are multiple. 

<?php 
        $sessionid = $_SESSION['id'];
        $sql = "SELECT * FROM users WHERE id = '$sessionid';";
        $rsults = mysqli_query($conn, $sql);
        $resultsCheck = mysqli_num_rows($rsults);
        if ($resultsCheck > 0) {
            while ($row = mysqli_fetch_assoc($rsults)){
                $follow = $row['follow'];
                $loc = $row['places'];
            }
        }
    $sql = "SELECT * FROM posts WHERE ext LIKE '$loc' OR username LIKE '$follow' ORDER BY `a_date` DESC LIMIT 4";
    $rsults = mysqli_query($conn, $sql);
    $resultsCheck = mysqli_num_rows($rsults);
    if ($resultsCheck > 0) {
        while ($row = mysqli_fetch_assoc($rsults)){
            echo '<div class="posts">';
            echo '<img class="img"src='.$row['img'].' width="1500px">';
            echo '</div>';
            echo '<div class="contain">';
            echo '<div class="over">';
            echo '<div class="username2">';
            echo '<img src="focus.png" width="25px" height="25px" style="padding-right: 10px;">'.'<a href="./Profile.php?data='.$row['username'].'">'.$row['username'].'</a>'.'<img src="loc.png" width="25px" height="25px" style="padding-right: 5px; padding-left: 10px;">'.'<a href="./Location.php?data='.$row['ext'].'">'.$row['ext'].'</a>';
            echo '</div>';
            echo '<div class="content">';
            echo $row['content'];
            echo '</div>';         
            echo '</div>';
        }
    }

?>

Also I know this is open to sql injection, it's just a proof of concept. Thanks for the help!!

Upvotes: 0

Views: 669

Answers (3)

geek_10101010
geek_10101010

Reputation: 90

Use an "%" sign when using the LIKE functionality.

$sql = "SELECT * FROM posts WHERE ext LIKE '%$loc%' OR username LIKE '$follow' ORDER BY `a_date` DESC LIMIT 4";

This would allow you to search through the content of a field.

Upvotes: 0

MilanG
MilanG

Reputation: 7114

When using LIKE you should add '%' sign at start and/or at end of LIKE parameter so it won't search for identical term but one starting or ending with the string you provided. Something like:

$sql = "SELECT * FROM posts WHERE ext LIKE '%$loc%' OR username LIKE '$follow' ORDER BY `a_date` DESC LIMIT 4";

So it will find location that contains your search criteria and not just those identical to it. '%' is replacing any number of characters here.

This goes if you have one search term and you have multiple terms in database column. But if you have multiple search terms you will have to generate your query dynamically, adding one "OR ext LIKE '%$loc%'" for every term.

Upvotes: 1

khan Farman
khan Farman

Reputation: 366

like operator in mysql comes with wild card e.g. '%', '_' etc.

you can use mysql query like this

$sql = "SELECT * FROM posts WHERE ext LIKE '%$loc%' OR username LIKE '$follow' ORDER BY `a_date` DESC LIMIT 4";

for example if $loc = 'canada', it will return all the rows which have 'canada' in thier column whether the column contains 'canada, abc' or 'abc, canada'

Upvotes: 1

Related Questions