The $user variable will now contain an array of all values from the first row found in the query result. To get a single value from that query, such as the username, you can do the following:

$username = $user['username'];

Warning

Database queries written directly and passed to the database adapter are not automatically "safe". They pose a risk of a SQL injection vulnerability if user input is not sanitised and not passed into the query without being prepared. The way to do that properly is using prepared statements, like in the example above. Parameters are represented in the query itself using the ? placeholder. These placeholders are then replaced with the values in the next argument after they have been appropriately escaped. If you have the need to use more than a single parameter, that should be passed into the fetch type method as an array. Should the need arise, you can escape or quote values directly using $db->quote($value).

Both of these methods will return an array of arrays that represent each user record. The difference between the fetchAll and fetchAllKeyed methods is that the returned array will be keyed differently. With fetchAll the array will be keyed with numerically consecutive integers. With fetchAllKeyed the array will be keyed by the name of the field named in the second argument.

Note

If you are using fetchAllKeyed note that the second argument is the field to key the array by, but the third argument is where you pass in the param values to match the ? placeholders.

There are some other fetch type methods available including fetchAllColumn for grabbing an array of a specific column's values from all returned rows:

XF2 includes an all new way to manage the database schema which takes an object oriented approach to performing certain table operations. Let's first look at a traditional alter, using the database adapter like we have above:

When you alter the existing XenForo tables, or create your own tables, you MUST specify a default value otherwise you will encounter problems when querying the table.

Both of these examples produce the exact same query as their more direct counterparts above. Though you might notice that some things are (deliberately) missing. For example, none of the examples specify a length for the int fields. This is simply because by omitting that, MySQL will provide it with a default, which is 10 for unsigned integers. Speaking of which, we also don't specify that the some_id column is unsigned. Using unsigned integers within XF is by far the most common use case, so it is automatically added. If you genuinely need the ability to support negative integers, you can reverse that with the ->unsigned(false) method. Another omission is the lack of defining NOT NULL for everything. Again, this is applied automatically, but you can reverse that with ->nullable(true).

It may not be clear from the alter example, but when changing existing fields, the existing field definition is automatically retained. This means that, rather than having to specify the full column definition, including all of the bits that haven't actually changed, you can just specify the parts you want to change.

There is some other automatic inference that happens with regards to primary keys. You can explicitly define the primary key (or any other type of key) if you wish, but often auto incremented fields will usually be your primary key for the table. So in the create table example, the some_id field is automatically assigned as the primary key for that table.

Finally, for the create table approach, we can automatically add the correct table config for the storage engine specified (which defaults to InnoDB but can be changed easily to other engine types).