Nnaerich Doughan
Nnaerich Doughan

Reputation: 31

PHP : Cannot get Exact match From MYSQL Database

This php has been really giving me issues. I want to Echo back the data from the database and i want it to show the main data from the database and i want it to show the data from the database to match the information entered from the database for instance if i enter the name "Paul Mason" and click the search button , it connects to the datbase and echos back the information on the site.

Code i have written shows below.

<html>
<title>Search Records</title>
<head>
<body>
    <form name="" id="" method="post" action="search.php"/>
    <p> Enter Student name : <input type="text" name="fullname" id="fullname"/>
    <input type="submit" name="senda" value="Search Data" />

</form>

<?php
if(isset($_POST['senda'])){

    include 'mysqlconn.php';
    $con = mysqli_connect($host, $dbuser, $pass, $db) or die('Cannot Connect');

    $name = $_POST['fullname'];

        $sql = "SELECT * FROM scores WHERE MATCH(fullname) AGAINST('$name')";
        $result = mysqli_query($con,$sql)  or die("Error: ".mysqli_error($con));

    while($row = mysqli_fetch_array($result,MYSQLI_ASSOC))
    {
        printf("%s (%s)\n", $row['Fullname'] ." ". $row['studentNo'] ." ". $row['SubjectName'] ." ". $row['GPA'] ." ". $row['CGPA'] ." ". $row['SCORE']);

        mysqli_free_result($result);
    }
     mysqli_close($con);
    }

?>
</body>
</head>
</html>

Instead it shows something else like this : Error: Can't find FULLTEXT index matching the column list

What ould be wrong, I need someone to correct me programmatically!

Upvotes: 2

Views: 894

Answers (1)

fully stacked geek
fully stacked geek

Reputation: 536

There is no FULLTEXT index on the column you are referencing. For a single column search, if you dont wish to create an index, try (converted into a prepared statement for you as well):

for loose match:

$con = new mysqli($host, $dbuser, $pass, $db) or die('Cannot Connect');
$name = $_POST['fullname'];
$query = $con->prepare("SELECT * FROM scores WHERE fullname LIKE '%$name%'");
$query->execute();

For exact match:

$con = new mysqli($host, $dbuser, $pass, $db) or die('Cannot Connect');
$name = $_POST['fullname'];
$query = $con->prepare("SELECT * FROM scores WHERE fullname = '$name'");
$query->execute();

If you do have or create a FULLTEXT index, then it will work but would still recommend preparing the query to prevent injection attacks:

$con = new mysqli($host, $dbuser, $pass, $db) or die('Cannot Connect');
$name = $_POST['fullname'];
$query = $con->prepare("WHERE MATCH(fullname) AGAINST('$name')");
$query->execute();

Upvotes: 1

Related Questions