0

I'm trying to create a simple search page for my website, but don't know how to search my database without using something like strpos(), which returns all results containg the query in order they are listed in the database.

I have tried to use strpos(), but I only get a list of results containing the query. Yes, I know that that is exactly what it does, but what would the better solution be?

Right now, my PHP looks like the below

$dbc = mysqli_connect ('localhost', 'username', 'password', 'DBName')
        or die ('Error connecting to the database.');

//Get and cleanse the query
$q = mysqli_real_escape_string($dbc, strtolower($_GET['q']));
$query = "SELECT * FROM games";
$result = mysqli_query($dbc, $query)
        or die('Error querying the database');

while($row = mysqli_fetch_array($result)) {
    $name = strtolower($row['name']);
    $img = $row['img'];
    $description = $row['description'];
    $url = $row['URL'];
    $creator = $row['creator'];

    if (strpos($name, $q) !== false){
        $name = $row['name'];
        echo "
            <div class='result'>
            <a class='result-link' href='$url'>
            <p class='name'>$name</p>
            </a>
            <p class='url'>$url</p>
            <p class='description'>$description</p>
            </div>
            \n";
    }
}

mysqli_close($dbc);

Please note that I am using strtolower() as a way to make the query and result non-case sensitive.

I expect the results would be in the order that they show up in the database, which makes sense and is happening, but what can I do to sort them?

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • It's a simple `ORDER BY` statement? – Progman Apr 20 '19 at 15:49
  • 1
    Look for full text search in SQL. – Michas Apr 20 '19 at 15:51
  • It depends how you want to order the results. Also, pulling *all* the database records out then checking if they match the query string is arguably not a great idea. You *might* wanna read up on [ILIKE / case-insensitive querying](https://stackoverflow.com/a/2876820/742129) or [full-text searching](https://www.digitalocean.com/community/tutorials/how-to-improve-database-searches-with-full-text-search-in-mysql-5-6-on-ubuntu-16-04) – nahanil Apr 20 '19 at 15:52
  • 1
    Did you every try the [LIKE syntax](https://dev.mysql.com/doc/refman/8.0/en/string-comparison-functions.html) – RiggsFolly Apr 20 '19 at 15:55
  • @nahanil should have thought of that –  Apr 20 '19 at 15:56
  • Note: The [object-oriented interface to `mysqli`](https://www.php.net/manual/en/mysqli.quickstart.connections.php) is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface where missing a single `i` can cause trouble. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is largely an artifact from the PHP 4 era when `mysqli` API was introduced and should not be used in new code. – tadman Apr 20 '19 at 18:33
  • You're escaping the string and comparing it its escaped form. While this will work for many strings, it won't work on any that have SQL characters in them. You should do this as a `WHERE` clause using an index, full-text or otherwise, to select the right records. Loading the entire database and going through it row by row is not a scalable solution. – tadman Apr 20 '19 at 18:34

1 Answers1

-1

I should see your database structure but supposing to have a relevence column then you simply have to make the query using the keywork order by this way:

$query = "SELECT * FROM games ORDER BY relevence ASC"; //Or you might use DESC depending on what kind of sort you need.
thesimon
  • 34
  • 5