• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.
  • As we have now upgraded the main XenForo community, this forum is now being retired. It will remain open in a mostly read only state. Any further feedback, suggestions or bug reports should be posted on the main forum. We will still process the existing bug reports from this forum.

    When we approach a stable release, this forum will become inaccessible. If there is any content you wish to save, please do so before then.

Whats the correct way to INSERT / UPDATE and DELETE?

Andrew

Active Member
#1
There a lot of documentation on how to use finder, entities and repositories to SELECT data, but I am having a hard time with how to correctly do other SQL statements. (insert, update and delete). Now I know I could just submit the form to the same page and check the $_GET and process a statement using the db adapter ( $db = \XF::db(); $db->query('insert into ...') ). I guess I am trying to understand the correct XF2 way to do this.
 

Liam W

Active Member
#3
You use the entity to save the data. Set the values on the entity, and then call the save method.

The correct way to do it, which is more friendly to addons, is to use a different method that uses the FormAcrion system.

Look at some of the existing controllers.

Liam
 

Andrew

Active Member
#4
You use the entity to save the data. Set the values on the entity, and then call the save method.

The correct way to do it, which is more friendly to addons, is to use a different method that uses the FormAcrion system.

Look at some of the existing controllers.

Liam
Which file would you open? I opened XF/Pub/Controller/Thread.php and didn't find anything that I understood to insert.
 
#6
I am looking at the files inside of "src/XF/Db" and don't see what you mentioned. Which file should I open?
src/XF/Db/AbstractAdapter.php
PHP:
public function insert($table, array $rawValues, $replaceInto = false, $onDupe = false, $modifier = '')
    {
        if (!$rawValues)
        {
            throw new \InvalidArgumentException('Values must be provided to insert');
        }

        $cols = [];
        $sqlValues = [];
        $bind = [];
        foreach ($rawValues AS $key => $value)
        {
            $cols[] = "`$key`";
            $bind[] = $value;
            $sqlValues[] = '?';
        }

        $keyword = ($replaceInto ? 'REPLACE' : 'INSERT');
        if ($replaceInto)
        {
            $onDupe = false;
        }

        $res = $this->query(
            "$keyword $modifier INTO $table (" . implode(', ', $cols) . ') VALUES '
            . '(' . implode(', ', $sqlValues) . ')'
            . ($onDupe ? " ON DUPLICATE KEY UPDATE $onDupe" : ''),
            $bind
        );
        return $res->rowsAffected();
    }

    public function insertBulk($table, array $rows, $replaceInto = false, $onDupe = false)
    {
        if (!$rows)
        {
            throw new \InvalidArgumentException('Rows must be provided to bulk insert');
        }

        $firstRow = reset($rows);
        $cols = array_keys($firstRow);

        $rowSql = [];
        foreach ($rows AS $row)
        {
            $values = [];
            foreach ($cols AS $col)
            {
                if (!array_key_exists($col, $row))
                {
                    throw new \InvalidArgumentException("Row $row missing column $col in bulk insert");
                }

                $values[] = $this->quote($row[$col]);
            }

            $rowSql[] = '(' . implode(',', $values) . ')';
        }

        foreach ($cols AS &$col)
        {
            $col = "`$col`";
        }

        $keyword = ($replaceInto ? 'REPLACE' : 'INSERT');
        if ($replaceInto)
        {
            $onDupe = false;
        }

        $res = $this->query(
            $keyword . ' INTO ' . $table . ' (' . implode(', ', $cols) . ') VALUES '
            . implode(",\n", $rowSql)
            . ($onDupe ? " ON DUPLICATE KEY UPDATE $onDupe" : '')
        );
        return $res->rowsAffected();
    }

    public function delete($table, $where = '', $params = [], $modifier = '', $order = '', $limit = 0)
    {
        $res = $this->query(
            "DELETE $modifier FROM $table WHERE " . ($where ? $where : '1=1')
            . ($order ? " ORDER BY $order" : '')
            . ($limit ? ' LIMIT ' . intval($limit) : ''),
            $params
        );
        return $res->rowsAffected();
    }

    public function update($table, array $cols, $where = '', $params = [], $modifier = '', $order = '', $limit = 0)
    {
        if (!$cols)
        {
            return 0;
        }

        $sqlValues = [];
        $bind = [];
        foreach ($cols AS $col => $value)
        {
            $bind[] = $value;
            $sqlValues[] = "`$col` = ?";
        }

        $bind = array_merge($bind, is_array($params) ? $params : [$params]);

        $res = $this->query(
            "UPDATE $modifier $table SET " . implode(', ', $sqlValues)
            . ' WHERE ' . ($where ? $where : '1=1')
            . ($order ? " ORDER BY $order" : '')
            . ($limit ? ' LIMIT ' . intval($limit) : ''),
            $bind
        );
        return $res->rowsAffected();
    }

    public function emptyTable($table)
    {
        $method = isset($this->config['emptyMethod']) ? $this->config['emptyMethod'] : 'TRUNCATE';
        switch (strtoupper($method))
        {
            case 'TRUNCATE':
                $query = "TRUNCATE TABLE `$table`";
                break;

            case 'DELETE':
                $query = "DELETE FROM `$table`";
                break;

            default:
                throw new \InvalidArgumentException("Unknown emptyMethod '$method'.");
        }
        $res = $this->query($query);
        return $res->rowsAffected();
    }