Continuing on with our mini-series on the WordPress Database class, today we’ll be talking about using the class to insert and update data in the database. The WPDB class makes this super simple, no SQL queries required.

Inserting Data

Using the WPDB class, you can insert an array of data into a table in the database. The syntax of this function is simple:

[code='php']$wpdb->insert( $table, $data );[/code]

$table is the name of the table you want to insert the data into and $data is an array of field names and data you want added to $table. For the data array, the field name should be the key, while the value should be the data for that field name. So, if we (for example purposes) wanted to use this function to add an option to the options table, we we do so like this:

[code='php']$data_array = array('option_name' => 'example_option',
'option_value' => 'This is an example option!');
$wpdb->insert( $wpdb->prefix . 'options', $data_array );[/code]

Which would add a new row in the options table, with an option_name of example_option and an option_value of This is an example option!. You only need to specify the field names that you would like data added to. $data should not be escaped first. Obviously, we could accomplish the same result much easier using add_option, but this is simply for example purposes.

Notice the $wpdb->prefix part. Because it is possible to set WordPress up with a prefix different from wp_, it is good programming practice to always use $wpdb->prefix for the prefix in table names.

Updating Data

If you want to update an existing row in the database, we can use a WPDB function for this. This function is almost the same as insert, but it also allows you to specify the WHERE clause. Thus, the syntax is:

[code='php']$wpdb->update( $table, $data, $where );[/code]

The $table and $data values are the same as in the insert function. $where is an array where the keys are column names and the values are what their values should be; therefore creating the WHERE clause. So, if we wanted to update our example option from above, we could do this:

[code='php']$data_array = array('option_value' => 'This is a *new* example option!');
$where_array = array('option_name' => 'example_option');
$wpdb->update( $wpdb->prefix . 'options', $data_array, $where_array );[/code]

Which would update any row whose option_name is example_option, in the options table, with the new value for option_value. Again, $data should not be escaped before hand.

To conclude

This concludes our mini-series on the WordPress Database class. If you have any questions, feel free to ask them in the comments.

For this week’s episode of Everything PHP, we are going to be talking about the final looping method in PHP. For loops are the most complex loop available in PHP. Being the most complex loop in PHP, they are also the most powerful. As with other loops in PHP, you can nest loops within other loops.

A for loop can accept multiple expressions to be evaluated. The basic for loop has three expressions, only one of which determines if the loop will be executed or not. The other expressions are for executing expressions whenever the loop is run (more on this in a bit). Here’s one of the most commonly used examples for the for loop:

[code='php']for ($i = 0; 10 > $i; $i++) {
echo $i;
}[/code]

Which results in the output of:

0123456789

The for loop is broken up into three groups of expressions, which are separated by semi-colons. The first group will only be run once, at the first iteration of the loop. The second group determines whether the loop will proceed or not. If the expression results to true, the loop will be executed, if the result is false, the loop will stop. The third group will be executed at the end of each loop iteration (after the code inside the loop has been executed).

The first expression group is used for setting variables that you will use within the second expression group. The second group is an expression which should result in either true or false (like something you would use in an if). You can use variables and functions (a note on using functions here later in the post) within this group. The third group is usually used for incrementing a variable used in the second group. Is it possible to leave any (or all) of the expression groups blank. If you leave the second expression blank, the loop will run indefinitely (which isn’t useless, as you may initially assume).

You can specify multiple expressions to be executed within any expression group. Simply separate the expression with commas. In the case of the second group, multiple expressions can still be used, but only the last expression in the list will be used to determine whether the loop will run or not.

When using functions in the second group, if you do not need the function to be run on each iteration of the loop, you should put the functions inside a variable in the first group. Otherwise, your loop will run slowly, because that function will be executed each time the loop iterates again. Take the example below, where we’re using count() on an array:

[code='php']$array = array('a', 'b', 'c');
for ($i = 0; $i <= count($array); $i++) {
echo $i;
}[/code]

Because the count of $array is not going to change, we do not need to calculate it with every iteration of the loop. Therefore, we should add in to a variable within the first group, which will only be run once.

[code='php']$array = array('a', 'b', 'c');
for ($i = 0, $array_count = count($array); $i <= $array_count; $i++) {
echo $i;
}[/code]

This will result in a faster loop.

Continue, Break and Infinite Loops

Remember how leaving the second group of expressions empty would result in an indefinite loop? Well, there actually are uses for this, because we can end the looping in a different way. We can use the break control structure to break out of the current loop (stop it from iterating again). Let's look at an example:

[code='php']for ($i = 0; ; $i++) {
if ($i == 26) {
break;
}
echo $i;
}[/code]

In this example, when $i reaches 26, the loop will be broken, and will stop iterating. If you have nested loops, you can specify the number of loops it should break out of (such as break 2;). break is different from exit in that the script will not end, just the current loop.

If is also possible to skip to the end of the current iteration of a loop, and continue on with the expression. We do this by using the continue control structure. It is used in the same way as break, and also accepts the number of loops it should skip to the end of.

break;

;) That concludes our article on for loops. Next week we'll be going over switch control structures.

