WordPress Essentials: Interacting With The WordPress Database

Advertisement

While you already use many functions in WordPress to communicate with the database, there is an easy and safe way to do this directly, using the $wpdb class. Built on the great ezSQL class1 by Justin Vincent, $wpdb enables you to address queries to any table in your database, and it also helps you handle the returned data. Because this functionality is built into WordPress, there is no need to open a separate database connection (in which case, you would be duplicating code), and there is no need to perform hacks such as modifying a result set after it has been queried.

$wpdb class2
The $wpdb class modularizes and automates a lot of database-related tasks.

In this article, I will show you how to get started with the $wpdb class, how to retrieve data from your WordPress database and how to run more advanced queries that update or delete something in the database. The techniques here will remove some of the constraints that you run into with functions such as get_posts() and wp_list_categories(), allowing you to tailor queries to your particular needs. This method can also make your website more efficient by getting only the data that you need — nothing more, nothing less.

Getting Started

If you know how MySQL or similar languages work, then you will be right at home with this class, and you will need to keep only a small number of function names in mind. The basic usage of this class can be best understood through an example, so let’s query our database for the IDs and titles of the four most recent posts, ordered by comment count (in descending order).

<?php
   $posts = $wpdb->get_results("SELECT ID, post_title FROM $wpdb->posts WHERE post_status = 'publish'
   AND post_type='post' ORDER BY comment_count DESC LIMIT 0,4")
?>

As you can see, this is a basic SQL query, with some PHP wrapped around it. The $wpdb class contains a method (a method is a special name for functions that are inside classes), named get_results(), which will not only fetch your results but put them in a convenient object. You might have noticed that, instead of using wp_posts for the table’s name, I have used $wpdb->posts, which is a helper to access your core WordPress tables. More on why to use these later.

The $results object now contains your data in the following format:

Array
(
   [0] => stdClass Object
      (
         [ID] => 6
         [post_title] => The Male Angler Fish Gets Completely Screwed
      )

   [1] => stdClass Object
      (
         [ID] => 25
         [post_title] => 10 Truly Amazing Icon Sets From Germany
      )

   [2] => stdClass Object
      (
         [ID] => 37
         [post_title] => Elderberry Is Awesome
      )

   [3] => stdClass Object
      (
         [ID] => 60
         [post_title] => Gathering Resources and Inspiration With Evernote
      )

)

Retrieving Results From The Database

If you want to retrieve some information from the database, you can use one of four helper functions to structure the data.

get_results()

This is the function that we looked at earlier. It is best for when you need two-dimensional data (multiple rows and columns). It converts the data into an array that contains separate objects for each row.

<?php
   $posts = $wpdb->get_results("SELECT ID, post_title FROM wp_posts WHERE post_status = 'future'
   AND post_type='post' ORDER BY post_date ASC LIMIT 0,4")

   // Echo the title of the first scheduled post
   echo $posts[0]->post_title;
?>

get_row

When you need to find only one particular row in the database (for example, the post with the most comments), you can use get_row(). It pulls the data into a one-dimensional object.

<?php
   $posts = $wpdb->get_row("SELECT ID, post_title FROM wp_posts WHERE post_status = 'publish'
   AND post_type='post' ORDER BY comment_count DESC LIMIT 0,1")

   // Echo the title of the most commented post
   echo $posts->post_title;
?>

get_col

This method is much the same as get_row(), but instead of grabbing a single row of results, it gets a single column. This is helpful if you would like to retrieve the IDs of only the top 10 most commented posts. Like get_row(), it stores your results in a one-dimensional object.

<?php
   $posts = $wpdb->get_col("SELECT ID FROM wp_posts WHERE post_status = 'publish'
   AND post_type='post' ORDER BY comment_count DESC LIMIT 0,10")

   // Echo the ID of the 4th most commented post
   echo $posts[3]->ID;
?>

get_var

In many cases, you will need only one value from the database; for example, the email address of one of your users. In this case, you can use get_var to retrieve it as a simple value. The value’s data type will be the same as its type in the database (i.e. integers will be integers, strings will be strings).

<?php
   $email = $wpdb->get_var("SELECT user_email FROM wp_users WHERE user_login = 'danielpataki' ")

   // Echo the user's email address
   echo $email;
?>

Inserting Into The Database

To perform an insert, we can use the insert method:

$wpdb->insert( $table, $data, $format);

This method takes three arguments. The first specifies the name of the table into which you are inserting the data. The second argument is an array that contains the columns and their respective values, as key-value pairs. The third parameter specifies the data type of your values, in the order you have given them. Here’s an example:

<?php
   $wpdb->insert($wpdb->usermeta, array("user_id" => 1, "meta_key" => "awesome_factor", "meta_value" => 10), array("%d", %s", "%d"));

   // Equivalent to:
   // INSERT INTO wp_usermeta (user_id, meta_key, meta_value) VALUES (1, "awesome_factor", 10);
?>

If you’re used to writing out your inserts, this may seem unwieldy at first, but it actually gives you a lot of flexibility because it uses arrays as inputs.

Specifying the format is optional; all values are treated as strings by default, but including this in the method is a good practice. The three values you can use are %s for strings, %d for decimal numbers and %f for floats.

Updating Your Data

By now, you won’t be surprised to hear that we also have a helper method to update our data — shockingly, called update(). Its use resembles what we saw above; but to handle the where clause of our update, it needs two extra parameters.

$wpdb->update( $table, $data, $where, $format = null, $where_format = null );

The $table, $data and $format parameters should be familiar to you; they are the same as before. Using the $where parameter, we can specify the conditions of the update. It should be an array in the form of column-value pairs. If you specify multiple parameters, then they will be joined with AND logic. The $where_format is just like $format: it specifies the format of the values in the $where parameter.

$wpdb->update( $wpdb->posts, array("post_title" => "Modified Post Title"), array("ID" => 5), array("%s"), array("%d") );

Other Queries

While the helpers above are great, sometimes performing different or more complex queries than the helpers allow is necessary. If you need to perform an update with a complex where clause containing multiple AND/OR logic, then you won’t be able to use the update() method. If you wanted to do something like delete a row or set a connection character set, then you would need to use the “general” query() method, which let’s you perform any sort of query.

$wpdb->query("DELETE FROM wp_usermeta WHERE meta_key = 'first_login' OR meta_key = 'security_key' ");

Protection And Validation

I hope I don’t have to tell you how important it is to make sure that your data is safe and that your database can’t be tampered with! Data validation is a bit beyond the scope of this article, but do take a look at what the WordPress Codex has to say about “Data Validation3” at some point.

In addition to validating, you will need to escape all queries. Even if you are not familiar with SQL injection4 attacks, still use this method and then read up on it later, because it is critical.

The good news is that if you use any of the helper functions, then you don’t need to do anything: the query is escaped for you. If you use the query() method, however, you will need to escape manually, using the prepare() method.

$sql = $wpdb->prepare( 'query' [, value_parameter, value_parameter ... ] );

To make this a bit more digestible, let’s rewrite this basic format a bit.

$sql = $wpdb->prepare( "INSERT INTO $wpdb->postmeta (post_id, meta_key, meta_value ) VALUES ( %d, %s, %d )", 3342, 'post_views', 2290 )
$wpdb->query($sql);

As you can see, this is not that scary. Instead of adding the actual values where you usually would, you enter the type of data, and then you add the actual data as subsequent parameters.

Class Variables And Other Methods

Apart from these excellent methods, there are quite a few other functions and variables to make your life easier. I’ll show you some of the most common ones, but please do look at the WordPress Codex page linked to above for a full list of everything that $wpdb has to offer.

insert_id()

Whenever you insert something into a table, you will very likely have an auto-incrementing ID in there. To find the value of the most recent insert performed by your script, you can use $wpdb->insert_id.

$sql = $wpdb->prepare( "INSERT INTO $wpdb->postmeta (post_id, meta_key, meta_value ) VALUES ( %d, %s, %d )", 3342, 'post_views', 2290 )
   $wpdb->query($sql);

   $meta_id = $wpdb->insert_id;

num_rows()

If you’ve performed a query in your script, then this variable will return the number of results of your last query. This is great for post counts, comment counts and so on.

Table Names

All the core table names are stored in variables whose names are exactly the same as their core table equivalent. The name of your posts table (probably wp_posts) would be stored in the $posts variable, so you could output it by using $wpdb->posts.

We use this because we are allowed to choose a prefix for our WordPress tables. While most people use the default wp, some users want or need a custom one. For the sake of flexibility, this prefix is not hardcoded, so if you are writing a plug-in and use wp_postmeta in a query instead of $wpdb->postmeta, your code will not work on some websites.

If you want to get data from a non-core WordPress table, no special variable will be available for it. In this case, you can just write the table’s name as usual.

Error Handling

By calling the show_errors() or hide_errors() methods, you can turn error-reporting on or off (it’s off by default) to get some more info about what’s going on. Either way, you can also use the print_error() method to print the errors for the latest query.

$wpdb->show_errors();
   $wpdb->query("DELETE FROM wp_posts WHERE post_id = 554 ");

   // When run, because show_errors() is set, the error message will tell you that the "post_id" field is an unknown
   // field in this table (since the correct field is ID)

Building Some Basic Tracking With Our New $wpdb Knowledge

If you’re new to all of this, you probably get what I’m going on about but may be finding it hard to implement. So, let’s take the example of a simple WordPress tracking plug-in that I made for a website.

For simplicity’s sake, I won’t describe every detail of the plug-in. I’ll just show the database’s structure and some queries.

Our Table’s Structure

To keep track of ad clicks and impressions, I created a table; let’s call it “tracking.” This table records user actions in real time. Each impression and click is recorded in its own row in the following structure:

  • ID
    The auto-incremented ID.
  • time
    The date and time that the action occurred.
  • deal_id
    The ID of the deal that is connected to the action (i.e. the ad that was clicked or viewed).
  • action
    The type of action (i.e. click or impression).
  • action_url
    The page on which the action was initiated.
  • user_id
    If the user is logged in, their ID.
  • user_ip
    The IP of the user, used to weed out any naughty business.

This table will get pretty big pretty fast, so it is aggregated into daily statistics and flushed periodically. But let’s just work with this one table for now.

Inserting Data Into Our Tables

When a user clicks an ad, it is detected, and the information that we need is sent to our script in the form of a $_POST array, with the following data:

Array
(
   [deal_id] => 643
   [action] => click
   [action_url] => http://thisiswhereitwasclicked.com/about/
   [user_id] => 223
   [user_ip] = 123.234.223.12
)

We can then insert this data into the database using our helper method, like so:

$wpdb->insert('tracking', array("deal_id" => 643, "action" => "click", "action_url" => "http://thisiswhereitwasclicked.com/about/",
"user_id" => 223, "user_ip" => "123.234.223.12"), array("%d", %s", "%s", "%d", "%s"));

At the risk of going on a tangent, I’ll address some questions you might have about this particular example. You may be thinking, what about data validation? The click could have come from a website administrator, or a user could have clicked twice by mistake, or a bunch of other things might have happened.

We decided that because we don’t need real-time stats (daily stats is enough), there is no point to check the data at every insert. Data is aggregated into a new table every day around midnight, a low traffic time. Before aggregating the data, we take care to clean it up, taking out duplicates and so on. The data is, of course, escaped before being inserted into the table, because we are using a helper function; so, we are safe there.

Just deleting in bulk all at once the ones that are made by administrators is easier than checking at every insert. This takes a considerable amount of processing off our server’s shoulders.

Deleting Actions From a Blacklisted IP

If we find that the IP address 168.211.23.43 is being naughty-naughty, we could blacklist it. In this case, when we aggregate the daily data, we would need to delete all of the entries by this IP.

$sql = $wpdb->prepare("DELETE FROM tracking WHERE user_ip = %s ", '168.211.23.43');
   $wpdb->query($sql);

You have probably noticed that I am still escaping the data, even though the IP was received from a secure source. I would suggest escaping your data no matter what. First of all, proper hackers are good at what they do, because they are excellent programmers and can outsmart you in ways that you wouldn’t think of. Also, I personally have done more to hurt my own websites than hackers have, so I do these things as a safety precaution against myself as well.

Updating Totals

We store our ads as custom post types; and to make statistical reporting easier, we store the total amount of clicks that an ad receives separately as well. We could just add up all of the clicks in our tracking database for the given deal as well, so let’s look at that first.

$total = $wpdb->get_var("SELECT COUNT(ID) WHERE deal_id = 125 ");

Because getting a single variable is easier than always burdening ourselves with a more complex query, whenever we aggregate our data, we would store the current total separately. Our ads are stored as posts with a custom post type, so a logical place to store this total is in the postmeta table. Let’s use the total_clicks meta key to store this data.

$wpdb->update( $wpdb->postmeta, array("meta_value" => $total), array("ID" => 125), array("%d"), array("%d") );

   // note that this should be done with update_post_meta(), I just did it the way I did for example's sake

Final Thoughts And Tips

I hope you have gained a better understanding of the WordPress $wpdb class and that you will be able to use it to make your projects better. To wrap up, here are some final tips and tricks for using this class effectively.

  • I urge you to be cautious: with great power comes great responsibility. Make sure to escape your data and to validate it, because improper use of this class is probably a leading cause of hacked websites!
  • Ask only for the data that you need. If you will only be displaying an article’s title, there is no need to retrieve all of the data from each row. In this case, just ask for the title and the ID: SELECT title, ID FROM wp_posts ORDER BY post_date DESC LIMIT 0,5.
  • While you can use the query() method for any query, using the helper methods (insert, update, get_row, etc.) is better if possible. They are more modular and safer, because they escape your data automatically.
  • Take care when deleting records from a WordPress (or any other) database. When WordPress deletes a comment, a bunch of other actions also take place: the comment count in the wp_posts table needs to be reduced by one, all of the data in the comment_meta table needs to be deleted as well, and so on. Make sure to clean up properly after yourself, especially when deleting things.
  • Look at all of the class variables5 and other bits of information in the official documentation. These will help you use the class to its full potential. I also recommend looking at the ezSQL6 class for general use in your non-WordPress projects; I use it almost exclusively for everything I do.

Further Reading

(al)

Footnotes

  1. 1 http://justinvincent.com/ezsql
  2. 2 http://www.smashingmagazine.com/wp-content/uploads/2011/09/wpdboverview.jpg
  3. 3 http://codex.wordpress.org/Data_Validation
  4. 4 http://en.wikipedia.org/wiki/SQL_injection
  5. 5 http://codex.wordpress.org/Class_Reference/wpdb#Class_Variables
  6. 6 http://justinvincent.com/ezsql
  7. 7 http://codex.wordpress.org/Database_Description
  8. 8 http://codex.wordpress.org/Class_Reference/wpdb
  9. 9 http://codex.wordpress.org/Data_Validation
  10. 10 http://en.wikipedia.org/wiki/SQL_injection
  11. 11 http://www.unixwiz.net/techtips/sql-injection.html
  12. 12 http://jvmultimedia.com/docs/ezsql/ez_sql_help.htm

↑ Back to topShare on Twitter

Hallo, my name is Daniel :) I build plugins, themes and apps - then proceed to write or talk about them. I'm the editor for the WordPress section here on Smashing Magazine and I contribute to various other online sites. When not coding or writing you'll find me playing board games or running with my dog. Drop me a line on Twitter or visit my personal website.

Advertising

Note: Our rating-system has caused errors, so it's disabled at the moment. It will be back the moment the problem has been resolved. We're very sorry. Happy Holidays!

  1. 1

    Konstantin Kovshenin

    September 21, 2011 10:48 pm

    Great overview, thank you Daniel! I think you should mention that $wpdb has to be declared global before it can be used.

    • 2

      Hi Konstantin!

      Yes, thanks for pointing that out, this is probably not self explanatory. I would add that you only need to declare it in functions, where you would normally declare any outside variables. You can use $wpdb without declaring it in your theme’s files though.

  2. 3

    This is an in-depth guide on $wpdb. Thank you for compiling it..

  3. 4

    Good article, but i don’t like your teaser image. The $wpdb is NOT clean or modularized. Its a very bad practice to use global vars such as the $wpdb object. Furthermore, the $wpdb is just another database wrapper, which is not better than php-native PDO (PHP Data Objects). Recently i took a look at the source code from the wbdp object and its horrible programmed. Old and deprecated functions like mysql_* were used.

    • 5

      Hi Daniel,

      Thanks for the critique, I actually agree with most of what you said. In fact, there is no real benefit to using ANY database layer, it just ads another layer of complexity. However, this article was not intended for those who are familiar with databases.

      In addition, $wpdb is WAY better than a lot of things out there in plugins. Since it is, called and used in WP by default it is the way you should go when using WP since you will just be duplicating code or opening a new connection to the database if you don’t.

      In general use however you are right, if you are not using a framework, or using your own, there are much better ways.

    • 6

      Sigh. $wpdb is the best for one reason and one reason alone; because it is the standard database access layer in WordPress core! So besides that Mrs. Lincoln, how was the play? (Sorry if that reference is lost on non-US citizens.)

  4. 7

    You must be insane if you are saying that there is anything great about ezSQL. It’s old and totally outlived concept.
    Sorry, I’ve had to post this. Thanks for the article, tho.

  5. 8

    i think wordpress provides awesome platform for the new bloggers……

  6. 9

    well hell, way to come out swinging! Excellent start to the new WP section here! I can’t comment as to the technical aspect of the article, I know little about this stuff. However, the writing is fantastic. Very easy to read and understand. Thanks, and I’m looking forward to seeing where the WP section leads. This article definitely shows the potential!

  7. 10

    Thank you for this Daniel.

  8. 11

    Fantastic article. This is why I’m excited to see the new WP-specific Smashing section.

  9. 12

    Thanks for the article!
    I’ve been building sites with WP for a long time now but still find that this stuff goes way over my head.
    Can you recommend any articles or sites that bridge the gap between the PHP/MySQL novice to the kind of stuff you’re doing here?
    It would be greatly appreciated.

    Thanks again! Looking forward to better understanding these concepts..

    • 13

      Hi Maren,

      I’m sure there are some things out there, however this seems to be one area where you need to make most of the jump yourself. If you know PHP and know SQL it shouldn’t be that difficult, but I recall being plenty confused at times back when I was jumping in :)

      This is an area where trial and error is particularly helpful. I would suggest just copy pasting my first example and then tweaking it a bit. Copy paste the first example, and be sure to print the result. Once done, just make one small change in it and see what happens.

      If you’re not sure how and why $wpdb->get_row() works, that’s fine as long as you can use it. Once you are comfortable with it, you will understand more and more, and you’ll see how it works eventually much more clearly.

      I bought a book for someone I was teaching once, please take a look at the link below. I can’t remember, but I seem to recall this being a very down to earth book. It eases you in pretty well.
      http://headfirstlabs.com/books/hfphp/

      Also, if you know SQL you can do this. Decide on the query and enter it into the SQL tab in phpMyAdmin or something similar. Once you’ve tested it there and it works, all you need to do is figure out the data it gives you. Is it just one row (user get_row)? Is it a column (get_col), is it s single value (get_var) or multiple rows and values (get_results). After that its a simple matter of: $wpdb->get_row(“SQL”).

  10. 14

    While this is a really well-written and in-depth reprise of the existing documentation on $wpdb in the Codex (http://codex.wordpress.org/Class_Reference/wpdb), presenting this as one of your earliest WP articles is probably not the most responsible thing to do.

    Many programmers, coming at WordPress from an existing background, which most likely already includes database design and development will naturally gravitate to $wpdb as the answer to all their query needs. The example you provide using a custom table is an excellent use case for leveraging the wpdb class, but most fledgling WordPress enthusiasts will (ab)use it for all manner of pedestrian tasks like subqueries and sub loops, updating metadata, or extracting odd bits and pieces like author information, thumbnail images, etc.

    While it’s quite emancipating to dig into the WordPress database structure (http://codex.wordpress.org/Database_Description), doing so early often leads to more harm than good.

    The simple reason: WordPress provides way more powerful ways to access and query the database than the (relatively, at least from a WP perspective) low-level SQL queries applied through this class.

    Unless you’re doing extremely complex joins (and even then!) where performance is a serious issue (10,000+ posts/pages) you’re doing yourself a grave disservice by skipping over all the added intelligence and optimization found in the higher-level functions.

    Consider WP_Query and its derivatives (query_posts, get_posts). This is a truly powerful class that builds generally optimized mySQL queries based on the ever-changing (to a degree) topology of the core WordPress database structure. Using these high-level classes provides the following benefits:
    – it’s easier to use (for most people)
    – it takes advantage of built-in cacheing wherever possible
    – queries are generally optimized (note that for complex custom joins you may be able to eke out better performance by doing your own joins, but the instances are rarer than you might think)
    – there’s additional intelligence, sanitization and texturizing that may occur, which is most often desirable
    – your theme, plugin or widget will be much more future-proof (the database layer may change, but the APIs that access that layer won’t, so your code won’t break if the underlying structure changes)

    My recommendation for your readers is to bookmark this excellent post, but then to return and do their homework on the Codex, in particular, the WP_Query class, and take advantage of all the wholesome goodness that the core team has built into WordPress, before rolling up their sleeves and mucking about directly in mySQL.

    • 15

      Hi Tom,

      These are some excellent points, maybe I should have stressed this more, rather than just mentioning it in passing. The reason it gets a bit confusing is that I used easy examples which can (an in most cases should) be accomplished using built in functions such as get_posts and whatnot. So overall, from a beginner-intermediate point of view, I completely agree with you. $wpdb is a tool, but as all tools, you need to know when to use it, and more importantly when not to use it. However, I think this can only be learned through trial and error and failure. You can’t really introduce a concept like this at the “right” time. Since its new and awesome, most people will go in and use it for everything, which is fine, we live and learn, this is how we develop :)

      From a more advanced point of view, I somewhat disagree. WP_Query is very inefficient. Not in a “this is badly coded” sort of way, but in many cases when all I need is an ID and a title to display a set of articles, there’s no need to use WP_Query, all it does is add a layer of complexity (which would be ok), but also a layer of added required processing (which is less ok). I LOVE the power WP_Query ads to WordPress, but sometimes when you want to shave that extra bit off, it is helpful to not use it.

      I suggest checking out ‘define(‘SAVEQUERIES’, true);’ in the wp_config.php file for awesome SQL debugging and optimization.

  11. 16

    Make sure to wpdb->escape() if you don’t use prepare!

    That is one of the ways to protect from mySQL injection. If you do not understand how to properly protect your code from mysql injection and you are using wordpress, please only use wpdb->prepare for your queries.

    • 17

      Good comments, but also make sure to NOT escape if you are using the update or insert methods as this is done for you automatically

  12. 18

    Fun article. Typo in first code example: you do not use $wpdb->posts, but you state you do.

    • 19

      Yup. I was wondering about that too.

      “You might have noticed that, instead of using wp_posts for the table’s name, I have used $wpdb->posts, which is a helper to access your core WordPress tables. More on why to use these later.”

      I’m glad I’m not imagining things.

    • 20

      Thanks for that seth, I ‘ve changed it, sorry for the confusion mary :)

  13. 21

    Great post .. Its what have been
    Looking for ..

  14. 22

    likely your things encircling it WordPress Essentials: Interacting Attending The WordPress Database – Smashing WordPress

  15. 23

    This should be helpful while I learn how to use wordpress as a CMS

  16. 24

    This may be a redundant question, but here goes…
    Traditionally, the basics cover C.R.U.D. (as I’m used to LINQ) Which you did very well.
    I’m clear on create, read, and update, but not delete entirely.
    For instance, can you use the line you expressed with “error handling” to do this?

    Ie. $wpdb->query(“DELETE FROM wp_posts WHERE post_id = 554 “);
    (or something similar)

    ps. Great tutorial though! Thank you.

    • 25

      I’m not quite sure I understand the question, sorry just woke up :) Let me quickly explain this, but it might turn out to be not what you are asking so do stop me if I’m rambling.

      In short, when $wpdb->show_errors is set, it will spit out errors for any queries you make. Since there is an error in that query you will get an “unknown column” error. The id field in the wp_posts table is named “ID” not “post_id”.

      You need to be careful with deleting things though. For example if you wanted to delete a comment you would do something like:

      $wpdb->query(“DELETE FROM wp_comments WHERE comment_id = 124″);

      This is fine, but when you delete a comment through the actual WordPress admin this is not the only thing that happens. At the very least, the comment_count field is reduced by one, which does NOT happen if you delete a comment using and SQL query of course.

      Similarly, if you want to delete a user you would not only need to delete the record from the wp_users table, but also every record for that user from the wp_usermeta table, his posts from the wp_posts table, his comments from the wp_comments table, etc.

  17. 26

    I think that the idea of having a nother database layer are more tempting. The codeigniter way of storing objects are a better practice(i think). Maybe there are some additional functionality for wordpress that i dont know of?

  18. 27

    i would like to see some examples for multisite. how do you query from one blog when the database is shared?

  19. 28

    I’m not a developer nor have I written code of any kind…nor do I want to write code of any kind…except I’m resigned to having to grasp and learn how to write a modicum of certain kinds of code. Hey, I’m 69, a pretty old dog and I only have so many new tricks in me…and for all I know, I may not have any new tricks left in me. Guess I’ll find out.

    My website software (Freeway Pro 5.5) is not up to a voluntary project I’m now involved in to create a site for the Vietnam Veterans of Oregon Memorial in Portland. I’ve been steered to WordPress (2011 theme) and to Thesis…but my most crucial priority is to be able to build a database of the 803 Vets who were killed in combat or went missing. I was a Marine platoon leader back in 1966-67, got wounded twice, and have a PTSD rating so this project has a lot of meaning to me and I want to do things right.

    What I’m hoping is that someone here can advise me whether I myself might be able to create such a database—ie, searchable by first letter of last name…by last name…by hometown in Oregon…by branch of the service…by year killed or gone missing, etc. I just don’t want to waste my time, your time, or the time of my fellow vets.

    • 29

      This calls for some type of style to be called to make it look beter. Can you post that info too? I added this to my site but it looks like poop.

  20. 30

    Hello everyone, please tell how can i access database(tables) of wordpress through android application please share any code example if any body have …

  21. 31

    i get

    array (size=5)
    0 =>
    object(stdClass)[310]
    public ‘GROUP_CONCAT( value
    SEPARATOR ‘ ‘ )’ => string ‘lasitha gamage’ (length=14)
    1 =>
    object(stdClass)[309]
    public ‘GROUP_CONCAT( value
    SEPARATOR ‘ ‘ )’ => string ‘lasitha gamage’ (length=14)
    2 =>
    object(stdClass)[308]
    public ‘GROUP_CONCAT( value
    SEPARATOR ‘ ‘ )’ => string ‘Kumara hasitha’ (length=14)
    3 =>
    object(stdClass)[307]
    public ‘GROUP_CONCAT( value
    SEPARATOR ‘ ‘ )’ => string ‘lasitha gamage’ (length=14)
    4 =>
    object(stdClass)[304]
    public ‘GROUP_CONCAT( value
    SEPARATOR ‘ ‘ )’ => string ‘Kumara hasitha’ (length=14)

    as out of a sql query and i want to you know show it in nice looking in a wp plugin admin page ,iam new to wordpress ..,how i do i..,i use normal php array print method but it did’t work for me …,please help me

  22. 32

    Hi Daniel, congrats fot great post. Man, see my consult

    $meta = $wpdb->get_results(“SELECT * FROM wp_postmeta WHERE meta_key = ‘imovelCidade’ “);

    echo what?
    echo $meta->postmeta?
    echo $meta?
    echo $meta->meta_key?
    echo $meta_value?
    ?>

    I dont know get the results :(

    I need get all results meta_key imovelCidade

  23. 33

    How I can all meta values of a meta key from the current category page?

  24. 34

    Let’s not forget, before anything:
    global $wpdb;

    I was stumped for about 5 mins…

  25. 35

    Hi,
    You didn’t say where I enter the code?
    do I create a new post, new page or what? Or am I creating a file?
    if I create a file then how can I run it?
    thanks

  26. 36

    Try using the prepare() function wrapper if you want more security when making calls to the database.

    For example:

    global $wpdb;

    $thepost = $wpdb->get_row( $wpdb->prepare( “SELECT * FROM $wpdb->posts WHERE ID = 1″) );

    echo $thepost->post_title;

  27. 37

    Thanks for the tutorial! It really helped me understand better how i can manage wordpress database with the correct tool WordPress provides!
    I noticed some errors, though. Just in case you can check them:

    1) Getting Started – Third paragraph: states “The $results object…” and it should be “The $posts object”.

    2) Also, after “AND post_type=’post’ ORDER BY comment_count DESC LIMIT 0,4″)” there should be a semi-colon (;). The same for all other code blocks within “Retrieving Results From The Database”

  28. 38

    Hi,

    Thanks much for the article!

    Just wanted to mention that there is a typo in the code under the Inserting Into The Database section: The 2nd format indicator is missing a leading quotation mark.

    $wpdb->insert($wpdb->usermeta, array("user_id" => 1, "meta_key" => "awesome_factor", "meta_value" => 10), array("%d", %s", "%d"));

  29. 39

    Thanks so much for this! I am a complete noob when it comes to wordpress and databases and this really helped. But I would add that you might want to let people know to use global $wpdb because my code wasn’t working until I did that, and because I’m an idiot it took me awhile to figure it all out.

↑ Back to top