Directly Querying MySQL Database in WordPress
WordPress now provides a very useful class in order to directly query the database for any information desired. This can be useful when working with items outside of the loop, or when working with a certain theme that stores data in an abnormal way. This class utilizes the Php Data Object extension to safely send and receive data from the database.
For this example, we want to query the database for a specific field value that stores client profile data. In this case, the data is stored in one field as serialized data that is used by a nice client widget for the website. But lets say you want to display all the client profiles on one page as page content not just sidebar content. Well the wpdb object is the solution.
This object allows us to interact with the database in the safest way possible in order to minimize the risk of SQL injections. All we simply do is pass the object the required list of parameters including our query and how the query should be run. In this example, since we are only trying to get one field value, we must tell the query what to select (option_value) from a specific table (wp_options) where the option_name is client_profile. Click image for larger view.
Things to note:
- You must declare the global $wpdb
- It is always a good idea to use prepare statements for the most security. This means passing any values via variables.
After unserializing (via the unserialize() function) this particular query, the value returned is a 3D array, which can be very difficult to effectively loop through. It is always a good idea to use var_dump() just to see what all is being returned. This gives you a much better idea of what you are dealing with and how to properly display the data. In this example, $data is the top level array, $data[‘data’] is the second, and then the information we want is basically stored in a bunch of client arrays.
The real trick to looping through this array is getting down to the proper level. Ã‚Â Running a foreach loop on $data[‘data’] as $profile splits off each individual array, and then we can simply echo the values by referencing $profile[‘key’] and placing the desired formatting around it. Click image for larger view.