How to Run the Bind_Param() Statement in PHP

How to run the bind_param() statement in PHP?

When binding parameters you need to pass a variable that is used as a reference:

$var = 1;

$stmt->bind_param('i', $var);

See the manual: http://php.net/manual/en/mysqli-stmt.bind-param.php

Note that $var doesn't actually have to be defined to bind it. The following is perfectly valid:

$stmt->bind_param('i', $var);

foreach ($array as $element)
{

    $var = $element['foo'];

    $stmt->execute();

}

Prepared statements and bind_param error handling

You can't get an error (nor have you gotten one) out of something that hasn't yet been executed, therefore the second conditional statement won't throw an error. You need to check if the execution was successful and not the against the bind_param() method.

Then the third (conditional statement) won't theoretically thrown an error because of what you have in your query that would theoretically be considered as being a valid (query) statement.

What you need to do is to remove the if(!$statement) statement from the bind, but keep it in the execution part.

You will then receive an error.

Your first conditional statement for if($statement = $con->prepare($sqlQuery)) is valid, so the else for it won't throw an error since it hasn't been executed.

Consult the following reference manuals on PHP.net on how to query/check for errors properly and don't try to reinvent what wasn't intended to throw errors in the first place:

• http://php.net/manual/en/mysqli.query.php
• http://php.net/manual/en/mysqli.quickstart.prepared-statements.php

In short, error handling is done on the query (and its execution) and not on the binding.

Consult the manual on bind_param():

• http://php.net/manual/en/mysqli-stmt.bind-param.php

There is no mention or examples of error handling on that method.

How to select row using bind_param php mysqli?

Problem description:

The mysqli_result object returned by the method get_result looks something like this:

mysqli_result Object
(
    [current_field] => 0
    [field_count] => 3
    [lengths] => 
    [num_rows] => 1
    [type] => 0
)

As you can see, this object exposes only some properties (number of fields, number of rows, etc) about the record set from which you need to reference your data. So, you can not directly reference field values from it.

Solution:

In order to reach to the needed data you'll have to call one of the methods defined in the mysqli_result class (fetch_all, fetch_array, fetch_assoc, etc):

//...
$result = $stmt->get_result();
$row = $result->fetch_array(MYSQLI_ASSOC);
$page = $row['page'];
//...

with $row representing the fetched record and being an array like this:

Array
(
    [id] => 13
    [page] => 21
    ...
)

For more details read The mysqli_result class.

About error and exception handling:

Please note that a proper error and exception handling system is essential in the developing process. This article describes the steps needed to activate it in an elegant and thoroughly manner.

Extensive example:

For clarity, I prepared an extensive example with all components needed for accessing a database using the mysqli extension. It presents the situation of having to fetch one or more records from a list of users - saved in a db table named users. Each user is described by its id, name and age.

It's up to you to implement the error/exception handling system - as described in the above mentioned article.

index.php:

Option 1) Fetching only one record:

<?php

require 'connection.php';

// Assign the values used to replace the sql statement markers.
$id = 10;

/*
 * The SQL statement to be prepared. Notice the so-called markers, 
 * e.g. the "?" signs. They will be replaced later with the 
 * corresponding values when using mysqli_stmt::bind_param.
 * 
 * @link http://php.net/manual/en/mysqli.prepare.php
 */
$sql = 'SELECT 
            id,
            name,
            age 
        FROM users 
        WHERE id = ?';

/*
 * Prepare the SQL statement for execution - ONLY ONCE.
 * 
 * @link http://php.net/manual/en/mysqli.prepare.php
 */
$statement = $connection->prepare($sql);

/*
 * Bind variables for the parameter markers (?) in the 
 * SQL statement that was passed to prepare(). The first 
 * argument of bind_param() is a string that contains one 
 * or more characters which specify the types for the 
 * corresponding bind variables.
 * 
 * @link http://php.net/manual/en/mysqli-stmt.bind-param.php
 */
$statement->bind_param('i', $id);

