Viewed   87 times

I am trying to write prepared statement for user input. parameter numbers are variable depends on user input. i am trying this code

php code:

$string          = "my name";
$search_exploded = explode( " ", $string );
$num             = count( $search_exploded );
$cart            = array();
for ( $i = 1; $i <= $num; $i ++ ) {
    $cart[] = 's';
$str          = implode( '', $cart );
$inputArray[] = &$str;
$j            = count( $search_exploded );
for ( $i = 0; $i < $j; $i ++ ) {
    $inputArray[] = &$search_exploded[ $i ];
print_r( $inputArray );
foreach ( $search_exploded as $search_each ) {
    $x ++;
    if ( $x == 1 ) {
        $construct .= "name LIKE %?%";
    } else {
        $construct .= " or name LIKE %?%";
$query = "SELECT * FROM info WHERE $construct";
$stmt  = mysqli_prepare( $conn, $query );
call_user_func_array( array( $stmt, 'bind_param' ), $inputArray );
if ( mysqli_stmt_execute( $stmt ) ) {

    $result = mysqli_stmt_get_result( $stmt );
    if ( mysqli_num_rows( $result ) > 0 ) {
        echo $foundnum = mysqli_num_rows( $result );
        while( $row = mysqli_fetch_array( $result, MYSQLI_ASSOC ) ) {

            echo $id = $row['id'];
            echo $name = $row['name'];

when i print_r $inputArray output is this:

   Array ( [0] => ss [1] => my [2] => name ) 

there is no error showing in error log. what is wrong i am doing here please tell me.



The % wrapping goes around the parameters, not the placeholders.

My snippet will be using object-oriented mysqli syntax instead of the procedural syntax that your code demonstrates.

First you need to set up the necessary ingredients:

  1. the WHERE clause expressions -- to be separated by ORs
  2. the data types of your values -- your values are strings, so use "s"
  3. the parameters to be bound to the prepared statement

I am going to combine #2 and #3 into one variable for simpler "unpacking" with the splat operator (...). The data type string must be the first element, then one or more elements will represent the bound values.

As a logical inclusion, if you have no conditions in your WHERE clause, there is no benefit to using a prepared statement; just directly query the table.

Code: (100% Tested / Successful Code)

$string = "my name";

$conditions = [];
$parameters = [''];
foreach (array_unique(explode(' ', $string)) as $value) {
    $conditions[] = "name LIKE ?";
    $parameters[0] .= 's';
    $parameters[] = "%{$value}%";
// $parameters now holds ['ss', '%my%', '%name%']

$query = "SELECT * FROM info";
if ($conditions) {
    $stmt = $conn->prepare($query . ' WHERE ' . implode(' OR ', $conditions));
    $result = $stmt->get_result();
} else {
    $result = $conn->query($query);
foreach ($result as $row) {
    echo "<div>{$row['name']} and whatever other columns you want</div>"; 

For anyone looking for similar dynamic querying techniques:

  • SELECT with dynamic number of values in IN()
  • INSERT dynamic number of rows with one execute() call
Tuesday, August 2, 2022

NO, A prepared statement would not be a solution because it is not possible to bind the table name. So avoid to use prepared statement for Truncate Table.

You cannot bind any SQL literal but data one. So keywords, operators and any identifier can not be bind using prepared statement. You can only bind data.

PDO prepared statements are useful when running queries with user input as they allow you to use features such as bound parameters to sanitise user input.

So In my suggestion you should not use prepared statement for truncate table.

If you really want to truncate using prepared , In case of Opencart which you are using, Use the code:

$sql = sprintf('TRUNCATE TABLE %s%s', DB_PREFIX, $table);

try with this once and let me know

Friday, October 21, 2022

you want the following:

$start = 1; $postsPerPage = 1;
$sql = "SELECT id, title, author, LEFT(description, 40) AS excerpt, 
               image_small, image_med, date 
        FROM posts 
        ORDER BY id DESC 
        LIMIT ?, ?";

$stmt = $connect->prepare($sql) or die ('error');
$stmt->bind_param('ii', $start, $postsPerPage);
$stmt->bind_result($id, $title, $author, $excerpt, $image_small, $image_med, $date);

while($stmt->fetch()) {
  printf('<h1>%s</h1><p>%s <small> by %s on %s</small></p>',

this binds both question marks to integer (i) values of $start and $postsPerPage. do NOT use variables directly in prepared statements, because that would defeat the whole purpose of prepared statements (apart from eliminating parsing time)

Friday, December 23, 2022

If you need to perform a selection of all of the columns:

SELECT * FROM `table`

You would use PHP's get_result() rather than bind_result().

bind_result() is better when you're specifying each column that you're retrieving where get_result() will allow you to work with a more generic return of data from your tables.

Saturday, September 10, 2022


ps.setString(1, "%" + o.getOrgan() + "%");

Note the absence of quotes.

Friday, October 14, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :