XF 2.2 Example to insert into database

[stromb0li]

Sorry for newbie question, but is there an example on how to insert and get data from the database via controller logic? If using
$this->filter() is that sufficient enough to insert into the database directly, or should an additional method be called to help with sanitize input prior to insert into the database (i.e. prevent sql injection)?

Cheers!

 

[Jeremy P]

You should use either entities or the database adapter methods with prepared statements and/or quoting to interface with the database.

$db = \XF::db();

// insert row -- automatically uses prepared statements
$rowsAffected = $db->insert(
    'xf_some_table',
    [
        'some_column' => $someColumn,
        'other_column' => $otherColumn,
    ]
);

// fetch row using prepared statements
$row = $db->fetchRow(
    'SELECT some_column
        FROM xf_some_table
        WHERE some_column = ?',
    [$someColumn]
);

// fetch rows using quoting, keyed by the some_id column
$rows = $db->fetchAllKeyed(
    'SELECT some_column
        FROM xf_some_table
        WHERE some_column IN (' . $db->quote($someColumns) . ')',
    'some_id'
);

 

[stromb0li]

Do you have an example of the entities approach? It seems like most don't use query string append? Also, what does the \ mean on \XF::db()? I haven't seen that in PHP before using xenForo.

 

[Jeremy P]

Do you have an example of the entities approach? It seems like most don't use query string append?

The entity system is an ORM, which is a bit broad in scope. The vast majority of systems use the ORM.

General information is available here:

Entities, finders, and repositories | Developer Documentation | XenForo

Developer documentation for XenForo

xenforo.com

An example is available as part of the add-on tutorial:

Let's build an add-on | Developer Documentation | XenForo

Developer documentation for XenForo

xenforo.com

The gist of it is you create a class for each table, and each instance of the class corresponds to a row in the table. You can instantiate a new entity to create a new row or fetch a hydrated entity for an existing row. You set values on the object and call the save method to persist your changes.

If you have any specific questions, feel free to ask.

Also, what does the \ mean on \XF::db()? I haven't seen that in PHP before using xenForo.

It means the class name (XF) is fully-qualified, rather than being relative to the current namespace:

PHP: Using namespaces: Basics - Manual

www.php.net

There are convenience methods on controllers and other objects, which allow you to grab the database adapter from the service container by calling a local method ($this->db()), but they return the same underlying object as \XF::db().

 

[stromb0li]

Sorry to bubble this up again, but do you have an example on best practices for updates?

After using $db->query, I had tried $db->affected_rows, similar to mysqli, but that doesn't seem to be implemented.

Thank you!

 

[Jeremy P]

Sorry to bubble this up again, but do you have an example on best practices for updates?

$rowsAffected = $db->update(
    'xf_some_table',
    [
        'some_column' => $someColumn,
        'other_column' => $otherColumn,
    ],
    'some_id = ?', // where clause
    [$someId] // bind params for prepared statement (where clause)
);

After using $db->query, I had tried $db->affected_rows, similar to mysqli, but that doesn't seem to be implemented.

The query will give you back a \XF\Db\Mysqli\Statement object:

$statement = $db->query($someQueryString);
$rowsAffected = $statement->rowsAffected();

If you aren't already, I highly recommend using an IDE, or a text editor with LSP support, for code navigation and autocomplete functionality.

And I would add that you should always use the entity system for core records as the life-cycle hooks are required to keep data consistent.

 

[stromb0li]

This is perfect! Thank you!