Okay, so I have one more post on CSS. This week we’ll be going over validating and compressing your CSS.

Validation

CSS that you write should be valid. Valid meaning that it should be free from any syntax errors or the use of deprecated properties. Syntax errors will sometimes be the cause of something getting screwed up when rendered in the browser, or why something isn’t working as expected. Syntax errors are usually caused by a simple typing error, and can often be hard to spot. I’ve been known to spend large amounts of time staring at code for a reason why it isn’t working as expected. Sometimes it’ll end up being something very simple… ;)

Luckily, there’s a tool available to help you spot and correct those evasive little syntax errors. The W3C CSS Validator Service will allow you to easily validate your CSS. You can either provide it with the URL to the stylesheet, upload the stylesheet file or directly enter the CSS into a text box.

Using the W3C Markup Validator Service we can also validate our XHTML. It works in the same way as the CSS validator in that it checks for invalid syntax and deprecated tags.

Compression

The format people choose to write CSS in usually results in unnecessarily large file sizes. To ensure users of your website download the files as quickly as possible (and save you some bandwidth), you should compress your CSS files. Compression of stylesheets usually involves converting it to short form and removing spaces and line-breaks. This will result in a document that is almost impossible to read, but has a substantially smaller file size. Doing this compression manually would be too time consuming (not to mention incredibly boring), so, luckily, there’s a tool available to do this for you.

Clean CSS allows us to enter in CSS, choose your preferred compression options, and then have it do all the work for us. I recommend choosing the highest level of compression. What you can do, to avoid having a hard time editing your compressed stylesheet afterwards, is to create a copy of the uncompressed stylesheet first. By doing this, you can make any changes easily to the uncompressed version and then you can run it through the compressor, and then serve the compressed file to your site’s viewers.

(Yes, you could argue that this isn’t compression in the technical sense, but it is close.)

In closing

Validating your XHTML and CSS can help you solve strange rendering problems quickly and easily. Compressing your CSS will allow users to load your site faster and save you some bandwidth along the way. Keep both these tools in your toolbox, as they will be useful quite often.

Domain Name Systems (DNS) is a vital part of a web server, allowing the use of domain names to access the web server. If you want to learn of the technical details of DNS, you can read about it on Wikipedia. We will be installing and using the BIND DNS Server and Webmin to create our DNS records.

Setting Up Custom Name Servers

First of all, you should collect some information. You’ll need a domain to use for name servers (you don’t need a specific domain for this, a domain you already use will work), you’ll need to know your server’s primary IP address and one of the secondary IP addresses.

After you’ve collected that information, you’ll need to go to the registrar of your domain to register custom nameservers. The process of doing this greatly varies from registrar to registrar, some have it available in their online control panel, and others require you to email them to do it. Look at your registrar’s documentation or contact them if you’re unsure how to do this. Your primary name server should be ns1.yourdomain.com, primary name server IP should be your main server IP, secondary name server should be ns2.yourdomain.com and secondary name server IP should be one of your secondary IP addresses (replace yourdomain.com with the domain you’re going to use for your name servers – it doesn’t have to be a .com).

Install BIND

After you’ve setup your custom name servers with your domain registrar, you’re ready to install the BIND DNS Server on your server. SSH into your server and run the following command to install BIND (it may already be installed):

yum install bind

Setting Up DNS Records

Now, login to your Webmin panel. Expand the “Servers” link on the sidebar. Click on BIND DNS Server, if it isn’t there find it under “Un-used Modules” and enable it.

Now, under Existing DNS Zones, click on Create master zone. We’re going to setup a DNS record for our main domain and name servers. Replace yourdomain.com with the domain you used. Enter the following information into the textboxes:

1. Domain name: yourdomain.com
2. Master server: ns1.yourdoman.com
3. Ensure Add NS record for master server? is checked
4. Email address: Enter a “webmaster” email address here

Now, click the Create button. Click on the Name Server icon. ns1.yourdomain.com. should have already been created, so type ns2.yourdomain.com. (including the period at the end) into the Name server textbox and click Create.

Click Return to record types at the bottom of the page. Then click on the Address icon. Make the following records:

• Name: ns1 Address: Your primary IP address. Click Create
• Name: ns2 Address: The secondary IP address you used for your custom name servers. Click Create
• Name: www Address: Your primary IP address. Click Create
• Name: leave blank Address: Your primary IP address. Click Create

Click Return to record types at the bottom of the page. Then click on the Check Records button at the bottom of the screen. If no errors are reported, click on Apply Zone, then on Apply Configuration at the top-right of the screen.

You have now created custom name servers, installed BIND and added a DNS record for your name servers and domain. You can now start to point your other domains to your name servers. For each domain you host on your server, you will have to add a DNS record for it. Just follow the instruction above, however leave out the ns1 and ns2 records under Address. Don’t forget to add these domains to your Apache Virtual Host configuration.

Next week

At this point in the series, you should now have a fully-functional web server. Next week we’ll be securing our server by installing a software firewall, changing the SSH port, etc. So, you should (try to) refrain from using your server for hosting sites until then.

