Using PHP mysqli_query

The following are the common steps while using the PHP mysqli_query() function to execute queries on a SQL database server.

1. Create a connection to the server using mysqli_connect()

2. Specify a SQL query string.

3. Call mysqli_query() with the connection object and SQL string.
4. Consume the return value.

?php
$servername = "localhost";
$username = "fuelingphp";
$password = "fuelingphp";
$database = "fuelingphp";


// Creates a connection
$connection = mysqli_connect($servername, $username, $password, $database);


// Checks if the connection has been established.
if (!$connection) {
 die("Connection failed: " . mysqli_connect_error());
}


// SQL query to create a new table
$sql = "CREATE TABLE articles (
   id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
   title VARCHAR(30) NOT NULL,
   category VARCHAR(30) NOT NULL
   )";


//Executes query
if (mysqli_query($connection, $sql)) {
 echo "New table articles has been created";
} else {
 echo "Error: " . $sql . " : " . mysqli_error($connection);
}




// SQL query to insert a new row
$sql = "INSERT INTO articles (title, category)
VALUES ('State Pattern', 'Design Patterns')";


//Executes query
if (mysqli_query($connection, $sql)) {
 echo "New row has been created";
} else {
 echo "Error: " . $sql . " : " . mysqli_error($connection);
}


$rows = [
   ["title" => "Command Pattern", "category" => "Design Patterns"],
   ["title" => "Singleton Pattern", "category" => "Design Patterns"],
   ["title" => "Strategy Pattern", "category" => "Design Patterns"],
];


foreach($rows as $row) {


   //1. Do some preprocessing


   //2. Create a string from row data
   $values = "('{$row['title']}', '{$row['category']}')";
  
   //3. SQL query to insert a new row
   $sql = "INSERT INTO articles (title, category)
           VALUES {$values}";


   //4. Executes query
   if (mysqli_query($connection, $sql)) {
   echo "New row has been created";
   } else {
   echo "Error: " . $sql . " : " . mysqli_error($connection);
   }


}


// SQL query to select all rows from the table articles.
$sql = "SELECT * from articles";


$result = mysqli_query($connection, $sql); //Returns a result set


//Checks if the result set is not empty
if (mysqli_num_rows($result) > 0) {
 //Loop through result set
 foreach($result as $row) {
   echo "Title: " . $row["title"]. " | Category: " . $row["category"]. "\n";
 }
} else {
 echo "Table is empty";
}


// SQL query to select titles starting with ‘St’
$sql = "SELECT title, category FROM articles WHERE title LIKE 'St%'";


$result = mysqli_query($connection, $sql); //Returns a result set


//Checks if the result set is not empty
if (mysqli_num_rows($result) > 0) {
 //Loop through result set
 foreach($result as $row) {
   echo "Title: " . $row["title"]. " | Category: " . $row["category"]. "\n";
 }
} else {
 echo "Table is empty";
}


// SQL query to update article title
$sql = "UPDATE articles SET title='Template Method' WHERE title='Singleton Pattern'";


// Executes SQL
if (mysqli_query($connection, $sql)) {
   echo "Row(s) updated successfully";
 } else {
   echo "Error updating record: " . mysqli_error($conn);
}


// SQL query to delete titles starting with ‘St’
$sql = "DELETE FROM articles WHERE title LIKE 'St%'";


// Executes SQL
if (mysqli_query($connection, $sql)) {
   echo "Row(s) deleted successfully";
 } else {
   echo "Error deleting record: " . mysqli_error($conn);
}


//Closes connection
mysqli_close($connection);


/*
OUTPUT
New table articles has been created


New row has been created


New row has been created (x3)


Title: State Pattern | Category: Design Patterns
Title: Command Pattern | Category: Design Patterns
Title: Singleton Pattern | Category: Design Patterns
Title: Strategy Pattern | Category: Design Patterns


Title: State Pattern | Category: Design Patterns
Title: Strategy Pattern | Category: Design Patterns




Row(s) updated successfully


Row(s) deleted successfully
*/

Table of Contents

• Prerequisites
• Quick Steps to Setup an SQL Database
• PHP mysqli_query() Function
• Create a Connection with the Database Server
• Create a Database
• Create a Table
• Insert a row
• Insert many rows
• Select Data
• Query Data
• Delete a row
• Update a row
• Complete Example
• Conclusion

Prerequisites

The article assumes that you have:

• Installed and configured SQL database.
• Familiarity with basic SQL syntax.

Quick Steps to Setup an SQL Database

We will be using the MySQL database. However, it is not necessary as you can choose any SQL database of your choice.

1. Install MySQL.
   
2. Connect to MySQL server.
   
3. Create a dedicated SQL user

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

4. Grant privileges to the newly created user.

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, INDEX, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'user'@'localhost' WITH GRANT OPTION;

That should do it. The article doesn’t focus much on troubleshooting SQL servers. Hopefully, you can do that by yourself if necessary.

PHP mysqli_query() Function

PHP mysqli_query() function performs a query on the database. The query has to be a valid SQL command with the right syntax.

mysqli_query(mysqli $mysql, string $query, int $result_mode = MYSQLI_STORE_RESULT): mysqli_result|bool

Parameters

$mysql	A mysqli connection object.
$query	A query string
$result_mode	Alter how the function returns the result set. By default, it is set to return a buffered result set.

Return Type

Returns a mysqli_result object for queries that return a result set. These queries are usually SELECT, DESCRIBE, or EXPLAIN.

Returns true for other types of queries. For instance, CREATE or INSERT. Else, returns false on failure.

PHP mysqli_query | Create Connection

The first step is connecting to the SQL database server. PHP features the mysqli_connect() function, which returns a connection object on success or false on failure. 

The mysqli_connect() expects some parameters, including hostname, user & password, which are always required. 

Other parameters are database name, port, and socket, which are optional and assume default value if not specified.

<?php
$servername = "localhost";
$username = "fuelingphp";
$password = "fuelingphp";


// Creates a connection
$connection = mysqli_connect($servername, $username, $password);


// Checks if the connection has been established.
if (!$connection) {
 die("Connection failed: " . mysqli_connect_error());
}
?>

Note

Hardcoding credentials, as shown in the demo example, are a huge security vulnerability. Always use an environment file or a secrets manager like the AWS parameter store or secrets manager.

PHP MYSQLi Create Database

Once the connection has been established, we can use the connection object to execute queries on the SQL server. 

The following script uses mysqli_query() to create a database named ‘fuelingphp’. The function returns a boolean for CREATE query. 

The SQL statement to create a database goes as follows.

CREATE DATABASE [databasename]

<?php
$servername = "localhost";
$username = "fuelingphp";
$password = "fuelingphp";


// Creates a connection
$connection = mysqli_connect($servername, $username, $password);


// Checks if the connection has established.
if (!$connection) {
 die("Connection failed: " . mysqli_connect_error());
}


// SQL query
$sql = "CREATE DATABASE fuelingphp";


//Executes query
if (mysqli_query($connection, $sql)) {
 echo "New database fuelingphp has been created";
} else {
 echo "Error: " . $sql . " : " . mysqli_error($connection);
}


//Closes connection
mysqli_close($connection);


//OUTPUT
//New database fuelingphp has been created
?>

PHP mysqli_query | Create Table

The example will create a connection with the database name parameter set to ‘fuelingphp’.

The rest is just similar to the previous example. 

SQL statement to create a table is.

CREATE TABLE table_name (
	column1 datatype,
	column2 datatype,
	column3 datatype,
   ....
)

The example will create a table named ‘articles’ with three rows: ID, title & category.

<?php
$servername = "localhost";
$username = "fuelingphp";
$password = "fuelingphp";
$database = "fuelingphp";


// Creates a connection
$connection = mysqli_connect($servername, $username, $password, $database);


// Checks if the connection has established.
if (!$connection) {
 die("Connection failed: " . mysqli_connect_error());
}


// SQL query
$sql = "CREATE TABLE articles (
   id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
   title VARCHAR(30) NOT NULL,
   category VARCHAR(30) NOT NULL
   )";


//Executes query
if (mysqli_query($connection, $sql)) {
 echo "New table articles has been created";
} else {
 echo "Error: " . $sql . " : " . mysqli_error($connection);
}


