CODEWITHSUNDEEP
phpmysqlpdoprepared-statement
Guy Sudai
Guy Sudai

Reputation: 36

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064

I am making a shop and using an input to get results, now I have the AJAX that calls the PHP script and it calls it fine, but I get an error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064

NOTE: The error line is the $query->execute(array(':input'=>$input)) line

here's the AJAX script ( + HTML calling the function ) 

                     <input type="text" name="search_item" onkeyup="showItems(this.value)" id="search_item">
                     <script>
                        function showItems(str) {
                            if (str.length == 0) { 

                            } else {
                                var xmlhttp = new XMLHttpRequest();
                                xmlhttp.onreadystatechange = function() {
                                    if (this.readyState == 4 && this.status == 200) {
                                        document.getElementById("items").innerHTML = this.responseText;
                                    }
                                };
                                xmlhttp.open("GET", "searchScript.php?iName=" + str, true);
                                xmlhttp.send();
                            }
                        }
                    </script>

and here's the called PHP:

    $input = $_REQUEST["iName"];
    $input = "%".$input."%"; 
$dsn = 'mysql:host=xxx.com;dbname=dbNameHidden;charset=utf8mb4';
$username = 'hidden';
$password = 'hidden';

try{
    // connect to mysql
    $con = new PDO($dsn,$username,$password);
    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (Exception $ex) {
    echo 'Not Connected '.$ex->getMessage();
}
$query = $con->prepare("SELECT * FROM store AS s INNER JOIN product_pictures AS pp ON s.product_id = pp.id INNER JOIN product_name AS pn ON s.product_id = pn.id WHERE product_name LIKE %:input% LIMIT 9 ");
$query->execute(array(':input' => $input));
$items = $query->fetchAll();

Upvotes: 0

Views: 195

Answers (2)

Sᴀᴍ Onᴇᴌᴀ
Sᴀᴍ Onᴇᴌᴀ

Reputation: 8297

Add the wildcards to the parameter:

$query = $con->prepare("SELECT ... WHERE product_name LIKE :input LIMIT 9 ");
$query->execute(array(':input' => '%' . $input. '%'));

That way the wildcards are contained in the value, essentially making the query like this:

SELECT .... WHERE product_name LIKE '%name%'

Upvotes: 1

AbraCadaver
AbraCadaver

Reputation: 79024

Your query results in LIKE %'something'% which is not correct. Add % to the variable not the query. You want something like:

$input = "%$input%";

$query = $con->prepare("SELECT * FROM store AS s 
                        INNER JOIN product_pictures AS pp ON s.product_id = pp.id
                        INNER JOIN product_name AS pn ON s.product_id = pn.id
                        WHERE product_name LIKE :input LIMIT 9 ");
$query->execute(array(':input' => $input));

Upvotes: 0

Related Questions