LIKE clause , IN clause and Limit

PDO is easy to use, but there are some common mistakes developer would do as mentioned below.

One of them is using placeholders with LIKE SQL clause. Developer may run query like below :

$stmt = $pdo->prepare("SELECT * FROM table WHERE name LIKE '%?%'");

but  it will produce an error. To understand its nature one need to know, Parameter markers can represent a complete data literal only - a string or a number.  You cannot use  arbitrary SQL part. So, when working with LIKE, we have to send the query the usual way and add our "arbitrary SQL part" like below :-

<?php

$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');

$search = "%$search%";

$stmt = $db->prepare("SELECT * FROM table WHERE name LIKE ?");

$stmt->execute([$search]);

$data = $stmt->fetchAll();

The '%' needs to be in the variable, not in the statement, similarly you can use other.

Prepared statements and IN clause

As mentioned above, you cannot use an arbitrary query part with a placeholder. Thus, for a comma-separated placeholders like IN() SQL operator, you need to do it differently see below  :-

<?php

$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');

$arr = [1,2,3];

$sql = "SELECT * FROM table WHERE column IN (".implode(',',$arr).")";

$stm = $db->prepare($sql);

$stm->execute($arr);

$data = $stm->fetchAll();

LIMIT clause

PDO treats every parameter as a string. As a result, the prepared LIMIT ?,? query becomes LIMIT '10', '10' which is invalid syntax and query will fail.

There are two solutions:

  1. Turn the Emulation off 
    <?php
    
    $db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');
    
    $db->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );

    And parameters can be kept in execute():

    <?php
    
    $db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');
    
    $db->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );
    
    $stmt = $db->prepare('SELECT * FROM table LIMIT ?, ?');
    
    $stmt->execute([$offset, $limit]);
    
    $data = $stmt->fetchAll();
  2. Another way would be to bind these variables explicitly while setting the proper param type:

    <?php
    
    $db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');
    
    $stmt = $db->prepare('SELECT * FROM table LIMIT ?, ?');
    
    $stmt->bindParam(1, $offset,PDO::PARAM_INT);
    
    $stmt->bindParam(2, $limit,PDO::PARAM_INT);
    
    $stmt->execute();
    
    $data = $stmt->fetchAll();
    

Remember : PDO::PARAM_INT does not do type casting,  Thus, using it on a number that has a string type will cause the syntax error. If you want to type cast the string to int, you can use (int) to typecast.


Loading ...

Related Results :

  1. LIKE clause , IN clause and Limit
Note :
  • Related Posts are generally User Blog posts.
  • or Other tutorials from other networks of w3clan.com.
  • Any registered user can create related posts based on search term tags.

About the Author