Why don't i get any results when my input variable has more than 1 character?

Question

So this is supposed to be a search field for songs. When i search for a single character like "o" for example, the result is songs with the letter "o" in any position. When i search for more than one character like "oo" for example, i don't get any results at all, even though i got songs with "oo" in the title.

<?php
include 'conn.php';

if (isset($_POST['submitsearch'])){
    $search=$_POST['search'];
    $query2 = "select * from songs where Title LIKE '%".$search."%'";

    $result2 = mysqli_query($connection, $query2);

    $row = mysqli_fetch_array($result2, MYSQLI_ASSOC);
    while($row = mysqli_fetch_array($result2, MYSQLI_ASSOC)){
      echo $row['Title'];
    }
}

Possible duplicate of [LIKE operator with $variable](https://stackoverflow.com/questions/1843640/like-operator-with-variable) — Ronnie Oosting, Nov 30 '17 at 14:34
What do you get if you search for ``' AND (SELECT LEFT(`name`,1) FROM `users` WHERE `id`=1)='A' AND '``? Answer: all the songs, but only if the admin username starts with an A (assuming it has ID of 1). Repeat until you find the letters of the entire username. Repeat until you find the password (hopefully hash thereof, but given the quality of the code you've shown... probably not). Pwn your website. — Niet the Dark Absol, Nov 30 '17 at 14:36

score 4 · Accepted Answer · answered Nov 30 '17 at 14:34

Possibly you only have one row with a oo in it. Your code will always loose the first row of the results set as you have a fetch OutSide the while loop.

Try without that like this

<?php
include 'conn.php';

if (isset($_POST['submitsearch'])){
    $search=$_POST['search'];
    $query2 = "select * from songs where Title LIKE '%".$search."%'";

    $result2 = mysqli_query($connection, $query2);

    // gets first row and then does nothing with it
    //$row = mysqli_fetch_array($result2, MYSQLI_ASSOC);
    while($row = mysqli_fetch_array($result2, MYSQLI_ASSOC)){
      echo $row['Title'];
    }
}

You should also know that your script is at risk of SQL Injection Attack Even if you are escaping inputs, its not safe! Use prepared parameterized statements

Why don't i get any results when my input variable has more than 1 character?

1 Answers1