Introduction
The wpdb class is the database interface used by WordPress. It is possible to use wpdb to interact with other tables in the database, not just the default WordPress tables. This can be useful when developing a WordPress plugin that needs to store data in a custom table.
Creating a Custom Table
First, you need to create a custom table to store your data. You can do this by running a CREATE TABLE SQL query. The following example creates a table named my_table with two columns: id and name:
$sql = "CREATE TABLE my_table (
id int NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
PRIMARY KEY (id)
);";
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql );For more information on creating custom tables, see the Creating Tables with Plugins article in the WordPress Codex.
Inserting Data
Once you have created your custom table, you can insert data into it using the wpdb::insert() method. The following example inserts a row into the my_table table with a name value of “John”:
$wpdb->insert(
'my_table',
array(
'name' => 'John'
)
);Updating Data
You can update data in your custom table using the wpdb::update() method. The following example updates the row with an id value of 1, setting the name value to “John Smith”:
$wpdb->update(
'my_table',
array(
'name' => 'John Smith'
),
array(
'id' => 1
)
);Deleting Data
You can delete data from your custom table using the wpdb::delete() method. The following example deletes the row with an id value of 1:
$wpdb->delete(
'my_table',
array(
'id' => 1
)
);Selecting Data
You can select data from your custom table using the wpdb::get_results() method. The following example selects all rows from the my_table table:
$rows = $wpdb->get_results( "SELECT * FROM my_table" );
foreach ( $rows as $row ) {
echo $row->name;
}Conclusion
In this article, we have seen how to use the wpdb class to interact with custom tables in WordPress. We have seen how to create a custom table, insert data into it, update data, delete data, and select data. This can be useful when developing a WordPress plugin that needs to store data in a custom table.
If your plugin needs to store data in a custom database table, the WordPress wpdb class is the tool you need to interact with your database. In this article, we’ll show you how to use wpdb to create, update, and delete data in your custom database table.
First, you’ll need to create a new database table for your plugin. You can do this by running a SQL query, or by using a tool like phpMyAdmin. Once your database table is created, you’ll need to add the following code to your plugin to initialize the wpdb class:
global $wpdb;
$wpdb = new wpdb( ‘DB_USER’, ‘DB_PASSWORD’, ‘DB_NAME’, ‘DB_HOST’ );
Replace DB_USER, DB_PASSWORD, DB_NAME, and DB_HOST with the appropriate values for your database.
Now that you’ve initialized the wpdb class, you can start using it to interact with your database.
To insert data into your database table, you can use the $wpdb->insert() method. The first parameter of this method is the name of your database table, and the second parameter is an array of data to be inserted. For example, the following code would insert a new row into a database table named “my_table”:
$wpdb->insert(
‘my_table’,
array(
‘column1’ => ‘value1’,
‘column2’ => ‘value2’,
‘column3’ => ‘value3’,
)
);
To update an existing row in your database table, you can use the $wpdb->update() method. The first parameter of this method is the name of your database table, the second parameter is an array of data to be updated, and the third parameter is an array of WHERE conditions. For example, the following code would update a row in a database table named “my_table”:
$wpdb->update(
‘my_table’,
array(
‘column1’ => ‘value1’,
‘column2’ => ‘value2’,
),
array(
‘ID’ => 1,
)
);
To delete a row from your database table, you can use the $wpdb->delete() method. The first parameter of this method is the name of your database table, and the second parameter is an array of WHERE conditions. For example, the following code would delete a row from a database table named “my_table”:
$wpdb->delete(
‘my_table’,
array(
‘ID’ => 1,
)
);
And that’s all there is to using the wpdb class to interact with your custom database table!
Now that you have your custom table set up, you can start using the wpdb class to interact with it. The first thing you need to do is get an instance of the wpdb class:
$wpdb = new wpdb( ‘username’, ‘password’, ‘database’, ‘localhost’ );
Once you have an instance of the wpdb class, you can start running queries against your custom table. For example, let’s say you want to get all of the rows from your table:
$rows = $wpdb->get_results( “SELECT * FROM {$wpdb->prefix}my_table” );
You can also run UPDATE, DELETE, and INSERT queries:
$wpdb->update(
$wpdb->prefix . ‘my_table’,
array(
‘column1’ => ‘value1’,
‘column2’ => ‘value2’,
),
array(
‘ID’ => 1,
),
array(
‘%s’,
‘%s’,
),
array(
‘%d’,
)
);
$wpdb->delete(
$wpdb->prefix . ‘my_table’,
array(
‘ID’ => 2,
),
array(
‘%d’,
)
);
$wpdb->insert(
$wpdb->prefix . ‘my_table’,
array(
‘column1’ => ‘value1’,
‘column2’ => ‘value2’,
),
array(
‘%s’,
‘%s’,
)
);
Remember to always escape your data before inserting it into the database:
$value1 = $wpdb->_real_escape( ‘some value’ );
$value2 = $wpdb->_real_escape( ‘some other value’ );
$wpdb->insert(
$wpdb->prefix . ‘my_table’,
array(
‘column1’ => $value1,
‘column2’ => $value2,
),
array(
‘%s’,
‘%s’,
)
);
You can also use prepare() to prepare your SQL queries:
$sql = $wpdb->prepare( “INSERT INTO {$wpdb->prefix}my_table ( column1, column2 ) VALUES ( %s, %s )”, $value1, $value2 );
$wpdb->query( $sql );
Depending on what you’re doing, you may also need to use $wpdb->prefix to prepend the database prefix to your table names. This is especially important when you’re writing code that other people will use, as it will ensure that your code works even if they have a different database prefix.
That’s all there is to using the wpdb class to interact with custom tables in WordPress. With a little practice, you’ll be able to use wpdb to easily create, update, and delete data in your custom tables.
Leave a Reply