hi,
I want to find something in wikidb. But I don't exectly know how to use the select statement write in WIKI.
For ecample . DatabaseBase::select ($ table, $ vars, $ conds = '', $ fname = 'Database::select', $ options = array(), $ join_conds = array() )
How can I use this to rewrite ' select * from recentchangs where rc_id in(selcet max(rc_id) from recentchangs group by rc_title) and rc_title='Wiki' '.
Thanks very much!
vanessa lee
李琴 wrote:
hi,
I want to find something in wikidb. But I don't exectly know how to use the select statement write in WIKI.
For ecample . DatabaseBase::select ($ table, $ vars, $ conds = '', $ fname = 'Database::select', $ options = array(), $ join_conds = array() )
How can I use this to rewrite ' select * from recentchangs where rc_id in(selcet max(rc_id) from recentchangs group by rc_title) and rc_title='Wiki' '.
Thanks very much!
vanessa lee
Don't use subselects, they're not supported by MySQL 4.0 which is what we target.
$dbr = wfGetDB( DB_SLAVE );
$max = $dbr->selectField( 'recentchanges', 'max(rc_id)', false, __METHOD__, array( 'GROUP BY' => 'rc_title' );
$res = $dbr->select( 'recentchanges', '*', array( 'rc_id' => $max, 'rc_namespace' => 0, 'rc_title' => 'Wiki', ), __METHOD__ );
foreach ( $res as $row ) { // $row is the result row }
The rc_namespace condition is necessary.
-- Tim Starling
2009/11/26 Tim Starling tstarling@wikimedia.org:
Don't use subselects, they're not supported by MySQL 4.0 which is what we target.
$dbr = wfGetDB( DB_SLAVE );
$max = $dbr->selectField( 'recentchanges', 'max(rc_id)', false, __METHOD__, array( 'GROUP BY' => 'rc_title' );
$res = $dbr->select( 'recentchanges', '*', array( 'rc_id' => $max, 'rc_namespace' => 0, 'rc_title' => 'Wiki', ), __METHOD__ );
Note that the GROUP BY condition in the first query is unnecessary, and that the whole thing could be rewritten to SELECT * FROM recentchanges WHERE rc_namespace=0 AND rc_title='Wiki' ORDER BY rc_id DESC LIMIT 1;
Roan Kattouw (Catrope)
hi.sorry to borther again
I want to ask how to rewrite those statement ,too.
Like '>,<,LIMIT '.
And when I use 'order by ' how to change the order of 'ASC,DESC'?
Thanks very much.
vanessa lee
-----Original Message----- From: Roan Kattouw roan.kattouw@gmail.com To: Wikimedia developers wikitech-l@lists.wikimedia.org Date: Thu, 26 Nov 2009 13:32:10 +0100 Subject: Re: [Wikitech-l] SQL
2009/11/26 Tim Starling tstarling@wikimedia.org:
Don't use subselects, they're not supported by MySQL 4.0 which is what we target.
$dbr = wfGetDB( DB_SLAVE );
$max = $dbr->selectField( 'recentchanges', 'max(rc_id)', false, __METHOD__, array( 'GROUP BY' => 'rc_title' );
$res = $dbr->select( 'recentchanges', '*', array( 'rc_id' => $max, 'rc_namespace' => 0, 'rc_title' => 'Wiki', ), __METHOD__ );
Note that the GROUP BY condition in the first query is unnecessary, and that the whole thing could be rewritten to SELECT * FROM recentchanges WHERE rc_namespace=0 AND rc_title='Wiki' ORDER BY rc_id DESC LIMIT 1;
Roan Kattouw (Catrope)
_______________________________________________ Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
2009/11/26 李琴 qli@ica.stc.sh.cn:
hi.sorry to borther again
I want to ask how to rewrite those statement ,too.
Like '>,<,LIMIT '.
And when I use 'order by ' how to change the order of 'ASC,DESC'?
LIMIT and ORDER BY are similar to GROUP BY, which Tim already demonstrated. ASC and DESC can just be appended to the order by column, like 'ORDER BY' => 'rc_id DESC' . Note that ASC is the default and never has to be specified explicitly. Tim also demonstrated how to do equalities (like 'rc_namespace' => 0 ); other conditions go in the same array, but as one string (like 'rc_namespace != 0', 'rc_namespace < 2' and 'rc_namespace=page_namespace' ).
There's also lots of examples in the MediaWiki codebase, just grep for 'select('.
Roan Kattouw (Catrope)
wikitech-l@lists.wikimedia.org