Please help search results between MySQL and PHP using the LIKE operator

Question

I am trying to create a search bar on my website to search for users name. When using mysql on my database the code works fine but when I use it on my website the results are not the same or don't work at all. Not sure if there is a setting I'am missing. Any help would be great.

In mysql database this works.

SELECT * 
  FROM `users` 
 WHERE username LIKE '%neva%'

This returns the user named Nevada.

On my site this doesn't work.

$friendquery = "neva"

if(isset($_GET['friendquery']){
    $friendquery = preg_replace("/[^0-9a-zA-Z ]/", "", $_GET['friendquery']);
    $sql = "SELECT * FROM users WHERE username LIKE '%$friendquery%'";

    $query = mysqli_query($db_connect, $sql);
    $u = $row["username"];
    echo $u;
}

This returns nothing. Yes I'am connected to the database.

The weird part is that some names work and some don't. Like I said any help would be great.

you have to fetch the date before using as $row["username"]; — Abhik Chakraborty, Mar 11 '14 at 06:34
Sorry about the typo. I realize that I forgot the round bracket at the end of the if statement. — user3399921, Mar 12 '14 at 04:05

NoobEditor · Accepted Answer · 2014-03-11T06:52:14.670

You have error in your php condition, so control is not going to SQL at all

here is error :

(isset($_GET['friendquery'])

u need extra closing braces => )

So condition should be :

if(isset($_GET['friendquery']))
     /* notice extra closing ^^ braces */

In addition

$friendquery = "neva"

if(isset($_GET['friendquery']))

^^ this condition is bound to fail always if you are not fetching friendquery from url....so, if u want to execute it in relations to $friendquery = "neva", then, use

if($friendquery != "")

And finally :

$row = mysqli_fetch_array($query)

is also missing from your code.

So final code should be :

if(isset($_GET['friendquery'])){ /* close extra brackets*/
    $friendquery = preg_replace("/[^0-9a-zA-Z ]/", "", $_GET['friendquery']);
    $sql = "SELECT * FROM users WHERE username LIKE '%$friendquery%'";

    $query = mysqli_query($db_connect, $sql);
    $row = mysqli_fetch_array($query) /* fetch array */
    $u = $row["username"];
    echo $u;
}

@noodeditor this worked $row = mysqli_fetch_array($query) thanks. — user3399921, Mar 12 '14 at 04:07

webzar · Answer 2 · 2014-03-11T06:46:24.853

I think you missed the $row. Try this code

if(isset($_GET['friendquery'])){
    $friendquery = preg_replace("/[^0-9a-zA-Z ]/", "", $_GET['friendquery']);
    $sql = "SELECT * FROM users WHERE username LIKE '%$friendquery%'";

    $query = mysqli_query($db_connect, $sql);
    if(mysqli_num_rows($query) > 0)
    {
       while($row =   mysqli_fetch_array($query))
       {
         $u = $row["username"];
         echo $u;
       }
    }
}

score 0 · Answer 3 · answered Mar 11 '14 at 06:42

Your code has two problems:

Minor typo, major implications: you're missing a parenthesis ()) in the if statement
Your mysqli_query results need to be fetched before it can be used. We use a loop which is run for each of the matching rows.

So your PHP would be:

$friendquery = "neva"

if(isset($_GET['friendquery'])){ // typo: missing a closing parenthesis
    $friendquery = preg_replace("/[^0-9a-zA-Z ]/", "", $_GET['friendquery']);
    $sql = "SELECT * FROM users WHERE username LIKE '%$friendquery%'";

    $query = mysqli_query($db_connect, $sql); // run the mysql query
    while($row = mysqli_fetch_array($query)){ // actually get each query results and 
                                              // put them in $row
    $u = $row["username"]; // use them!
    echo $u;
}

Please help search results between MySQL and PHP using the LIKE operator

3 Answers3