www.webdeveloper.com
Results 1 to 3 of 3

Thread: PDOException error when i try to add 2nd bindValue

  1. #1
    Join Date
    Nov 2013
    Posts
    4

    PDOException error when i try to add 2nd bindValue

    Hi All,

    This one has me stumped.

    I have the following code which is working just fine:

    Code:
    $options = array(PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC);
    $db = new PDO("mysql:host={$host};dbname={$dbname};charset=utf8", $username, $password, $options);
    Code:
    //BUILD SQL QUERY FOR DISTRIBUTORS OVERVIEW
    $stmt = $db->prepare("SELECT distributors.ID, distributors.Distributor, distributors.Region, distributors.CM, users.First_Name, users.Last_Name FROM distributors INNER JOIN users ON distributors.CM=users.ID LIMIT :page, 18");
    $stmt->bindValue(':page', $page, PDO::PARAM_STR);
    $stmt->execute(); 
    $rows = $stmt->fetchAll();
    I would like to insert another parameter:

    Code:
    if ($s == 'search'){$search_query_terms = "WHERE $filter LIKE '%$terms%'";} else {$search_query_terms = null;};
    Code:
    $stmt = $db->prepare("SELECT distributors.ID, distributors.Distributor, distributors.Region, distributors.CM, users.First_Name, users.Last_Name FROM distributors INNER JOIN users ON distributors.CM=users.ID :search_query_terms LIMIT :page, 18");
    $stmt->bindValue(':page', $page, PDO::PARAM_STR);
    $stmt->bindValue(':search_query_terms', $search_query_terms, PDO::PARAM_STR);
    $stmt->execute(); 
    $rows = $stmt->fetchAll();
    But this returns an error:

    PHP 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 '? LIMIT ?, 18' at line 1' in

    Any ideas? It works fine if I insert the variable directly into the query.

  2. #2
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    731
    IIRC - you cannot use anything but distinct values as arms so your method of setting your where clause up to be used in the bindparam line is invalid.

  3. #3
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,337
    Yep, to the above reply.

    I've handled it in the past by having two if statements: one to add the additional clause to the SQL string when needed, including a place-holder for the value; then another if() with the same condition to execute the bind function for that parameter.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles