[GitHub/WoltLab/woltlab.github.io.git] / 5.3 / php_database-access.html

<!DOCTYPE html>
<head>
    <meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta name="description" content="">
<meta name="keywords" content=" ">
<title>Database Access | WoltLab Suite 5.3 Documentation</title>

<link rel="stylesheet" href="https://docs.woltlab.com/5.3/css/syntax.css">
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Open+Sans:400,300,600">
<link rel="stylesheet" href="https://docs.woltlab.com/5.3/css/font-awesome.min.css">
<!--<link rel="stylesheet" type="text/css" href="css/bootstrap.min.css">-->
<link rel="stylesheet" href="https://docs.woltlab.com/5.3/css/modern-business.css">
<link rel="stylesheet" href="https://docs.woltlab.com/5.3/css/lavish-bootstrap.css">
<link rel="stylesheet" href="https://docs.woltlab.com/5.3/css/customstyles.css">
<link rel="stylesheet" href="https://docs.woltlab.com/5.3/css/theme-blue.css?v=3">

<script src="https://docs.woltlab.com/5.3/js/jquery.min.js"></script>
<script src="https://docs.woltlab.com/5.3/js/jquery.cookie.min.js"></script>
<script src="https://docs.woltlab.com/5.3/js/jquery.navgoco.min.js"></script>
<script src="https://docs.woltlab.com/5.3/js/bootstrap.min.js"></script>
<script src="https://docs.woltlab.com/5.3/js/anchor.min.js"></script>
<script src="https://docs.woltlab.com/5.3/js/toc.js"></script>
<script src="https://docs.woltlab.com/5.3/js/customscripts.js"></script>

<link rel="shortcut icon" href="https://docs.woltlab.com/5.3/images/favicon.ico">

<link rel="alternate" type="application/rss+xml" title="woltlab.github.io" href="https://docs.woltlab.com/5.3feed.xml">

    <script>
        $(document).ready(function() {
            // Initialize navgoco with default options
            $("#mysidebar").navgoco({
                caretHtml: '',
                accordion: true,
                openClass: 'active', // open
                save: false, // leave false or nav highlighting doesn't work right
                cookie: {
                    name: 'navgoco',
                    expires: false,
                    path: '/'
                },
                slide: {
                    duration: 400,
                    easing: 'swing'
                }
            });

            $("#collapseAll").click(function(e) {
                e.preventDefault();
                $("#mysidebar").navgoco('toggle', false);
            });

            $("#expandAll").click(function(e) {
                e.preventDefault();
                $("#mysidebar").navgoco('toggle', true);
            });

        });

    </script>
    <script>
        $(function () {
            $('[data-toggle="tooltip"]').tooltip()
        })
    </script>
    

</head>
<body>
  <!-- Navigation -->
<nav class="navbar navbar-inverse navbar-fixed-top">
    <div class="container topnavlinks">
        <div class="navbar-header">
            <button type="button" class="navbar-toggle" data-toggle="collapse" data-target="#bs-example-navbar-collapse-1">
                <span class="sr-only">Toggle navigation</span>
                <span class="icon-bar"></span>
                <span class="icon-bar"></span>
                <span class="icon-bar"></span>
            </button>
            <a class="fa fa-home fa-lg navbar-brand" href="index.html">&nbsp;<span class="projectTitle"> WoltLab Suite 5.3 Documentation</span></a>
        </div>
        <div class="collapse navbar-collapse" id="bs-example-navbar-collapse-1">
            <ul class="nav navbar-nav navbar-right">
                <!-- entries without drop-downs appear here -->
                
                
                <li><a href="https://www.woltlab.com" target="_blank">woltlab.com</a></li>
                
                
                <li><a href="https://github.com/WoltLab/WCF/" target="_blank">Code on github.com</a></li>
                
                
                <!-- entries with drop-downs appear here -->
                <!-- conditional logic to control which topnav appears for the audience defined in the configuration file.-->
                
                
                <!--comment out this block if you want to hide search-->
                <li>
                    <!--start search-->
                    <div id="search-demo-container">
                        <input type="text" id="search-input" placeholder="search...">
                        <ul id="results-container"></ul>
                    </div>
                    <script src="https://docs.woltlab.com/5.3/js/jekyll-search.js" type="text/javascript"></script>
                    <script type="text/javascript">
                            SimpleJekyllSearch.init({
                                searchInput: document.getElementById('search-input'),
                                resultsContainer: document.getElementById('results-container'),
                                dataSource: 'https://docs.woltlab.com/5.3/search.json',
                                searchResultTemplate: '<li><a href="{url}" title="Database Access">{title}</a></li>',
                    noResultsText: 'No results found.',
                            limit: 10,
                            fuzzy: true,
                    })
                    </script>
                    <!--end search-->
                </li>
            </ul>
        </div>
        </div>
        <!-- /.container -->
