Working with Database Tables in Your Plugins (Part 3)

in WordPress Tutorials

This post is the third part of a series on working with database tables in WordPress plugins. Read the first and second post before proceeding.

In the last post, I showed you how to retrieve data from a table, in this post I’m going to demonstrate how to insert, delete, and update data in a tables.

Insert Rows into Your Table

Putting data into your table is pretty simple. All you have to do is run a simple insert query with the $wpdb class.

1
2
3
4
5
6
7
8
9
function insert_my_data()	{
	global $wpdb;
 
	$wpdb->insert( 'your_table_name', array( 
		'column1' => 'value1', 
		'column2' => 123 
		) 
	);
}

This function will insert “value1″ into “column1″, and “123” into “column2″, and it will create a new row in which to store the column data.

Updating Rows in Your Table

Updating data in a table is just a little be more complicated than inserting because we have to tell WordPress which rows to update, but it’s still pretty simple.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
function update_my_data()	{
	global $wpdb;
 
	$wpdb->update( 'your_table_name', 
		array( 
			'column1' => 'value1', 
			'column2' => 123 
		),
		array(
			'ID' => 1
		)
 
	);
}

This function will update “column1″ and “column2″ in the row that has an ID of 1.

Deleting a Row from a Table

Deleting data is almost identical to updating: we have to specify which row to remove. The query function also looks a little different.

1
2
3
4
5
function delete_my_data()	{
	global $wpdb;
 
	$wpdb->query(" DELETE FROM $wpdb->your_table_name WHERE ID = '1' ");
}

This will delete the row that has an ID of 1.

Anything not making sense or working? Ask! Getting a good hold of these techniques will greatly improve your plugin development skills.

Enjoy!

Comments (6)

  • Comment by poc
    poc
    says:

    Hi,

    I tried to use your steps but I’m getting the same result as here http://stackoverflow.com/questions/20428783/delet-row-in-custom-wp-db

    any ideas?

  • Comment by Pippin
    Pippin
    says:

    Oh yeah, that is a minor detail that is not explained very well. Glad you figured it out!

  • Comment by Garrett
    Garrett
    says:

    Oh, so here’s what I ended up doing that worked:

    global $wpdb;
    $table_name = $wpdb->prefix . “audio_rating”;

    //insert values into table
    $rows_affected = $wpdb->insert( $table_name, array( ‘time’ => current_time(‘mysql’), ‘audio_post_id’ => $audio_post_id, ‘doctor_staff’ => $doctor_staff, ‘audio_rating’ => $audio_rating ) );

  • Comment by Garrett
    Garrett
    says:

    I figured it out, WordPress’ documentation on $wpdb is a little misleading. Here’s how they say to insert rows:
    insert( $table, $data, $format ); ?>

    But $table isn’t just a string containing the name of your table. It’s really ‘$wpdb->your_table_name’. I finally figured that out after hours of searching. Thanks for your reply though, you’re the only one that has.

    -Garrett

  • Comment by Pippin
    Pippin
    says:

    Can you show me the code that you are using?

  • Comment by Garrett
    Garrett
    says:

    Hey man, I appreciate the toots. However I’m having a problem inserting data and it’s making me crazy. I’ve followed your tutorial exactly yet nothing is being inserted into my table. All my names are correct so I don’t understand what’s wrong. Any suggestions?