[GitHub/WoltLab/woltlab.github.io.git] / latest / php / database-access / index.html


<!doctype html>
<html lang="en" class="no-js">
  <head>
    
      <meta charset="utf-8">
      <meta name="viewport" content="width=device-width,initial-scale=1">
      
      
      <link rel="icon" href="../../assets/default.favicon.ico">
      <meta name="generator" content="mkdocs-1.1.2, mkdocs-material-7.1.0">
    
    
        <title>Database Access - WoltLab Suite Documentation</title>
      
    
      <link rel="stylesheet" href="../../assets/stylesheets/main.33e2939f.min.css">
      
        
        <link rel="stylesheet" href="../../assets/stylesheets/palette.ef6f36e2.min.css">
        
          
          <meta name="theme-color" content="#009485">
        
      
      <link rel="stylesheet" href="../../stylesheets/extra.css">
    
    
  </head>
  
  
    <body dir="ltr" data-md-color-scheme="" data-md-color-primary="teal" data-md-color-accent="">
  
    
    <script>function __prefix(e){return new URL("../..",location).pathname+"."+e}function __get(e,t=localStorage){return JSON.parse(t.getItem(__prefix(e)))}</script>
    
    <input class="md-toggle" data-md-toggle="drawer" type="checkbox" id="__drawer" autocomplete="off">
    <input class="md-toggle" data-md-toggle="search" type="checkbox" id="__search" autocomplete="off">
    <label class="md-overlay" for="__drawer"></label>
    <div data-md-component="skip">
      
        
        <a href="#database-access" class="md-skip">
          Skip to content
        </a>
      
    </div>
    <div data-md-component="announce">
      
        <aside class="md-announce">
          <div class="md-announce__inner md-grid md-typeset">
            
    <a href="https://www.woltlab.com">Back to <strong>woltlab.com</strong></a>

          </div>
        </aside>
      
    </div>
    
      <header class="md-header" data-md-component="header">
  <nav class="md-header__inner md-grid" aria-label="Header">
    <a href="../.." title="WoltLab Suite Documentation" class="md-header__button md-logo" aria-label="WoltLab Suite Documentation" data-md-component="logo">
      
  <img src="../../assets/logo.png" alt="logo">

    </a>
    <label class="md-header__button md-icon" for="__drawer">
      <svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24"><path d="M3 6h18v2H3V6m0 5h18v2H3v-2m0 5h18v2H3v-2z"/></svg>
    </label>
    <div class="md-header__title" data-md-component="header-title">
      <div class="md-header__ellipsis">
        <div class="md-header__topic">
          <span class="md-ellipsis">
            WoltLab Suite Documentation
          </span>
        </div>
        <div class="md-header__topic" data-md-component="header-topic">
          <span class="md-ellipsis">
            
              Database Access
            
          </span>
        </div>
      </div>
    </div>
    
    
      <label class="md-header__button md-icon" for="__search">
        <svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24"><path d="M9.5 3A6.5 6.5 0 0 1 16 9.5c0 1.61-.59 3.09-1.56 4.23l.27.27h.79l5 5-1.5 1.5-5-5v-.79l-.27-.27A6.516 6.516 0 0 1 9.5 16 6.5 6.5 0 0 1 3 9.5 6.5 6.5 0 0 1 9.5 3m0 2C7 5 5 7 5 9.5S7 14 9.5 14 14 12 14 9.5 12 5 9.5 5z"/></svg>
      </label>
      
<div class="md-search" data-md-component="search" role="dialog">
  <label class="md-search__overlay" for="__search"></label>
  <div class="md-search__inner" role="search">
    <form class="md-search__form" name="search">
      <input type="text" class="md-search__input" name="query" aria-label="Search" placeholder="Search" autocapitalize="off" autocorrect="off" autocomplete="off" spellcheck="false" data-md-component="search-query" data-md-state="active" required>
      <label class="md-search__icon md-icon" for="__search">
        <svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24"><path d="M9.5 3A6.5 6.5 0 0 1 16 9.5c0 1.61-.59 3.09-1.56 4.23l.27.27h.79l5 5-1.5 1.5-5-5v-.79l-.27-.27A6.516 6.516 0 0 1 9.5 16 6.5 6.5 0 0 1 3 9.5 6.5 6.5 0 0 1 9.5 3m0 2C7 5 5 7 5 9.5S7 14 9.5 14 14 12 14 9.5 12 5 9.5 5z"/></svg>
        <svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24"><path d="M20 11v2H8l5.5 5.5-1.42 1.42L4.16 12l7.92-7.92L13.5 5.5 8 11h12z"/></svg>
      </label>
      <button type="reset" class="md-search__icon md-icon" aria-label="Clear" tabindex="-1">
        <svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24"><path d="M19 6.41 17.59 5 12 10.59 6.41 5 5 6.41 10.59 12 5 17.59 6.41 19 12 13.41 17.59 19 19 17.59 13.41 12 19 6.41z"/></svg>
      </button>
    </form>
    <div class="md-search__output">
      <div class="md-search__scrollwrap" data-md-scrollfix>
        <div class="md-search-result" data-md-component="search-result">
          <div class="md-search-result__meta">
            Initializing search
          </div>
          <ol class="md-search-result__list"></ol>
        </div>
      </div>
    </div>
  </div>
</div>
    
    
      <div class="md-header__source">
        
