How do I query a database in PHP and return results based on matching user-input?

Question

I’m trying to write a PHP script with MySQLi to query a database.

I’d like it if the user-input could be checked against the database and then return a result from the column ‘conjugation’ if the string in the column ‘root’ of the table ‘normal_verbs’ is in the input.

So if the user input is something like "foobar", and the root-column has "foo", I'd like it to see 'foo' in 'foobar' and return that value of 'conjugation' in that row.

I can’t seem to get the query to work like I want it to. The one I'm using below is basically just a placeholder. I don't entirely understand why it doesn't work.

What I’m trying, is this :

        function db_connect() {

            static $connection;

            if(!isset($connection)) {
                $connection = mysqli_connect('localhost','user','password','Verb_Bank');
            }

            if($connection === false) {
                return mysqli_connect_error(); 
            }
            return $connection;
        }

        function db_query($query) {
            $connection = db_connect();
            $result = mysqli_query($connection,$query);

            return $result;
        } 

        function db_quote($value) {
                $connection = db_connect();
                return "'" . mysqli_real_escape_string($connection,$value) . "'";
            }    
$m= db_query("SELECT `conjugation` from normal_verbs where `root` in (" . $y . ")");
    if($m === false) {
        // Handle failure - log the error, notify administrator, etc.
    } else {
        // Fetch all the rows in an array
        $rows = array();
        while ($row = mysqli_fetch_assoc($m)) {
            $rows[] = $row;
        }
    }
    print_r ($rows);

It’s not giving me any errors, so I think it’s connecting to the database.

EDIT2: I was wrong. I was missing something obvious due to misunderstanding MySQLi and have edited the code accordingly. So the above code does work in that it connects to the database and returns a result, but I'm still stumped on a viable SQL statement to do what I want it to do.

Answer 1

Please try this:

SELECT 'conjugation' FROM 'normal_verbs' WHERE " . $y . " LIKE CONCAT('%',root,'%')

It selects all rows where root contains $y anywhere.

In addition, your code is vulnerable to SQL injections. Please look here for more information.