Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
If you've created your own table in WordPress, you can insert and update data from it using the same wp_db class we used to get data out. We'll look at how to use the functions to do that, and I also want to discuss how to protect your application from SQL injection attacks. A quick note: just because you can insert an update directly to the WordPress database, it's a better practice to use the functions that are there to do this. That way if the table names ever change in future versions, your code won't break. So this is our plugin that we've created to detect browsers.
Currently, we have a database that's been created. Now we can use this plugin functionality to actually go in and update the database whenever a new user comes to the web site. So let's create a statement that's going to insert data into our database table. So the first step is we are going to create a function, and this is really going to be the hook that's going to be called. We are going to set it to the wp_footer hook. That action is used when the footer is generated, so it's a good time to essentially insert into our database that a new user has come to the web site. So I am going to call the function bdetector_insert_useragent.
Again, I am going to use the wpdb class to access my data. So I am going to declare it in the global scope. So, now I have access to it. I am going to create my table name and set it equal to wpdb->prefix, and I am going to add on bdetector. The next step is to use the insert function of the wpdb class to insert the data into the database. This is what it looks like: wpdb insert.
You pass the table_name that you want to insert into, and then you create an array, and in that array are going to be all the fields that you want to enter and their values. So it's going to be an associative array. So it looks like this, array. We are going to enter the user_ agent, and I didn't just make that up; this is this field here that I created earlier in my database. I am going to set the value to the $_SERVER string--this is a global PHP variable--and I'm going to get the HTTP_USER_AGENT.
There are number of things in that server scope, and most of these are obtained from browser header. So this should then insert that data into the database. The last thing I need to do is simply register this in a wp_footer action. So I'll call the add_action function, pass wp_footer, and I am going to call bdetector_insert_useragent. The next time I visit this site, this data should get inserted into the table.
So let's go ahead and visit our site and see what happens. So, I am going to open our site from the WordPress admin by clicking on the title. That should have loaded it. You can see it loaded. So hopefully it should be entered into the database. I can go in to my MAMP screen. This is my table from my database. If I click on the Browse tab, it should make a selection and get data out, and you can see indeed it did. It gave it an id of 1, it inserted the hit_date because I told it to default to the current date and time when the data was entered, and it input my user_agent.
So you can see I am using this Mozilla /5.0, which is Firefox browser, and it tells me my operating system, and there's a bunch of other information we can glean from this at a later date when we build the reporting tools. One other note: when you do insert like this, you can always access the data that was inserted by referring to the insert_id property of wpdb. So if you need to get that for any reason, you can access it using this methodology.
One other thing to go over quickly is in order to secure things, you want to make sure that you specify the types that come in. In this example, we're getting something from a server scope, but if we are getting something that's user entered, it's possible that they can do what's called the SQL injection attack. What this will do is they essentially append SQL on here that can do deleterious things to your database. It's not something to be looking forward to, and the way you can get around this is this insert method actually allows you to pass what types of data you're entering as variables.
So that way anything that comes it will verify that the date you entered is indeed the right type. So what you do is you add another argument after this and you specify for the array what the different data types are, and they're always going to start with the percent sign, and then you can do S for a string, D for decimal, and F for a Float, so I would add an array. Since I am just inserting the single string, I am just going to verify that this is a string. That will enforce this data type and make sure that no one entered any SQL into here.
So if I were to run it again, it would work the same way, and you'd see it enters another bit of data, but in that instance it verified that it was that type of data. There is also a prepare method that you can use if you're doing a select statement. It's not necessary to insert an update because they're safe; however, if you're running a select query with dynamic variables, it's good to use the prepare statement. It works in basically the same way, only you write your SQL and afterwards, you append an array of the values you need.
There is further information if you look in the function reference for wpdb. You should be able to find the information about that function. So, the WordPress wpdb has insert and update methods that essentially allow us to enter data into the database safely and easily. We can also use the prepare method to clean other dynamic SQL statements. The update method works basically the same way. The difference is there's a third argument to the update method that will take the where clause information-- essentially the ID and value that you want to use to update--and then your data types will be in the fourth argument.
We'll look at the prepare statement, and we'll talk more about SQL injection hacks when we get into the Security video later on. So the WordPress database class comes with some special functions--insert and update--to edit the data in your database. This can be extremely helpful, and it can also keep you secure from SQL injection hacks.
Get unlimited access to all courses for just $25/month.Become a member