PHP : Cannot get Exact match From MYSQL Database

Question

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!

[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). — Jay Blanchard, Feb 09 '16 at 13:03
Have you specified a FULLTEXT index on the column you're querying? — Jay Blanchard, Feb 09 '16 at 13:04

fully stacked geek · Accepted Answer · 2016-02-09T13:34:22.690

1

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();

edited Feb 09 '16 at 13:34

answered Feb 09 '16 at 13:20

fully stacked geek

536
4
10

1

I think you might need `LIKE` rather than `=` in that first one. – Matt Gibson Feb 09 '16 at 13:26
I think the OP requires *exact match* (see title) so `=` might be correct – Kaii Feb 09 '16 at 13:33
added in both ways to cover. – fully stacked geek Feb 09 '16 at 13:34
1

Also, your prepared statement is also prone to SQL injection. Instead of putting the user input in the SQL string directly, you should bind it as a parameter. This is how prepared statements should be generally used. – Kaii Feb 09 '16 at 13:34
Yea , i require an Exact match, For instance I click the page and it opensi search for "Paul Mason" and it goes and fetches the exact name of Paul Mason in the db as well as the Student Number,subjectname, cgpa , score and prints on the webpage – Nnaerich Doughan Feb 09 '16 at 13:36

PHP : Cannot get Exact match From MYSQL Database

1 Answers1