searching through sql

Question

i want to make sql search

for now i am using this

    $term="red sky";
    $query=explode(' ', $term);         
    $sql = "SELECT * as result FROM `disk` WHERE ";
    $a=0;
    foreach ($query as $part)
    {
        $part=mysqli_real_escape_string($con, $part);
        $a++;
        if ($a==1)
        {
            $sql .= " title like '%".$part."%'";
        }
        else
        {
            $sql .= " and title like '%".$part."%'";    
        }
    }
    $sql = $sql." order by time desc";
    $i=0;
    if ($term!="")
    {
        $result = mysqli_query($con, $sql);
        $row = mysqli_fetch_array($result);
    }

so this results in showing results like:

vredy tskyi
redy sky
vredy sky
red sky

i want to order it somehow so it show "red sky" at first and other results are same so they can arrange themselves.

second thing--- this type of searching is a bit slow, i want to make it faster. please help me by making a new faster script or update in this one. (must work same but faster)

for more details plz comment. its hard to explain

check this thread. https://stackoverflow.com/questions/153633/natural-sort-in-mysql — SynapseIndia, Jul 07 '18 at 10:36
Use full text searching. https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html — user3783243, Jul 07 '18 at 10:41
@SynapseIndia similar problems but not same answer, for now i am only dealing with strings... — fghuj, Jul 07 '18 at 10:42
Make an index, use the `match` and `against`, and then it should give you back a score with how frequent the term(s) come up. Order by the score. — user3783243, Jul 07 '18 at 10:48
@user3783243 that seems good,but i dont know how to use it, can you change my query in fulltext search thing??? — fghuj, Jul 07 '18 at 10:51

score 0 · Answer 1 · answered Jul 07 '18 at 11:00

Sorry, but your question is not clear enough. I think this code should render fast.

$sql = ""; $add = "";
$term="red sky";
$query=explode(' ', $term);         
foreach ($query as $part)
{
    $add .= " title like '%".$part."%' and";
}
$add .= substr($add,0,-3);
$sql = "SELECT * as result FROM `disk` WHERE".$add."order by time desc";
if ($term!="")
{
    $result = mysqli_query($con, $sql);
    $row = mysqli_fetch_array($result);
}

And If you want to get the specific result on red & sky then you should use IN clause instead of LIKE. Example:

$sql = "";$new= [];
$term="red sky";
$query=explode(' ', $term); 
foreach($query as $part)
{
    $new[].= "'".$part."'";
}
$term=implode(',', $new);    
$sql = "SELECT * FROM `disk` WHERE title IN(".$term.") order by time desc";
if ($term!="")
{
    $result = mysqli_query($con, $sql);
    $row = mysqli_fetch_array($result);
}

also $term is a variable... so someone can also search for "more than 3 words" your code will not work properly' — fghuj, Jul 07 '18 at 11:06
check the links https://stackoverflow.com/questions/1250156/how-do-i-return-rows-with-a-specific-value-first and https://dba.stackexchange.com/questions/9109/case-order-by-with-multiple-columns-and-different-sort-options — Sourav Das, Jul 07 '18 at 11:14

searching through sql

1 Answers1