- PHP Basics
- Learn PHP
- PHP Comments
- PHP Data Types
- PHP Variables
- PHP Operators
- PHP echo
- PHP print
- PHP echo vs. print
- PHP if else
- PHP switch
- PHP for Loop
- PHP while Loop
- PHP do...while Loop
- PHP foreach Loop
- PHP break and continue
- PHP Arrays
- PHP print_r()
- PHP unset()
- PHP Strings
- PHP Functions
- PHP File Handling
- PHP File Handling
- PHP Open File
- PHP Create a File
- PHP Write to File
- PHP Read File
- PHP feof()
- PHP fgetc()
- PHP fgets()
- PHP Close File
- PHP Delete File
- PHP Append to File
- PHP Copy File
- PHP file_get_contents()
- PHP file_put_contents()
- PHP file_exists()
- PHP filesize()
- PHP Rename File
- PHP fseek()
- PHP ftell()
- PHP rewind()
- PHP disk_free_space()
- PHP disk_total_space()
- PHP Create Directory
- PHP Remove Directory
- PHP Get Files/Directories
- PHP Get filename
- PHP Get Path
- PHP filemtime()
- PHP file()
- PHP include()
- PHP require()
- PHP include() vs. require()
- PHP and MySQLi
- PHP and MySQLi
- PHP MySQLi Setup
- PHP MySQLi Create DB
- PHP MySQLi Create Table
- PHP MySQLi Connect to DB
- PHP MySQLi Insert Record
- PHP MySQLi Update Record
- PHP MySQLi Fetch Record
- PHP MySQLi Delete Record
- PHP MySQLi SignUp Page
- PHP MySQLi LogIn Page
- PHP MySQLi Store User Data
- PHP MySQLi Close Connection
- PHP Misc Topics
- PHP Object Oriented
- PHP new Keyword
- PHP Cookies
- PHP Sessions
- PHP Date and Time
- PHP GET vs. POST
- PHP File Upload
- PHP Image Processing
PHP bind_param() and mysqli_stmt_bind_param()
This article is created to cover the two functions in PHP, that are:
- bind_param()
- mysqli_stmt_bind_param()
Both functions are used to bind variables to a prepared statement, as parameters. The only difference is, the bind_param() is used with object-oriented script, whereas the mysqli_stmt_bind_param() is used with procedural script.
PHP bind_param()
The PHP bind_param() function is used to bind variables to a prepared statement, as parameters, in PHP MySQLi object-oriented style. For example:
<?php $server = "localhost"; $user = "root"; $pass = ""; $db = "fresherearth"; $conn = new mysqli($server, $user, $pass, $db); if($conn->connect_errno) { echo "Database connection failed!<BR>"; echo "Reason: ", $conn->connect_error; exit(); } $sql = "INSERT INTO `customer`(`name`, `age`, `email`) VALUES (?, ?, ?)"; $stmt = $conn -> prepare($sql); $stmt -> bind_param("sis", $name, $age, $email); $name = "Susan"; $age = 35; $email = "susan@xyz.com"; if($stmt -> execute()) { echo "Record inserted successfully."; // block of code, to process further } $conn->close(); ?>
The output produced by above PHP example on bind_param(), is shown in the snapshot given below:
Note - The mysqli() is used to open a connection to the MySQL database server, in object-oriented style.
Note - The new keyword is used to create a new object.
Note - The connect_errno is used to get/return the error code (if any) from last connect call, in object-oriented style.
Note - The connect_error is used to get the error description (if any) from last connection, in object-oriented style.
Note - The prepare() is used to prepare an SQL statement before its execution on the MySQL database, in object-oriented style, to avoid SQL injection.
Note - The execute() is used to execute a prepared statement on the MySQL database, in object-oriented style.
Note - The close() is used to close an opened connection to the MySQL database, in object-oriented style.
The above example can also be written as:
<?php $conn = new mysqli("localhost", "root", "", "fresherearth"); if(!$conn->connect_errno) { $sql = "INSERT INTO `customer`(`name`, `age`, `email`) VALUES (?, ?, ?)"; $stmt = $conn -> prepare($sql); $stmt -> bind_param("sis", $name, $age, $email); $name = "Susan"; $age = 35; $email = "susan@xyz.com"; $stmt -> execute(); } $conn->close(); ?>
Note - In above example, the sis refers to string integer string, used to define the type of three parameters, given to bind_param(), that are $name (s for string), $age (i for integer), and $email (s for string).
PHP bind_param() Syntax
The syntax of bind_param() function in PHP, is:
$mysqli_stmt -> bind_param(types, variables..);
The types parameter is used to specify the parameters(s) types. Here are the characters, used to specify the type:
- s - Used for string type
- i - Used for integer type
- d - Used for double type
- b - Used for blob to sent in packets
The variables are basically the set of parameters, used to replace question marks (?) from the prepared SQL statement. For example:
<?php $conn = new mysqli("localhost", "root", "", "fresherearth"); if(!$conn->connect_errno) { $stmt = $conn->prepare("SELECT name FROM customer where id=?"); if($stmt==true) { $stmt->bind_param('i', $id); $id = 2; if($stmt->execute()) { $stmt->bind_result($res); $stmt->fetch(); echo $res; } } } $conn->close(); ?>
Since in the table named customer, available in the database fresherearth, at id number 2, the name Charlotte is stored. Therefore the output should be:
Charlotte
Note - The bind_result() is used to bind variables to a prepared statement for result storage, in object-oriented style.
Note - The fetch() is used to fetch results from a prepared statement into bound variables, in object-oriented style.
PHP mysqli_stmt_bind_param()
The PHP mysqli_stmt_bind_param() function is used to bind variables to prepared statement as parameters in PHP MySQLi procedural style. For example:
<?php $conn = mysqli_connect("localhost", "root", "", "fresherearth"); if(!mysqli_connect_errno()) { $sql = "INSERT INTO `customer`(`name`, `age`, `email`) VALUES (?, ?, ?)"; $stmt = mysqli_prepare($conn, $sql); mysqli_stmt_bind_param($stmt, "sis", $name, $age, $email); $name = "Susan"; $age = 35; $email = "susan@xyz.com"; mysqli_stmt_execute($stmt); } mysqli_close($conn); ?>
Note - The mysqli_connect() is used to open a connection to the MySQL database server, in procedural style.
Note - The mysqli_connect_errno() is used to get/return the error code (if any) from last connect call, in procedural style.
Note - The mysqli_prepare() is used to prepare an SQL statement before its execution on the MySQL database, in procedural style, to avoid SQL injection.
Note - The mysqli_stmt_bind_param() is used to bind variables to a prepared statement, as parameters, in procedural style.
Note - The mysqli_stmt_execute() is used to execute a prepared statement on the MySQL database, in procedural style.
Note - The mysqli_close() is used to close an opened connection to the MySQL database, in procedural style.
PHP mysqli_stmt_bind_param() Syntax
The syntax of mysqli_stmt_bind_param() function in PHP, is:
mysqli_stmt_bind_param($mysqli_stmt, types, parameters...);
« Previous Tutorial Next Tutorial »