</nav>


  <div class="container">
    <div class="col-lg-12">&nbsp;</div>

    <div class="row">
      <div class="col-md-3">
        

<ul id="mysidebar" class="nav">
    <li class="sidebarTitle">WoltLab Suite 5.3</li>

    
            <li>
                <a href="#">Getting Started</a>
                <ul>
                    
                        
                                <li data-identifier="index"><a href="index.html">Introduction</a></li>
                            
                        
                                <li data-identifier="getting-started_quick-start"><a href="getting-started_quick-start.html">Quick Start</a></li>
                            
                        
                </ul>
            </li>
        
            <li>
                <a href="#">PHP API</a>
                <ul>
                    
                        
                                <li data-identifier="php_pages"><a href="php_pages.html">Pages</a></li>
                            
                        
                                <li data-identifier="php_database-objects"><a href="php_database-objects.html">Database Objects</a></li>
                            
                        
                                <li class="active" data-identifier="php_database-access"><a href="php_database-access.html">Database Access</a></li>
                            
                        
                                <li data-identifier="php_exceptions"><a href="php_exceptions.html">Exceptions</a></li>
                            
                        
                            <li class="subfolders">
                                <a href="#">API</a>
                                <ul>
                                    
                                        
                                            <li data-identifier="php_api_caches"><a href="php_api_caches.html">Caches</a></li>
                                        
                                    
                                            <li data-identifier="php_api_comments"><a href="php_api_comments.html">Comments</a></li>
                                        
                                    
                                            <li data-identifier="php_api_cronjobs"><a href="php_api_cronjobs.html">Cronjobs</a></li>
                                        
                                    
                                            <li data-identifier="php_api_events"><a href="php_api_events.html">Events</a></li>
                                        
                                    
                                            <li data-identifier="php_api_form_builder"><a href="php_api_form_builder.html">Form Builder</a></li>
                                        
                                    
                                            <li data-identifier="php_api_package_installation_plugins"><a href="php_api_package_installation_plugins.html">Package Installation Plugins</a></li>
                                        
                                    
                                            <li data-identifier="php_api_user_activity_points"><a href="php_api_user_activity_points.html">User Activity Points</a></li>
                                        
                                    
                                            <li data-identifier="php_api_user_notifications"><a href="php_api_user_notifications.html">User Notifications</a></li>
                                        
                                    
                                            <li data-identifier="php_api_sitemaps"><a href="php_api_sitemaps.html">Sitemaps</a></li>
                                        
                                    
                                </ul>
                            </li>
                        
                    
                                <li data-identifier="php_code-style"><a href="php_code-style.html">Code Style</a></li>
                            
                        
                                <li data-identifier="php_apps"><a href="php_apps.html">Apps</a></li>
                            
                        
                                <li data-identifier="php_gdpr"><a href="php_gdpr.html">GDPR</a></li>
                            
                        
                </ul>
            </li>
        
            <li>
                <a href="#">Languages, Templates & CSS</a>
                <ul>
                    
                        
                                <li data-identifier="view_languages"><a href="view_languages.html">Languages</a></li>
                            
                        
                                <li data-identifier="view_templates"><a href="view_templates.html">Templates</a></li>
                            
                        
                                <li data-identifier="view_css"><a href="view_css.html">CSS</a></li>
                            
                        
                </ul>
            </li>
        
            <li>
                <a href="#">JavaScript API</a>
                <ul>
                    
                        
                                <li data-identifier="javascript_general-usage"><a href="javascript_general-usage.html">General Usage</a></li>
                            
                        
                            <li class="subfolders">
                                <a href="#">New API</a>
                                <ul>
                                    
                                        
                                            <li data-identifier="javascript_new-api_writing-a-module"><a href="javascript_new-api_writing-a-module.html">Writing a module</a></li>
                                        
                                    
                                            <li data-identifier="javascript_new-api_data-structures"><a href="javascript_new-api_data-structures.html">Data Structures</a></li>
                                        
                                    
                                            <li data-identifier="javascript_new-api_core"><a href="javascript_new-api_core.html">Core Functions</a></li>
                                        
                                    
                                            <li data-identifier="javascript_new-api_dom"><a href="javascript_new-api_dom.html">DOM</a></li>
                                        
                                    
                                            <li data-identifier="javascript_new-api_events"><a href="javascript_new-api_events.html">Event Handling</a></li>
                                        
                                    
                                            <li data-identifier="javascript_new-api_ajax"><a href="javascript_new-api_ajax.html">Ajax</a></li>
                                        
                                    
                                            <li data-identifier="javascript_new-api_dialogs"><a href="javascript_new-api_dialogs.html">Dialogs</a></li>
                                        
                                    
                                            <li data-identifier="javascript_new-api_browser"><a href="javascript_new-api_browser.html">Browser and Screen Sizes</a></li>
                                        
                                    
                                            <li data-identifier="javascript_new-api_ui"><a href="javascript_new-api_ui.html">User Interface</a></li>
                                        
                                    
                                </ul>
                            </li>
                        
                    
                                <li data-identifier="javascript_legacy-api"><a href="javascript_legacy-api.html">Legacy API</a></li>
                            
                        
                                <li data-identifier="javascript_helper-functions"><a href="javascript_helper-functions.html">Helper Functions</a></li>
                            
                        
                                <li data-identifier="javascript_code-snippets"><a href="javascript_code-snippets.html">Code Snippets</a></li>
                            
                        
                </ul>
            </li>
        
            <li>
                <a href="#">Package Components</a>
                <ul>
                    
                        
                                <li data-identifier="package_package-xml"><a href="package_package-xml.html">package.xml</a></li>
                            
                        
                                <li data-identifier="package_pip"><a href="package_pip.html">PIPs</a></li>
                            
                        
                </ul>
            </li>
        
            <li>
                <a href="#">Migration</a>
                <ul>
                    
                        
                            <li class="subfolders">
                                <a href="#">Migrating from WSC 5.2</a>
                                <ul>
                                    
                                        
                                            <li data-identifier="migration_wsc-52_php"><a href="migration_wsc-52_php.html">PHP API</a></li>
                                        
                                    
                                            <li data-identifier="migration_wsc-52_templates"><a href="migration_wsc-52_templates.html">Templates and Languages</a></li>
                                        
                                    
                                            <li data-identifier="migration_wsc-52_libraries"><a href="migration_wsc-52_libraries.html">Third Party Libraries</a></li>
                                        
                                    
                                </ul>
                            </li>
                        
                            <li class="subfolders">
                                <a href="#">Migrating from WSC 3.1</a>
                                <ul>
                                    
                                        
                                            <li data-identifier="migration_wsc-31_php"><a href="migration_wsc-31_php.html">PHP API</a></li>
                                        
                                    
                                </ul>
                            </li>
                        
                            <li class="subfolders">
                                <a href="#">Migrating from WSC 3.0</a>
                                <ul>
                                    
                                        
                                            <li data-identifier="migration_wsc-30_php"><a href="migration_wsc-30_php.html">PHP API</a></li>
                                        
                                    
                                            <li data-identifier="migration_wsc-30_javascript"><a href="migration_wsc-30_javascript.html">JavaScript API</a></li>
                                        
                                    
                                            <li data-identifier="migration_wsc-30_templates"><a href="migration_wsc-30_templates.html">Templates</a></li>
                                        
                                    
                                            <li data-identifier="migration_wsc-30_css"><a href="migration_wsc-30_css.html">CSS</a></li>
                                        
                                    
                                            <li data-identifier="migration_wsc-30_package"><a href="migration_wsc-30_package.html">Package Components</a></li>
                                        
                                    
                                </ul>
                            </li>
                        
                            <li class="subfolders">
                                <a href="#">Migrating from WCF 2.1</a>
                                <ul>
                                    
                                        
                                            <li data-identifier="migration_wcf-21_php"><a href="migration_wcf-21_php.html">PHP API</a></li>
                                        
                                    
                                            <li data-identifier="migration_wcf-21_templates"><a href="migration_wcf-21_templates.html">Templates</a></li>
                                        
                                    
                                            <li data-identifier="migration_wcf-21_css"><a href="migration_wcf-21_css.html">CSS</a></li>
                                        
                                    
                                            <li data-identifier="migration_wcf-21_package"><a href="migration_wcf-21_package.html">Package Components</a></li>
                                        
                                    
                                </ul>
                            </li>
                        
                    
                </ul>
            </li>
        
            <li>
                <a href="#">Tutorials</a>
                <ul>
                    
                        
                                <li data-identifier="tutorial_tutorial-series"><a href="tutorial_tutorial-series.html">Tutorial Series</a></li>
                            
                        
                </ul>
            </li>
        
    
</ul>

<script>
(function() {
    var sidebar = $('#mysidebar');
    var item = sidebar.find('.active');
    if (item.length === 0) {
        var parent = '';
        if (parent) {
            sidebar.find('li[data-identifier="' + parent + '"]').addClass('active');
        }
    }

    sidebar.find(".active").parents('li').toggleClass("active");
})();
</script>

      </div>

      <div class="col-md-9">
        <div class="post-header">
   <h1 class="post-title-main">Database Access</h1>
</div>


<div class="post-content">

   
<!-- this handles the automatic toc. use ## for subheads to auto-generate the on-page minitoc. if you use html tags, you must supply an ID for the heading element in order for it to appear in the minitoc. -->
<script>
$( document ).ready(function() {
  // Handler for .ready() called.

$('#toc').toc({ minimumHeaders: 0, listType: 'ul', showSpeed: 0, headers: 'h2,h3,h4' });

/* this offset helps account for the space taken up by the floating toolbar. */
$('#toc').on('click', 'a', function() {
  var target = $(this.getAttribute('href'))
    , scroll_target = target.offset().top

  $(window).scrollTop(scroll_target - 10);
  return false
})
  
});
</script>

<div id="toc"></div>

    
  <p><a href="php_database-objects.html">Database Objects</a> provide a convenient and object-oriented approach to work with the database, but there can be use-cases that require raw access including writing methods for model classes. This section assumes that you have either used <a href="https://en.wikipedia.org/wiki/Prepared_statement">prepared statements</a> before or at least understand how it works.</p>