//Closes connection
mysqli_close($connection);


//OUTPUT
//New table articles has been created
?>

PHP mysqli_query | Insert Rows

The following example inserts a single row into the table ‘articles’.

The SQL statement to insert a row into a table has the following syntax.

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

<?php
$servername = "localhost";
$username = "fuelingphp";
$password = "fuelingphp";
$database = "fuelingphp";


// Creates a connection
$connection = mysqli_connect($servername, $username, $password, $database);


// Checks if the connection has established.
if (!$connection) {
 die("Connection failed: " . mysqli_connect_error());
}


// SQL query
$sql = "INSERT INTO articles (title, category)
VALUES ('State Pattern', 'Design Patterns')";


//Executes query
if (mysqli_query($connection, $sql)) {
 echo "New row has been created";
} else {
 echo "Error: " . $sql . " : " . mysqli_error($connection);
}


//Closes connection
mysqli_close($connection);


//OUTPUT
//New row has been created
?>

PHP mysqli_query | Insert Multiple Rows

The following example uses a foreach loop to iterate over an array containing some data. It creates a data string appropriate for SQL insert statement and then adds one row at a time using similar logic as we have seen in the preceding examples.

You can also have a business or pre-processing logic before creating the data string. The example will skip that part and focus on the core logic instead.

<?php
$servername = "localhost";
$username = "fuelingphp";
$password = "fuelingphp";
$database = "fuelingphp";


// Creates a connection
$connection = mysqli_connect($servername, $username, $password, $database);


// Checks if the connection has established.
if (!$connection) {
 die("Connection failed: " . mysqli_connect_error());
}


$rows = [
   ["title" => "Command Pattern", "category" => "Design Patterns"],
   ["title" => "Singleton Pattern", "category" => "Design Patterns"],
   ["title" => "Strategy Pattern", "category" => "Design Patterns"],
];


foreach($rows as $row) {


   //1. Do some preprocessing


   //2. Create a string from row data
   $values = "('{$row['title']}', '{$row['category']}')";
  
   //3. SQL query
   $sql = "INSERT INTO articles (title, category)
           VALUES {$values}";


   //4. Executes query
   if (mysqli_query($connection, $sql)) {
   echo "New row has been created";
   } else {
   echo "Error: " . $sql . " : " . mysqli_error($connection);
   }


}


//Closes connection
mysqli_close($connection);


//OUTPUT
//New row has been created (x3)
?>

PHP MYSQLi Select Data

Time to fetch data from the table. We will use the following SQL query to get all the rows from the ‘articles’ table.

SELECT * from [tablename]

The mysqli_query() function will return a mysqli_result object which is also an iterator and could be easily looped over using a foreach loop.

<?php
$servername = "localhost";
$username = "fuelingphp";
$password = "fuelingphp";
$database = "fuelingphp";


// Creates a connection
$connection = mysqli_connect($servername, $username, $password, $database);


// Checks if the connection has established.
if (!$connection) {
 die("Connection failed: " . mysqli_connect_error());
}




// SQL query
$sql = "SELECT * from articles";


$result = mysqli_query($connection, $sql); //Returns a result set


//Checks if the result set is not empty
if (mysqli_num_rows($result) > 0) {
 //Loop through result set
 foreach($result as $row) {
   echo "Title: " . $row["title"]. " | Category: " . $row["category"]. "\n";
 }
} else {
 echo "Table is empty";
}


//Closes connection
mysqli_close($connection);


//OUTPUT
/*
Title: State Pattern | Category: Design Patterns
Title: Command Pattern | Category: Design Patterns
Title: Singleton Pattern | Category: Design Patterns
Title: Strategy Pattern | Category: Design Patterns
*/
?>

PHP MYSQLi Query Data

The following example uses SQL WHERE clause with LIKE to select ‘titles’ starting with the alphabets ‘St’

<?php
$servername = "localhost";
$username = "fuelingphp";
$password = "fuelingphp";
$database = "fuelingphp";


// Creates a connection
$connection = mysqli_connect($servername, $username, $password, $database);


