plugins - How to delete all records from or empty a custom database table?

admin管理员组

文章数量:1414614

I can't seem to find a simple reliable way to do this - perhaps?:

// delete row ##
global $wpdb;
$delete = $wpdb->query( 
    $wpdb->prepare( 
         "DELETE * FROM `wp_table_name`"
    )
);

// return ##
$return = __('Table Emptied.');
if ( $wpdb->last_error ) {
    $return = $wpdb->last_error;
}

But this gives me the classic:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM wp_table_name' at line 1

I can't seem to find a simple reliable way to do this - perhaps?:

// delete row ##
global $wpdb;
$delete = $wpdb->query( 
    $wpdb->prepare( 
         "DELETE * FROM `wp_table_name`"
    )
);

// return ##
$return = __('Table Emptied.');
if ( $wpdb->last_error ) {
    $return = $wpdb->last_error;
}

But this gives me the classic:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM wp_table_name' at line 1

• plugins
• customization
• database
• wpdb

Share Improve this question edited Dec 7, 2013 at 16:02 Q Studio asked Dec 4, 2013 at 17:59 Q StudioQ Studio 2,52677 gold badges2525 silver badges3939 bronze badges 5

• Not sure but shouldn't you use $wpdb instead of $this? – GhostToast Commented Dec 4, 2013 at 18:07
• 1 The SQL looks right if you have a table named wp_table_name which I doubt. There is also truncate. I don't see how this is a WordPress question though. – s_ha_dum Commented Dec 4, 2013 at 18:11
• @s_ha_dum - thanks, the table_name is whitelisted.. I think using $wpdb makes this a WP question.. this is a WP plugin using a table in the WP DB.. I've added an answer below - thanks again! – Q Studio Commented Dec 4, 2013 at 18:17
• But it isn't a $wpdb error. It is a just a SQL error. – s_ha_dum Commented Dec 4, 2013 at 18:53
• 1 isn't it true that all WordPress errors are PHP errors, by the same logic ;) – Q Studio Commented Dec 4, 2013 at 21:29

Add a comment  | 

4 Answers 4

Sorted by: Reset to default 15

I would just modify Q Studio example to:

global $wpdb;
$table  = $wpdb->prefix . 'table_name';
$delete = $wpdb->query("TRUNCATE TABLE $table");

Thanks @s_ha_dum - Truncate does the job nicely:

// delete row ##
global $wpdb;
$delete = $wpdb->query("TRUNCATE TABLE `wp_table_name`");

2 years late, but maybe this will help someone in the future :)

The correct syntax for deleting rows from a MySQL table is:

DELETE FROM `wp_table_name`
WHERE 1 = 1;

This is late and the answer been accepted. But no one mentioned that your DELETE SQL statement is incorrect. Try this:

DELETE FROM `wp_table_name`

However, this might still not work. You might need to change your MariaDB/MySQL settings, which protects such deletes, to prevent you from mistakenly deleting a full set of data. To make it work, you have to

DELETE FROM `wp_table_name` WHERE `some_column` = 'some_value'

Cheers

本文标签： pluginsHow to delete all records from or empty a custom database table