<a href="https://github.com/WoltLab/docs.woltlab.com/" title="Go to repository" class="md-source" data-md-component="source">
  <div class="md-source__icon md-icon">
    
    <svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 448 512"><path d="M439.55 236.05 244 40.45a28.87 28.87 0 0 0-40.81 0l-40.66 40.63 51.52 51.52c27.06-9.14 52.68 16.77 43.39 43.68l49.66 49.66c34.23-11.8 61.18 31 35.47 56.69-26.49 26.49-70.21-2.87-56-37.34L240.22 199v121.85c25.3 12.54 22.26 41.85 9.08 55a34.34 34.34 0 0 1-48.55 0c-17.57-17.6-11.07-46.91 11.25-56v-123c-20.8-8.51-24.6-30.74-18.64-45L142.57 101 8.45 235.14a28.86 28.86 0 0 0 0 40.81l195.61 195.6a28.86 28.86 0 0 0 40.8 0l194.69-194.69a28.86 28.86 0 0 0 0-40.81z"/></svg>
  </div>
  <div class="md-source__repository">
    GitHub
  </div>
</a>
      </div>
    
  </nav>
</header>
    
    <div class="md-container" data-md-component="container">
      
      
      <main class="md-main" data-md-component="main">
        <div class="md-main__inner md-grid">
          
            
              <div class="md-sidebar md-sidebar--primary" data-md-component="sidebar" data-md-type="navigation" >
                <div class="md-sidebar__scrollwrap">
                  <div class="md-sidebar__inner">
                    

<nav class="md-nav md-nav--primary" aria-label="Navigation" data-md-level="0">
  <label class="md-nav__title" for="__drawer">
    <a href="../.." title="WoltLab Suite Documentation" class="md-nav__button md-logo" aria-label="WoltLab Suite Documentation" data-md-component="logo">
      
  <img src="../../assets/logo.png" alt="logo">

    </a>
    WoltLab Suite Documentation
  </label>
  
    <div class="md-nav__source">
      
<a href="https://github.com/WoltLab/docs.woltlab.com/" title="Go to repository" class="md-source" data-md-component="source">
  <div class="md-source__icon md-icon">
    
    <svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 448 512"><path d="M439.55 236.05 244 40.45a28.87 28.87 0 0 0-40.81 0l-40.66 40.63 51.52 51.52c27.06-9.14 52.68 16.77 43.39 43.68l49.66 49.66c34.23-11.8 61.18 31 35.47 56.69-26.49 26.49-70.21-2.87-56-37.34L240.22 199v121.85c25.3 12.54 22.26 41.85 9.08 55a34.34 34.34 0 0 1-48.55 0c-17.57-17.6-11.07-46.91 11.25-56v-123c-20.8-8.51-24.6-30.74-18.64-45L142.57 101 8.45 235.14a28.86 28.86 0 0 0 0 40.81l195.61 195.6a28.86 28.86 0 0 0 40.8 0l194.69-194.69a28.86 28.86 0 0 0 0-40.81z"/></svg>
  </div>
  <div class="md-source__repository">
    GitHub
  </div>
</a>
    </div>
  
  <ul class="md-nav__list" data-md-scrollfix>
    
      
    <li class="md-nav__item">
      <a href="../../getting-started/" class="md-nav__link">
        Getting Started
      </a>
    </li>
  

    <li class="md-nav__item md-nav__item--active md-nav__item--nested">
      
      
        <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_2" type="checkbox" id="__nav_2" checked>
      
      <label class="md-nav__link" for="__nav_2">
        PHP API
        <span class="md-nav__icon md-icon"></span>
      </label>
      <nav class="md-nav" aria-label="PHP API" data-md-level="1">
        <label class="md-nav__title" for="__nav_2">
          <span class="md-nav__icon md-icon"></span>
          PHP API
        </label>
        <ul class="md-nav__list" data-md-scrollfix>
          
            
    <li class="md-nav__item">
      <a href="../pages/" class="md-nav__link">
        Pages
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../database-objects/" class="md-nav__link">
        Database Objects
      </a>
    </li>
  

    <li class="md-nav__item md-nav__item--active">
      
      <input class="md-nav__toggle md-toggle" data-md-toggle="toc" type="checkbox" id="__toc">
      
        
        <label class="md-nav__link md-nav__link--active" for="__toc">
          Database Access
          <span class="md-nav__icon md-icon"></span>
        </label>
      
      <a href="./" class="md-nav__link md-nav__link--active">
        Database Access
      </a>
      
        
<nav class="md-nav md-nav--secondary" aria-label="Table of contents">
  
  
    <label class="md-nav__title" for="__toc">
      <span class="md-nav__icon md-icon"></span>
      Table of contents
    </label>
    <ul class="md-nav__list" data-md-component="toc" data-md-scrollfix>
      
        <li class="md-nav__item">
  <a href="#the-preparedstatement-object" class="md-nav__link">
    The PreparedStatement Object
  </a>
  
    <nav class="md-nav" aria-label="The PreparedStatement Object">
      <ul class="md-nav__list">
        
          <li class="md-nav__item">
  <a href="#query-parameters" class="md-nav__link">
    Query Parameters
  </a>
  
</li>
        
          <li class="md-nav__item">
  <a href="#fetching-a-single-result" class="md-nav__link">
    Fetching a Single Result
  </a>
  
</li>
        
          <li class="md-nav__item">
  <a href="#fetch-by-column" class="md-nav__link">
    Fetch by Column
  </a>
  
</li>
        
          <li class="md-nav__item">
  <a href="#fetching-all-results" class="md-nav__link">
    Fetching All Results
  </a>
  
</li>
        
      </ul>
    </nav>
  
</li>
      
        <li class="md-nav__item">
  <a href="#building-complex-conditions" class="md-nav__link">
    Building Complex Conditions
  </a>
  
</li>
      
        <li class="md-nav__item">
  <a href="#insert-or-update-in-bulk" class="md-nav__link">
    INSERT or UPDATE in Bulk
  </a>
  
</li>
      
    </ul>
  
</nav>
      
    </li>
  

    <li class="md-nav__item">
      <a href="../exceptions/" class="md-nav__link">
        Exceptions
      </a>
    </li>
  

    <li class="md-nav__item md-nav__item--nested">
      
      
        <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_2_5" type="checkbox" id="__nav_2_5" >
      
      <label class="md-nav__link" for="__nav_2_5">
        API
        <span class="md-nav__icon md-icon"></span>
      </label>
      <nav class="md-nav" aria-label="API" data-md-level="2">
        <label class="md-nav__title" for="__nav_2_5">
          <span class="md-nav__icon md-icon"></span>
          API
        </label>
        <ul class="md-nav__list" data-md-scrollfix>
          
            
    <li class="md-nav__item md-nav__item--nested">
      
      
        <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_2_5_1" type="checkbox" id="__nav_2_5_1" >
      
      <label class="md-nav__link" for="__nav_2_5_1">
        Caches
        <span class="md-nav__icon md-icon"></span>
      </label>
      <nav class="md-nav" aria-label="Caches" data-md-level="3">
        <label class="md-nav__title" for="__nav_2_5_1">
          <span class="md-nav__icon md-icon"></span>
          Caches
        </label>
        <ul class="md-nav__list" data-md-scrollfix>
          
            
    <li class="md-nav__item">
      <a href="../api/caches/" class="md-nav__link">
        Overview
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../api/caches_persistent-caches/" class="md-nav__link">
        Persistent Caches
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../api/caches_runtime-caches/" class="md-nav__link">
        Runtime Caches
      </a>
    </li>
  

        </ul>
      </nav>
    </li>
  

    <li class="md-nav__item">
      <a href="../api/comments/" class="md-nav__link">
        Comments
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../api/cronjobs/" class="md-nav__link">
        Cronjobs
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../api/events/" class="md-nav__link">
        Events
      </a>
    </li>
  

    <li class="md-nav__item md-nav__item--nested">
      
      
        <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_2_5_5" type="checkbox" id="__nav_2_5_5" >
      
      <label class="md-nav__link" for="__nav_2_5_5">
        Form Builder
        <span class="md-nav__icon md-icon"></span>
      </label>
      <nav class="md-nav" aria-label="Form Builder" data-md-level="3">
        <label class="md-nav__title" for="__nav_2_5_5">
          <span class="md-nav__icon md-icon"></span>
          Form Builder
        </label>
        <ul class="md-nav__list" data-md-scrollfix>
          
            
    <li class="md-nav__item">
      <a href="../api/form_builder/overview/" class="md-nav__link">
        Overview
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../api/form_builder/structure/" class="md-nav__link">
        Structure
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../api/form_builder/form_fields/" class="md-nav__link">
        Fields
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../api/form_builder/validation_data/" class="md-nav__link">
        Validation and Data
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../api/form_builder/dependencies/" class="md-nav__link">
        Dependencies
      </a>
    </li>
  

        </ul>
      </nav>
    </li>
  

    <li class="md-nav__item">
      <a href="../api/package_installation_plugins/" class="md-nav__link">
        Package Installation Plugins
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../api/user_activity_points/" class="md-nav__link">
        User Activity Points
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../api/user_notifications/" class="md-nav__link">
        User Notifications
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../api/sitemaps/" class="md-nav__link">
        Sitemaps
      </a>
    </li>
  

        </ul>
      </nav>
    </li>
  

    <li class="md-nav__item">
      <a href="../code-style/" class="md-nav__link">
        Code Style
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../apps/" class="md-nav__link">
        Apps
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../gdpr/" class="md-nav__link">
        GDPR
      </a>
    </li>
  

        </ul>
      </nav>
    </li>
  

    <li class="md-nav__item md-nav__item--nested">
      
      
        <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_3" type="checkbox" id="__nav_3" >
      
      <label class="md-nav__link" for="__nav_3">
        Languages, Templates & CSS
        <span class="md-nav__icon md-icon"></span>
      </label>
      <nav class="md-nav" aria-label="Languages, Templates & CSS" data-md-level="1">
        <label class="md-nav__title" for="__nav_3">
          <span class="md-nav__icon md-icon"></span>
          Languages, Templates & CSS
        </label>
        <ul class="md-nav__list" data-md-scrollfix>
          
            
    <li class="md-nav__item">
      <a href="../../view/languages/" class="md-nav__link">
        Languages
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../view/templates/" class="md-nav__link">
        Templates
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../view/template-plugins/" class="md-nav__link">
        Template Plugins
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../view/css/" class="md-nav__link">
        CSS
      </a>
    </li>
  

        </ul>
      </nav>
    </li>
  

    <li class="md-nav__item md-nav__item--nested">
      
      
        <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_4" type="checkbox" id="__nav_4" >
      
      <label class="md-nav__link" for="__nav_4">
        TypeScript and JavaScript API
        <span class="md-nav__icon md-icon"></span>
      </label>
      <nav class="md-nav" aria-label="TypeScript and JavaScript API" data-md-level="1">
        <label class="md-nav__title" for="__nav_4">
          <span class="md-nav__icon md-icon"></span>
          TypeScript and JavaScript API
        </label>
        <ul class="md-nav__list" data-md-scrollfix>
          
            
    <li class="md-nav__item">
      <a href="../../javascript/general-usage/" class="md-nav__link">
        General Usage
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../javascript/typescript/" class="md-nav__link">
        TypeScript
      </a>
    </li>
  

    <li class="md-nav__item md-nav__item--nested">
      
      
        <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_4_3" type="checkbox" id="__nav_4_3" >
      
      <label class="md-nav__link" for="__nav_4_3">
        New API
        <span class="md-nav__icon md-icon"></span>
      </label>
      <nav class="md-nav" aria-label="New API" data-md-level="2">
        <label class="md-nav__title" for="__nav_4_3">
          <span class="md-nav__icon md-icon"></span>
          New API
        </label>
        <ul class="md-nav__list" data-md-scrollfix>
          
            
    <li class="md-nav__item">
      <a href="../../javascript/new-api_writing-a-module/" class="md-nav__link">
        Writing a module
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../javascript/new-api_data-structures/" class="md-nav__link">
        Data Structures
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../javascript/new-api_core/" class="md-nav__link">
        Core Functions
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../javascript/new-api_dom/" class="md-nav__link">
        DOM
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../javascript/new-api_events/" class="md-nav__link">
        Event Handling
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../javascript/new-api_ajax/" class="md-nav__link">
        Ajax
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../javascript/new-api_dialogs/" class="md-nav__link">
        Dialogs
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../javascript/new-api_browser/" class="md-nav__link">
        Browser and Screen Sizes
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../javascript/new-api_ui/" class="md-nav__link">
        User Interface
      </a>
    </li>
  

        </ul>
      </nav>
    </li>
  

    <li class="md-nav__item">
      <a href="../../javascript/legacy-api/" class="md-nav__link">
        Legacy API
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../javascript/helper-functions/" class="md-nav__link">
        Helper Functions
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../javascript/code-snippets/" class="md-nav__link">
        Code Snippets
      </a>
    </li>
  

        </ul>
      </nav>
    </li>
  

    <li class="md-nav__item md-nav__item--nested">
      
      
        <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_5" type="checkbox" id="__nav_5" >
      
      <label class="md-nav__link" for="__nav_5">
        Package Components
        <span class="md-nav__icon md-icon"></span>
      </label>
      <nav class="md-nav" aria-label="Package Components" data-md-level="1">
        <label class="md-nav__title" for="__nav_5">
          <span class="md-nav__icon md-icon"></span>
          Package Components
        </label>
        <ul class="md-nav__list" data-md-scrollfix>
          
            
    <li class="md-nav__item">
      <a href="../../package/package-xml/" class="md-nav__link">
        package.xml
      </a>
    </li>
  

    <li class="md-nav__item md-nav__item--nested">
      
      
        <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_5_2" type="checkbox" id="__nav_5_2" >
      
      <label class="md-nav__link" for="__nav_5_2">
        PIPs
        <span class="md-nav__icon md-icon"></span>
      </label>
      <nav class="md-nav" aria-label="PIPs" data-md-level="2">
        <label class="md-nav__title" for="__nav_5_2">
          <span class="md-nav__icon md-icon"></span>
          PIPs
        </label>
        <ul class="md-nav__list" data-md-scrollfix>
          
            
    <li class="md-nav__item">
      <a href="../../package/pip/" class="md-nav__link">
        Overview
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/acl-option/" class="md-nav__link">
        aclOption
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/acp-menu/" class="md-nav__link">
        acpMenu
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/acp-search-provider/" class="md-nav__link">
        acpSearchProvider
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/acp-template/" class="md-nav__link">
        acpTemplate
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/bbcode/" class="md-nav__link">
        bbcode
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/box/" class="md-nav__link">
        box
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/clipboard-action/" class="md-nav__link">
        clipboardAction
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/core-object/" class="md-nav__link">
        coreObject
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/cronjob/" class="md-nav__link">
        cronjob
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/database/" class="md-nav__link">
        database
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/event-listener/" class="md-nav__link">
        eventListener
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/file/" class="md-nav__link">
        file
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/language/" class="md-nav__link">
        language
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/media-provider/" class="md-nav__link">
        mediaProvider
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/menu/" class="md-nav__link">
        menu
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/menu-item/" class="md-nav__link">
        menuItem
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/object-type/" class="md-nav__link">
        objectType
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/object-type-definition/" class="md-nav__link">
        objectTypeDefinition
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/option/" class="md-nav__link">
        option
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/page/" class="md-nav__link">
        page
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/pip/" class="md-nav__link">
        pip
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/script/" class="md-nav__link">
        script
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/smiley/" class="md-nav__link">
        smiley
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/sql/" class="md-nav__link">
        sql
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/style/" class="md-nav__link">
        style
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/template/" class="md-nav__link">
        template
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/template-listener/" class="md-nav__link">
        templateListener
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/user-group-option/" class="md-nav__link">
        userGroupOption
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/user-menu/" class="md-nav__link">
        userMenu
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/user-notification-event/" class="md-nav__link">
        userNotificationEvent
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/user-option/" class="md-nav__link">
        userOption
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/pip/user-profile-menu/" class="md-nav__link">
        userProfileMenu
      </a>
    </li>
  

        </ul>
      </nav>
    </li>
  

    <li class="md-nav__item">
      <a href="../../package/database-php-api/" class="md-nav__link">
        Database PHP API
      </a>
    </li>
  

        </ul>
      </nav>
    </li>
  

    <li class="md-nav__item md-nav__item--nested">
      
      
        <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_6" type="checkbox" id="__nav_6" >
      
      <label class="md-nav__link" for="__nav_6">
        Migration
        <span class="md-nav__icon md-icon"></span>
      </label>
      <nav class="md-nav" aria-label="Migration" data-md-level="1">
        <label class="md-nav__title" for="__nav_6">
          <span class="md-nav__icon md-icon"></span>
          Migration
        </label>
        <ul class="md-nav__list" data-md-scrollfix>
          
            
    <li class="md-nav__item md-nav__item--nested">
      
      
        <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_6_1" type="checkbox" id="__nav_6_1" >
      
      <label class="md-nav__link" for="__nav_6_1">
        Migrating from WSC 5.3
        <span class="md-nav__icon md-icon"></span>
      </label>
      <nav class="md-nav" aria-label="Migrating from WSC 5.3" data-md-level="2">
        <label class="md-nav__title" for="__nav_6_1">
          <span class="md-nav__icon md-icon"></span>
          Migrating from WSC 5.3
        </label>
        <ul class="md-nav__list" data-md-scrollfix>
          
            
    <li class="md-nav__item">
      <a href="../../migration/wsc53/php/" class="md-nav__link">
        PHP API
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../migration/wsc53/session/" class="md-nav__link">
        Session Handling and Authentication
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../migration/wsc53/javascript/" class="md-nav__link">
        TypeScript and JavaScript
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../migration/wsc53/templates/" class="md-nav__link">
        Templates
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../migration/wsc53/libraries/" class="md-nav__link">
        Third Party Libraries
      </a>
    </li>
  

        </ul>
      </nav>
    </li>
  

    <li class="md-nav__item md-nav__item--nested">
      
      
        <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_6_2" type="checkbox" id="__nav_6_2" >
      
      <label class="md-nav__link" for="__nav_6_2">
        Migrating from WSC 5.2
        <span class="md-nav__icon md-icon"></span>
      </label>
      <nav class="md-nav" aria-label="Migrating from WSC 5.2" data-md-level="2">
        <label class="md-nav__title" for="__nav_6_2">
          <span class="md-nav__icon md-icon"></span>
          Migrating from WSC 5.2
        </label>
        <ul class="md-nav__list" data-md-scrollfix>
          
            
    <li class="md-nav__item">
      <a href="../../migration/wsc52/php/" class="md-nav__link">
        PHP API
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../migration/wsc52/templates/" class="md-nav__link">
        Templates and Languages
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../migration/wsc52/libraries/" class="md-nav__link">
        Third Party Libraries
      </a>
    </li>
  

        </ul>
      </nav>
    </li>
  

    <li class="md-nav__item md-nav__item--nested">
      
      
        <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_6_3" type="checkbox" id="__nav_6_3" >
      
      <label class="md-nav__link" for="__nav_6_3">
        Migrating from WSC 3.1
        <span class="md-nav__icon md-icon"></span>
      </label>
      <nav class="md-nav" aria-label="Migrating from WSC 3.1" data-md-level="2">
        <label class="md-nav__title" for="__nav_6_3">
          <span class="md-nav__icon md-icon"></span>
          Migrating from WSC 3.1
        </label>
        <ul class="md-nav__list" data-md-scrollfix>
          
            
    <li class="md-nav__item">
      <a href="../../migration/wsc31/php/" class="md-nav__link">
        PHP API
      </a>
    </li>
  

        </ul>
      </nav>
    </li>
  

    <li class="md-nav__item md-nav__item--nested">
      
      
        <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_6_4" type="checkbox" id="__nav_6_4" >
      
      <label class="md-nav__link" for="__nav_6_4">
        Migrating from WSC 3.0
        <span class="md-nav__icon md-icon"></span>
      </label>
      <nav class="md-nav" aria-label="Migrating from WSC 3.0" data-md-level="2">
        <label class="md-nav__title" for="__nav_6_4">
          <span class="md-nav__icon md-icon"></span>
          Migrating from WSC 3.0
        </label>
        <ul class="md-nav__list" data-md-scrollfix>
          
            
    <li class="md-nav__item">
      <a href="../../migration/wsc30/php/" class="md-nav__link">
        PHP API
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../migration/wsc30/javascript/" class="md-nav__link">
        JavaScript API
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../migration/wsc30/templates/" class="md-nav__link">
        Templates
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../migration/wsc30/css/" class="md-nav__link">
        CSS
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../migration/wsc30/package/" class="md-nav__link">
        Package Components
      </a>
    </li>
  

        </ul>
      </nav>
    </li>
  

    <li class="md-nav__item md-nav__item--nested">
      
      
        <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_6_5" type="checkbox" id="__nav_6_5" >
      
      <label class="md-nav__link" for="__nav_6_5">
        Migrating from WCF 2.1
        <span class="md-nav__icon md-icon"></span>
      </label>
      <nav class="md-nav" aria-label="Migrating from WCF 2.1" data-md-level="2">
        <label class="md-nav__title" for="__nav_6_5">
          <span class="md-nav__icon md-icon"></span>
          Migrating from WCF 2.1
        </label>
        <ul class="md-nav__list" data-md-scrollfix>
          
            
    <li class="md-nav__item">
      <a href="../../migration/wcf21/php/" class="md-nav__link">
        PHP API
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../migration/wcf21/templates/" class="md-nav__link">
        Templates
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../migration/wcf21/css/" class="md-nav__link">
        CSS
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../migration/wcf21/package/" class="md-nav__link">
        Package Components
      </a>
    </li>
  

        </ul>
      </nav>
    </li>
  

        </ul>
      </nav>
    </li>
  

    <li class="md-nav__item md-nav__item--nested">
      
      
        <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_7" type="checkbox" id="__nav_7" >
      
      <label class="md-nav__link" for="__nav_7">
        Tutorials
        <span class="md-nav__icon md-icon"></span>
      </label>
      <nav class="md-nav" aria-label="Tutorials" data-md-level="1">
        <label class="md-nav__title" for="__nav_7">
          <span class="md-nav__icon md-icon"></span>
          Tutorials
        </label>
        <ul class="md-nav__list" data-md-scrollfix>
          
            
    <li class="md-nav__item md-nav__item--nested">
      
      
        <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_7_1" type="checkbox" id="__nav_7_1" >
      
      <label class="md-nav__link" for="__nav_7_1">
        Tutorial Series
        <span class="md-nav__icon md-icon"></span>
      </label>
      <nav class="md-nav" aria-label="Tutorial Series" data-md-level="2">
        <label class="md-nav__title" for="__nav_7_1">
          <span class="md-nav__icon md-icon"></span>
          Tutorial Series
        </label>
        <ul class="md-nav__list" data-md-scrollfix>
          
            
    <li class="md-nav__item">
      <a href="../../tutorial/series/overview/" class="md-nav__link">
        Overview
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../tutorial/series/part_1/" class="md-nav__link">
        Part 1
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../tutorial/series/part_2/" class="md-nav__link">
        Part 2
      </a>
    </li>
  

    <li class="md-nav__item">
      <a href="../../tutorial/series/part_3/" class="md-nav__link">
        Part 3
      </a>
    </li>
  

        </ul>
      </nav>
    </li>
  

        </ul>
      </nav>
    </li>
  

  </ul>
