Deleting MySQL Table record

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

Источник

Delete mysql with php

Для удаления данных применяется 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 ""; foreach($result as $row)< echo ""; echo ""; echo ""; echo ""; echo ""; > echo "
Имя
Возраст
 
" . $row["name"] . "
" . $row["age"] . "
  
"; $result->free(); > else< echo "Ошибка: " . $conn->error; > $conn->close(); ?>  

В таблицы для каждой строки определена форма, которая посылает данные в 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 ""; foreach($result as $row)< echo ""; echo ""; echo ""; echo ""; echo ""; > echo "
Имя
Возраст
 
" . $row["name"] . "
" . $row["age"] . "
  
"; mysqli_free_result($result); > else < echo "Ошибка: " . mysqli_error($conn); >mysqli_close($conn); ?>  

 $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); > ?>

Источник

Delete row with php

i currently have a SQL Database where i store some data. That data is getting viewed on a website with the following code:

 echo "" . $row["project"]. " " . "" . " ". $row[ "project_desc"]. " " . "" . " ". $row["hours"]. "" . " ". $day. ""; 

But now i want it to have a extra tab where you can delete the row that is showed on the website from the database. How can i do that? if you need it, i use MySQL And im using mysqli

@Nordy Vlasman. I have provided you with clear explanation about what you needed and made my suggestions too. Have a look and share thoughts. If you face any hindrance let me know.

2 Answers 2

You should add this code inside your row-

when user clicks «Delete» then delete.php is called, so purchase id which want to be deleted should be transfer via delete.php,

$id = $_GET['id']; mysql_query("DELETE from purchase WHERE "); 

you have to create a delete.php page and you have to connect with the database and after that you have to run this operation- $result = mysql_query(«DELETE from purchase WHERE if($result) < redirect('');>

Note: This extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used along with prepared statements .

Hence i am suggesting you to learn the PDO tutorial from this link and this is very useful for developing in the future projects.

To delete a single row in a table, you use the DELETE statement with a WHERE clause that specifies which row to delete.

Delete Example using mysql:

1. In the list page where you display the data you need to add up the code for sending it to the delete.php page or you can delete it in the same page where you list the data.
2. Below i will provide you with two methods of how to pass the data.

Method One: Same page delete

Method Two: Delete.php page delete

PHP code to delete the data from the table:

If you need to delete the data from the list page itself or you are going to delete in the delete.php page you need to follow up the same code to get the REQUEST from the URL.

If you are going to delete in the delete.php page you need to connect the database.php file into that page.

Hope so my explanation will be clear for your understanding purpose and you can get it make working on the next projects on the fly.

Delete Example using PDO:

pdo = new PDO($conStr, self::DB_USER, self::DB_PASSWORD); > catch (PDOException $e) < die($e->getMessage()); > > /** * Delete a task based on a specified task id * @param int $id * @return bool true on success or false on failure */ public function delete($id) < $sql = 'DELETE FROM tasks WHERE task_id = :task_id'; $q = $this->pdo->prepare($sql); return $q->execute([':task_id' => $id]); > /** * close the database connection */ public function __destruct() < $this->pdo = null; > > $obj = new DeleteDataDemo(); // delete id 2 $obj->delete(2); 

Here i have found a very good tutorial for learning purpose please learn using this link.

Источник

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

Источник