- Select Insert Update Delete Record using PHP and MySQL
- How to Select Insert Update Delete Record using PHP and MySQL
- 1 – DB.PHP – Connecting Database
- 2 – Insert.PHP – Insert Records Into MySQL DB
- 3 – Select.php – Select Record From MySQL DB
- 4 – Update.php – Update Record Into MySQL DB
- 5 – Delete.php – Delete Record From MySQL DB
- MySQL — DELETE Query
- Syntax
- Deleting Data from the Command Prompt
- Example
- Deleting Data Using a PHP Script
- Syntax
- Example
- Output
- Mysql php delete record
- Объектно-ориентированный стиль
- Список пользователей
- Процедурный стиль
- Список пользователей
- PHP MySQL Delete Data
- Delete Data From a MySQL Table Using MySQLi and PDO
- Example (MySQLi Object-oriented)
- Example (MySQLi Procedural)
- Example (PDO)
- MySQLi — Delete Query
- Syntax
- Deleting Data from the Command Prompt
- Example
- Deleting Data Using a PHP Script
- Syntax
- Example
- Output
Select Insert Update Delete Record using PHP and MySQL
How to insert update delete record using php and mysql. In this tutorial we will show you step by step how to insert update delete record using php and mysql.
As well as you can use this insert update delete in php with source code to personally and professinally.
How to Select Insert Update Delete Record using PHP and MySQL
- DB.PHP – Connecting Database
- Insert.PHP – Insert Records Into MySQL DB
- Select.php – Select Record From MySQL DB
- Update.php – Update Record Into MySQL DB
- Delete.php – Delete Record From MySQL DB
- In starting, we will create MySQL database connection file in PHP.
- Will use mysqli_connect() function to connecting database with PHP.
1 – DB.PHP – Connecting Database
First of all, Create db.php file to connecting mysql database with PHP:
2 – Insert.PHP – Insert Records Into MySQL DB
Then, Create a new file named insert.php and add the following code into insert.php:
Note that, The SQL INSERT statement will insert record in MySQL database using PHP function.
3 – Select.php – Select Record From MySQL DB
Then, create a new file named Select.php and add the following code into selete.php:
while($data = mysqli_fetch_array($query)) < echo "Id = ".$data['id']."
"; echo "Firstname = ".$data['firstname']."
"; echo "Lastname = ".$data['lastname']."
"; echo "Mobile = ".$data['mobile']."
"; > ?>Note that, The SQL SELECT statement will SELECT record FROM MySQL database using PHP function.
If you want to select limited records from MySQL database, you can use LIMIT clause with SQL query like following:
$sql = "SELECT * FROM users Limit 10";
Or, if you want to fetch record with ascending or descending order. So, you can use ASC OR DESC with SQL query.
4 – Update.php – Update Record Into MySQL DB
Now, you can modify database table records using SQL “UPDATE “ statement query.
So, Create a new file named Update.php and add the following code into update.php:
5 – Delete.php – Delete Record From MySQL DB
If you want to delete some records or all records from database, you can use DELETE SQL statement with PHP.
So, Create a new file named Delete.php and add the following code into delete.php:
MySQL — DELETE Query
If you want to delete a record from any MySQL table, then you can use the SQL command DELETE FROM. You can use this command at the mysql> prompt as well as in any script like PHP.
Syntax
The following code block has a generic SQL syntax of the DELETE command to delete data from a MySQL table.
DELETE FROM table_name [WHERE Clause]
- If the WHERE clause is not specified, then all the records will be deleted from the given MySQL table.
- You can specify any condition using the WHERE clause.
- You can delete records in a single table at a time.
The WHERE clause is very useful when you want to delete selected rows in a table.
Deleting Data from the Command Prompt
This will use the SQL DELETE command with the WHERE clause to delete selected data into the MySQL table – tutorials_tbl.
Example
The following example will delete a record from the tutorial_tbl whose tutorial_id is 3.
root@host# mysql -u root -p password; Enter password:******* mysql> use TUTORIALS; Database changed mysql> DELETE FROM tutorials_tbl WHERE tutorial_id=3; Query OK, 1 row affected (0.23 sec) mysql>
Deleting Data Using a PHP Script
PHP uses mysqli query() or mysql_query() function to delete records in a MySQL table. This function takes two parameters and returns TRUE on success or FALSE on failure.
Syntax
Required — SQL query to delete records in a MySQL table.
Optional — Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.
Example
Try the following example to delete a record in a table −
Copy and paste the following example as mysql_example.php −
connect_errno ) < printf("Connect failed: %s
", $mysqli->connect_error); exit(); > printf('Connected successfully.
'); if ($mysqli->query('DELETE FROM tutorials_tbl where tutorial_id = 4')) < printf("Table tutorials_tbl record deleted successfully.
"); > if ($mysqli->errno) < printf("Could not delete record from table: %s
", $mysqli->error); > $sql = "SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl"; $result = $mysqli->query($sql); if ($result->num_rows > 0) < while($row = $result->fetch_assoc()) < printf("Id: %s, Title: %s, Author: %s, Date: %d
", $row["tutorial_id"], $row["tutorial_title"], $row["tutorial_author"], $row["submission_date"]); > > else < printf('No record found.
'); > mysqli_free_result($result); $mysqli->close(); ?>Output
Access the mysql_example.php deployed on apache web server and verify the output. Here we’ve entered multiple records in the table before running the select script.
Connected successfully. Table tutorials_tbl record deleted successfully. Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021 Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021 Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021 Id: 5, Title: Apache Tutorial, Author: Suresh, Date: 2021
Mysql php delete record
Для удаления данных применяется sql-команда DELETE :
DELETE FROM Таблица WHERE столбец = значение
Для удаления данных возьмем использованную в прошлых темах таблицу Users со следующим определением:
CREATE TABLE Users (id INTEGER AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30), age INTEGER)
Объектно-ориентированный стиль
Вначале определим для вывода всех объектов из БД скрипт index.php :
Список пользователей
connect_error)< die("Ошибка: " . $conn->connect_error); > $sql = "SELECT * FROM Users"; if($result = $conn->query($sql))< echo "
"; $result->free(); > else< echo "Ошибка: " . $conn->error; > $conn->close(); ?>Имя Возраст "; echo " "; > echo "" . $row["name"] . " "; echo "" . $row["age"] . " "; echo "В таблицы для каждой строки определена форма, которая посылает данные в POST-запросе скрипту delete.php . Чтобы передать в delete.php идентификатор удаляемого объекта, на форме определено скрытое поле для хранения id объекта.
Обратите внимание, что в данном случае применяется не ссылка для удаления типа
которая оправляет данные в GET-запросе, а именно форма, которая отправляет данные в POST-запросе. Почему? Подобные GET-запросы потенциально небезопасны. Допустим, нам пришло электронное письмо, в которое была внедрена картинка посредством тега:
В итоге при открытии письма 1-я запись в таблице может быть удалена. Уязвимость касается не только писем, но может проявляться и в других местах, но смысл один — GET-запрос к скрипту, который удаляет данные, несет потенциальную уязвимость.
Теперь определим сам скрипт delete.php , который будет выполнять удаление:
connect_error)< die("Ошибка: " . $conn->connect_error); > $userid = $conn->real_escape_string($_POST["id"]); $sql = "DELETE FROM Users WHERE "; if($conn->query($sql)) < header("Location: index.php"); >else< echo "Ошибка: " . $conn->error; > $conn->close(); > ?>В данном случае скрипт получает через POST-запрос значение id и по этому идентификатору выполняет удаление. После чего происходит переадресация на скрипт index.php .
Процедурный стиль
Список пользователей
$sql = "SELECT * FROM Users"; if($result = mysqli_query($conn, $sql))< echo "
"; mysqli_free_result($result); > else < echo "Ошибка: " . mysqli_error($conn); >mysqli_close($conn); ?>Имя Возраст "; echo " "; > echo "" . $row["name"] . " "; echo "" . $row["age"] . " "; echo "$userid = mysqli_real_escape_string($conn, $_POST["id"]); $sql = "DELETE FROM Users WHERE "; if(mysqli_query($conn, $sql)) < header("Location: index.php"); >else < echo "Ошибка: " . mysqli_error($conn); >mysqli_close($conn); > ?>PHP MySQL Delete Data
Delete Data From a MySQL Table Using MySQLi and PDO
The DELETE statement is used to delete records from a table:
Notice the WHERE clause in the DELETE syntax: The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!
To learn more about SQL, please visit our SQL tutorial.
Let’s look at the «MyGuests» table:
id firstname lastname email reg_date 1 John Doe john@example.com 2014-10-22 14:26:15 2 Mary Moe mary@example.com 2014-10-23 10:22:30 3 Julie Dooley julie@example.com 2014-10-26 10:48:23 The following examples delete the record with in the «MyGuests» table:
Example (MySQLi Object-oriented)
$servername = «localhost»;
$username = «username»;
$password = «password»;
$dbname = «myDB»;// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) die(«Connection failed: » . $conn->connect_error);
>// sql to delete a record
$sql = «DELETE FROM MyGuests WHERE ($conn->query($sql) === TRUE) echo «Record deleted successfully»;
> else echo «Error deleting record: » . $conn->error;
>Example (MySQLi Procedural)
$servername = «localhost»;
$username = «username»;
$password = «password»;
$dbname = «myDB»;// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) die(«Connection failed: » . mysqli_connect_error());
>// sql to delete a record
$sql = «DELETE FROM MyGuests WHERE (mysqli_query($conn, $sql)) echo «Record deleted successfully»;
> else echo «Error deleting record: » . mysqli_error($conn);
>Example (PDO)
$servername = «localhost»;
$username = «username»;
$password = «password»;
$dbname = «myDBPDO»;try $conn = new PDO(«mysql:host=$servername;dbname=$dbname», $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);// sql to delete a record
$sql = «DELETE FROM MyGuests WHERE // use exec() because no results are returned
$conn->exec($sql);
echo «Record deleted successfully»;
> catch(PDOException $e) echo $sql . «
» . $e->getMessage();
>After the record is deleted, the table will look like this:
id firstname lastname email reg_date 1 John Doe john@example.com 2014-10-22 14:26:15 2 Mary Moe mary@example.com 2014-10-23 10:22:30 MySQLi — Delete Query
If you want to delete a record from any MySQL table, then you can use the SQL command DELETE FROM. You can use this command at the mysql> prompt as well as in any script like PHP.
Syntax
The following code block has a generic SQL syntax of the DELETE command to delete data from a MySQL table.
DELETE FROM table_name [WHERE Clause]
- If the WHERE clause is not specified, then all the records will be deleted from the given MySQL table.
- You can specify any condition using the WHERE clause.
- You can delete records in a single table at a time.
The WHERE clause is very useful when you want to delete selected rows in a table.
Deleting Data from the Command Prompt
This will use the SQL DELETE command with the WHERE clause to delete selected data into the MySQL table – tutorials_tbl.
Example
The following example will delete a record from the tutorial_tbl whose tutorial_id is 3.
root@host# mysql -u root -p password; Enter password:******* mysql> use TUTORIALS; Database changed mysql> DELETE FROM tutorials_tbl WHERE tutorial_id=3; Query OK, 1 row affected (0.23 sec) mysql>
Deleting Data Using a PHP Script
PHP uses mysqli query() or mysql_query() function to delete records in a MySQL table. This function takes two parameters and returns TRUE on success or FALSE on failure.
Syntax
Required — SQL query to delete records in a MySQL table.
Optional — Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.
Example
Try the following example to delete a record in a table −
Copy and paste the following example as mysql_example.php −
", $mysqli→connect_error); exit(); > printf('Connected successfully.
'); if ($mysqli→query('DELETE FROM tutorials_tbl where tutorial_id = 4')) < printf("Table tutorials_tbl record deleted successfully.
"); > if ($mysqli→errno) < printf("Could not delete record from table: %s
", $mysqli→error); > $sql = "SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl"; $result = $mysqli→query($sql); if ($result→num_rows > 0) < while($row = $result→fetch_assoc()) < printf("Id: %s, Title: %s, Author: %s, Date: %d
", $row["tutorial_id"], $row["tutorial_title"], $row["tutorial_author"], $row["submission_date"]); > > else < printf('No record found.
'); > mysqli_free_result($result); $mysqli→close(); ?>Output
Access the mysql_example.php deployed on apache web server and verify the output. Here we’ve entered multiple records in the table before running the select script.
Connected successfully. Table tutorials_tbl record deleted successfully. Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021 Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021 Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021 Id: 5, Title: Apache Tutorial, Author: Suresh, Date: 2021