</nav>
                  </div>
                </div>
              </div>
            
            
              <div class="md-sidebar md-sidebar--secondary" data-md-component="sidebar" data-md-type="toc" >
                <div class="md-sidebar__scrollwrap">
                  <div class="md-sidebar__inner">
                    
<nav class="md-nav md-nav--secondary" aria-label="Table of contents">
  
  
    <label class="md-nav__title" for="__toc">
      <span class="md-nav__icon md-icon"></span>
      Table of contents
    </label>
    <ul class="md-nav__list" data-md-component="toc" data-md-scrollfix>
      
        <li class="md-nav__item">
  <a href="#the-preparedstatement-object" class="md-nav__link">
    The PreparedStatement Object
  </a>
  
    <nav class="md-nav" aria-label="The PreparedStatement Object">
      <ul class="md-nav__list">
        
          <li class="md-nav__item">
  <a href="#query-parameters" class="md-nav__link">
    Query Parameters
  </a>
  
</li>
        
          <li class="md-nav__item">
  <a href="#fetching-a-single-result" class="md-nav__link">
    Fetching a Single Result
  </a>
  
</li>
        
          <li class="md-nav__item">
  <a href="#fetch-by-column" class="md-nav__link">
    Fetch by Column
  </a>
  
</li>
        
          <li class="md-nav__item">
  <a href="#fetching-all-results" class="md-nav__link">
    Fetching All Results
  </a>
  
