Search code examples
phpmysqlpdoreplacetrim

Inches quotes and spaces making troubles inside MySQL

I have a long list of electrical equipment with a column for details where it contain information for each electrical component. (About 1k of rows).

So for LG, I have a detail like: LG SCREEN 40''

Now I have this query inisde my PHP page:

$type = $_REQUEST['type'];
$details = $_REQUEST['details'];
$quantity = $_REQUEST['quantity'];
$price = $_REQUEST['price'];
$update = "SELECT * FROM purchases
              WHERE sale_type = :type AND sale_details LIKE %:details%";
$prepUpd = $conn->prepare($update);
$prepUpd->bindValue(':type', $type);
$prepUpd->bindValue(':details', $details);
$prepUpdExec = $prepUpd->execute();
$result = $prepUpd->fetchAll();

The reason that I added LIKE %:details% is because my client added some data using multiple space so I have some variables like that: LG LED 32'' (I DON'T KNOW EVEN WHY HE DID THIS) and this space make errors when I want to select this type of details.

So the use of % gives me this error:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%'LED SAMSUNG 40\'\'''

And I think that the inches '' in database is making some trouble when selecting and updating, so any help is appreciated.

And is their any way to remove the extra space inside each cell? And how to solve the problem of inches quote '', and of course the error about like % :...

Solution

  • 1. Using like

    You have to add the % to the value being queried, not to your sql statement.

    $details = '%' . $_REQUEST['details'] .'%';
$update = "SELECT * FROM purchases WHERE sale_type = :type AND sale_details LIKE :details";
$prepUpd = $conn->prepare($update);

    2. Removing spaces

    Best solution is to "normalize" your type column. Substitute any sequence of spaces by a single space. This should be done when storing the data in your table. If there are no multiple spaces everything will become muchg easier.

    Edit 1: If you want to sanitize your data manually you can use the following sql command

    update purchases set sale_type = replace(sale_type,'  ',' ');

    Repeat this multiple times until no more rows are affected by the query.

    Probably you don't like to do that... So let's look at the alternatives.

    MySql offers replace() and trim()for string substitution and removal of leading/trailing spaces.

    trim() example:

    select trim('     XXX     '); # result 'XXX'

    This is a good result and will be useful but now comes the problem...

    replace() example

    # Replace two spaces by one space
select replace('     XXX     ','  ',' '); # result'   XXX   '

    As you can see this will indeed replace two spaces with one space but the result may be somehow surprising. The output string will still have multiple spaces.

    It may be tempting to use multiple replace() calls to get better results:

    # replace three spaces by one space, than replace two spaces by one space
select replace(replace('     XXX     ','   ',' '),'  ',' '); # result: ' XXX '

    Still one space left :-(

    You can use as many replace() inside replace() you will never get a solution which works in each and every case.

    Conlusion: This may work in many situations but it will not work in all situations.

    If you want to apply replace() to your code nevertheless then try it like this:

    $update = "SELECT * FROM purchases
          WHERE replace(sale_type,'  ',' ') = replace(:type,'  ',' ') AND sale_details LIKE %:details%";

    Edit 2 You could switch from = to like

    // Replace each sequence of spaces with "% %"
$type = preg_replace('/\s+/','% %',$type);
$update = "SELECT * FROM purchases WHERE sale_type like :type AND sale_details LIKE %:details%";

    3. And how to solve the problem of inches quote ''

    I really believe you have no problem with quotes. When using prepared statement care is taken to escape those characters.