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

[subtato]

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

 

[ragtek]

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

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

 

[subtato]

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

so you have to call it direct

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

 

[ragtek]

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

 

[subtato]

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

 

[ragtek]

Yea, developers life is always full off new things

 

[Luciano]

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()....
?

 

[Kier]

No, you don't need to worry about cleanup like that in the vast majority of cases. It's micro-optimisation anyway, and largely pointless.

 

[bambua]

No, you don't need to worry about cleanup like that in the vast majority of cases. It's micro-optimisation anyway, and largely pointless.

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?

 

[dmnkhhn]

One thing I find noteworthy about the queries.

Instead of doing

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

you could also use:

$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

 

[Robbo]

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.

 

[dmnkhhn]

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

 

[Kier]

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.

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

Should never appear, it's a huge waste of resources. The only time PHP's count() should be used in this way is to count the number of records from a data set that has been fetched already for its own purpose:

$things = $db->fetchAll('SELECT * FROM xf_thing');

$numThings = count($things);

If you just need to count the number of things in the database, use SQL's COUNT(*).

$numThings = $db->fetchOne('SELECT COUNT(*) FROM xf_thing');

 

[Robbo]

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

Should never appear, it's a huge waste of resources. The only time PHP's count() should be used in this way is to count the number of records from a data set that has been fetched already for its own purpose:

$things = $db->fetchAll('SELECT * FROM xf_thing');

$numThings = count($things);

If you just need to count the number of things in the database, use SQL's COUNT(*).

$numThings = $db->fetchOne('SELECT COUNT(*) FROM xf_thing');

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

 

[physicspirate]

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.

 

[Lu Jia]

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

 

[Liam O'Neill]

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

Thanks