Case insensitive but sensitive

Question

That's my database: database

and that's two rows rows

When i try to find "CEC" in "banca" row, everything works fine (display my rows with CEC banca). If i try to find "cec" or "Cec" or "ceC" in "banca" row, no result and i don't know WHY!!! My search works if i submit the exact "TEXT" and i don't want that.

That's the php code:

$valueToSearch = $_POST['valueToSearch'];
// search in all table columns
// using concat mysql function
$query = "SELECT * FROM fachitate WHERE CONCAT (ID, data, furnizor, suma, banca, firma, observatii) LIKE '%".$valueToSearch."%'" ;
$search_result = filterTable($query);

// function to connect and execute the query

    function filterTable($query)
    {
        $connect = mysqli_connect("localhost", "root", "MYPASS", "facturi_db");
        $filter_Result = mysqli_query($connect, $query);
        return $filter_Result;
    }


echo "<table>
<tr>
<th>ID</th>
<th>Data</th>
<th>Furnizor</th>
<th>Suma</th>
<th>Banca</th>
<th>Firma</th>
<th>Observatii</th>
</tr>";
while($row = mysqli_fetch_array($search_result))
  {
    echo "<td><font color='#000000'>" . $row['ID'] . "</td>";
    echo "<td><font color='#000000'>" . $row['data'] . "</td>";
    echo "<td><font color='#000000'>" . $row['furnizor'] . "</td>";
    echo "<td><font color='#000000'>" . $row['suma'] . "</td>";
    echo "<td><font color='#000000'>" . $row['banca'] . "</td>";
    echo "<td><font color='#000000'>" . $row['firma'] . "</td>";
    echo "<td><font color='#000000'>" . $row['observatii'] . "</td>";
  
  echo "</tr>";
    }

And the form:

    <form action="myfilter.php" method="post">
        <input type="text" name="valueToSearch" placeholder="Adauga nou filtru">
        <input type="submit" name="search" value="Filtreaza"><br><br>
     </form>

Please help! Thank you!

See this previous post : http://stackoverflow.com/questions/5938037/how-to-make-a-select-in-php-mysql-case-insensitive Hope it helps — Renaud Michotte, Oct 25 '16 at 17:35
what's the collation on that table/field? If it's a case-sensitive collation, then `cEc != CEC` — Marc B, Oct 25 '16 at 17:36
Following @MarcB's comment - run a `show create` query on your table and add the complete creation query to the question. — Dekel, Oct 25 '16 at 17:41

score 0 · Answer 1 · answered Oct 25 '16 at 17:39

0

Update your select like this :

$query = "SELECT * FROM fachitate WHERE LOWER(CONCAT(ID, data, furnizor, suma, banca, firma, observatii)) LIKE '%".strtolower($valueToSearch)."%'" ;

If you convert your search key and the search term into lowercase, the case become insensitive ;)

answered Oct 25 '16 at 17:39

Renaud Michotte

389
4
17

Such a waste of resources! – Dekel Oct 25 '16 at 17:40

Case insensitive but sensitive

1 Answers1