</li>
        
      </ul>
    </nav>
  
</li>
      
        <li class="md-nav__item">
  <a href="#building-complex-conditions" class="md-nav__link">
    Building Complex Conditions
  </a>
  
</li>
      
        <li class="md-nav__item">
  <a href="#insert-or-update-in-bulk" class="md-nav__link">
    INSERT or UPDATE in Bulk
  </a>
  
</li>
      
    </ul>
  
</nav>
                  </div>
                </div>
              </div>
            
          
          <div class="md-content" data-md-component="content">
            <article class="md-content__inner md-typeset">
              
                
                  <a href="https://github.com/WoltLab/docs.woltlab.com/edit/5.4/docs/php/database-access.md" title="Edit this page" class="md-content__button md-icon">
                    <svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24"><path d="M20.71 7.04c.39-.39.39-1.04 0-1.41l-2.34-2.34c-.37-.39-1.02-.39-1.41 0l-1.84 1.83 3.75 3.75M3 17.25V21h3.75L17.81 9.93l-3.75-3.75L3 17.25z"/></svg>
                  </a>
                
                
                <h1 id="database-access">Database Access<a class="headerlink" href="#database-access" title="Permanent link">#</a></h1>
<p><a href="../database-objects/">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<a class="headerlink" href="#the-preparedstatement-object" title="Permanent link">#</a></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>PDOStatement</code> so that you call all of <code>PDOStatement</code>'s methods, and each query requires you to obtain a statement object.</p>
<div class="highlight"><pre><span></span><code><span class="o">&lt;?</span><span class="nx">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">&quot;SELECT * FROM wcf&quot;</span><span class="o">.</span><span class="nx">WCF_N</span><span class="o">.</span><span class="s2">&quot;_example&quot;</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>
<h3 id="query-parameters">Query Parameters<a class="headerlink" href="#query-parameters" title="Permanent link">#</a></h3>
<p>The example below illustrates the usage of parameters where each value is replaced with the generic <code>?</code>-placeholder. Values are provided by calling <code>$statement-&gt;execute()</code> with a continuous, one-dimensional array that exactly match the number of question marks.</p>
<div class="highlight"><pre><span></span><code><span class="o">&lt;?</span><span class="nx">php</span>
<span class="nv">$sql</span> <span class="o">=</span> <span class="s2">&quot;SELECT  *</span>
<span class="s2">        FROM    wcf&quot;</span><span class="o">.</span><span class="nx">WCF_N</span><span class="o">.</span><span class="s2">&quot;_example</span>
<span class="s2">        WHERE   exampleID = ?</span>
<span class="s2">                OR bar IN (?, ?, ?, ?, ?)&quot;</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>
<h3 id="fetching-a-single-result">Fetching a Single Result<a class="headerlink" href="#fetching-a-single-result" title="Permanent link">#</a></h3>
<div class="admonition danger">
<p class="admonition-title">Do not attempt to use <code>fetchSingleRow()</code> or <code>fetchSingleColumn()</code> if the result contains more than one row.</p>
</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="highlight"><pre><span></span><code><span class="o">&lt;?</span><span class="nx">php</span>
<span class="nv">$sql</span> <span class="o">=</span> <span class="s2">&quot;SELECT  *</span>
<span class="s2">        FROM    wcf&quot;</span><span class="o">.</span><span class="nx">WCF_N</span><span class="o">.</span><span class="s2">&quot;_example</span>
<span class="s2">        WHERE   exampleID = ?&quot;</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>
<p>There are two distinct differences when comparing with the example on query parameters above:</p>
<ol>
<li>The method <code>prepareStatement()</code> receives a secondary parameter that will be appended to the query as <code>LIMIT 1</code>.</li>
<li>Data is read using <code>fetchSingleRow()</code> instead of <code>fetchArray()</code> or similar methods, that will read one result and close the cursor.</li>
</ol>
<h3 id="fetch-by-column">Fetch by Column<a class="headerlink" href="#fetch-by-column" title="Permanent link">#</a></h3>
<div class="admonition warning">
<p class="admonition-title">There is no way to return another column from the same row if you use <code>fetchColumn()</code> to retrieve data.</p>
</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="highlight"><pre><span></span><code><span class="o">&lt;?</span><span class="nx">php</span>
<span class="nv">$sql</span> <span class="o">=</span> <span class="s2">&quot;SELECT  bar</span>
<span class="s2">        FROM    wcf&quot;</span><span class="o">.</span><span class="nx">WCF_N</span><span class="o">.</span><span class="s2">&quot;_example&quot;</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>
<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="highlight"><pre><span></span><code><span class="o">&lt;?</span><span class="nx">php</span>
<span class="nv">$sql</span> <span class="o">=</span> <span class="s2">&quot;SELECT  bar</span>
<span class="s2">        FROM    wcf&quot;</span><span class="o">.</span><span class="nx">WCF_N</span><span class="o">.</span><span class="s2">&quot;_example</span>
<span class="s2">        WHERE   exampleID = ?&quot;</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>
<h3 id="fetching-all-results">Fetching All Results<a class="headerlink" href="#fetching-all-results" title="Permanent link">#</a></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>PDOStatement::fetchAll()</code> with <code>\PDO::FETCH_ASSOC</code> as the first parameter:</p>
<div class="highlight"><pre><span></span><code><span class="o">&lt;?</span><span class="nx">php</span>
<span class="nv">$sql</span> <span class="o">=</span> <span class="s2">&quot;SELECT  *</span>
<span class="s2">        FROM    wcf&quot;</span><span class="o">.</span><span class="nx">WCF_N</span><span class="o">.</span><span class="s2">&quot;_example&quot;</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>
<p>As a result, you get an array containing associative arrays with the rows of the <code>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>\PDO::FETCH_COLUMN</code> as the first parameter:</p>
<div class="highlight"><pre><span></span><code><span class="o">&lt;?</span><span class="nx">php</span>
<span class="nv">$sql</span> <span class="o">=</span> <span class="s2">&quot;SELECT  exampleID</span>
<span class="s2">        FROM    wcf&quot;</span><span class="o">.</span><span class="nx">WCF_N</span><span class="o">.</span><span class="s2">&quot;_example&quot;</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>
<p>As a result, you get an array with all <code>exampleID</code> values.</p>
<p>The <code>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>PreparedStatement::fetchMap()</code>:</p>
<div class="highlight"><pre><span></span><code><span class="o">&lt;?</span><span class="nx">php</span>
<span class="nv">$sql</span> <span class="o">=</span> <span class="s2">&quot;SELECT  exampleID, userID</span>
<span class="s2">        FROM    wcf&quot;</span><span class="o">.</span><span class="nx">WCF_N</span><span class="o">.</span><span class="s2">&quot;_example_mapping&quot;</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">&#39;exampleID&#39;</span><span class="p">,</span> <span class="s1">&#39;userID&#39;</span><span class="p">);</span>
</code></pre></div>
<p><code>$map</code> is a one-dimensional array where each <code>exampleID</code> value maps to the corresponding <code>userID</code> value.</p>
<div class="admonition warning">
<p class="admonition-title">If there are multiple entries for a certain <code>exampleID</code> value with different <code>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.</p>
</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>userID</code> values with the same <code>exampleID</code>, you can set the third parameter of <code>fetchMap()</code> to <code>false</code> and get a list:</p>
<div class="highlight"><pre><span></span><code><span class="o">&lt;?</span><span class="nx">php</span>
<span class="nv">$sql</span> <span class="o">=</span> <span class="s2">&quot;SELECT  exampleID, userID</span>
<span class="s2">        FROM    wcf&quot;</span><span class="o">.</span><span class="nx">WCF_N</span><span class="o">.</span><span class="s2">&quot;_example_mapping&quot;</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">&#39;exampleID&#39;</span><span class="p">,</span> <span class="s1">&#39;userID&#39;</span><span class="p">,</span> <span class="k">false</span><span class="p">);</span>
</code></pre></div>
<p>Now, as a result, you get a two-dimensional array with the array keys being the <code>exampleID</code> values and the array values being arrays with all <code>userID</code> values from rows with the respective <code>exampleID</code> value.</p>
<h2 id="building-complex-conditions">Building Complex Conditions<a class="headerlink" href="#building-complex-conditions" title="Permanent link">#</a></h2>
<p>Building conditional conditions can turn out to be a real mess and it gets even worse with SQL's <code>IN (…)</code> which requires as many placeholders as there will be values. The solutions is <code>PreparedStatementConditionBuilder</code>, a simple but useful helper class with a bulky name, it is also the class used when accessing <code>DatabaseObjecList::getConditionBuilder()</code>.</p>
<div class="highlight"><pre><span></span><code><span class="o">&lt;?</span><span class="nx">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">&quot;exampleID = ?&quot;</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">&quot;(bar IN (?) OR baz = ?)&quot;</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>
<p>The <code>IN (?)</code> in the example above is automatically expanded to match the number of items contained in <code>$valuesForBar</code>. Be aware that the method will generate an invalid query if <code>$valuesForBar</code> is empty!</p>
<h2 id="insert-or-update-in-bulk">INSERT or UPDATE in Bulk<a class="headerlink" href="#insert-or-update-in-bulk" title="Permanent link">#</a></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="highlight"><pre><span></span><code><span class="o">&lt;?</span><span class="nx">php</span>
<span class="nv">$data</span> <span class="o">=</span> <span class="p">[</span><span class="s1">&#39;abc&#39;</span><span class="p">,</span> <span class="s1">&#39;def&#39;</span><span class="p">,</span> <span class="s1">&#39;ghi&#39;</span><span class="p">];</span>

<span class="nv">$sql</span> <span class="o">=</span> <span class="s2">&quot;INSERT INTO  wcf&quot;</span><span class="o">.</span><span class="nx">WCF_N</span><span class="o">.</span><span class="s2">&quot;_example</span>
<span class="s2">                     (bar)</span>
<span class="s2">        VALUES       (?)&quot;</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>
<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="highlight"><pre><span></span><code><span class="o">&lt;?</span><span class="nx">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">&#39;abc&#39;</span><span class="p">,</span>
    <span class="mi">3</span> <span class="o">=&gt;</span> <span class="s1">&#39;def&#39;</span><span class="p">,</span>
    <span class="mi">4</span> <span class="o">=&gt;</span> <span class="s1">&#39;ghi&#39;</span>
<span class="p">];</span>