<h2 id="the-preparedstatement-object">The PreparedStatement Object</h2>

<p>The database access is designed around <a href="https://github.com/WoltLab/WCF/blob/master/wcfsetup/install/files/lib/system/database/statement/PreparedStatement.class.php">PreparedStatement</a>, built on top of PHP’s <code class="language-plaintext highlighter-rouge">PDOStatement</code> so that you call all of <code class="language-plaintext highlighter-rouge">PDOStatement</code>’s methods, and each query requires you to obtain a statement object.</p>

<div class="language-php highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="cp">&lt;?php</span>
<span class="nv">$statement</span> <span class="o">=</span> <span class="nx">\wcf\system\WCF</span><span class="o">::</span><span class="na">getDB</span><span class="p">()</span><span class="o">-&gt;</span><span class="na">prepareStatement</span><span class="p">(</span><span class="s2">"SELECT * FROM wcf"</span><span class="o">.</span><span class="nx">WCF_N</span><span class="o">.</span><span class="s2">"_example"</span><span class="p">);</span>
<span class="nv">$statement</span><span class="o">-&gt;</span><span class="na">execute</span><span class="p">();</span>
<span class="k">while</span> <span class="p">(</span><span class="nv">$row</span> <span class="o">=</span> <span class="nv">$statement</span><span class="o">-&gt;</span><span class="na">fetchArray</span><span class="p">())</span> <span class="p">{</span>
    <span class="c1">// handle result</span>
<span class="p">}</span>
</code></pre></div></div>

<h3 id="query-parameters">Query Parameters</h3>

<p>The example below illustrates the usage of parameters where each value is replaced with the generic <code class="language-plaintext highlighter-rouge">?</code>-placeholder. Values are provided by calling <code class="language-plaintext highlighter-rouge">$statement-&gt;execute()</code> with a continuous, one-dimensional array that exactly match the number of question marks.</p>

<div class="language-php highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="cp">&lt;?php</span>
<span class="nv">$sql</span> <span class="o">=</span> <span class="s2">"SELECT  *
        FROM    wcf"</span><span class="o">.</span><span class="nx">WCF_N</span><span class="o">.</span><span class="s2">"_example
        WHERE   exampleID = ?
                OR bar IN (?, ?, ?, ?, ?)"</span><span class="p">;</span>
<span class="nv">$statement</span> <span class="o">=</span> <span class="nx">\wcf\system\WCF</span><span class="o">::</span><span class="na">getDB</span><span class="p">()</span><span class="o">-&gt;</span><span class="na">prepareStatement</span><span class="p">(</span><span class="nv">$sql</span><span class="p">);</span>
<span class="nv">$statement</span><span class="o">-&gt;</span><span class="na">execute</span><span class="p">([</span>
    <span class="nv">$exampleID</span><span class="p">,</span>
    <span class="nv">$list</span><span class="p">,</span> <span class="nv">$of</span><span class="p">,</span> <span class="nv">$values</span><span class="p">,</span> <span class="nv">$for</span><span class="p">,</span> <span class="nv">$bar</span>
<span class="p">]);</span>
<span class="k">while</span> <span class="p">(</span><span class="nv">$row</span> <span class="o">=</span> <span class="nv">$statement</span><span class="o">-&gt;</span><span class="na">fetchArray</span><span class="p">())</span> <span class="p">{</span>
    <span class="c1">// handle result</span>
<span class="p">}</span>
</code></pre></div></div>

<h3 id="fetching-a-single-result">Fetching a Single Result</h3>

<div class="bs-callout bs-callout-danger">Do not attempt to use <code class="language-plaintext highlighter-rouge">fetchSingleRow()</code> or <code class="language-plaintext highlighter-rouge">fetchSingleColumn()</code> if the result contains more than one row.</div>

<p>You can opt-in to retrieve only a single row from database and make use of shortcut methods to reduce the code that you have to write.</p>

<div class="language-php highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="cp">&lt;?php</span>
<span class="nv">$sql</span> <span class="o">=</span> <span class="s2">"SELECT  *
        FROM    wcf"</span><span class="o">.</span><span class="nx">WCF_N</span><span class="o">.</span><span class="s2">"_example
        WHERE   exampleID = ?"</span><span class="p">;</span>
<span class="nv">$statement</span> <span class="o">=</span> <span class="nx">\wcf\system\WCF</span><span class="o">::</span><span class="na">getDB</span><span class="p">()</span><span class="o">-&gt;</span><span class="na">prepareStatement</span><span class="p">(</span><span class="nv">$sql</span><span class="p">,</span> <span class="mi">1</span><span class="p">);</span>
<span class="nv">$statement</span><span class="o">-&gt;</span><span class="na">execute</span><span class="p">([</span><span class="nv">$exampleID</span><span class="p">]);</span>
<span class="nv">$row</span> <span class="o">=</span> <span class="nv">$statement</span><span class="o">-&gt;</span><span class="na">fetchSingleRow</span><span class="p">();</span>
</code></pre></div></div>

<p>There are two distinct differences when comparing with the example on query parameters above:</p>

<ol>
  <li>The method <code class="language-plaintext highlighter-rouge">prepareStatement()</code> receives a secondary parameter that will be appended to the query as <code class="language-plaintext highlighter-rouge">LIMIT 1</code>.</li>
  <li>Data is read using <code class="language-plaintext highlighter-rouge">fetchSingleRow()</code> instead of <code class="language-plaintext highlighter-rouge">fetchArray()</code> or similar methods, that will read one result and close the cursor.</li>
</ol>

<h3 id="fetch-by-column">Fetch by Column</h3>

<div class="bs-callout bs-callout-warning">There is no way to return another column from the same row if you use <code class="language-plaintext highlighter-rouge">fetchColumn()</code> to retrieve data.</div>

<p>Fetching an array is only useful if there is going to be more than one column per result row, otherwise accessing the column directly is much more convenient and increases the code readability.</p>

<div class="language-php highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="cp">&lt;?php</span>
<span class="nv">$sql</span> <span class="o">=</span> <span class="s2">"SELECT  bar
        FROM    wcf"</span><span class="o">.</span><span class="nx">WCF_N</span><span class="o">.</span><span class="s2">"_example"</span><span class="p">;</span>
<span class="nv">$statement</span> <span class="o">=</span> <span class="nx">\wcf\system\WCF</span><span class="o">::</span><span class="na">getDB</span><span class="p">()</span><span class="o">-&gt;</span><span class="na">prepareStatement</span><span class="p">(</span><span class="nv">$sql</span><span class="p">);</span>
<span class="nv">$statement</span><span class="o">-&gt;</span><span class="na">execute</span><span class="p">();</span>
<span class="k">while</span> <span class="p">(</span><span class="nv">$bar</span> <span class="o">=</span> <span class="nv">$statement</span><span class="o">-&gt;</span><span class="na">fetchColumn</span><span class="p">())</span> <span class="p">{</span>
    <span class="c1">// handle result</span>
<span class="p">}</span>
<span class="nv">$bar</span> <span class="o">=</span> <span class="nv">$statement</span><span class="o">-&gt;</span><span class="na">fetchSingleColumn</span><span class="p">();</span>
</code></pre></div></div>

<p>Similar to fetching a single row, you can also issue a query that will select a single row, but reads only one column from the result row.</p>

<div class="language-php highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="cp">&lt;?php</span>
<span class="nv">$sql</span> <span class="o">=</span> <span class="s2">"SELECT  bar
        FROM    wcf"</span><span class="o">.</span><span class="nx">WCF_N</span><span class="o">.</span><span class="s2">"_example
        WHERE   exampleID = ?"</span><span class="p">;</span>
<span class="nv">$statement</span> <span class="o">=</span> <span class="nx">\wcf\system\WCF</span><span class="o">::</span><span class="na">getDB</span><span class="p">()</span><span class="o">-&gt;</span><span class="na">prepareStatement</span><span class="p">(</span><span class="nv">$sql</span><span class="p">,</span> <span class="mi">1</span><span class="p">);</span>
<span class="nv">$statement</span><span class="o">-&gt;</span><span class="na">execute</span><span class="p">([</span><span class="nv">$exampleID</span><span class="p">]);</span>
<span class="nv">$bar</span> <span class="o">=</span> <span class="nv">$statement</span><span class="o">-&gt;</span><span class="na">fetchSingleColumn</span><span class="p">();</span>
</code></pre></div></div>

<h3 id="fetching-all-results">Fetching All Results</h3>

<p>If you want to fetch all results of a query but only store them in an array without directly processing them, in most cases, you can rely on built-in methods.</p>

<p>To fetch all rows of query, you can use <code class="language-plaintext highlighter-rouge">PDOStatement::fetchAll()</code> with <code class="language-plaintext highlighter-rouge">\PDO::FETCH_ASSOC</code> as the first parameter:</p>