// Checks if the connection has established.
if (!$connection) {
 die("Connection failed: " . mysqli_connect_error());
}




// SQL query
$sql = "SELECT title, category FROM articles WHERE title LIKE 'St%'";


$result = mysqli_query($connection, $sql); //Returns a result set


//Checks if the result set is not empty
if (mysqli_num_rows($result) > 0) {
 //Loop through result set
 foreach($result as $row) {
   echo "Title: " . $row["title"]. " | Category: " . $row["category"]. "\n";
 }
} else {
 echo "Table is empty";
}


//Closes connection
mysqli_close($connection);


//OUTPUT
/*
Title: State Pattern | Category: Design Patterns
Title: Strategy Pattern | Category: Design Patterns
*/
?>

PHP mysqli_query | Delete a Row

The examples are pretty much the same in terms of syntax. We have a connection that we use to execute SQL on the server using the mysqli_query() function.

This example uses the SQL DELETE clause, which follows the following syntax.

DELETE FROM table_name
WHERE some_column = some_value

The following example deletes rows where the ‘title’ begins with the alphabets ‘St’.

<?php
$servername = "localhost";
$username = "fuelingphp";
$password = "fuelingphp";
$database = "fuelingphp";


// Creates a connection
$connection = mysqli_connect($servername, $username, $password, $database);


// Checks if the connection has beens established.
if (!$connection) {
 die("Connection failed: " . mysqli_connect_error());
}




// SQL query
$sql = "DELETE FROM articles WHERE title LIKE 'St%'";


// Executes SQL
if (mysqli_query($connection, $sql)) {
   echo "Row(s) deleted successfully";
 } else {
   echo "Error deleting record: " . mysqli_error($conn);
}




//Closes connection
mysqli_close($connection);


//OUTPUT
/*
Row(s) deleted successfully
*/
?>

PHP mysqli_query | Update a Row

The following example updates the title ‘Singleton Pattern’ to ‘Template Method Pattern.’

The SQL syntax for updating tables is as follows.

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value 

<?php
$servername = "localhost";
$username = "fuelingphp";
$password = "fuelingphp";
$database = "fuelingphp";


// Creates a connection
$connection = mysqli_connect($servername, $username, $password, $database);


// Checks if the connection has established.
if (!$connection) {
 die("Connection failed: " . mysqli_connect_error());
}




// SQL query
$sql = "UPDATE articles SET title='Template Method' WHERE title='Singleton Pattern'";


// Executes SQL
if (mysqli_query($connection, $sql)) {
   echo "Row(s) updated successfully";
 } else {
   echo "Error updating record: " . mysqli_error($conn);
}


//Closes connection
mysqli_close($connection);


//OUTPUT
/*
Row(s) updated successfully
*/
?>

PHP mysqli_query | Complete Example

<?php
$servername = "localhost";
$username = "fuelingphp";
$password = "fuelingphp";
$database = "fuelingphp";


// Creates a connection
$connection = mysqli_connect($servername, $username, $password, $database);


// Checks if the connection has been established.
if (!$connection) {
 die("Connection failed: " . mysqli_connect_error());
}


// SQL query to create a new table
$sql = "CREATE TABLE articles (
   id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
   title VARCHAR(30) NOT NULL,
   category VARCHAR(30) NOT NULL
   )";


//Executes query
if (mysqli_query($connection, $sql)) {
 echo "New table articles has been created";
} else {
 echo "Error: " . $sql . " : " . mysqli_error($connection);
}




// SQL query to insert a new row
$sql = "INSERT INTO articles (title, category)
VALUES ('State Pattern', 'Design Patterns')";


//Executes query
if (mysqli_query($connection, $sql)) {
 echo "New row has been created";
} else {
 echo "Error: " . $sql . " : " . mysqli_error($connection);
}


$rows = [
   ["title" => "Command Pattern", "category" => "Design Patterns"],
   ["title" => "Singleton Pattern", "category" => "Design Patterns"],
   ["title" => "Strategy Pattern", "category" => "Design Patterns"],
];


