Questions tagged [mysqli]

The mysqli PHP extension is a PHP database driver. Not to be confused with MySQL database.

The mysqli PHP extension is the successor to the mysql PHP extension. It provides a low-level mapping to MySQL's C interface. The extension's principle features consist of:

  • An object-oriented interface
  • Support for prepared statements
  • Support for multiple statements
  • Support for transactions
  • Enhanced debugging support
  • Embedded server support

Deprecation of the mysql extension

The mysql extension was deprecated in PHP version 5.5, and removed in version 7.0. Code written for modern servers must use the mysqli or pdo extensions instead.

In addition to an object-oriented interface, most mysqli features also provide an equivalent procedural interface through functions prefixed mysqli_. However, these functions were primarily intended for users transitioning away from legacy code using the mysql extension. Code in a modern environment is expected to use object-oriented programming.

The mysqli extension's prepared statement support makes use of ? placeholders bound to variable references for input, and variable references bound to columns when fetching output rows. Please note that, in order to use some aspects of mysqli prepared statements (most notably mysqli_stmt_get_result), your installation of PHP must use the Mysql Native Driver (mysqlnd), which also provides improved performance over the older MySQL Client Library.

A simple MySQLi SELECT query example:

The following example retrieves 2 output columns from a SELECT query using both an integer and a string parameter.

// A form post has supplied the input values in:
// $_POST['fruit']
// $_POST['age']

// Enable mysqli error reporting. Errors will be reported as exceptions
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

// Open a new connection to the MySQL server.
$mysqli = new mysqli("host", "user", "password", "database");

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

// SQL string with input placeholders
$SQL = "SELECT firstName, email FROM users WHERE favorite_fruit = ? AND age > ?";

// Prepare the statement
$stmt = $mysqli->prepare($SQL);

// Bind parameters:
// "s" indicates a string parameter (favorite_fruit)
// "i" indicates an integer parameter (age)
$stmt->bind_param("si", $_POST['fruit'], $_POST['age']);

// Execute the statement
$stmt->execute();

// Bind result variables to fetch the columns returned:
// Supply one variable for each column. Variables are bound by reference
$stmt->bind_result($firstName, $email);

// Fetch rows:
// On each loop iteration, the variables $firstName, $email will be 
// populated with values from the currently fetched row.
while ($stmt->fetch()) {
    echo "Name: $firstName, Email: $email\n";
}

// Close the prepared statement (optional)
$stmt->close();

Related tags

24962 questions
3
votes
4 answers

How do I process an associative array properly in a PDO (or MySQLi) query?

I'm using MySQLi for a project (but could easily switch to PDO, read the edit at the bottom). What I want is to be able to process an associative array into an mysql query without typing every key/value pair manually. I know that might be simply…
Bob
  • 1,066
  • 2
  • 14
  • 28
3
votes
1 answer

PHP/MySQLi returning incorrect float values using prepared statements

OK. I may be being thick here (it has been known) but have I found an undocumented "feature" of prepared statements in MySQLi in PHP 5.3.x or have I missed something pretty fundamental. Short version - Prepared statements in PHP via MySQLi and…
DaveyBoy
  • 2,928
  • 2
  • 17
  • 27
3
votes
3 answers

PHP Fetch data twice

My function looks like that. private function generateTree($courseID) { $q = "SELECT l.id, l.name AS lesson_name, c.name AS course_name FROM lessons AS l, courses AS c WHERE l.course_id=c.id AND c.id=?"; $stmt =…
heron
  • 3,611
  • 25
  • 80
  • 148
3
votes
3 answers

mysql real escape string - is this normal?

When I use mysqli->real_escape_string and add a string value to the db such as "who's", it adds the string without modifying the quote in any way. When I check the db, the value within it is "who's". When I do not use mysqli->real_escape_string and…
steeped
  • 2,613
  • 5
  • 27
  • 43
3
votes
5 answers

Looking for a simple mySQLi class example with descriptions

I am looking for an example of a simple, but functional example of a class extending the mySQLi class in PHP5. I am interested in stored procedures also. Basically what I need is an example of a practical and usable class. I learn best by example…
Jason
3
votes
2 answers

PHP & mysqli: What is the difference between persistent connection and interactive?

What are the differences between the following: // Persistent connection $mysqli->real_connect("p:" . $host, $user, $pass, $db, null, null, 0); and // Interactive connection $mysqli->real_connect($host, $user, $pass, $db, null, null,…
Simon Forsberg
  • 13,086
  • 10
  • 64
  • 108
3
votes
2 answers

User already has more than 'max_user_connections' active connections

I have db class that nearly all classes are extending: class db { protected $db; public function __construct() { $this->connect(); } protected function connect() { $this->db = new MySQLi(db_host, db_user, db_pass,…
heron
  • 3,611
  • 25
  • 80
  • 148
3
votes
1 answer

Troubleshooting "Warning: mysqli_result::fetch_array() expects parameter 1 to be long, object given"

The warning I'm getting is: Warning: mysqli_result::fetch_array() expects parameter 1 to be long, object given in...line 103. I've commented next to the line 103 while ($row = $result->fetch_array($result)) { Question 2: Can I store any of this…
Josh
  • 133
  • 1
  • 4
  • 11
3
votes
1 answer

Do PHP child processes use the same Mysqli Connection Pool?

This is what the PHP Documentation has to say on Connection Pooling: The mysqli extension supports persistent database connections, which are a special kind of pooled connections. and Every PHP process is using its own Mysqli connection…
Andy
  • 4,783
  • 2
  • 26
  • 51
3
votes
1 answer

How to access mysqli connection in another class on another page?

How can I open a database connection in user class , where I can do database operation? and why need to define inbuilt created functions in DBConnection class ..???? I have created db.php user.php result.php in db.php class DBConnection { …
mkat
  • 149
  • 1
  • 3
  • 12
3
votes
1 answer

fatal error is causing no results to appear (Mysqli)

I am getting this error in my script which I think is causing the search bar not to work: Fatal error: Call to a member function bind_param() on a non-object in /web/stud/xxx/Mobile_app/previousquestions.php on line 89. The line it is pointing to…
user1394925
  • 754
  • 9
  • 28
  • 51
3
votes
1 answer

Fatal error: Class 'mysqli' not found in

I execute this on my centos/WHM server : Result is: We don't have mysqli!!! My problem is that:…
ERC20
  • 61
  • 1
  • 1
  • 3
3
votes
3 answers

MySQL: How do I do ON DUPLICATE KEY UPDATE where two primary keys MUST match?

I need to be able to run all of the following within one query call using mysqli->multi_query, which is why this is tricky. I have a table that consists of the following columns: id email event_promo_code event_id When the script is run, I need to…
user1418227
  • 201
  • 1
  • 4
  • 12
3
votes
5 answers

PHP/mysqli , how to know about newly inserted ID

I have a simple table , contains a primary key which has an Auto Increment index. When I do insertion with mysqli , is there any chance to know the last inserted value of the Auto Increment column ? Note: There might be multiple people trying to…
daisy
  • 22,498
  • 29
  • 129
  • 265
3
votes
2 answers

Slow parent::__construct in extended mysqli class

I'm extending mysqli in a database class. I've noticed that calling the parent constructor takes nearly 2 seconds. It is possible, I suppose, that it is my environment since I am developing on my desktop prior to deployment. But that does not seem…
Ron G
  • 31
  • 2