How to Implement a Database Search Function with wpdb in WordPress Plugin
Posted on 17th June 2023
When developing a WordPress plugin, you may need to access data from the WordPress database. The $wpdb class is the class that gives you access to the WordPress database. In this article, we will show you how to use the $wpdb class to search data from a WordPress database table.
Step 1: Creating a Database Table
The first step is to create a database table. For this example, we will create a table called people. This table will have three columns: id, name, and age. The id column is the primary key and is used to uniquely identify each row in the table. The name and age columns are used to store the name and age of each person in the table.
Step 2: Inserting Data into the Database Table
Once the database table has been created, we can insert data into it. For this example, we will insert three rows of data into the people table. The first row will have an id of 1, a name of John, and an age of 30. The second row will have an id of 2, a name of Jane, and an age of 25. The third row will have an id of 3, a name of Joe, and an age of 35.
Step 3: Searching the Database Table
Now that we have data in our database table, we can search it. For this example, we will search for all rows where the name column contains the letter “J”. We will do this by using the $wpdb->get_results() method. This method accepts two parameters: the SQL query and the data type. The SQL query is the query that we want to execute on the database. The data type is the data type that we want the results to be returned as. In this example, we will set the data type to OBJECT.
The $wpdb->get_results() method will return an array of objects. Each object will represent a row in the database table. In our example, we will have an array with three objects. Each object will have three properties: id, name, and age. We can access these properties by using the object’s id property. For example, we can access the name property of the first object in the array by using the code $results[0]->name. This will return the value “John”.
Step 4: Displaying the Results
Once we have the results of our database search, we can display them to the user. In this example, we will loop through the results array and print each row to the screen. We will also format the output so that it is easy to read. For this example, we will use an unordered list (<ul>) to display the results.
The code for this example is as follows:
<?php
$results = $wpdb->get_results(
"SELECT * FROM people WHERE name LIKE '%J%'",
OBJECT
);
foreach ( $results as $result ) {
printf(
'<li>%1$s (%2$s years old)</li>',
$result->name,
$result->age
);
}
?>
</code></pre>
This code will output the following:
- John (30 years old)
- Jane (25 years old)
- Joe (35 years old)
Conclusion
In this article, we have shown you how to use the $wpdb class to search data from a WordPress database table. We have also shown you how to display the results of the search to the user. This example can be easily extended to allow for more complex searches. For example, you could add a form to the user interface that allows the user to specify the search criteria. You could also add pagination to the results so that only a certain number of results are displayed at a time.
Assuming you have a search form with an input field and a submit button, the first thing you need to do is get the value of the search input field. This can be done with the $_GET superglobal array:
$search_term = $_GET['search_term'];
Once you have the search term, you can use the WordPress $wpdb class to query the database. The $wpdb class has a number of methods for querying the database, but the one we'll be using for this example is the $wpdb->get_results() method.
This method takes a SQL query as its first argument and an optional array of arguments to be replaced in the query. The query we'll be using is a simple SELECT statement:
SELECT * FROM wp_posts WHERE post_title LIKE '%$search_term%' OR post_content LIKE '%$search_term%'
This query will select all posts from the database where the post title or post content contains the search term. The % symbols are wildcards that match any characters.
Once you have the results of the query, you can loop through them and display them however you like. In this example, we'll just print the title and content of each post:
foreach ( $results as $result ) {
echo '
' . $result->post_title . '
';
echo '
' . $result->post_content . '
';
}
That's all there is to it! With just a few lines of code, you can add a search function to your WordPress plugin.
This is the code that we will be using to implement the database search:
$results = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM $wpdb->posts WHERE post_title LIKE '%s'", $search_term ) );
foreach ( $results as $result ) {
// do something with $result
}
