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: Basics - Manual

PHP is a popular general-purpose scripting language that powers everything from your blog to the most popular websites in the world.

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!

 

[stromb0li]

Sorry to bubble up an older thread, but is there a best practice when inserting timestamps into the database? I'm noticing all my dates are skewed due to timezones when I use the default time() method built into PHP. Is there a function that generates with the timestamp, with adding the offset of the user's timezone?

 

[digitalpoint]

Sorry to bubble up an older thread, but is there a best practice when inserting timestamps into the database? I'm noticing all my dates are skewed due to timezones when I use the default time() method built into PHP. Is there a function that generates with the timestamp, with adding the offset of the user's timezone?

By definition, a unix timestamp is the time for a specific time zone (UTC). Offsets should be done based on the time zone you want to offset to (for example XenForo, you can set the default time zone and users set their time zone). As long as those are set properly, the date/time displayed will be local time.

PHP's time() function has nothing to do with time zones because it's a UNIX timestamp.

From: https://www.php.net/manual/en/function.time.php

Unix timestamps do not contain any information with regards to any local timezone. It is recommended to use the DateTimeImmutable class for handling date and time information in order to avoid the pitfalls that come with just Unix timestamps.

From: https://en.wikipedia.org/wiki/Unix_time

The Unix time 0 is exactly midnight UTC on 1 January 1970, with Unix time incrementing by 1 for every non-leap second after this.

TL;DR: When you want to display local time, you don't alter the UNIX timestamp, you take the UNIX timestamp and adjust that for whatever time zone you want to display.

 

[stromb0li]

The problem is when I use the datepicker and specify Jan 27, 2024 for example, it computes Jan 27, 2024 based on UTC-0 at 0:0:0. With a timezone offset of -8 for pacific time for example, the date will show up at Jan 26th; even though the user specified Jan 27th given XenForo is already considering the offset as part of their user experience.

 

[Jeremy P]

You can set the timezone when capturing the input:

$date = $this->filter('some_input', 'datetime,tz:UTC');

 

[stromb0li]

You can set the timezone when capturing the input:

$date = $this->filter('some_input', 'datetime,tz:UTC');

Is the filter be supported with the xf:datatime control? From what I've seen that control returns a value in yyyy-mm-dd format; so I've been using str and then strtotime :/

 

[Jeremy P]

The datetime filter works with <xf:dateinput>, if that's what you meant.

 

[stromb0li]

The datetime filter works with <xf:dateinput>, if that's what you meant.

Seems like a given, but didn't want to assume.

Aside, do I need ,tz:UTC if the board timezone by default is set to UTC? I.e. will the filter do the offset automatically based on the user's timezone? From my understanding, the filter would account for the server offset if it wasn't UTC?

 

[Jeremy P]

It will default to the visitor's time zone, which I would argue is usually what users expect. If I entered a date from a date picker and it wound up saving the input or selecting results with a different date because of UTC differences, I might be confused. So it depends on your use case, but if you want UTC you'd need to be explicit about it.

 

[digitalpoint]

Seems like a given, but didn't want to assume.

Aside, do I need ,tz:UTC if the board timezone by default is set to UTC? I.e. will the filter do the offset automatically based on the user's timezone? From my understanding, the filter would account for the server offset if it wasn't UTC?

The issue is that without explicitly specifying the timezone on the filter, is that it defaults to the user's time zone which is what's causing the problem. The timestamp itself is "timezone-less" (well it's always UTC by definition), that's why forcing the filter to display the date as UTC will always give a "whole" date (if that makes sense). Any other time zone (like a user specified time zone) will have potentially different calendar dates depending on the time of day it was recorded.

So ya... forcing the time zone with the filter parameter should be all you need to do (the issue is that you weren't specifying one, so it was defaulting to the user's time zone). Nothing more you need to do (don't need to specify a default time zone of UTC for the board or anything).

 

[stromb0li]

It will default to the visitor's time zone, which I would argue is usually what users expect. If I entered a date from a date picker and it wound up saving the input or selecting results with a different date because of UTC differences, I might be confused. So it depends on your use case, but if you want UTC you'd need to be explicit about it.

This is exactly what I was looking for, datetime without tz variant. Before I was doing the string manipulations myself, which effectively was doing the same as datetime,tz:UTC (which prevents considerations of the user's timezone)

 

[stromb0li]

Sorry to bump up an old topic, but similarly, when working with $db->fetchAll, how do you handle arrays as part of the prepared statement?

For example:

// fetch row using prepared statements
$ids = array(1,2,3,4,5,6);
$row = $db->fetchRow(
    'SELECT some_column
        FROM xf_some_table
        WHERE id in (?)',
    [implode(',',$ids)]
);

Will return nothing.

But this will return results

// fetch row using prepared statements
$ids = array(1,2,3,4,5,6);
$row = $db->fetchRow(
    'SELECT some_column
        FROM xf_some_table
        WHERE id in ('.implode(',',$ids).')',
    []
);

 

[Jeremy P]

Generally you would just quote the value and inline it instead.

$ids = [1,2,3,4,5,6];

$row = $db->fetchRow(
    'SELECT some_column
        FROM xf_some_table
        WHERE id IN (' . $db->quote($ids) . ')'
);

 

[stromb0li]

Generally you would just quote the value and inline it instead.

$ids = [1,2,3,4,5,6];

$row = $db->fetchRow(
    'SELECT some_column
        FROM xf_some_table
        WHERE id IN (' . $db->quote($ids) . ')'
);

Does this open risk to injection if not using a known safe array?