Php Mysqli Prepare Kullanimi

All About MYSQLI Prepared Statements in PHP

In this tutorial, we will learn how to perform queries like SELECT, UPDATE, DELETE, etc. with different conditions with MYSQLI prepared statements in PHP. Before we start I'll briefly introduce you prepared statements.

What are MYSQLI Prepared Statements?

MYSQL is a popular relational database system. MYSQLI is a powerful PHP extension to connect with MYSQL. Prepared statements as queries that are previously prepared and executed later with data.

Why are Prepared Statements Important?

Simply, prepared statements protect websites from SQL Injection which can be used to attack a website. Also, prepared statements can be faster than normal queries according to some sources (But, in my experience, they are almost the same when performing simple queries. But, for recurring queries prepared statements are super fast than normal queries). The best thing about prepared statements is readability. They can easily be read, understood and managed.

Prerequisites

Before you start you will need these things.

• A computer that has PHP and MYSQL installed.
• A PHP script that has already connected to a database. We will save the database connection in $mysqli.

  <?php
  $mysqli = new mysqli( 'hostname', 'username', 'password', 'database' );

• In this tutorial, we will have a "user" table like this in our database

  id	name	email
  1	Teodor	teod@gmail.com
  2	Christ	christoperkhawand@gmail.com
  3	Austin	austin@gmail.com
  4	Ayush	ayushagarwal@gmail.com

How To Use Prepared Statements in PHP

Let's see how to perform a prepared statement in PHP, using MYSQLI. This is the basic concept. In various queries (SELECT, UPDATE etc.) we will use different ways (and tricks).

• First, a query should be prepared.

  $stmt = $mysqli -> prepare('SELECT * FROM users WHERE id = ?');

  Here we store the statement in $stmt. This is a mysqli_stmt object. In the next steps, we will call methods of this class to perform actions.
  Note that ? (question mark) in the query is used as a placeholder. We say to MYSQL that this is an empty box which we will fill in the future.
• Next, we bind the data. This is like filling the box.

  $userId = 2;
  $stmt -> bind_param('i', $userId);

  Here we will bind 2 as the id of the user. In the bind_param() method, the first parameter indicates the data types of each variable. For instance, if you had three variables (empty boxes) to bind which are an integer, integer, string, integer respectively, you may use 'iisi'.
  For no good reason, in PHP, the following is invalid. The arguments for the bind_param function should be variables except the first one.

  $stmt -> bind_param('i', 2);

• Next, we execute the query

  $stmt -> execute();

  After this step, the procedure differs according to the query you will perform. Let's see some examples.

1. SELECT - Selecting One Row

$stmt = $mysqli -> prepare('SELECT name, email FROM users WHERE id = ?');

$userId = 1; // or $_GET['userId'];

$stmt -> bind_param('i', $userId);
$stmt -> execute();
$stmt -> store_result();
$stmt -> bind_result($name, $email);
$stmt -> fetch();

echo $name; // Teodor
echo $email; // teod@gmail.com

• store_result() stores the result.
• bind_result() binds the values to variables.
• fetch() fetches results to variables.

At first, this is tricky if you are a beginner. But, you will understand when you do other steps. Just remember that fetch() saves the result of the current row in the variables in bind_result(). By default, the current row is the first one in the result set. When we call fetch() one time, the current row is the second one in the results. However, we only have one row in this query.

2. SELECT - Selecting Multiple Rows

$stmt = $mysqli -> prepare('SELECT name, email FROM users');

$stmt -> execute();
$stmt -> store_result();
$stmt -> bind_result($name, $email);

while ($stmt -> fetch()) {
 echo $name;
 echo $email;
}

bind_param() function is not needed when you don't have any variable to pass. This will select all the users and echo out the name and email of all the users.

Special Note:

• fetch() returns true on success, and false on failure. If no row was found, it will return false. So, we can directly use it as the condition for the while loop.
• In each time fetch() is called, the result of the current row is saved in $name and $email variables. And, the cursor is moved to the next row. (So, when we call fetch next time, it will fetch the next row)

3. SELECT - Getting Number of Selected Rows

$stmt = $mysqli -> prepare('SELECT name, email FROM users');

$stmt -> execute();
$stmt -> store_result();
// 4
echo $stmt -> num_rows;

One thing to remember, store_result() should be called before using the num_rows property.

4. SELECT - Get Results

$stmt = $mysqli -> prepare('SELECT name, email FROM users WHERE id > ?');

$greaterThan = 1;
$stmt -> bind_param('i', $greaterThan);
$stmt -> execute();
$result = $stmt -> get_result();

Now $result is the same as doing $mysqli -> query(...). You can use something like following to use results.

while ($row = $result -> fetch_assoc()) {
 echo $row['name']; 
 echo $row['email'];
}

5. SELECT - With Wildcards

Wildcards are used to match patterns in MYSQL.

<?php
$stmt = $mysqli -> prepare('SELECT name, email FROM users WHERE name LIKE ?');

$like = 'a%';
$stmt -> bind_param('s', $like);
$stmt -> execute();
$stmt -> store_result();
$stmt -> bind_result($name, $email);

while ($stmt -> fetch()) {
 echo $name;
 echo $email;
}

In this example, we will select all the users whose name starts from the letter a. (austin and ayush)

6. SELECT - With An Array of IDs

This is a pretty hard thing to do when using with prepared statements. We will need to dynamically add the question marks into the query.

