last modified July 13, 2022 Show PHP mysqli tutorial shows how to program MySQL in PHP with mysqli extension. PHP tutorial is a comprehensive tutorial on PHP. PHP PDO tutorial shows how to program databases in PHP with PDO. MySQLMySQL is a leading open source database management system. It is a multiuser, multithreaded database management system. MySQL is especially popular on the web. It is one of the parts of the very popular LAMP platform. Linux, Apache, MySQL, and PHP. Currently MySQL is owned by Oracle. MySQL database is available on most important OS platforms. It runs on BSD Unix, Linux, Windows, or Mac OS. PHP mysqliThe MySQLi Extension (MySQL Improved) is a relational database driver used in the PHP scripting language to provide an interface with MySQL databases. It provides both object oriented and procedural APIs. Other ways to interact with MySQL are: PDO and ORM solutions. The There are also ORM solutions for working with MySQL in PHP such as Doctrine or Eloquent. $ sudo apt install php8.1-mysql We install the PHP mysqli versionIn the following example, we determine the version of the MySQL database. version.php <?php $con = new mysqli("localhost", "dbuser", "passwd", "mydb"); if ($con->connect_errno) { printf("connection failed: %s\n", $con->connect_error()); exit(); } $res = $con->query("SELECT VERSION()"); if ($res) { $row = $res->fetch_row(); echo $row[0]; } $res->close(); $con->close(); The example prints the version of MySQL. $con = new mysqli("localhost", "dbuser", "passwd", "mydb"); A connection to the database is created. The if ($con->connect_errno) { printf("connection failed: %s\n", $con->connect_error()); exit(); } The $res = $con->query("SELECT VERSION()"); The if ($res) { $row = $res->fetch_row(); echo $row[0]; } The $res->close(); $con->close(); In the end, we release the resources. $ php version.php 8.0.29-0ubuntu0.22.04.2 This is a sample output. The version2.php <?php $con = mysqli_connect("localhost", "dbuser", "passwd", "mydb"); if (mysqli_connect_errno()) { printf("connection failed: %s\n", mysqli_connect_error()); exit(); } $query = "SELECT VERSION()"; $res = mysqli_query($con, $query); if ($res) { $row = mysqli_fetch_row($res); echo $row[0]; } mysqli_free_result($res); mysqli_close($con); The example returns the version of MySQL with procedural functions. PHP mysqli create table The following example creates a new database table. A table is created with the create_table.php <?php $host = "localhost"; $user = "dbuser"; $passwd = "passwd"; $db = "mydb"; function execute_query($query, $con) { $res = $con->query($query); if (!$res) { echo "failed to execute query: $query\n"; } else { echo "Query: $query executed\n"; } if (is_object($res)) { $res->close(); } } $con = new mysqli($host, $user, $passwd, $db); if ($con->connect_errno) { printf("connection failed: %s\n", $con->connect_error()); exit(); } $query = "DROP TABLE IF EXISTS cars"; execute_query($query, $con); $query = "CREATE TABLE cars(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255), price INT)"; execute_query($query, $con); $query = "INSERT INTO cars(name, price) VALUES('Audi', 52642)"; execute_query($query, $con); $query = "INSERT INTO cars(name, price) VALUES('Mercedes', 57127)"; execute_query($query, $con); $query = "INSERT INTO cars(name, price) VALUES('Skoda', 9000)"; execute_query($query, $con); $query = "INSERT INTO cars(name, price) VALUES('Volvo', 29000)"; execute_query($query, $con); $query = "INSERT INTO cars(name, price) VALUES('Bentley', 350000)"; execute_query($query, $con); $query = "INSERT INTO cars(name, price) VALUES('Citroen', 21000)"; execute_query($query, $con); $query = "INSERT INTO cars(name, price) VALUES('Hummer', 41400)"; execute_query($query, $con); $query = "INSERT INTO cars(name, price) VALUES('Volkswagen', 21600)"; execute_query($query, $con); $con->close(); The example creates the PHP mysqli prepared statements When we write prepared statements, we use placeholders instead of directly writing the values into the statements. Prepared statements increase
security and performance. In mysqli, the prepared_statement.php <?php $host = "localhost"; $user = "dbuser"; $passwd = "passwd"; $db = "mydb"; $con = new mysqli($host, $user, $passwd, $db); if ($con->connect_errno) { printf("connection failed: %s\n", $con->connect_error()); exit(); } $id = 3; $query = "SELECT id, name, price FROM cars WHERE id = ?"; if ($stmt = $con->prepare($query)) { $stmt->bind_param('i', $id); $stmt->execute(); $stmt->bind_result($row_id, $name, $price); $stmt->fetch(); echo "$row_id $name $price\n"; $stmt->close(); } else { echo "failed to fetch data\n"; } $con->close(); The example selects a specific row from the table. It uses a prepared statement. $query = "SELECT id, name, price FROM cars WHERE id = ?"; When we write prepared statements, we use placeholders instead of directly writing the values into the statements. Prepared statements are faster and guard against SQL injection attacks. The $stmt->bind_param('i', $id); The value of the $stmt->execute(); The statement is executed. $stmt->bind_result($row_id, $name, $price); The echo "$row_id $name $price\n"; We print the variables to the terminal. $con->close(); We close the statement. $ php prepared_statement.php 3 Skoda 9000 This is the output. PHP mysqli fetch_row
The fetch_rows.php <?php $host = "localhost"; $user = "dbuser"; $passwd = "passwd"; $db = "mydb"; $con = new mysqli($host, $user, $passwd, $db); if ($con->connect_errno) { printf("connection failed: %s\n", $con->connect_error()); exit(); } $query = "SELECT * FROM cars"; if ($res = $con->query($query)) { printf("Select query returned %d rows.\n", $res->num_rows); while ($row = $res->fetch_row()) { printf("%s %s %s\n", $row[0], $row[1], $row[2]); } $res->close(); } else { echo "failed to fetch data\n"; } $con->close(); The example returns all rows from the $query = "SELECT * FROM cars"; This SELECT query selects all rows from the table. if ($res = $con->query($query)) { We execute the SELECT query with the
printf("Select query returned %d rows.\n", $res->num_rows); The number of returned rows is stored in the while ($row = $res->fetch_row()) { printf("%s %s %s\n", $row[0], $row[1], $row[2]); } With the $ php fetch_rows.php Select query returned 8 rows. 1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 6 Citroen 21000 7 Hummer 41400 8 Volkswagen 21600 This is the output. PHP mysqli fetch_assoc The fetch_rows2.php <?php $host = "localhost"; $user = "dbuser"; $passwd = "passwd"; $db = "mydb"; $con = new mysqli($host, $user, $passwd, $db); if ($con->connect_errno) { printf("connection failed: %s\n", $con->connect_error()); exit(); } $query = "SELECT * FROM cars"; if ($res = $con->query($query)) { printf("Select query returned %d rows.\n", $res->num_rows); while ($row = $res->fetch_assoc()) { printf("%s %s %s\n", $row['id'], $row['name'], $row['price']); } $res->close(); } else { echo "failed to fetch data\n"; } $con->close(); The example returns all rows from the while ($row = $res->fetch_assoc()) { printf("%s %s %s\n", $row['id'], $row['name'], $row['price']); } When we use PHP mysqli fetch_object The fetch_rows3.php <?php $host = "localhost"; $user = "dbuser"; $passwd = "passwd"; $db = "mydb"; $con = new mysqli($host, $user, $passwd, $db); if ($con->connect_errno) { printf("connection failed: %s\n", $con->connect_error()); exit(); } $query = "SELECT * FROM cars"; if ($res = $con->query($query)) { printf("Select query returned %d rows.\n", $res->num_rows); while ($row = $res->fetch_object()) { printf("%s %s %s\n", $row->id, $row->name, $row->price); } $res->close(); } else { echo "failed to fetch data\n"; } $con->close(); The example returns all rows from the while ($row = $res->fetch_object()) { printf("%s %s %s\n", $row->id, $row->name, $row->price); } When we use PHP mysqli column namesThe next example prints column names with the data from the database table. We refer to column names as meta data. column_names.php <?php $host = "localhost"; $user = "dbuser"; $passwd = "passwd"; $db = "mydb"; $con = new mysqli($host, $user, $passwd, $db); if ($con->connect_errno) { printf("connection failed: %s\n", $con->connect_error()); exit(); } $query = "SELECT * FROM cars"; if ($res = $con->query($query)) { $num_rows = $res->num_rows; $num_fields = $res->field_count; printf("Select query returned %d rows.\n", $num_rows); printf("Select query returned %d columns.\n", $num_fields); $fields = $res->fetch_fields(); while ($row = $res->fetch_row()) { for ($i = 0; $i < $num_fields; $i++) { echo $fields[$i]->name . ": " . $row[$i] . "\n"; } echo "*******************************\n"; } $res->close(); } else { echo "failed to fetch data\n"; } $con->close(); The example prints all rows of the $num_rows = $res->num_rows; $num_fields = $res->field_count; The $fields = $res->fetch_fields(); The while ($row = $res->fetch_row()) { for ($i = 0; $i < $num_fields; $i++) { echo $fields[$i]->name . ": " . $row[$i] . "\n"; } echo "*******************************\n"; } We show the column names and the data. $ php column_names.php Select query returned 8 rows. Select query returned 3 columns. id: 1 name: Audi price: 52642 ******************************* id: 2 name: Mercedes price: 57127 ******************************* id: 3 name: Skoda price: 9000 ******************************* ... This is the output. This was PHP mysqli tutorial. List all PHP tutorials. Does PHP come with MySQLi?The mysqli extension, or as it is sometimes known, the MySQL improved extension, was developed to take advantage of new features found in MySQL systems versions 4.1. 3 and newer. The mysqli extension is included with PHP versions 5 and later.
Is MySQLi a database?The MySQLi Extension (MySQL Improved) is a relational database driver used in the PHP scripting language to provide an interface with MySQL databases.
Is MySQLi an OOP?The mysqli extension features a dual interface. It supports the procedural and object-oriented programming paradigm. Users migrating from the old mysql extension may prefer the procedural interface. The procedural interface is similar to that of the old mysql extension.
What is difference between MySQL and MySQLi?Basically, MySQL is the old database driver, and MySQLi is the Improved driver. The "i" stands for "improved" so it is MySQL improved. MySQLi can be done procedural and object-oriented whereas MySQL can only be used procedurally. Mysqli also supports prepared statements which protect from SQL Injection.
|