MySQL Queries - How to use $db in XenForo & small notes

dmnkhhn is correct. I do believe, extending the XenForo_Model class, you can simply type fetchAllKeyed(...) instead of doing $this->_getDb() .. etc.

fetchAllKeyed is a method of the Model Class AND NOT from ZEND DB

so you have to call it direct
For example:
XenForo_Model_AddOn

PHP:

    public function getAllAddOns()
    {
        return $this->fetchAllKeyed('
            SELECT *
            FROM xf_addon
            ORDER BY title
        ', 'addon_id');
    }

Correct, this has been established. Can you just do fetchAllKeyed() or does the code require you do $this->fetchAllKeyed()?

Methods can only be run with $this-> like in this case (or self:: or parent:: or classname:: for static methods)

Odd, didn't know that. As far as PHP goes anyways. Learn something new everyday, eh?

I will play around with the models and see what documentation comes up. Thanks

Yea, developers life is always full off new things

This stuff is new to me also, just one stupid question, so please dont all laugh at the same time...
Using this, but at any moment to I need (in some cases) to do what we used to in vb:
either unsetting some arrays and or $db->freeresult()....
?

Theoretically doesn't the server just take the class out of memory(actually the whole php script for that matter) as soon as it's done completely executing the page?

One thing I find noteworthy about the queries.

Instead of doing

PHP:


$this->_getDb()->fetchAll('
        SELECT *
        FROM xyz
        WHERE abc = 1');


you could also use:

PHP:


$this->_getDb()->fetchAll('
        SELECT *
        FROM xyz
        WHERE abc = ?', 1);


The '?' acts as a placeholder, the 1 is the value that is used instead.
If I read the code correctly, you can even use multiple '?' as long as you provide an array of data to bind into those placeholders.

It's called 'prepared statements', you can read more about it at: http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html

It seems a bit weird to me that you can't query the number of rows faster. I don't see how it could be good for performance to have to query every row of a table and then use count() on it. Come to think of it COUNT(*) would be better then what you have said.

So instead of
$numRows = count($db->fetchAll("SELECT * FROM `xf_user`"));

Do...
list($numRows) = $db->fetchRow("SELECT COUNT(*) FROM `xf_user`");

Note: I am fairly tired so may have messed something up there.

It's old but I hope it's still valid: http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/

Yeah I was really hoping they weren't right in the OP. And I was close to getting it right, guess I should actually look at the database layer oneday

How do i create a new table where the primary key is the logged in user ID, and it stores a URL, and a text field for each user that exists in the main database? I want to add some profile fields to my user profile pages, but not sure how to do this.

To return a single column from the next row of the result set, use fetchColumn(). The optional argument is the integer index of the column, and it defaults to 0. This method returns a scalar value, orFALSE if all rows of the result set have been fetched.

Note this method operates differently than the fetchCol() method of the Adapter class. ThefetchColumn() method of a statement returns a single value from one row. The fetchCol() method of an adapter returns an array of values, taken from the first column of all rows of the result set. (source)

To retrieve a row from the result set structured as an object, use the fetchObject(). This method takes two optional arguments. The first argument is a string that names the class name of the object to return; the default is 'stdClass'. The second argument is an array of values that will be passed to the constructor of that class.

There are 3 more fetch method if someone need

What pages do I need to include to access the DB externally - in a wordpress page for example.

Thanks