How to run a SELECT query using Mysqli

1. SELECT query without variables
2. A hint: fetch_all()
3. SELECT query with variables
4. Explanation
5. SELECT query with a helper function
6. Comments (5)

There are basically two ways to run a SELECT query with mysqli.

• in case there are no variables going to be used in the query, we can run our query using a conventional query() method (mysqli_query() function)
• if even a single variable is going to be used in the query, a prepared statement must be used. Below we will see both methods explained.

Just make sure you've got a properly configured mysqli connection variable that is required in order to run SQL queries and to inform you of the possible errors.

SELECT query without variables

When no variables are going to be used in the query, a familiar query() method, that returns a familiar mysqli result variable can be used. The result can be iterated over using a familiar while loop.

$result = $conn->query("SELECT * FROM users");
while ($row = $result->fetch_assoc()) {
    echo $row['name']."<br />\n";
}

In case you are expecting only a single row, just fetch it right away without a loop:

$result = $conn->query("SELECT * FROM users WHERE id=1");
$user = $result->fetch_assoc();

A hint: fetch_all()

There as a very handy function that deserves a better recognition. In case you need to get an array with all query results, you can use fetch_all() method instead of a loop. So instead of four lines

$data = [];
while ($row = $result->fetch_assoc()) {
    $data[] = $row;
}

it could be just a single line:

$data = $result->fetch_all(MYSQLI_ASSOC);

By default this function returns enumerated arrays, so to get associative arrays the MYSQLI_ASSOC mode must be set explicitly.

SELECT query with variables

You must use prepared statements for any SQL query that would contain a PHP variable. To do so, always follow the below steps

• create a correct SQL SELECT statement. Test it in mysql console/phpmyadmin if needed
• replace all variables in the query with with question marks (called placeholders or parameters)
• Prepare the resulting query
• Bind all variables to the previously prepared statement
• Execute the statement
• get the mysqli result variable from the statement.
• fetch your data

Long story short, here is the code:

$sql = "SELECT * FROM users WHERE id=?"; // SQL with parameters
$stmt = $conn->prepare($sql); 
$stmt->bind_param("i", $id);
$stmt->execute();
$result = $stmt->get_result(); // get the mysqli result
$user = $result->fetch_assoc(); // fetch the data   

And have your SELECT query executed without a single syntax error or SQL injection.

Explanation

What is going on here?

$sql = "SELECT * FROM users WHERE id=?";

Like it was said above, first we are writing an SQL query where all variables are substituted with question marks.

IMPORTANT! there should be no quotes around question marks, you are adding placeholders, not strings.

$stmt= $conn->prepare($sql);

Then, the query is prepared. The idea is very smart. To avoid even a possibility of the SQL injection or a syntax error caused by the input data, the query and the data are sent to database server separately. So it goes on here: with prepare() we are sending the query to the database server ahead. A special variable, a statement is created as a result. We would use this variable from now on.

$stmt->bind_param("i", $id);

Then variables must be bound to the statement. The call consists of two parts - the string with types and the list of variables. With mysqli, you have to designate the type for each bound variable. It is represented by a single letter in the first parameter. The number of letters should be always equal to the number of variables. The possible types are

• i for integer
• d for double (float)
• s for string
• b for blobs

A HINT: you can almost always safely use "s" for any variable.

So now you can tell that "s" means "there would be 1 variable, of string type". And then, naturally, a variable follows.

$stmt->execute();

Then the query finally gets executed. Means variables get sent to database server and the query is actually executed.

$result = $stmt->get_result(); // get the mysqli result

Here we are calling a very smart function. By default, for some reason it's impossible to fetch a familiar array (like we did with mysql_fetch_array()) from a mysqli statement. So this function is here to help, getting a mysqli result from a mysqli statement.

$user = $result->fetch_assoc(); // fetch data   

Finally, fetching a row using a familiar fetch_assoc() method.

NOTE that you don't have to check the execution result. Given you have the proper connection code mentioned above, in case of error mysqli will raise an error automatically.

SELECT query with a helper function

As you may noted, the code is quite verbose. If you like to build a code like a Lego figure, with shining ranks of operators, you may keep it as is. If you, like me, hate useless repetitions and like to write concise and meaningful code, then there is a simple helper function. With it, the code will become two times shorter:

$sql = "SELECT * FROM users WHERE id=?"; // SQL with parameters
$stmt = prepared_query($conn, $sql, [$id]);
$user = $stmt->get_result()->fetch_assoc(); // fetch data  

Only three lines instead of six!