Manual talk:Database access

Thanks for starting this page.

Not very well described

Where does it say what does "fname" or "options" or "vars" mean?

I believe those are described here. Tisane 20:11, 26 February 2010 (UTC)

I agree like almost all wikipedia technical and mediawiki articles, this article is terribly written, and difficult to understand for those who are not computer programmers. Errectstapler 14:03, 4 May 2011 (UTC)

I added a much needed explanation of how to login to mysql.[1] Errectstapler 18:24, 4 May 2011 (UTC)

The information was reverted, here is is for others:

==Logging in to MySQL== In localsettings.php will be your MySQL password and username, for example: ## Database settings $wgDBtype = "mysql"; $wgDBserver = "localhost"; $wgDBname = yourwebsite $wgDBuser = yourwebsite $wgDBpassword = LongPassword With PuTTY (a telnet/ssh client), login by entering the following: mysql -u <$wgDBuser> -p --database=<$wgDBname> Replacing <$wgDBuser> and <$wgDBname> with the localsettings.php information. You will then be prompted for your password (wgDBpassword). You will then see the mysql> prompt.

Errectstapler 19:56, 4 May 2011 (UTC)

SQL Injection

How safe are these functions against SQL Injection? Does the extension-coder have to take care of this themselves? —The preceding unsigned comment was added by 194.176.105.40 (talk • contribs) {{{2}}}

If you feed an array to select, it's safe. If you construct a string, even a string to be passed to ::select, then it's up to you to take care of safety. Gigs 03:07, 30 December 2010 (UTC)

select-statement

Where would I find documentation on the select-statement (no, not sql in general)?

I would assume:

$res = $dbr->select(Tables, Fields returned, WHERE-clause, calling function, options);

Tables

array of tables, if more then on, where-clause needs a join
Example: array('pagelinks', 'page') or just 'page'

Fields returned

array of fields returned
Example: 'pl_title'

WHERE-clause

array which holds all conditions, will be joined with AND (And how do I OR clauses?)
Example: array('page_id = pl_from', 'pl_namespace' => NS_TEMPLATE, 'page_namespace' => $disPageObj->getNamespace(), 'page_title' => $disPageObj->getDBkey())

calling function

just for the fun of it?
Example: 'DisambiguationsPage::getSQL'

options

seems to be added after the WHERE clause, like LIMIT or ORDER BY

Full example:

$res = $dbr->select(array('pagelinks', 'page'),
                    'pl_title',
                     array('page_id = pl_from', 'pl_namespace' => NS_TEMPLATE,
                     'page_namespace' => $disPageObj->getNamespace(), 'page_title' => $disPageObj->getDBkey()),
                    'DisambiguationsPage::getSQL' );

—The preceding unsigned comment was added by 85.177.206.125 (talk • contribs) {{{2}}}

The WHERE clause is hard coded to use AND and only do equality when you give it an array. An important caveat is that if you give a string instead of an array, you have to addQuotes and do all your sanitation manually. I have updated the page accordingly. Gigs 03:02, 30 December 2010 (UTC)

How do I make queries with OR

Is this possible?

immediateBegin() vs. begin()

This document suggests using immediateBegin() over begin(), as begin() 'may not do what you expect'. Same for immediateCommit() vs. commit(). However, the MW source code specifies that both immediateBegin() and begin() do the same thing, and that immediateBegin() is deprecated in favor of begin(). Same for commit(). Unless anyone objects, I'm going to change the page to state that begin() and commit() should be used, and that the immediateX() have been deprecated. --Msul01 20:50, 12 December 2008 (UTC)

ORDER BY and GROUP BY

Hi, i want to roder an group my query but it doesn't work. here my source

                $res = $dbr->select(
                                        'flaggedrevs',
                                        array( 'fr_page_id' , 'fr_user' , 'fr_timestamp' , 'fr_comment' , 'fr_quality' ),
                                        '',
                                        '',
                                        'GROUP BY fr_page_id ORDER BY fr_timestamp DESC'
                );

the query it self is ok, but it seems like the order and group option does not work. hre the raw query from the cli

select fr_timestamp,fr_user,fr_page_id,fr_quality from flaggedrevs group by fr_page_id order by fr_timestamp DESC;

it works perfectly there. is there another way to query the database? This API seems to be not very efficient. --213.214.18.64 18:06, 20 January 2010 (UTC)

   $res = $dbr->select( 'flaggedrevs', 
      array( 'fr_page_id' , 'fr_user' , 'fr_timestamp' , 'fr_comment' , 'fr_quality' ),  
      '', '', 
      array( 'GROUP BY' => 'fr_page_id' , 'ORDER BY' => 'fr_timestamp DESC' ) );

Max Semenik 18:34, 20 January 2010 (UTC)

thank you --213.214.18.64 16:29, 21 January 2010 (UTC)

CREATE TABLE

There should be a wrapper for CREATE TABLE. (Other than a duplicate of an existing table) Tisane 07:05, 12 March 2010 (UTC)

$res - and then?

There should be more documentation about what to do with the result of a query. I found a pointer to DatabaseType::fetchObject($res), but do I need to use it? What if there will always only be one result row? --Patrick Nagel 04:06, 4 May 2011 (UTC)

It is mentioned at the very beginning: foreach( $res as $row ) { ... }. Max Semenik 04:53, 4 May 2011 (UTC)