0

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
  • 10,148
  • 7
  • 57
  • 107
jfisk
  • 6,125
  • 20
  • 77
  • 113
  • You need to be more specific. Are you using `mysqli`? `PDO`? Do you want to wipe out all rows from a table? `TRUNCATE table` or just a specific rows from a table based upon a condition? `DELETE FROM table WHERE field = 'identifier'` Hopefully your SQL user inside PHP doesn't have permissions to truncate, but that's neither here nor there. – MetalFrog Apr 26 '12 at 16:06
  • 2
    possible duplicate of [Delete data from all tables in MYSQL](http://stackoverflow.com/questions/1885101/delete-data-from-all-tables-in-mysql) – nickb Apr 26 '12 at 16:07
  • 2
    A trivial search for "[mysql delete all records](https://duckduckgo.com/?q=mysql+delete+all+records)" gave the answer. – halfer Apr 26 '12 at 16:07

2 Answers2

12

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" );
Sampson
  • 265,109
  • 74
  • 539
  • 565
3

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

<?php

header('Content-Type: text/plain');

$link = mysqli_connect('host', 'username', 'password');
mysqli_select_db($link, 'database_name');
mysqli_set_charset($link, 'utf8');

$sql = 'SHOW TABLE STATUS FROM `database_name`;';
$result = mysqli_query($link, $sql);

$rows = array();
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
    $rows[] = $row;
}

$n = 0;
foreach ($rows as $row) {
    $sql = 'DROP TABLE IF EXISTS `' . mysql_real_escape_string($row['Name']) . '`;';
    mysqli_query($link, $sql);
    ++$n;
}

echo $n . 'tables dropped' . PHP_EOL;
exit(__FILE__ . ': ' . __LINE__);
TRiG
  • 10,148
  • 7
  • 57
  • 107