// array of user IDs
$userIdArray = [1,2,3,4];
// number of question marks
$questionMarksCount = count($userIdArray);
// create a array with question marks
$questionMarks = array_fill(0, $questionMarksCount, '?');
// join them with ,
$questionMarks = implode(',', $questionMarks);
// data types for bind param
$dataTypes = str_repeat('i', $questionMarksCount);

$stmt = $mysqli -> prepare("SELECT name, email FROM users WHERE id IN ($questionMarks)");

$stmt -> bind_param($dataTypes, ...$userIdArray);
$stmt -> execute();
$stmt -> store_result();
$stmt -> bind_result($name, $email);

while ($stmt -> fetch()) {
 echo $name;
 echo $email;
}

7. SELECT - LIMIT and OFFSET

$stmt = $mysqli -> prepare("SELECT name, email FROM users LIMIT ? OFFSET ?");

// limit of rows
$limit = 2;
// skip n rows
$offset = 1;

$stmt -> bind_param('ii', $limit, $offset);
$stmt -> execute();
$stmt -> store_result();
$stmt -> bind_result($name, $email);

while ($stmt -> fetch()) {
 echo $name;
 echo $email;
}

8. SELECT - BETWEEN

$stmt = $mysqli -> prepare("SELECT name, email FROM users WHERE id BETWEEN ? AND ?");

$betweenStart = 2;
$betweenEnd = 4;

$stmt -> bind_param('ii', $betweenStart, $betweenEnd);
$stmt -> execute();
$stmt -> store_result();
$stmt -> bind_result($name, $email);

while ($stmt -> fetch()) {
 echo $name;
 echo $email;
}

9. INSERT - One Row

$stmt = $mysqli -> prepare('INSERT INTO users (name, email) VALUES (?,?)');

$name = 'Akhil';
$email = 'akhilkumar@gmail.com';

$stmt -> bind_param('ss', $name, $email);
$stmt -> execute();

10. INSERT - Getting Insert ID

If you had an auto incremental column for saving the ID, in many cases we need to know what's the ID of the user who we have just inserted into the database. The $stmt -> insert_id property is used in this case.

$stmt = $mysqli -> prepare('INSERT INTO users (name, email) VALUES (?,?)');

$name = 'Akhil';
$email = 'akhilkumar@gmail.com';

$stmt -> bind_param('ss', $name, $email);
$stmt -> execute();

echo 'Your account id is ' . $stmt -> insert_id;

11. INSERT - Multiple Rows (Recursive)

Recursive insertions are very powerful when done with prepared statements. We prepare one statement and use it to insert multiple rows.

$newUsers = [
 [ 'sulliops', 'sulliops@gmail.com' ],
 [ 'infinity', 'infinity@gmail.com' ],
 [ 'aivarasco', 'aivarasco@gmail.com' ]
];

$stmt = $mysqli -> prepare('INSERT INTO users (name, email) VALUES (?,?)');

foreach ($newUsers as $user) {
  
 $name = $user[0];
 $email = $user[1];

 $stmt -> bind_param('ss', $name, $email);
 $stmt -> execute();

 echo "{$name}'s account id is {$stmt -> insert_id}";

}

You will see that $stmt -> insert_id updates each time you insert a new row.

12. UPDATE

$stmt = $mysqli -> prepare('UPDATE users SET email = ? WHERE id = ? LIMIT 1');
 
$email = 'newemail@hyvor.com';
$id = 2;

$stmt -> bind_param('si', $email, $id);
$stmt -> execute();

13. UPDATE - Get Affected Rows

Sometimes you will need to know how many rows are affected by our UPDATE query.

$stmt = $mysqli -> prepare('UPDATE users SET email = ? WHERE name = ? LIMIT 1');
 
$email = 'newemail@hyvor.com';
$name = 'teodor';

$stmt -> bind_param('ss', $email, $name);
$stmt -> execute();

// 1
echo $stmt -> affected_rows;

14. DELETE

$stmt = $mysqli -> prepare('DELETE FROM users WHERE id = ?');
 
$userId = 4;

$stmt -> bind_param('i', $userId);
$stmt -> execute();

// number of deleted rows
echo $stmt -> affected_rows;

Handling Errors

It's always very good to know how to debug MYSQLI prepared statements. Here are some tips.

1. When Preparation Fails

Sometimes the $mysqli -> prepare() function fails because of wrong query.

How To Detect?

$stmt = $mysqli -> prepare('SELECT * FROM no_table WHERE id = ?');
 
$id = 1;
$stmt -> bind_param('i', $id);

If you see an error in PHP like, "Call to a member function bind_param() on boolean" when calling methods of $stmt, then the preparation has failed. When $mysqil -> prepare() fails it returns false. Therefore $stmt is a boolean, not an object. Now we can use $mysqli -> error to find the error in the query.

$stmt = $mysqli -> prepare('SELECT * FROM no_table WHERE id = ?');
echo $mysqli -> error;

2. When Execution Fails

Execution failures normally do not throw errors. Therefore, you should add a condition to check if the execution was successful. If it wasn't, $stmt -> error will show you what's the error.

$stmt = $mysqli -> prepare('INSERT INTO stmt_users (name) VALUES (?)');
 
$name = 'User';
$stmt -> bind_param('i', $name);

if (! $stmt -> execute()) {
 echo $stmt -> error;
}

For our example table, the error is "Field 'email' doesn't have a default value".

Conclusion

My target was to cover all the ways to perform prepared statements in this tutorial. We discussed how to use prepared statements for SELECT, INSERT, UPDATE and DELETE. So, I hope I covered everything. If you are willing to learn the best way to perform MYSQLI prepared statements, this article is for you. I'm waiting to see your feedback in the comments section. Thank you for reading!