PHP MySQL: Search database for string (in array) missing results

Question

I am creating a feature which searches a database for a string. The PHP shows no errors using a PHP validator and, if the search term doesn't exist, it returns the correct errors. My problem is that, when searching for the term 'abandon hastily' in the column of the database entitled 'collocation' (which is currently the only entry in the database), no results are returned. Though I can see using phpMyAdmin that this entry does definitely exist.

The string is entered by a user into an input field using the following HTML:

<form action='http://www.murkyfiles.esy.es/search.php' method='GET'>
  <center>
    <p><label for='search'>Please enter your question as accurately as possible:</label></p>
    <p><input type='search' size='90' name='search'></p>
    <p><input type='submit'  name='submit' value='Find answer'></p>
  </center>
</form>

The term entered is searched on the database using the following PHP:

<?php
$button = $_GET [ 'submit' ];
$search = $_GET [ 'search' ];
$host = "[HOST URL]";
$username = "[USERNAME]";
$password = "[PASSWORD]";
$database = "[DATABASE]";
$searchlength = strlen($search);

if( !$button )
  echo "You didn't submit a keyword";
else {
  if( strlen( $search ) <= 1 )
    echo "Search term too short";
  else {
    echo "You searched for <b> $search </b> <hr size='1' > </ br > ";

    // Connect to database

    $con = mysqli_connect ( $host, $username, $password );

    if(!$con) {
        die('Could not connect: ' .PDO::errorInfo());
    }

    mysqli_select_db ( $con, $database );

    $search = str_split($search, $searchlength);

    $construct = " SELECT * FROM 'coll_test' WHERE collocation LIKE '%$search%' ";

    $run = mysqli_query( $con, $construct );

    //Fetch and return search results.

    if ($foundnum == 0)
        echo "Sorry, there are no matching results for <b> $search[0] </b>.
        </ br >
        </ br > 1. Try presenting your Something is wrong in a more academic manner. Guidance can be found on the majority of University websites without need for registration.
        </ br > 2. Try more common words/phrases with similar meaning. This search focuses on colloquialisms - commonly used phrases within a language.
        </ br > 3. Please check your spelling";

    else {
      echo "$foundnum results found !<p>";

      while ( $runrows = mysqli_fetch_assoc($run) ) {
          $collocation = $runrows ['collocation'];
        echo "<a href='$url'> <b> $title </b> </a> <br> $desc <br> <a href='$url'> $url </a> <p>";
      }
    }
  }
}

I have looked at various similar questions and none of them offer solution.

To clarify, the database table column headers are as follows:

collocation | left | right | length | google-results | bing-results | yahoo-results | url-link | wiki | date

There is, so far, only one entry in my database:

collocation = abandon hastily
left = abandon
right = NULL
length = 2
google-results = 24000000
bing-results = 386000
yahoo-results = 385000
url-link = oxforddictionary.so8848.com/search1?word=abandon
wiki = 0
date = [TIMESTAMP]

Answer 1

Wrap off quotes form table and column name instead use backtick and your code is open for sql injection user prepare and bind statement to prevent it

$search = $_GET ['search'];// get your value
$like = "%$search%";//
$stmt = $con->prepare("SELECT `collocation`,`left`,`right` FROM `coll_test` WHERE collocation LIKE ?");
$stmt->bind_param('s', $like);
$stmt->execute();
$stmt->bind_result($collocation,$left,$right);
$rows = $stmt->num_rows;// check your query return result of not
if ($rows > 0) {

    while ($stmt->fetch()) {// fetch data from query
       printf ("%s (%s)\n", $collocation,$left,$right);
       // fetch data form result set
    }
} else {
    echo "Sorry, there are no matching results for <b> $search </b>.";
}

Answer 2

A quite rewrite, with notes below:

$con = mysqli_connect ( $host, $username, $password, $database );

//$search = str_split($search, $searchlength); ///??? See below
$searchSafe = preg_replace("/[^0-9a-z-_ ]/i","",$search); //example only.

$construct = " SELECT COUNT(*) AS found FROM `coll_test` 
WHERE collocation LIKE '%".$searchSafe."%' ";

$run = mysqli_query($con, $construct);
$result = mysqli_fetch_array($run);

print $result['found']." number of results found!"; //for example.

1) You can include the database reference in the MySQLi connection function.

2) str_split returns an array but you are using the result as a string. This is confusing and incorrect, what do you intend to do with this?

• $_GET['search'] will always be a string type, so you do not need to use it as an array or any array-based messing around with it.

3) Having outside functions manually returning a number_rows count can be inaccurate, instead use COUNT within the SELECT statment.

4) You forgot to return the result of your actual query! So above I have inserted a mysql_fetch_array result to see the number of results. You also did not define a value for your $foundnum variable.

5) You are mixing PDO with MySQLi, these two connection methods are mutually exclusive. They do not mix.

6) You are wide open to SQL injection and database compromise you need to use Prepared Statements (as well exampled by Saty ) and use something like preg_replace (or another REGEX parser) to remove invalid characters from strings, such as:

$searchSafe = preg_replace("/[^0-9a-z-%_ ]/i","",$search); //example only.

The above would mean only 0-9 or a-z (case insensitive, /i) or -, % or _ are allowed in the string.

7) table or column names ('coll_test') should not be encased in single quotes, instead they shold be encased in backticks, if at all. In MySQL single quotes are for containing data strings only.