PHP and Mysql delete multiple records.
目次
Table of Contents
1) Create an users table.
2) Mysql database connection.
3) Fetch users data.
4) Prepare a users table for delete records.
5) Delete multiple data using PHP.
1) Create an users table.
To delete multiple records from mysql database using php , let’s create a new table in our database to keep our user’s information.
CREATE TABLE users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50), password VARCHAR(255), role VARCHAR(20), created DATETIME DEFAULT NULL, modified DATETIME DEFAULT NULL );
You can add manually some users or can add below sql to add some example records
INSERT INTO
`users` (`id`, `username`, `password`, `role`, `created`, `modified`)
VALUES
(NULL, ‘Mark’, ‘1212’, ‘admin’, ‘2018-10-24 03:05:16’, ‘2018-10-24 03:05:16’),
(NULL, ‘Rana’, ‘232323’, ‘admin’, ‘2018-10-24 03:05:16’, ‘2018-10-24 03:05:16’);
2) Mysql database connection
For example we will use connection.php page to build our connection with mysql database. Let’s create a database connection using mysqli database.
connection.php
<?php
$servername = “localhost”;
$username = “root”;
$password = “root”;
$dbname = “your_db_name”;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die(“Connection failed: ” . $conn->connect_error);
}
?>
3) Fetch users data.
Now we will fetch users data in a html table. Here, we have used bootstrap CDN for a table design. Now for fetch users data you can follow the example below :
Example our page is index.php
<?php require_once “connection.php” ?>
<!DOCTYPE html>
<html lang=”en”>
<head>
<meta charset=”UTF-8″>
<title>Document</title>
<link rel=”stylesheet” href=”https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css”>
</head>
<body>
<div class=”container”>
<div class=”row”>
<?php
$sql = “SELECT * FROM users”;
$result = $conn->query($sql);
?>
<h2>User List :</h2>
<table class=”table table-condensed”>
<tr>
<th>Id</th>
<th>User Name</th>
<th>Created</th>
</tr>
<button class=”btn btn-info”>Delete</button>
<?php while($row = $result->fetch_object()) : ?>
<tr>
<td><?= $row->id; ?></td>
<td><?= $row->username; ?></td>
<td><?= $row->created; ?></td>
</tr>
<?php endwhile; ?>
<?php $conn->close(); ?>
</table>
</div>
</div>
</body>
</html>
for more : Introduction · Bootstrap (getbootstrap.com)
4) Prepare an users table for deleting multiple records.
Now we will prepare our index.php to delete multiple records. We need 3 changes here.
- Create checkboxes for users.
- Create a form to submit user id’s to delete.
- Create a button.
After added above changes our table will look like below :
<table class=”table table-condensed”>
<tr>
<th>#</th>
<th>Id</th>
<th>User Name</th>
<th>Created</th>
</tr>
<form action=”delete.php” method=”POST”>
<button class=”btn btn-info”>Delete</button>
<?php while($row = $result->fetch_object()) : ?>
<tr>
<td>
<input type=’checkbox’ name=’select[]’ value='<?= $row->id?>’ >
</td>
<td><?= $row->id; ?></td>
<td><?= $row->username; ?></td>
<td><?= $row->created; ?></td>
</tr>
<?php endwhile; ?>
<?php $conn->close(); ?>
</form>
</table>
Please , See the changes here
a new <th>#</th> has added
We also add a form before button
<form action=”delete.php” method=”POST”>
Notice form action is delerAll.php and form method is POST.
New td also included which is an input field
<input type=’checkbox’ name=’select[]’ value='<?= $row->id?>’ >
This input field is a checkbox and the name of my field is select, which is an array and field value is user id.
5) Delete multiple records using PHP.
In the final step we will create a file called delete.php, which was our form action.
Please see below code for delete.php
<?php
require_once “connection.php”; //connect with connection page
$ids = $_POST[‘select’]; // receive ids
foreach ($ids as $key => $id) {
$sql = “DELETE FROM users WHERE id={$id}”; //delete data from users table
$result = $conn->query($sql);
if($result){
header(‘Location:index.php’); //after delete redirect to index.php page
}
}
?>
So, That was the simple example.
