Search value in database with html and php

Question

I just want to add search system for my website My PHP Code:

<?php
$button = $_GET ['submit'];
$search = $_GET ['search'];

//connect db
$con=mysqli_connect("localhost","root","","website");
    $sql = "SELECT * FROM search WHERE MATCH(language, title, command) AGAINST ('$" . $search . "%')";

    $run = mysqli_query($con,$sql);
    $foundnum = mysqli_num_rows($run);

    if($foundnum == 0){
        echo "Sorry we don't found any '<b>$search</b>'.";
    }else{
        echo "<h1> $foundnum Result found for \"".$search."\"</h1>";

        //get num of results stored in db
        $sql = "SELECT * FROM website WHERE MATCH(language,title,command) AGAINST ('%" . $search . "%')";
        $getquery = mysqli_query($con,$sql);

        while($runrows = mysqli_fetch_array($getquery)){
            $link = $runrows["URL"];

            echo "<h5>".$runrows["title"]."</h5>";
        }
    }
?>

My HTML Code:

<!DOCTYPE html>
<html>
    <head>
        <title>Search</title>
    </head>
    <body>
        <form name="searchForm" method="get" action="search.php">
            <input type="text" placeholder="Search" name="search" aria-label="Search" required>
            <input type="submit" value="Search" name="submit"></input>
        </form>
    </body>
</html>

My database:

language    title          command
  sql    SELECT in sql     SELECT

but I got error when I press "Search" button :


Fatal error: Uncaught mysqli_sql_exception: Can't find FULLTEXT index matching the column list in D:\NewXAMPP\htdocs\Test\search\search.php:18 Stack trace: #0 D:\NewXAMPP\htdocs\Test\search\search.php(18): mysqli_query(Object(mysqli), 'SELECT * FROM s...') #1 {main} thrown in D:\NewXAMPP\htdocs\Test\search\search.php on line 18

I just try to search about my problem in internet but I didn't get any answer. Please help me everyone thanks!

Do not use this approach - it is wide open to sql injection. Always use a `prepared statement` when dealing with user supplied data. — Professor Abronsius, Apr 03 '23 at 09:56
The error message suggests that the column `fulltext` index assigned to the columns used and it depends upon which database engine you use as its not, afaik, available to innodb — Professor Abronsius, Apr 03 '23 at 10:00
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) — Dharman, Apr 03 '23 at 11:10

score 0 · Answer 1 · answered Apr 03 '23 at 10:06

As mentioned in following error, your table doesn't have FULLTEXT index. To use those 3 columns language,title,command in MATCH you need to add FULLTEXT index for those columns first.

FULLTEXT(language,title,command)

for that, write alter query for you table like this

ALTER TABLE `website` ADD FULLTEXT(`language`,`title`,`command`);

Then, when you will run your search query using MATCH, it will work.

Search value in database with html and php

1 Answers1