Essential Books

Prepared statements in MySQLi

suggest change

Please read http://stackoverflow.com/documentation/php/5828/pdo/2685/preventing-sql-injection-with-parametrized-queries for a complete discussion of why prepared statements help you secure your SQL statements from SQL Injection attacks

The $conn variable here is a MySQLi object. See MySQLi connect example for more details.

For both examples, we assume that $sql is 

$sql = "SELECT column_1 
    FROM table 
    WHERE column_2 = ? 
        AND column_3 > ?";

The ? represents the values we will provide later. Please note that we do not need quotes for the placeholders, regardless of the type. We can also only provide placeholders in the data portions of the query, meaning SET, VALUES and WHERE. You cannot use placeholders in the SELECT or FROM portions.

Object oriented style 

if ($stmt = $conn->prepare($sql)) {
  $stmt->bind_param("si", $column_2_value, $column_3_value);
  $stmt->execute();

  $stmt->bind_result($column_1);
  $stmt->fetch();
  //Now use variable $column_1 one as if it were any other PHP variable
  $stmt->close();
}

Procedural style 

if ($stmt = mysqli_prepare($conn, $sql)) {
  mysqli_stmt_bind_param($stmt, "si", $column_2_value, $column_3_value);
  mysqli_stmt_execute($stmt);
  // Fetch data here
  mysqli_stmt_close($stmt);
}

The first parameter of $stmt->bind_param or the second parameter of mysqli_stmt_bind_param is determined by the data type of the corresponding parameter in the SQL query:

Parameter | Data type of the bound parameter | ——— | –––––––––––––––– |i | integer |d | double |s | string |b | blob |

Your list of parameters needs to be in the order provided in your query. In this example si means the first parameter (column_2 = ?) is string and the second parameter (column_3 > ?) is integer.

For retrieving data, see http://stackoverflow.com/documentation/php/2784/php-mysqli/24001/how-to-get-data-from-a-prepared-statements

Found a mistake? Have a question or improvement idea? Let me know.

Feedback about page:

Feedback:
Optional: your email if you want me to get back to you:

PHP MySQLi:
* PHP MySQLi
* Close connection
* MySQLi connect
* Loop through MySQLi results
* Prepared statements in MySQLi
* Escaping Strings
* MySQLi query
* Debugging SQL in MySQLi
* How to get data from a prepared statement
* MySQLi Insert ID
Table Of Contents
0 Getting started
1 Variables
2 Arrays
3 Functional programming
4 Types
5 Autoloading primer
6 Exception handling and error reporting
7 Working with dates and time
8 Sending email
9 Sessions
10 Cookies
11 Classes and objects
12 Password hashing
13 Output buffering
14 JSON
15 SOAP
16 Reflection
17 cURL
18 Dependency injection
19 XML
20 Regular expressions
21 Traits
22 Namespaces
23 Parsing HTML
24 Composer dependency manager
25 Magic methods
26 Alternative syntax for control structures
27 File handling
28 Magic constants
29 Type hinting
30 Multi-threading extension
31 Filters, filter functions
32 Generators
33 Operators
34 Constants
35 UTF-8
36 URLs
37 Object serialization
38 PHPDoc
39 Contributing to PHP Manual
40 String parsing
41 Loops
42 Control structures
43 Serialization
44 Closur
45 Reading request data
46 Type juggling and non-strict comparison issues
47 Security
48 PHP MySQLi
49 Command Line Interface CLI
50 Localization
51 Debugging
52 Superglobal variables
53 Unit testing
54 Variable scope
55 References
56 Compilation errors and warning
57 Installing PHP on Windows
58 DateTime class
59 Headers manipulation
60 Performance
61 Common Errors
62 Installing on Linux / Unix
63 Contributing to PHP Core
64 Coding conventions
65 Using MongoDB
66 Asynchronous programming
67 Using SQLSRV
68 Unicode Support
69 Functions
70 Create PDF files
71 Hot to detect client IP address
72 YAML
73 Image processing with GD
74 Multiprocessing
75 SOAP Server
76 Machine learning
77 Cache
78 Streams
79 Array iteration
80 Cryptography
81 PDO
82 SQLite3
83 Sockets
84 Outputting the value of a variable
85 String formatting
86 Compile PHP extensions
87 MongoDB
88 Manipulating an array
89 Executing upon an array
90 Processing multiple arrays together
91 SPL data structures
92 Comments
93 IMAP
94 Redis
95 Imagick
96 SimplXML
97 HTTP Authentication
98 Recipies
99 BC Math Binary Calculator
100 Docker deployment
101 WebSockets
102 APCu
103 Design patterns
104 Secure remember me
105 PHP mysqli
106 PHP built-in server
107 How to break down an URL
108 PSR
109 Contributors