how to dynamically bind parameters using prepared statement?

Question 1

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.

Question 2

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:

the WHERE clause expressions -- to be separated by ORs
the data types of your values -- your values are strings, so use "s"
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));
    $stmt->bind_param(...$parameters);
    $stmt->execute();
    $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

Question 3

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);
$this->db->query($sql);

try with this once and let me know

Question 4

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->execute();
$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>',
    htmlspecialchars($title),
    htmlspecialchars($excerpt),
    htmlspecialchars($author),
    htmlspecialchars($date)
  );
}

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)

Question 5

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.

Question 6

Use

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

Note the absence of quotes.

how to dynamically bind parameters using prepared statement?

Answers

php,mysqli,prepared-statement