/*
 * Execute the prepared SQL statement.
 * When executed any parameter markers which exist will 
 * automatically be replaced with the appropriate data.
 * 
 * @link http://php.net/manual/en/mysqli-stmt.execute.php
 */
$statement->execute();

/*
 * Get the result set from the prepared statement.
 * 
 * NOTA BENE:
 * Available only with mysqlnd ("MySQL Native Driver")! If this 
 * is not installed, then uncomment "extension=php_mysqli_mysqlnd.dll" in 
 * PHP config file (php.ini) and restart web server (I assume Apache) and 
 * mysql service. Or use the following functions instead:
 * mysqli_stmt::store_result + mysqli_stmt::bind_result + mysqli_stmt::fetch.
 * 
 * @link http://php.net/manual/en/mysqli-stmt.get-result.php
 * @link https://stackoverflow.com/questions/8321096/call-to-undefined-method-mysqli-stmtget-result
 */
$result = $statement->get_result();

/*
 * Fetch data and save it into an array:
 * 
 *  Array
 *  (
 *      [id] => 10
 *      [name] => Michael
 *      [age] => 18
 *  )
 * 
 * @link https://secure.php.net/manual/en/mysqli-result.fetch-array.php
 */
$user = $result->fetch_array(MYSQLI_ASSOC);

/*
 * Free the memory associated with the result. You should 
 * always free your result when it is not needed anymore.
 * 
 * @link http://php.net/manual/en/mysqli-result.free.php
 */
$result->close();

/*
 * Close the prepared statement. It also deallocates the statement handle.
 * If the statement has pending or unread results, it cancels them 
 * so that the next query can be executed.
 * 
 * @link http://php.net/manual/en/mysqli-stmt.close.php
 */
$statement->close();

/*
 * Close the previously opened database connection.
 * 
 * @link http://php.net/manual/en/mysqli.close.php
 */
$connection->close();

// Reference the values of the fetched data.
echo 'User id is ' . $user['id'] . '<br/>';
echo 'User name is ' . $user['name'] . '<br/>';
echo 'User age is ' . $user['age'] . '<br/>';

Option 2) Fetching multiple records:

<?php

require 'connection.php';

$id1 = 10;
$id2 = 11;

$sql = 'SELECT 
            id,
            name,
            age 
        FROM users 
        WHERE 
            id = ? 
            OR id = ?';

$statement = $connection->prepare($sql);

$statement->bind_param('ii', $id1, $id2);

$statement->execute();
$result = $statement->get_result();

/*
 * Fetch data and save it into an array:
 * 
 *  Array
 *  (
 *      [0] => Array
 *          (
 *              [id] => 10
 *              [name] => Michael
 *              [age] => 18
 *          )
 *  
 *      [1] => Array
 *          (
 *              [id] => 11
 *              [name] => Harry
 *              [age] => 59
 *          )
 *  )
 * 
 * @link http://php.net/manual/en/mysqli-result.fetch-all.php
 */
$users = $result->fetch_all(MYSQLI_ASSOC);

$result->close();
$statement->close();
$connection->close();

// Reference the values of the fetched data.
foreach ($users as $key => $user) {
    echo 'User id is ' . $user['id'] . '<br/>';
    echo 'User name is ' . $user['name'] . '<br/>';
    echo 'User age is ' . $user['age'] . '<br/>';

    echo '<hr/>';
}

connection.php:

<?php

// Db configs.
define('HOST', 'localhost');
define('PORT', 3306);
define('DATABASE', 'tests');
define('USERNAME', 'root');
define('PASSWORD', 'root');

/*
 * Error reporting.
 * 
 * Also, define an error handler, an exception handler and, eventually, 
 * a shutdown handler function to handle the raised errors and exceptions.
 * 
 * @link https://phpdelusions.net/articles/error_reporting Error reporting basics
 * @link http://php.net/manual/en/function.error-reporting.php
 * @link http://php.net/manual/en/function.set-error-handler.php
 * @link http://php.net/manual/en/function.set-exception-handler.php
 * @link http://php.net/manual/en/function.register-shutdown-function.php
 */
error_reporting(E_ALL);
ini_set('display_errors', 1); /* SET IT TO 0 ON A LIVE SERVER! */