Last week we began covering the WPDB class available in WordPress. This week we will continue along that topic. WordPress provides multiple functions for doing SELECT queries on the database.

Generic Select

With the WPDB class, there are functions available to select specific variables, rows or columns, but there is also a “generic” SELECT. get_results() allows you to perform a SELECT query on the database, and have the results returned to you in an object, associative array or numerical array. This syntax of get_results is simple:

[code='php']$wpdb->get_results('query', output_type);[/code]

Where query is your SELECT query and output_type is either:

• OBJECT – result will be an object (default)
• ARRAY_A – result will be an associative array
• ARRAY_N – result will be a numerical array

Selecting a Row

If you want to retrieve an entire, single row, the WPDB class has a function for this. get_row() will output one row and cache the rest of the result.

[code='php']$wpdb->get_row('query', output_type, row_offset);[/code]

query is your SELECT query, output_type is the same as get_results() and row_offset is the row you want (starting from 0, which is the default).

Selecting a Column

Similar to get_row(), get_col() will return a single column from your SELECT query.

[code='php']$wpdb->get_col('query', column_offset);[/code]

query is your SELECT query and column_offset is the column you would like return (starting from 0, which is the default).

Flushing the Cache

Whenever you run a query using a function in the WPDB class, related information is cached in the variables $wpdb->last_result, $wpdb->last_query and $wpdb->col_info. You can empty this cache (and you should after you’re finished with it) using the flush() function.

[code='php']$wpdb->flush();[/code]

Next week

Next week, we’ll be covering how to use the WPDB class to insert and update data.

This week we’ll be going over arrays and foreach loops. Both are very powerful and useful, and will be used frequently in your programming.

Arrays

No, arrays are not loops, however we’ll be taking this slight detour as foreach loops are based around arrays. An array is a type, just as variables, strings and integers are all types. Arrays can be best described as an ordered map. They can either associate values to keys, or be a list of values. Arrays are often contained within a variables. Let’s look at a couple of value and key based arrays:

[code='php']$array1 = array('one' => 1,
'two' => 2,
'three' => 3);

$array2 = array('first_name' => 'Matt',
'last_name' => 'Freedman',
'website' => 'http://mattsblog.ca/');[/code]

Arrays syntax are simple, keys are placed first, followed by =>, followed by the key’s value. These “sets” are separated with a comma. Keys must either be a string or integer, while values can be of any type (including, yes, other arrays). You may have an infinite amount of key and value sets. Sets are usually written on new lines, to make the code easier to read, but it’s completely optional.

We can also choose to just specify a list of values:

[code='php']$array3 = array(1, 2, 3, 4, 5, 6, 7, 8, 9);[/code]

This will cause the keys to be automatically set to an incrementing number, starting from 0. So, the above array is equivalent to the following array:

[code='php']$array4 = array(0 => 1,
1 => 2,
2 => 3,
3 => 4,
4 => 5,
5 => 6,
6 => 7,
7 => 8,
8 => 9);[/code]

You can list an infinite amount of values in an array.

You may have noticed that attempting to echo or print an array results in an error. There is a special function for outputting entire arrays to a browser, print_r(). Which is used like this:

[code='php']$array5 = array('key0' => 'value0',
'key1' => 'value1');
print_r($array5);[/code]

And will output the following:

Array
(
[key0] => value0
[key1] => value1
)

It is also possible to retrieve the value of a specific key and also modify it. We can do that like so:

[code='php']echo $array5['key0'];
$array5['key1'] = 'banana';[/code]

Which would output value0 and change the value of the key key1 in $array5 to banana.

There are many aspects to arrays, and many functions available to manipulate them, that we cannot cover them all here. So, if you want to learn more about arrays, you can find more about them on their PHP page.

Foreach

Foreach is used to iterate over arrays. It allows you to loop through each array key and get its value.

[code='php']$array = array('1', '2', '3', '4', '5', '6');
foreach($array as $value) {
echo $value;
}[/code]

This will go through all the values of $array (starting from the beginning) and temporarily assign the current iteration’s value to $value, which you can then use within the curly brackets (in this example we just output the value to the browser). You’re free the call the $value variable whatever you want in the foreach statement.

If you’re dealing with more complex arrays, with keys and values, you can also choose to assign the key to a variable.

[code='php']$fruit_colours = array('banana' => 'yellow',
'apple' => 'red',
'pineapple' => 'yellow');
foreach($fruit_colours as $key => $value) {
echo $key . ': ' . $value;
}[/code]

This would assign the value of the key (the portion of the array before the =>) to $key and the corresponding value to $value.

If you need to ever modify the current key or value inside of a foreach loop, you can do so by assigning the variable by reference (this is PHP 5 and up only). So, let’s take our first example and change it to modify the current value instead.

[code='php']$array = array(1, 2, 3, 4, 5, 6);
foreach($array as &$value) {
$value = $value + 5;
}
unset($value);[/code]

As you can see, we set $value as a reference, then we’re able to modify that value within the foreach. After the foreach is complete, the $value reference will still exist, so we should destroy it using unset().

Closing statements

That concludes our article on arrays and foreach loops. Next week we’ll be looking at PHP’s most complex loop, for.