CODEWITHSUNDEEP
phpmysqlsql
user2141625
user2141625

Reputation: 291

SQL error with LIKE

SELECT * FROM `orders` WHERE id LIKE %1%
Fatal error: Uncaught exception 'PDOException' with message '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 '%1%' at line 1

PHP

$sql = "SELECT * FROM `orders` ";

switch ($_POST['criteria']) {

    case 'id':
        $sql .= "WHERE id LIKE %" . (int) $_POST['search_input'] . "%";
    break;
    case 'OCR':
        $sql .= "WHERE OCR LIKE %" . $db->quote($_POST['search_input']) . "%";
    break;
    case 'name':
        $arr = explode(' ', $_POST['search_input']);
        $firstname = $arr[0];

        if (isset($arr[1])) {
            $lastname = $arr[1];
        } else {
            $lastname = null;
        }

        $sql .= "WHERE firstname LIKE %" . $db->quote($firstname) . "% AND lastname LIKE %" . $db->quote($lastname) . "%";
    break;
}

echo $sql;

$stmt = $db->query($sql);

$rows = $stmt->fetchAll();

The query is being outputted and it looks fine to me, but for some reason I am getting a syntax error ( I assume it is), however I can't seem to spot any problems?

Upvotes: 1

Views: 970

Answers (3)

alok.kumar
alok.kumar

Reputation: 380

Kindly write pattern in single qoute '' and like me sure

         incorrect     SELECT * FROM `orders` WHERE id LIKE %1%
           correct-     SELECT * FROM `orders` WHERE id LIKE '%1%'

Upvotes: 2

Nabil Kadimi
Nabil Kadimi

Reputation: 10384

You have quotes missing around your strings, so your quesries look something like:

SELECT * FROM orders where id LIKE %55%

instead of:

SELECT * FROM orders where id LIKE '%55%'


$sql = "SELECT * FROM `orders` ";

switch ($_POST['criteria']) {

    case 'id':
        $sql .= "WHERE id LIKE '%" . (int) $_POST['search_input'] . "%'";
    break;
    case 'OCR':
        $sql .= "WHERE OCR LIKE '%" . $db->quote($_POST['search_input']) . "%'";
    break;
    case 'name':
        $arr = explode(' ', $_POST['search_input']);
        $firstname = $arr[0];

        if (isset($arr[1])) {
            $lastname = $arr[1];
        } else {
            $lastname = null;
        }

        $sql .= "WHERE firstname LIKE '%" . $db->quote($firstname) . "% AND lastname LIKE '%" . $db->quote($lastname) . "%'";
    break;
}

echo $sql;

$stmt = $db->query($sql);

$rows = $stmt->fetchAll();

This answer should fix your problem but I strongly suggest you use = instead of LIKE since you are looking for unique orders identified by id.

Yhe way you script is currently written, if id is 55, you will get orders 55, 255, 5500, 1559...

Upvotes: 3

Praveen Prasannan
Praveen Prasannan

Reputation: 7123

LIKE operator is a string function. So you need to enclose it with single quotes(').

SELECT * FROM `orders` WHERE id LIKE '%1%';

Upvotes: 3

Related Questions