What is the best MySQL sorting method for cases containing spaces?

Question

So, I have a project where the user can update a field and this functionality works fine.

The problem occurs when the user enters a space in the field ' '.

For example, suppose the field originally was test1 and the user changes it to test 1. The change will take effect and will also print as expected.

However, if the user continues to type anything from test1 to test1 or anything else, this change will not occur at all. No matter what the user inputs.

I have a feeling this has to do with the collation I'm using in the database, no matter which collation I use, the problem persists, and, frankly, I don't know much about collations.

The code to update the field is very simple:

`$query = $pdo -> prepare("SELECT 1 FROM table WHERE field = ?");
    $query -> bindValue(1, $new_name);

    $query -> execute();
    $num = $query -> rowCount();

    if ($num == 0) {

        $query = $pdo -> prepare("UPDATE table SET table = ? WHERE table = ?");
        $query -> bindValue(1, $new_name);
        $query -> bindValue(2, $old_name);

        $query -> execute();
    }`

Does anyone have any input on my issue, either on the cause of the problem or how to fix it?

Thank you in advance.

Answer 1

• To see the exact contents stored in column col, execute SELECT HEX(col) .... Spaces will appear as 20.

• To remove leading and trailing spaces entered in <form>, use PHP's trim() function.

• Depending on the data type of the column, MySQL will ignore trailing spaces. Are you using CHAR, VARCHAR, TEXT, BLOB, or another type?

• Otherwise, leading and internal whitespace will be preserved by all parties involved.

  mysql> SELECT HEX("test 1");
    +---------------+
    | HEX("test 1") |
    +---------------+
    | 746573742031  |
    +---------------+
      t e s t   1