How to delete a particular row,with out any Where condition, in mysql

Question

I have the following code

<?php

        $result = mysql_query("SELECT * FROM table1");
        $row = mysql_fetch_row($result);

        while($row = mysql_fetch_array($result))
        { 
            //Do some process

            //Then delete this row 
        }

I need to delete each row within the while loop.

is it possible?there is no primary key for this table

Why not a simple `DELETE FROM table` if you need to delete every row? — Passerby, Apr 29 '13 at 11:51
You will need some kind of key or reference to delete the needed rows, unless you want to delete the entire data set. — kabuto178, Apr 29 '13 at 11:51
@BartFriederichs Still be able to `TRUNCATE`, just need to run outside the loop. — Passerby, Apr 29 '13 at 11:56

score 1 · Answer 1 · answered Apr 29 '13 at 11:51

You can't, MySQL would have no clue on which record to delete.

You either use the primary key, or you add all fields and add a LIMIT, and even then you have no way of knowing for sure it is that row.

If you want to delete all records from a table, use

TRUNCATE TABLE table1

So, if you are actually doing SELECT * FROM table1, I'd code it as such:

$result = mysql_query("SELECT * FROM table1");
$row = mysql_fetch_row($result);

while($row = mysql_fetch_array($result))
{ 
    //Do some process

}
mysql_query("TRUNCATE TABLE table1");

(of course, don't use the mysql_* functions, but MySQLi or PDO)

score 1 · Answer 2 · edited May 23 '17 at 12:18

Please, don't use mysql_* functions in new code. They are no longer maintained and the deprecation process has begun on it. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which.

You could use the following MySQLI code:

$query = "SELECT * FROM tablename";
$result= $mysqli -> query($query);

while($row = $result -> fetch_object()){

      $row_identifier = $row -> column_with_unique_id;

      // Do the processing bit here.

      // Now Delete
      $del_query = "DELETE FROM tablename WHERE column_with_unique_id = ".$row_identifier;
      $del_result= $mysqli -> query($del_query);

}

score -1 · Answer 3 · answered Apr 29 '13 at 11:51

-1

From your code it looks like you wish to delete all entries from the table. (Because that's what your code/pseudocode does.) You can do that much easier with:

 DELETE FROM table

And please don't use mysql_* functions anymore. They are marked as deprecated and will eventually be removed from PHP. Use MySQLi or PDO instead.

answered Apr 29 '13 at 11:51

Till Helge

9,253
2
40
56

4

Faster even with `TRUNCATE TABLE` – Bart Friederichs Apr 29 '13 at 11:51

How to delete a particular row,with out any Where condition, in mysql

3 Answers3