<span class="nv">$sql</span> <span class="o">=</span> <span class="s2">&quot;UPDATE  wcf&quot;</span><span class="o">.</span><span class="nx">WCF_N</span><span class="o">.</span><span class="s2">&quot;_example</span>
<span class="s2">        SET     bar = ?</span>
<span class="s2">        WHERE   exampleID = ?&quot;</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>
                
                  
<hr>
<div class="md-source-date">
  <small>
    
      Last update: 2021-01-08
    
  </small>
</div>
                  
                
            </article>
          </div>
        </div>
        
      </main>
      
        
<footer class="md-footer">
  
    <nav class="md-footer__inner md-grid" aria-label="Footer">
      
        <a href="../database-objects/" class="md-footer__link md-footer__link--prev" rel="prev">
          <div class="md-footer__button md-icon">
            <svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24"><path d="M20 11v2H8l5.5 5.5-1.42 1.42L4.16 12l7.92-7.92L13.5 5.5 8 11h12z"/></svg>
          </div>
          <div class="md-footer__title">
            <div class="md-ellipsis">
              <span class="md-footer__direction">
                Previous
              </span>
              Database Objects
            </div>
          </div>
        </a>
      
      
        <a href="../exceptions/" class="md-footer__link md-footer__link--next" rel="next">
          <div class="md-footer__title">
            <div class="md-ellipsis">
              <span class="md-footer__direction">
                Next
              </span>
              Exceptions
            </div>
          </div>
          <div class="md-footer__button md-icon">
            <svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24"><path d="M4 11v2h12l-5.5 5.5 1.42 1.42L19.84 12l-7.92-7.92L10.5 5.5 16 11H4z"/></svg>
          </div>
        </a>
      
    </nav>
  
  <div class="md-footer-meta md-typeset">
    <div class="md-footer-meta__inner md-grid">
      <div class="md-footer-copyright">
        
          <div class="md-footer-copyright__highlight">
            Copyright © 2020 WoltLab GmbH
          </div>
        
        Made with
        <a href="https://squidfunk.github.io/mkdocs-material/" target="_blank" rel="noopener">
          Material for MkDocs
        </a>
        
      </div>
      <div class="md-footer-copyright">
	<a href="https://www.woltlab.com/legal-notice/">Legal Notice</a> 
	<a href="https://www.woltlab.com/privacy-policy/">Privacy Policy</a>
</div>
    </div>
  </div>
</footer>
      
    </div>
    <div class="md-dialog" data-md-component="dialog">
      <div class="md-dialog__inner md-typeset"></div>
    </div>
    <script id="__config" type="application/json">{"base": "../..", "features": [], "translations": {"clipboard.copy": "Copy to clipboard", "clipboard.copied": "Copied to clipboard", "search.config.lang": "en", "search.config.pipeline": "trimmer, stopWordFilter", "search.config.separator": "[\\s\\-]+", "search.placeholder": "Search", "search.result.placeholder": "Type to start searching", "search.result.none": "No matching documents", "search.result.one": "1 matching document", "search.result.other": "# matching documents", "search.result.more.one": "1 more on this page", "search.result.more.other": "# more on this page", "search.result.term.missing": "Missing"}, "search": "../../assets/javascripts/workers/search.fe42c31b.min.js", "version": {"provider": "mike"}}</script>
    
    
      <script src="../../assets/javascripts/bundle.d892486b.min.js"></script>
      
    
  </body>
</html>