Free up Some Space: Make MySQL Delete Row and Other Table Elements

Up to this point, we have presented how database management systems are used to hold our data. However, some data loses its relevance with time. Now, we will explain how to make MySQL delete rows and other table components.

Keeping unwanted data out of the database will provide you with more space and make it easier to organize and access crucial information. Sometimes, you might even need to make MySQL delete table. If you want to be specific about which data to remove, you will have to apply a conditional clause.

The MySQL delete row task is linked to the removal of old records. As rows are filled with data, you should eliminate them if you want to get rid of old information.

MySQL Delete Row: Main Tips

• By using PHP and SQL statements, you can make MySQL delete users or other records you don't need in your database anymore.
• It is important to remove unneccesary data instead of letting it take up space in your database.
• You can make your MySQL delete rows, users, or even whole tables.

Data Removal Process: Code Examples

Using MySQL DELETE statement, you can remove certain records from your tables:

<?php
  $host = 'host';
  $user = 'user';
  $pass = 'pass';
  $db = 'db';
  try { 
    $conn = new PDO("mysql:host=$host;dbname=$db",$user,$pass);      	
    // set the PDO error mode to exception 
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    
    // sql to delete a record  
    $sql= "DELETE FROM users WHERE user_id=3";  
    // use exec() because no results are returned    
    $conn->exec($sql);      	
    echo "Record deleted successfully";
  } catch(PDOException $e) {      	
    echo $sql . "<br>" . $e->getMessage();
  }
  $conn = null;  
?>

The WHERE clause in DELETE statements is crucial if you wish to specify which records you want to eliminate. If WHERE clause is not included, you will delete all of your records. In other words, MySQL delete table task will be completed.

By using an example, we wil now explain how to MySQL delete rows. Let's look at the table below: every row represents the details of a certain user. Therefore, if we wish to MySQL delete user details from the table, we need to delete their rows.

In the examples below, we write statements to PHP delete rows that represent the third user (user_id equals 3). Which one you should use depends on the MySQL connection type you chose: they are meant for MySQLi object oriented (first example), MySQLi procedural (second example) and PDO (last example) connections:

<?php
  $host = 'host';
  $user = 'user';
  $pass = 'pass';
  $db =  'db';
  // Create connection
  $conn = new mysqli($host, $user, $pass, $db);
  // Check connection
  if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
  }
  // sql to delete a record 
  $sql = "DELETE FROM users WHERE user_id=3";  	
  if ($conn->query($sql) === TRUE) {    
    echo "Record deleted successfully";
  } else { 
    echo "Failed to delete: " . $conn->error;
  }
  $conn->close();  
?>

<?php
  $host = 'host';
  $user = 'user';
  $pass = 'pass';
  $db = 'db';
  // Create connection  
  $conn = mysqli_connect($host, $user, $pass, $db);  	
  // Check connection  	
  if (!$conn) { 
    die("Connection failed: " . mysqli_connect_error());
  }
  // sql to delete a record 
  $sql = "DELETE FROM users WHERE user_id=3";  	
  if (mysqli_query($conn, $sql)) { 
    echo "Record deleted successfully";
  } else { 
    echo "Failed to delete: " . mysqli_error($conn);
  }
  mysqli_close($conn); 
?>

<?php
  $host = 'host';
  $user = 'user';
  $pass = 'pass';
  $db = 'db';
  try { 
    $conn = new PDO("mysql:host=$host;dbname=$db",$user,$pass);      	
    // set the PDO error mode to exception 
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    
    // sql to delete a record  
    $sql= "DELETE FROM users WHERE user_id=3";  
    // use exec() because no results are returned    
    $conn->exec($sql);      	
    echo "Record deleted successfully";
  } catch(PDOException $e) {      	
    echo $sql . "<br>" . $e->getMessage();
  }
  $conn = null;  
?>

Once the statement has been executed, we will have successfully made MySQL delete rows we didn't need. Let's see how the table will look like now:

MySQL Delete Row: Summary

• Whenever you no longer need certain information in your database, you can easily delete it using PHP and SQL statements.
• If you don't remove irrelevant data regularly, it ends up taking up way too much space in your database.
• In MySQL, you can easily remove both tables and their elements.