<div class="language-php highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="cp">&lt;?php</span>
<span class="nv">$sql</span> <span class="o">=</span> <span class="s2">"SELECT  *
        FROM    wcf"</span><span class="o">.</span><span class="nx">WCF_N</span><span class="o">.</span><span class="s2">"_example"</span><span class="p">;</span>
<span class="nv">$statement</span> <span class="o">=</span> <span class="nx">\wcf\system\WCF</span><span class="o">::</span><span class="na">getDB</span><span class="p">()</span><span class="o">-&gt;</span><span class="na">prepareStatement</span><span class="p">(</span><span class="nv">$sql</span><span class="p">);</span>
<span class="nv">$statement</span><span class="o">-&gt;</span><span class="na">execute</span><span class="p">();</span>
<span class="nv">$rows</span> <span class="o">=</span> <span class="nv">$statement</span><span class="o">-&gt;</span><span class="na">fetchAll</span><span class="p">(</span><span class="nx">\PDO</span><span class="o">::</span><span class="na">FETCH_ASSOC</span><span class="p">);</span>
</code></pre></div></div>

<p>As a result, you get an array containing associative arrays with the rows of the <code class="language-plaintext highlighter-rouge">wcf{WCF_N}_example</code> database table as content.</p>

<p>If you only want to fetch a list of the values of a certain column, you can use <code class="language-plaintext highlighter-rouge">\PDO::FETCH_COLUMN</code> as the first parameter:</p>

<div class="language-php highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="cp">&lt;?php</span>
<span class="nv">$sql</span> <span class="o">=</span> <span class="s2">"SELECT  exampleID
        FROM    wcf"</span><span class="o">.</span><span class="nx">WCF_N</span><span class="o">.</span><span class="s2">"_example"</span><span class="p">;</span>
<span class="nv">$statement</span> <span class="o">=</span> <span class="nx">\wcf\system\WCF</span><span class="o">::</span><span class="na">getDB</span><span class="p">()</span><span class="o">-&gt;</span><span class="na">prepareStatement</span><span class="p">(</span><span class="nv">$sql</span><span class="p">);</span>
<span class="nv">$statement</span><span class="o">-&gt;</span><span class="na">execute</span><span class="p">();</span>
<span class="nv">$exampleIDs</span> <span class="o">=</span> <span class="nv">$statement</span><span class="o">-&gt;</span><span class="na">fetchAll</span><span class="p">(</span><span class="nx">\PDO</span><span class="o">::</span><span class="na">FETCH_COLUMN</span><span class="p">);</span>
</code></pre></div></div>

<p>As a result, you get an array with all <code class="language-plaintext highlighter-rouge">exampleID</code> values.</p>

<p>The <code class="language-plaintext highlighter-rouge">PreparedStatement</code> class adds an additional methods that covers another common use case in our code:
Fetching two columns and using the first column’s value as the array key and the second column’s value as the array value.
This case is covered by <code class="language-plaintext highlighter-rouge">PreparedStatement::fetchMap()</code>:</p>

<div class="language-php highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="cp">&lt;?php</span>
<span class="nv">$sql</span> <span class="o">=</span> <span class="s2">"SELECT  exampleID, userID
        FROM    wcf"</span><span class="o">.</span><span class="nx">WCF_N</span><span class="o">.</span><span class="s2">"_example_mapping"</span><span class="p">;</span>
<span class="nv">$statement</span> <span class="o">=</span> <span class="nx">\wcf\system\WCF</span><span class="o">::</span><span class="na">getDB</span><span class="p">()</span><span class="o">-&gt;</span><span class="na">prepareStatement</span><span class="p">(</span><span class="nv">$sql</span><span class="p">);</span>
<span class="nv">$statement</span><span class="o">-&gt;</span><span class="na">execute</span><span class="p">();</span>
<span class="nv">$map</span> <span class="o">=</span> <span class="nv">$statement</span><span class="o">-&gt;</span><span class="na">fetchMap</span><span class="p">(</span><span class="s1">'exampleID'</span><span class="p">,</span> <span class="s1">'userID'</span><span class="p">);</span>
</code></pre></div></div>

<p><code class="language-plaintext highlighter-rouge">$map</code> is a one-dimensional array where each <code class="language-plaintext highlighter-rouge">exampleID</code> value maps to the corresponding <code class="language-plaintext highlighter-rouge">userID</code> value.</p>

<div class="bs-callout bs-callout-warning">If there are multiple entries for a certain <code class="language-plaintext highlighter-rouge">exampleID</code> value with different <code class="language-plaintext highlighter-rouge">userID</code> values, the existing entry in the array will be overwritten and contain the last read value from the database table. Therefore, this method should generally only be used for unique combinations.</div>

<p>If you do not have a combination of columns with unique pairs of values, but you want to get a list of <code class="language-plaintext highlighter-rouge">userID</code> values with the same <code class="language-plaintext highlighter-rouge">exampleID</code>, you can set the third parameter of <code class="language-plaintext highlighter-rouge">fetchMap()</code> to <code class="language-plaintext highlighter-rouge">false</code> and get a list:</p>

<div class="language-php highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="cp">&lt;?php</span>
<span class="nv">$sql</span> <span class="o">=</span> <span class="s2">"SELECT  exampleID, userID
        FROM    wcf"</span><span class="o">.</span><span class="nx">WCF_N</span><span class="o">.</span><span class="s2">"_example_mapping"</span><span class="p">;</span>
<span class="nv">$statement</span> <span class="o">=</span> <span class="nx">\wcf\system\WCF</span><span class="o">::</span><span class="na">getDB</span><span class="p">()</span><span class="o">-&gt;</span><span class="na">prepareStatement</span><span class="p">(</span><span class="nv">$sql</span><span class="p">);</span>
<span class="nv">$statement</span><span class="o">-&gt;</span><span class="na">execute</span><span class="p">();</span>
<span class="nv">$map</span> <span class="o">=</span> <span class="nv">$statement</span><span class="o">-&gt;</span><span class="na">fetchMap</span><span class="p">(</span><span class="s1">'exampleID'</span><span class="p">,</span> <span class="s1">'userID'</span><span class="p">,</span> <span class="kc">false</span><span class="p">);</span>
</code></pre></div></div>

<p>Now, as a result, you get a two-dimensional array with the array keys being the <code class="language-plaintext highlighter-rouge">exampleID</code> values and the array values being arrays with all <code class="language-plaintext highlighter-rouge">userID</code> values from rows with the respective <code class="language-plaintext highlighter-rouge">exampleID</code> value.</p>

<h2 id="building-complex-conditions">Building Complex Conditions</h2>

<p>Building conditional conditions can turn out to be a real mess and it gets even worse with SQL’s <code class="language-plaintext highlighter-rouge">IN (…)</code> which requires as many placeholders as there will be values. The solutions is <code class="language-plaintext highlighter-rouge">PreparedStatementConditionBuilder</code>, a simple but useful helper class with a bulky name, it is also the class used when accessing <code class="language-plaintext highlighter-rouge">DatabaseObjecList::getConditionBuilder()</code>.</p>

<div class="language-php highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="cp">&lt;?php</span>
<span class="nv">$conditions</span> <span class="o">=</span> <span class="k">new</span> <span class="nx">\wcf\system\database\util\PreparedStatementConditionBuilder</span><span class="p">();</span>
<span class="nv">$conditions</span><span class="o">-&gt;</span><span class="na">add</span><span class="p">(</span><span class="s2">"exampleID = ?"</span><span class="p">,</span> <span class="p">[</span><span class="nv">$exampleID</span><span class="p">]);</span>
<span class="k">if</span> <span class="p">(</span><span class="o">!</span><span class="k">empty</span><span class="p">(</span><span class="nv">$valuesForBar</span><span class="p">))</span> <span class="p">{</span>
    <span class="nv">$conditions</span><span class="o">-&gt;</span><span class="na">add</span><span class="p">(</span><span class="s2">"(bar IN (?) OR baz = ?)"</span><span class="p">,</span> <span class="p">[</span><span class="nv">$valuesForBar</span><span class="p">,</span> <span class="nv">$baz</span><span class="p">]);</span>
<span class="p">}</span>
</code></pre></div></div>

<p>The <code class="language-plaintext highlighter-rouge">IN (?)</code> in the example above is automatically expanded to match the number of items contained in <code class="language-plaintext highlighter-rouge">$valuesForBar</code>. Be aware that the method will generate an invalid query if <code class="language-plaintext highlighter-rouge">$valuesForBar</code> is empty!</p>

<h2 id="insert-or-update-in-bulk">INSERT or UPDATE in Bulk</h2>

<p>Prepared statements not only protect against SQL injection by separating the logical query and the actual data, but also provides the ability to reuse the same query with different values. This leads to a performance improvement as the code does not have to transmit the query with for every data set and only has to parse and analyze the query once.</p>

<div class="language-php highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="cp">&lt;?php</span>
<span class="nv">$data</span> <span class="o">=</span> <span class="p">[</span><span class="s1">'abc'</span><span class="p">,</span> <span class="s1">'def'</span><span class="p">,</span> <span class="s1">'ghi'</span><span class="p">];</span>

<span class="nv">$sql</span> <span class="o">=</span> <span class="s2">"INSERT INTO  wcf"</span><span class="o">.</span><span class="nx">WCF_N</span><span class="o">.</span><span class="s2">"_example
                     (bar)
        VALUES       (?)"</span><span class="p">;</span>
