Delete all rows from mySQL table in PHP

Question

I want to delete all rows in a table by clicking on a Button in PHP. I have used this code

<form>
  <input type="submit" class="button" name="delet" id="delet" value="DELETE WHOLE DATA"/>
</form>
<br></br>
<?php

  //if($_GET){
  if(isset($_GET['delet'])){

    $link = mysql_connect('localhost', 'root', '');
    if (!$link) {
      die('Could not connect: ' . mysql_error());
    }
    $sql = "DELETE  FROM tfinal";
    $sql1=mysql_query($sql, $link);
    if (mysql_query( $sql1)) {
      echo "Database dqqwewrw was successfully dropped\n";
    } else {
      echo 'Error dropping database: ' . mysql_error() . "\n";
    }
  }
?>

but after ruining it says: Error dropping database: Query was empty Any idea regarding overcome this problem

You don't use `DELETE` to remove a table, you use `DROP TABLE`. `DELETE` is for deleting rows from a table. — Devon Bessemer, Feb 17 '16 at 14:55

score 4 · Answer 1 · edited May 23 '17 at 11:50

Stop using mysql_* - it is officially deprecated and vulnerable to injection. Switch to using mysqli_* or PDO prepared statements.
DELETE is for deleting rows; DROP is for deleting tables. Your SQL query should look like:
```
$sql = "DROP TABLE `tablename`";
```
You are running the query twice - once in $sql1 = mysql_query(...) and once again in if(mysql_query(...)). For a DROP or DELETE query, it will return TRUE on success and FALSE on error.
The second time you run the query (if(mysql_query(...))) you are querying the result of the first query ($sql1) and not the query itself ($sql). You should instead write:
```
if(mysql_query($sql)){
```
...but in PDO/mysqli_ format instead.

References:

score 1 · Answer 2 · edited May 23 '17 at 11:45

1

if(mysql_query($sql1)) {

That is wrong. $sql1 is returned by a mysql_query(), so it is NOT a query, but a result.

related: What does a successful MySQL DELETE return? How to check if DELETE was successful?

edited May 23 '17 at 11:45

Community

1
1

answered Feb 17 '16 at 14:56

Franz Gleichmann

3,420
4
20
30

score 1 · Answer 3 · answered Feb 17 '16 at 15:06

Do following changes:

<form>
  <input type="submit" class="button" name="delet" id="delet" value="DELETE WHOLE DATA"/>
</form>
<br></br>
<?php

  //if($_GET){
  if(isset($_GET['delet'])){

    $link = mysqli_connect('localhost', 'root', '');
    if (!$link) {
      die('Could not connect: ' . mysql_error());
    }
    $sql = "DROP TABLE tfinal";
    //$sql1=mysql_query($sql, $link);
    if (mysqli_query($link, $sql)) {
      echo "Database dqqwewrw was successfully dropped\n";
    } else {
      echo 'Error dropping database: ' . mysqli_error() . "\n";
    }
  }
?>

score 1 · Accepted Answer · edited Jul 22 '19 at 03:56

1

Try this code:

<form>
    <input type="submit" class="button" name="delet" id="delet" value="DELETE WHOLE DATA"/>
</form>
<br></br>

<?php
if(isset($_GET['delet'])){
    $connection = mysqli_connect('localhost', 'root', 'root', 'testlo');
    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }
    $sql = 'DELETE FROM tbl_fordelete';
    $sql1=mysqli_query($connection, $sql);
    if ($sql1) {
        echo "Database dqqwewrw was successfully dropped\n";
    } else {
        echo 'Error dropping database: ' . mysql_error() . "\n";
    }
    $connection->close();
}

edited Jul 22 '19 at 03:56

ashleedawg

20,365
9
72
105

answered Feb 17 '16 at 15:11

rrr_2010

87
5

This code isn't "dropping a database", thankfully... it's deleting records from a table. Also btw, deleting all records from a table is also known as [truncating](https://dev.mysql.com/doc/refman/8.0/en/truncate-table.html). – ashleedawg Jul 22 '19 at 04:00

score 0 · Answer 5 · answered Jan 30 '18 at 13:47

if you want delete your table, first, you should create table temporary example:

$i="i";$joined=$i.$numCar;
        $sql = "CREATE TEMPORARY TABLE $joined (
                id  INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                km INT(11) UNSIGNED NOT NULL,
                gas INT(1) UNSIGNED NOT NULL,
                comment TEXT NOT NULL,
                date VARCHAR(32) NOT NULL,
                workId INT(11) UNSIGNED NOT NULL,
                name VARCHAR(32) NOT NULL,
                damage TINYINT(1) NOT NULL,
                image1 VARCHAR(128) NOT NULL,
                image2 VARCHAR(128) NOT NULL)";

and after you can delete the table, example:

$i="i";$joined1=$i.$numberCar;$sql = "DELETE FROM '$joined1'";mysqli_query($con,$sql);

You must have the DROP privilege for each table. the link that explain better: mySql

Delete all rows from mySQL table in PHP

5 Answers5