/*
 * Enable internal report functions. This enables the exception handling, 
 * e.g. mysqli will not throw PHP warnings anymore, but mysqli exceptions 
 * (mysqli_sql_exception).
 * 
 * MYSQLI_REPORT_ERROR: Report errors from mysqli function calls.
 * MYSQLI_REPORT_STRICT: Throw a mysqli_sql_exception for errors instead of warnings. 
 * 
 * @link http://php.net/manual/en/class.mysqli-driver.php
 * @link http://php.net/manual/en/mysqli-driver.report-mode.php
 * @link http://php.net/manual/en/mysqli.constants.php
 */
$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = (MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

// Create a new db connection.
$connection = new mysqli(HOST, USERNAME, PASSWORD, DATABASE, PORT);

// Set the desired connection charset
$connection->set_charset('utf8mb4');

Test data:

id  name    age
---------------
9   Julie   23
10  Michael 18
11  Harry   59

Create table syntax:

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

php-use s or i for long in bind_param()?

You should almost always use s for everything. There are only a handful of scenarios when you would like mysqli to cast the value to the appropriate type for you. Binding everything as a string is the safest option. SQL will cast the value anyway to the type that it needs.

The comment you have found on php.net website meant that if you cast an integer string to a PHP native integer then it might overflow on 32-bit systems. PHP uses signed integers which means that the max value on a 32-bit system is only 2147483647. If you have an integer larger than this, you should use strings to prevent overflow.

Both, BIGINT (64-bit integer) and signed INT, are larger than the maximum integer on a PHP 32-bit system. However, if bind everything as a string you don't need to worry about losing any value.

How to pass a list of parameters contained in an array to bind_param?

Using PHP 5.6, you can do this easily with the help of the unpacking Operator (...$var) and use get_result() instead of bind_result().

$stmt->bind_param($types, ...$list);
$stmt->get_result();

When do I call bind_param if using MySQLi prepared statements in a loop?

Because the parameters are passed by reference, you can assign the values to the variables before or after the call to bind_param. Either way will work.

If the variables were passed by value, you would need to bind them each time you changed their value. But since they are passed by reference, you only need to bind them once.

In the case of a single execute, the order of operation doesn't really matter and may depend on where the values are coming from. In the case of a loop, you should definitely call bind_param before the loop.

bind_param and execute in loop?

There is no need to prepare a statement at the beginning of each iteration.

The concept of prepared statements is to reuse the same statement multiple times in the first place, so it's good to go to prepare once and execute it multiple times.

See also this note on the manual page.

php mysqli bind_param $insert-execute() always returns true

This is normal behavior. If execute() returns true, this just means that there was no SQL code / database / network error when executing the statement. It is natural to not consider it an error if no records are affected by a statement.

As @SearchAndResQ has suggested, you could check the affected rows. But be aware that this has its caveats, so you should read MySQL's ROW_COUNT() documentation and mysql_affected_rows() documentation first (one of them must be used by PHP behind the scenes).

Notably, the behavior with UPDATE depends on flags which are set when the connection to the database is established, and it might behave unexpectedly with REPLACE and ON DUPLICATE KEY UPDATE.

Personally, I prefer another method in such situations. I first SELECT the record(s) in question FOR UPDATE and then look into the result set to check if any records are affected. Then I am doing the update while the records are still locked. You need to use transactions for that (which I am doing anyway, so the additional effort is not too bad).

IMHO, that method is more portable, and its results are more understandable, so it is generally safer than the affected_rows mechanism. Its disadvantage is that you need to use transactions and that it might be slower.

mysqli bind_param Number of variables doesn't match number of parameters in prepared statement

When using LIKE in a prepared statement, it's a little bit different. You should add the % to the parameter before binding it to the statement.

Try something like below:

$param = "%{$_GET['search']}%";
$stmt = $sql->prepare("SELECT name, site, message, `when` FROM messages WHERE message LIKE ?");
$stmt->bind_param('s', $param);
$stmt->execute();
$result = $stmt->get_result();

---

Related Topics