<span class="nv">$statement</span> <span class="o">=</span> <span class="nx">\wcf\system\WCF</span><span class="o">::</span><span class="na">getDB</span><span class="p">()</span><span class="o">-&gt;</span><span class="na">prepareStatement</span><span class="p">(</span><span class="nv">$sql</span><span class="p">);</span>

<span class="nx">\wcf\system\WCF</span><span class="o">::</span><span class="na">getDB</span><span class="p">()</span><span class="o">-&gt;</span><span class="na">beginTransaction</span><span class="p">();</span>
<span class="k">foreach</span> <span class="p">(</span><span class="nv">$data</span> <span class="k">as</span> <span class="nv">$bar</span><span class="p">)</span> <span class="p">{</span>
    <span class="nv">$statement</span><span class="o">-&gt;</span><span class="na">execute</span><span class="p">([</span><span class="nv">$bar</span><span class="p">]);</span>
<span class="p">}</span>
<span class="nx">\wcf\system\WCF</span><span class="o">::</span><span class="na">getDB</span><span class="p">()</span><span class="o">-&gt;</span><span class="na">commitTransaction</span><span class="p">();</span>
</code></pre></div></div>

<p>It is generally advised to wrap bulk operations in a transaction as it allows the database to optimize the process, including fewer I/O operations.</p>

<div class="language-php highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="cp">&lt;?php</span>
<span class="nv">$data</span> <span class="o">=</span> <span class="p">[</span>
    <span class="mi">1</span> <span class="o">=&gt;</span> <span class="s1">'abc'</span><span class="p">,</span>
    <span class="mi">3</span> <span class="o">=&gt;</span> <span class="s1">'def'</span><span class="p">,</span>
    <span class="mi">4</span> <span class="o">=&gt;</span> <span class="s1">'ghi'</span>
<span class="p">];</span>

<span class="nv">$sql</span> <span class="o">=</span> <span class="s2">"UPDATE  wcf"</span><span class="o">.</span><span class="nx">WCF_N</span><span class="o">.</span><span class="s2">"_example
        SET     bar = ?
        WHERE   exampleID = ?"</span><span class="p">;</span>
<span class="nv">$statement</span> <span class="o">=</span> <span class="nx">\wcf\system\WCF</span><span class="o">::</span><span class="na">getDB</span><span class="p">()</span><span class="o">-&gt;</span><span class="na">prepareStatement</span><span class="p">(</span><span class="nv">$sql</span><span class="p">);</span>

<span class="nx">\wcf\system\WCF</span><span class="o">::</span><span class="na">getDB</span><span class="p">()</span><span class="o">-&gt;</span><span class="na">beginTransaction</span><span class="p">();</span>
<span class="k">foreach</span> <span class="p">(</span><span class="nv">$data</span> <span class="k">as</span> <span class="nv">$exampleID</span> <span class="o">=&gt;</span> <span class="nv">$bar</span><span class="p">)</span> <span class="p">{</span>
    <span class="nv">$statement</span><span class="o">-&gt;</span><span class="na">execute</span><span class="p">([</span>
        <span class="nv">$bar</span><span class="p">,</span>
        <span class="nv">$exampleID</span>
    <span class="p">]);</span>
<span class="p">}</span>
<span class="nx">\wcf\system\WCF</span><span class="o">::</span><span class="na">getDB</span><span class="p">()</span><span class="o">-&gt;</span><span class="na">commitTransaction</span><span class="p">();</span>
</code></pre></div></div>


    <div class="tags">
        
    </div>

    
</div>

      </div>
    </div>
  </div>

  <div class="footerBox">
    <div class="container">
      <div class="footerBoxLeft">
        
        <a target="_blank" href="https://github.com/woltlab/woltlab.github.io/blob/master/pages/php/php_database-access.md" class="btn btn-default githubEditButton no_icon" role="button"><i class="fa fa-github fa-lg"></i> Edit on GitHub</a>
        <p>Site last generated: Mar 5, 2021</p>
      </div>
      <div class="footerBoxRight">
        <a class="no_icon" href="https://www.woltlab.com"><img src="https://docs.woltlab.com/5.3/images/woltlab-black.png" srcset="https://docs.woltlab.com/5.3/images/woltlab-black@2x.png 2x" height="40" width="204" alt=""></a>
      </div>
    </div>
  </div>

  <div class="pageFooter">
    <div class="container">
      &copy; 2001 ‐ 2021 <a class="no_icon" href="https://www.woltlab.com">WoltLab GmbH</a>. All rights reserved. | <a class="no_icon" href="https://www.woltlab.com/legal-notice/">Legal Notice</a> | <a class="no_icon" href="https://www.woltlab.com/privacy-policy/">Privacy Policy</a>
    </div>
  </div>
</body>

</html>