foreach($rows as $row) {


   //1. Do some preprocessing


   //2. Create a string from row data
   $values = "('{$row['title']}', '{$row['category']}')";
  
   //3. SQL query to insert a new row
   $sql = "INSERT INTO articles (title, category)
           VALUES {$values}";


   //4. Executes query
   if (mysqli_query($connection, $sql)) {
   echo "New row has been created";
   } else {
   echo "Error: " . $sql . " : " . mysqli_error($connection);
   }


}


// SQL query to select all rows from the table articles.
$sql = "SELECT * from articles";


$result = mysqli_query($connection, $sql); //Returns a result set


//Checks if the result set is not empty
if (mysqli_num_rows($result) > 0) {
 //Loop through result set
 foreach($result as $row) {
   echo "Title: " . $row["title"]. " | Category: " . $row["category"]. "\n";
 }
} else {
 echo "Table is empty";
}


// SQL query to select titles starting with ‘St’
$sql = "SELECT title, category FROM articles WHERE title LIKE 'St%'";


$result = mysqli_query($connection, $sql); //Returns a result set


//Checks if the result set is not empty
if (mysqli_num_rows($result) > 0) {
 //Loop through result set
 foreach($result as $row) {
   echo "Title: " . $row["title"]. " | Category: " . $row["category"]. "\n";
 }
} else {
 echo "Table is empty";
}


// SQL query to update title
$sql = "UPDATE articles SET title='Template Method' WHERE title='Singleton Pattern'";


// Executes SQL
if (mysqli_query($connection, $sql)) {
   echo "Row(s) updated successfully";
 } else {
   echo "Error updating record: " . mysqli_error($conn);
}


// SQL query to delete titles starting with ‘St’
$sql = "DELETE FROM articles WHERE title LIKE 'St%'";


// Executes SQL
if (mysqli_query($connection, $sql)) {
   echo "Row(s) deleted successfully";
 } else {
   echo "Error deleting record: " . mysqli_error($conn);
}


//Closes connection
mysqli_close($connection);


/*
OUTPUT
New table articles has been created


New row has been created


New row has been created (x3)


Title: State Pattern | Category: Design Patterns
Title: Command Pattern | Category: Design Patterns
Title: Singleton Pattern | Category: Design Patterns
Title: Strategy Pattern | Category: Design Patterns


Title: State Pattern | Category: Design Patterns
Title: Strategy Pattern | Category: Design Patterns


Row(s) updated successfully


Row(s) deleted successfully
*/
?>

Using PHP mysqli_query

This article features a complete walkthrough of using the mysqli_query() function in PHP. This function executes a SQL query given an active connection object to the database server and the query string. It returns a result set for queries that result in a collection of rows and a boolean for other query types.

The article starts by demonstrating how to get a connection object using the mysqli_connect() function. The rest of the examples use a similar code where we use the connection object and query string on the mysqli_query() function.

The article has examples of CRUD operations out of which the Read (select) returns a result set comprising rows of data. The example loops over a result set treating it as an associative array. Update and Delete operations return a boolean instead.

The examples are similar enough. Only the SQL syntax changes for different operations and queries. But you get the bigger picture anyhow. 🙂

Hopefully, this article has been insightful. Stay tuned for more at FuelingPHP.

PHP Fundamentals Recommendations

This article is part of our content on PHP Fundamentals. It includes the core concepts that build upon the foundation of writing high-quality PHP code. If you are looking to grow your PHP development abilities. Check out the following recommended affiliate resources.

We do make a commission if you do choose to buy through our links. It is one of the ways that help support our mission here at FuelingPHP.

Book: Fundamentals of Web Development

Book: Programming in PHP (O’Reilly)

Book: Design Patterns in PHP

Video Course: PHP Fundamentals (Pluralsight)

Complete Learning Path: Web Development (Pluralsight)

Want more reviews? Check out our HUGE list of Beginner Web Development Books We’ve Researched and Reviewed for you.

Learn the Fundamentals of Good Web Development

Please take a moment and sign up for our free email course on the fundamentals of good web development. Every week is packed with a roundup of articles on our site and from around the web, where we go deep into developing exceptional web applications. We have meetups, code reviews, slack chats, and more.

Click here to get started