How delete all data from database in php?

Delete Data From a MySQL Table Using MySQLi and PDO

The DELETE statement is used to delete records from a table:

DELETE FROM table_name
WHERE some_column = some_value

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:

The following examples delete the record with id=3 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 id=3";

if ($conn->query($sql) === TRUE) {
  echo "Record deleted successfully";
} else {
  echo "Error deleting record: " . $conn->error;
}

$conn->close();
?>

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 id=3";

if (mysqli_query($conn, $sql)) {
  echo "Record deleted successfully";
} else {
  echo "Error deleting record: " . mysqli_error($conn);
}

mysqli_close($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 id=3";

  // use exec() because no results are returned
  $conn->exec($sql);
  echo "Record deleted successfully";
} catch(PDOException $e) {
  echo $sql . "
" . $e->getMessage();
}

$conn = null;
?>

After the record is deleted, the table will look like this:

I would like to scrap all my records in a database. I want to use just a PHP script, not PhpMyAdmin. I have will be using a MySQL database administrator account. What SQL query should I use?

TRiG

9,8246 gold badges55 silver badges106 bronze badges

asked Apr 26, 2012 at 16:04

3

You could run a query:

/* Assumes the existence of a few key variables
   and further, that your user has the appropriate permissions */

mysql_connect( $host, $user, $pass ) or die( mysql_error() );
mysql_select_db( $db ) or die( mysql_error() );

mysql_query( "TRUNCATE TABLE tablename" );

Or

mysql_query( "DELETE FROM tablename" );

CAUTION!

These queries will result in all records being deleted. If you want only certain records to be dropped, add a where clause:

mysql_query( "DELETE FROM tablename WHERE userid = 5" );

answered Apr 26, 2012 at 16:06

SampsonSampson

261k74 gold badges530 silver badges559 bronze badges

To completely empty the database, dropping all tables (if that's what you really want to do), run this PHP script.

answered Apr 26, 2012 at 16:43

TRiGTRiG

9,8246 gold badges55 silver badges106 bronze badges