latest/php/database-access/index.html

   1
   2 <!doctype html>
   3 <html lang="en" class="no-js">
   4   <head>
   5
   6       <meta charset="utf-8">
   7       <meta name="viewport" content="width=device-width,initial-scale=1">
   8
   9
  10
  11
  12       <link rel="shortcut icon" href="../../assets/default.favicon.ico">
  13       <meta name="generator" content="mkdocs-1.1.2, mkdocs-material-7.0.5">
  14
  15
  16
  17         <title>Database Access - WoltLab Suite Documentation</title>
  18
  19
  20
  21       <link rel="stylesheet" href="../../assets/stylesheets/main.77f3fd56.min.css">
  22
  23
  24         <link rel="stylesheet" href="../../assets/stylesheets/palette.7fa14f5b.min.css">
  25
  26
  27
  28           <meta name="theme-color" content="#009485">
  29
  30
  31
  32
  33
  34
  35
  36
  37
  38       <link rel="stylesheet" href="../../stylesheets/extra.css">
  39
  40
  41
  42
  43
  44   </head>
  45
  46
  47
  48
  49
  50
  51
  52     <body dir="ltr" data-md-color-scheme="" data-md-color-primary="teal" data-md-color-accent="">
  53
  54
  55
  56     <input class="md-toggle" data-md-toggle="drawer" type="checkbox" id="__drawer" autocomplete="off">
  57     <input class="md-toggle" data-md-toggle="search" type="checkbox" id="__search" autocomplete="off">
  58     <label class="md-overlay" for="__drawer"></label>
  59     <div data-md-component="skip">
  60
  61
  62         <a href="#database-access" class="md-skip">
  63           Skip to content
  64         </a>
  65
  66     </div>
  67     <div data-md-component="announce">
  68
  69         <aside class="md-announce">
  70           <div class="md-announce__inner md-grid md-typeset">
  71
  72     <a href="https://www.woltlab.com">Back to <strong>woltlab.com</strong></a>
  73
  74           </div>
  75         </aside>
  76
  77     </div>
  78
  79
  80
  81 <header class="md-header" data-md-component="header">
  82   <nav class="md-header__inner md-grid" aria-label="Header">
  83     <a href="../.." title="WoltLab Suite Documentation" class="md-header__button md-logo" aria-label="WoltLab Suite Documentation">
  84
  85   <img src="../../assets/logo.png" alt="logo">
  86
  87     </a>
  88     <label class="md-header__button md-icon" for="__drawer">
  89       <svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24"><path d="M3 6h18v2H3V6m0 5h18v2H3v-2m0 5h18v2H3v-2z"/></svg>
  90     </label>
  91     <div class="md-header__title" data-md-component="header-title">
  92       <div class="md-header__ellipsis">
  93         <div class="md-header__topic">
  94           <span class="md-ellipsis">
  95             WoltLab Suite Documentation
  96           </span>
  97         </div>
  98         <div class="md-header__topic" data-md-component="header-topic">
  99           <span class="md-ellipsis">
 100
 101               Database Access
 102
 103           </span>
 104         </div>
 105       </div>
 106     </div>
 107     <div class="md-header__options">
 108
 109     </div>
 110
 111       <label class="md-header__button md-icon" for="__search">
 112         <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>
 113       </label>
 114
 115 <div class="md-search" data-md-component="search" role="dialog">
 116   <label class="md-search__overlay" for="__search"></label>
 117   <div class="md-search__inner" role="search">
 118     <form class="md-search__form" name="search">
 119       <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>
 120       <label class="md-search__icon md-icon" for="__search">
 121         <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>
 122         <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>
 123       </label>
 124       <button type="reset" class="md-search__icon md-icon" aria-label="Clear" tabindex="-1">
 125         <svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24"><path d="M19 6.41L17.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>
 126       </button>
 127     </form>
 128     <div class="md-search__output">
 129       <div class="md-search__scrollwrap" data-md-scrollfix>
 130         <div class="md-search-result" data-md-component="search-result">
 131           <div class="md-search-result__meta">
 132             Initializing search
 133           </div>
 134           <ol class="md-search-result__list"></ol>
 135         </div>
 136       </div>
 137     </div>
 138   </div>
 139 </div>
 140
 141
 142       <div class="md-header__source">
 143
 144 <a href="https://github.com/WoltLab/docs.woltlab.com/" title="Go to repository" class="md-source" data-md-component="source">
 145   <div class="md-source__icon md-icon">
 146
 147     <svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 448 512"><path d="M439.55 236.05L244 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>
 148   </div>
 149   <div class="md-source__repository">
 150     GitHub
 151   </div>
 152 </a>
 153       </div>
 154
 155   </nav>
 156 </header>
 157
 158     <div class="md-container" data-md-component="container">
 159
 160
 161
 162
 163       <main class="md-main" data-md-component="main">
 164         <div class="md-main__inner md-grid">
 165
 166
 167
 168               <div class="md-sidebar md-sidebar--primary" data-md-component="sidebar" data-md-type="navigation" >
 169                 <div class="md-sidebar__scrollwrap">
 170                   <div class="md-sidebar__inner">
 171
 172
 173
 174
 175
 176 <nav class="md-nav md-nav--primary" aria-label="Navigation" data-md-level="0">
 177   <label class="md-nav__title" for="__drawer">
 178     <a href="../.." title="WoltLab Suite Documentation" class="md-nav__button md-logo" aria-label="WoltLab Suite Documentation">
 179
 180   <img src="../../assets/logo.png" alt="logo">
 181
 182     </a>
 183     WoltLab Suite Documentation
 184   </label>
 185
 186     <div class="md-nav__source">
 187
 188 <a href="https://github.com/WoltLab/docs.woltlab.com/" title="Go to repository" class="md-source" data-md-component="source">
 189   <div class="md-source__icon md-icon">
 190
 191     <svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 448 512"><path d="M439.55 236.05L244 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>
 192   </div>
 193   <div class="md-source__repository">
 194     GitHub
 195   </div>
 196 </a>
 197     </div>
 198
 199   <ul class="md-nav__list" data-md-scrollfix>
 200
 201
 202
 203
 204
 205
 206
 207
 208     <li class="md-nav__item">
 209       <a href="../../getting-started/" class="md-nav__link">
 210         Getting Started
 211       </a>
 212     </li>
 213
 214
 215
 216
 217
 218
 219
 220
 221
 222
 223
 224
 225
 226     <li class="md-nav__item md-nav__item--active md-nav__item--nested">
 227
 228
 229         <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_2" type="checkbox" id="__nav_2" checked>
 230
 231       <label class="md-nav__link" for="__nav_2">
 232         PHP API
 233         <span class="md-nav__icon md-icon"></span>
 234       </label>
 235       <nav class="md-nav" aria-label="PHP API" data-md-level="1">
 236         <label class="md-nav__title" for="__nav_2">
 237           <span class="md-nav__icon md-icon"></span>
 238           PHP API
 239         </label>
 240         <ul class="md-nav__list" data-md-scrollfix>
 241
 242
 243
 244
 245
 246     <li class="md-nav__item">
 247       <a href="../pages/" class="md-nav__link">
 248         Pages
 249       </a>
 250     </li>
 251
 252
 253
 254
 255
 256
 257
 258     <li class="md-nav__item">
 259       <a href="../database-objects/" class="md-nav__link">
 260         Database Objects
 261       </a>
 262     </li>
 263
 264
 265
 266
 267
 268
 269
 270
 271
 272     <li class="md-nav__item md-nav__item--active">
 273
 274       <input class="md-nav__toggle md-toggle" data-md-toggle="toc" type="checkbox" id="__toc">
 275
 276
 277
 278
 279         <label class="md-nav__link md-nav__link--active" for="__toc">
 280           Database Access
 281           <span class="md-nav__icon md-icon"></span>
 282         </label>
 283
 284       <a href="./" class="md-nav__link md-nav__link--active">
 285         Database Access
 286       </a>
 287
 288
 289 <nav class="md-nav md-nav--secondary" aria-label="Table of contents">
 290
 291
 292
 293
 294
 295     <label class="md-nav__title" for="__toc">
 296       <span class="md-nav__icon md-icon"></span>
 297       Table of contents
 298     </label>
 299     <ul class="md-nav__list" data-md-component="toc" data-md-scrollfix>
 300
 301         <li class="md-nav__item">
 302   <a href="#the-preparedstatement-object" class="md-nav__link">
 303     The PreparedStatement Object
 304   </a>
 305
 306     <nav class="md-nav" aria-label="The PreparedStatement Object">
 307       <ul class="md-nav__list">
 308
 309           <li class="md-nav__item">
 310   <a href="#query-parameters" class="md-nav__link">
 311     Query Parameters
 312   </a>
 313
 314 </li>
 315
 316           <li class="md-nav__item">
 317   <a href="#fetching-a-single-result" class="md-nav__link">
 318     Fetching a Single Result
 319   </a>
 320
 321 </li>
 322
 323           <li class="md-nav__item">
 324   <a href="#fetch-by-column" class="md-nav__link">
 325     Fetch by Column
 326   </a>
 327
 328 </li>
 329
 330           <li class="md-nav__item">
 331   <a href="#fetching-all-results" class="md-nav__link">
 332     Fetching All Results
 333   </a>
 334
 335 </li>
 336
 337       </ul>
 338     </nav>
 339
 340 </li>
 341
 342         <li class="md-nav__item">
 343   <a href="#building-complex-conditions" class="md-nav__link">
 344     Building Complex Conditions
 345   </a>
 346
 347 </li>
 348
 349         <li class="md-nav__item">
 350   <a href="#insert-or-update-in-bulk" class="md-nav__link">
 351     INSERT or UPDATE in Bulk
 352   </a>
 353
 354 </li>
 355
 356     </ul>
 357
 358 </nav>
 359
 360     </li>
 361
 362
 363
 364
 365
 366
 367
 368     <li class="md-nav__item">
 369       <a href="../exceptions/" class="md-nav__link">
 370         Exceptions
 371       </a>
 372     </li>
 373
 374
 375
 376
 377
 378
 379
 380
 381     <li class="md-nav__item md-nav__item--nested">
 382
 383
 384         <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_2_5" type="checkbox" id="__nav_2_5" >
 385
 386       <label class="md-nav__link" for="__nav_2_5">
 387         API
 388         <span class="md-nav__icon md-icon"></span>
 389       </label>
 390       <nav class="md-nav" aria-label="API" data-md-level="2">
 391         <label class="md-nav__title" for="__nav_2_5">
 392           <span class="md-nav__icon md-icon"></span>
 393           API
 394         </label>
 395         <ul class="md-nav__list" data-md-scrollfix>
 396
 397
 398
 399
 400
 401     <li class="md-nav__item">
 402       <a href="../api/caches/" class="md-nav__link">
 403         Caches
 404       </a>
 405     </li>
 406
 407
 408
 409
 410
 411
 412
 413     <li class="md-nav__item">
 414       <a href="../api/comments/" class="md-nav__link">
 415         Comments
 416       </a>
 417     </li>
 418
 419
 420
 421
 422
 423
 424
 425     <li class="md-nav__item">
 426       <a href="../api/cronjobs/" class="md-nav__link">
 427         Cronjobs
 428       </a>
 429     </li>
 430
 431
 432
 433
 434
 435
 436
 437     <li class="md-nav__item">
 438       <a href="../api/events/" class="md-nav__link">
 439         Events
 440       </a>
 441     </li>
 442
 443
 444
 445
 446
 447
 448
 449
 450     <li class="md-nav__item md-nav__item--nested">
 451
 452
 453         <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_2_5_5" type="checkbox" id="__nav_2_5_5" >
 454
 455       <label class="md-nav__link" for="__nav_2_5_5">
 456         Form Builder
 457         <span class="md-nav__icon md-icon"></span>
 458       </label>
 459       <nav class="md-nav" aria-label="Form Builder" data-md-level="3">
 460         <label class="md-nav__title" for="__nav_2_5_5">
 461           <span class="md-nav__icon md-icon"></span>
 462           Form Builder
 463         </label>
 464         <ul class="md-nav__list" data-md-scrollfix>
 465
 466
 467
 468
 469
 470     <li class="md-nav__item">
 471       <a href="../api/form_builder/overview/" class="md-nav__link">
 472         Overview
 473       </a>
 474     </li>
 475
 476
 477
 478
 479
 480
 481
 482     <li class="md-nav__item">
 483       <a href="../api/form_builder/structure/" class="md-nav__link">
 484         Structure
 485       </a>
 486     </li>
 487
 488
 489
 490
 491
 492
 493
 494     <li class="md-nav__item">
 495       <a href="../api/form_builder/form_fields/" class="md-nav__link">
 496         Fields
 497       </a>
 498     </li>
 499
 500
 501
 502
 503
 504
 505
 506     <li class="md-nav__item">
 507       <a href="../api/form_builder/validation_data/" class="md-nav__link">
 508         Validation and Data
 509       </a>
 510     </li>
 511
 512
 513
 514
 515
 516
 517
 518     <li class="md-nav__item">
 519       <a href="../api/form_builder/dependencies/" class="md-nav__link">
 520         Dependencies
 521       </a>
 522     </li>
 523
 524
 525
 526         </ul>
 527       </nav>
 528     </li>
 529
 530
 531
 532
 533
 534
 535
 536     <li class="md-nav__item">
 537       <a href="../api/package_installation_plugins/" class="md-nav__link">
 538         Package Installation Plugins
 539       </a>
 540     </li>
 541
 542
 543
 544
 545
 546
 547
 548     <li class="md-nav__item">
 549       <a href="../api/user_activity_points/" class="md-nav__link">
 550         User Activity Points
 551       </a>
 552     </li>
 553
 554
 555
 556
 557
 558
 559
 560     <li class="md-nav__item">
 561       <a href="../api/user_notifications/" class="md-nav__link">
 562         User Notifications
 563       </a>
 564     </li>
 565
 566
 567
 568
 569
 570
 571
 572     <li class="md-nav__item">
 573       <a href="../api/sitemaps/" class="md-nav__link">
 574         Sitemaps
 575       </a>
 576     </li>
 577
 578
 579
 580         </ul>
 581       </nav>
 582     </li>
 583
 584
 585
 586
 587
 588
 589
 590     <li class="md-nav__item">
 591       <a href="../code-style/" class="md-nav__link">
 592         Code Style
 593       </a>
 594     </li>
 595
 596
 597
 598
 599
 600
 601
 602     <li class="md-nav__item">
 603       <a href="../apps/" class="md-nav__link">
 604         Apps
 605       </a>
 606     </li>
 607
 608
 609
 610
 611
 612
 613
 614     <li class="md-nav__item">
 615       <a href="../gdpr/" class="md-nav__link">
 616         GDPR
 617       </a>
 618     </li>
 619
 620
 621
 622         </ul>
 623       </nav>
 624     </li>
 625
 626
 627
 628
 629
 630
 631
 632
 633
 634
 635
 636     <li class="md-nav__item md-nav__item--nested">
 637
 638
 639         <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_3" type="checkbox" id="__nav_3" >
 640
 641       <label class="md-nav__link" for="__nav_3">
 642         Languages, Templates & CSS
 643         <span class="md-nav__icon md-icon"></span>
 644       </label>
 645       <nav class="md-nav" aria-label="Languages, Templates & CSS" data-md-level="1">
 646         <label class="md-nav__title" for="__nav_3">
 647           <span class="md-nav__icon md-icon"></span>
 648           Languages, Templates & CSS
 649         </label>
 650         <ul class="md-nav__list" data-md-scrollfix>
 651
 652
 653
 654
 655
 656     <li class="md-nav__item">
 657       <a href="../../view/languages/" class="md-nav__link">
 658         Languages
 659       </a>
 660     </li>
 661
 662
 663
 664
 665
 666
 667
 668     <li class="md-nav__item">
 669       <a href="../../view/templates/" class="md-nav__link">
 670         Templates
 671       </a>
 672     </li>
 673
 674
 675
 676
 677
 678
 679
 680     <li class="md-nav__item">
 681       <a href="../../view/css/" class="md-nav__link">
 682         CSS
 683       </a>
 684     </li>
 685
 686
 687
 688         </ul>
 689       </nav>
 690     </li>
 691
 692
 693
 694
 695
 696
 697
 698
 699
 700
 701
 702     <li class="md-nav__item md-nav__item--nested">
 703
 704
 705         <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_4" type="checkbox" id="__nav_4" >
 706
 707       <label class="md-nav__link" for="__nav_4">
 708         JavaScript API
 709         <span class="md-nav__icon md-icon"></span>
 710       </label>
 711       <nav class="md-nav" aria-label="JavaScript API" data-md-level="1">
 712         <label class="md-nav__title" for="__nav_4">
 713           <span class="md-nav__icon md-icon"></span>
 714           JavaScript API
 715         </label>
 716         <ul class="md-nav__list" data-md-scrollfix>
 717
 718
 719
 720
 721
 722     <li class="md-nav__item">
 723       <a href="../../javascript/general-usage/" class="md-nav__link">
 724         General Usage
 725       </a>
 726     </li>
 727
 728
 729
 730
 731
 732
 733
 734
 735     <li class="md-nav__item md-nav__item--nested">
 736
 737
 738         <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_4_2" type="checkbox" id="__nav_4_2" >
 739
 740       <label class="md-nav__link" for="__nav_4_2">
 741         New API
 742         <span class="md-nav__icon md-icon"></span>
 743       </label>
 744       <nav class="md-nav" aria-label="New API" data-md-level="2">
 745         <label class="md-nav__title" for="__nav_4_2">
 746           <span class="md-nav__icon md-icon"></span>
 747           New API
 748         </label>
 749         <ul class="md-nav__list" data-md-scrollfix>
 750
 751
 752
 753
 754
 755     <li class="md-nav__item">
 756       <a href="../../javascript/new-api_writing-a-module/" class="md-nav__link">
 757         Writing a module
 758       </a>
 759     </li>
 760
 761
 762
 763
 764
 765
 766
 767     <li class="md-nav__item">
 768       <a href="../../javascript/new-api_data-structures/" class="md-nav__link">
 769         Data Structures
 770       </a>
 771     </li>
 772
 773
 774
 775
 776
 777
 778
 779     <li class="md-nav__item">
 780       <a href="../../javascript/new-api_core/" class="md-nav__link">
 781         Core Functions
 782       </a>
 783     </li>
 784
 785
 786
 787
 788
 789
 790
 791     <li class="md-nav__item">
 792       <a href="../../javascript/new-api_dom/" class="md-nav__link">
 793         DOM
 794       </a>
 795     </li>
 796
 797
 798
 799
 800
 801
 802
 803     <li class="md-nav__item">
 804       <a href="../../javascript/new-api_events/" class="md-nav__link">
 805         Event Handling
 806       </a>
 807     </li>
 808
 809
 810
 811
 812
 813
 814
 815     <li class="md-nav__item">
 816       <a href="../../javascript/new-api_ajax/" class="md-nav__link">
 817         Ajax
 818       </a>
 819     </li>
 820
 821
 822
 823
 824
 825
 826
 827     <li class="md-nav__item">
 828       <a href="../../javascript/new-api_dialogs/" class="md-nav__link">
 829         Dialogs
 830       </a>
 831     </li>
 832
 833
 834
 835
 836
 837
 838
 839     <li class="md-nav__item">
 840       <a href="../../javascript/new-api_browser/" class="md-nav__link">
 841         Browser and Screen Sizes
 842       </a>
 843     </li>
 844
 845
 846
 847
 848
 849
 850
 851     <li class="md-nav__item">
 852       <a href="../../javascript/new-api_ui/" class="md-nav__link">
 853         User Interface
 854       </a>
 855     </li>
 856
 857
 858
 859         </ul>
 860       </nav>
 861     </li>
 862
 863
 864
 865
 866
 867
 868
 869     <li class="md-nav__item">
 870       <a href="../../javascript/legacy-api/" class="md-nav__link">
 871         Legacy API
 872       </a>
 873     </li>
 874
 875
 876
 877
 878
 879
 880
 881     <li class="md-nav__item">
 882       <a href="../../javascript/helper-functions/" class="md-nav__link">
 883         Helper Functions
 884       </a>
 885     </li>
 886
 887
 888
 889
 890
 891
 892
 893     <li class="md-nav__item">
 894       <a href="../../javascript/code-snippets/" class="md-nav__link">
 895         Code Snippets
 896       </a>
 897     </li>
 898
 899
 900
 901         </ul>
 902       </nav>
 903     </li>
 904
 905
 906
 907
 908
 909
 910
 911
 912
 913
 914
 915     <li class="md-nav__item md-nav__item--nested">
 916
 917
 918         <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_5" type="checkbox" id="__nav_5" >
 919
 920       <label class="md-nav__link" for="__nav_5">
 921         Package Components
 922         <span class="md-nav__icon md-icon"></span>
 923       </label>
 924       <nav class="md-nav" aria-label="Package Components" data-md-level="1">
 925         <label class="md-nav__title" for="__nav_5">
 926           <span class="md-nav__icon md-icon"></span>
 927           Package Components
 928         </label>
 929         <ul class="md-nav__list" data-md-scrollfix>
 930
 931
 932
 933
 934
 935     <li class="md-nav__item">
 936       <a href="../../package/package-xml/" class="md-nav__link">
 937         package.xml
 938       </a>
 939     </li>
 940
 941
 942
 943
 944
 945
 946
 947
 948     <li class="md-nav__item md-nav__item--nested">
 949
 950
 951         <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_5_2" type="checkbox" id="__nav_5_2" >
 952
 953       <label class="md-nav__link" for="__nav_5_2">
 954         PIPs
 955         <span class="md-nav__icon md-icon"></span>
 956       </label>
 957       <nav class="md-nav" aria-label="PIPs" data-md-level="2">
 958         <label class="md-nav__title" for="__nav_5_2">
 959           <span class="md-nav__icon md-icon"></span>
 960           PIPs
 961         </label>
 962         <ul class="md-nav__list" data-md-scrollfix>
 963
 964
 965
 966
 967
 968     <li class="md-nav__item">
 969       <a href="../../package/pip/" class="md-nav__link">
 970         Overview
 971       </a>
 972     </li>
 973
 974
 975
 976
 977
 978
 979
 980     <li class="md-nav__item">
 981       <a href="../../package/pip/acl-option/" class="md-nav__link">
 982         aclOption
 983       </a>
 984     </li>
 985
 986
 987
 988
 989
 990
 991
 992     <li class="md-nav__item">
 993       <a href="../../package/pip/acp-menu/" class="md-nav__link">
 994         acpMenu
 995       </a>
 996     </li>
 997
 998
 999
1000
1001
1002
1003
1004     <li class="md-nav__item">
1005       <a href="../../package/pip/acp-search-provider/" class="md-nav__link">
1006         acpSearchProvider
1007       </a>
1008     </li>
1009
1010
1011
1012
1013
1014
1015
1016     <li class="md-nav__item">
1017       <a href="../../package/pip/acp-template/" class="md-nav__link">
1018         acpTemplate
1019       </a>
1020     </li>
1021
1022
1023
1024
1025
1026
1027
1028     <li class="md-nav__item">
1029       <a href="../../package/pip/bbcode/" class="md-nav__link">
1030         bbcode
1031       </a>
1032     </li>
1033
1034
1035
1036
1037
1038
1039
1040     <li class="md-nav__item">
1041       <a href="../../package/pip/box/" class="md-nav__link">
1042         box
1043       </a>
1044     </li>
1045
1046
1047
1048
1049
1050
1051
1052     <li class="md-nav__item">
1053       <a href="../../package/pip/clipboard-action/" class="md-nav__link">
1054         clipboardAction
1055       </a>
1056     </li>
1057
1058
1059
1060
1061
1062
1063
1064     <li class="md-nav__item">
1065       <a href="../../package/pip/core-object/" class="md-nav__link">
1066         coreObject
1067       </a>
1068     </li>
1069
1070
1071
1072
1073
1074
1075
1076     <li class="md-nav__item">
1077       <a href="../../package/pip/cronjob/" class="md-nav__link">
1078         cronjob
1079       </a>
1080     </li>
1081
1082
1083
1084
1085
1086
1087
1088     <li class="md-nav__item">
1089       <a href="../../package/pip/event-listener/" class="md-nav__link">
1090         eventListener
1091       </a>
1092     </li>
1093
1094
1095
1096
1097
1098
1099
1100     <li class="md-nav__item">
1101       <a href="../../package/pip/file/" class="md-nav__link">
1102         file
1103       </a>
1104     </li>
1105
1106
1107
1108
1109
1110
1111
1112     <li class="md-nav__item">
1113       <a href="../../package/pip/language/" class="md-nav__link">
1114         language
1115       </a>
1116     </li>
1117
1118
1119
1120
1121
1122
1123
1124     <li class="md-nav__item">
1125       <a href="../../package/pip/media-provider/" class="md-nav__link">
1126         mediaProvider
1127       </a>
1128     </li>
1129
1130
1131
1132
1133
1134
1135
1136     <li class="md-nav__item">
1137       <a href="../../package/pip/menu/" class="md-nav__link">
1138         menu
1139       </a>
1140     </li>
1141
1142
1143
1144
1145
1146
1147
1148     <li class="md-nav__item">
1149       <a href="../../package/pip/menu-item/" class="md-nav__link">
1150         menuItem
1151       </a>
1152     </li>
1153
1154
1155
1156
1157
1158
1159
1160     <li class="md-nav__item">
1161       <a href="../../package/pip/object-type/" class="md-nav__link">
1162         objectType
1163       </a>
1164     </li>
1165
1166
1167
1168
1169
1170
1171
1172     <li class="md-nav__item">
1173       <a href="../../package/pip/object-type-definition/" class="md-nav__link">
1174         objectTypeDefinition
1175       </a>
1176     </li>
1177
1178
1179
1180
1181
1182
1183
1184     <li class="md-nav__item">
1185       <a href="../../package/pip/option/" class="md-nav__link">
1186         option
1187       </a>
1188     </li>
1189
1190
1191
1192
1193
1194
1195
1196     <li class="md-nav__item">
1197       <a href="../../package/pip/page/" class="md-nav__link">
1198         page
1199       </a>
1200     </li>
1201
1202
1203
1204
1205
1206
1207
1208     <li class="md-nav__item">
1209       <a href="../../package/pip/pip/" class="md-nav__link">
1210         pip
1211       </a>
1212     </li>
1213
1214
1215
1216
1217
1218
1219
1220     <li class="md-nav__item">
1221       <a href="../../package/pip/script/" class="md-nav__link">
1222         script
1223       </a>
1224     </li>
1225
1226
1227
1228
1229
1230
1231
1232     <li class="md-nav__item">
1233       <a href="../../package/pip/smiley/" class="md-nav__link">
1234         smiley
1235       </a>
1236     </li>
1237
1238
1239
1240
1241
1242
1243
1244     <li class="md-nav__item">
1245       <a href="../../package/pip/sql/" class="md-nav__link">
1246         sql
1247       </a>
1248     </li>
1249
1250
1251
1252
1253
1254
1255
1256     <li class="md-nav__item">
1257       <a href="../../package/pip/style/" class="md-nav__link">
1258         style
1259       </a>
1260     </li>
1261
1262
1263
1264
1265
1266
1267
1268     <li class="md-nav__item">
1269       <a href="../../package/pip/template/" class="md-nav__link">
1270         template
1271       </a>
1272     </li>
1273
1274
1275
1276
1277
1278
1279
1280     <li class="md-nav__item">
1281       <a href="../../package/pip/template-listener/" class="md-nav__link">
1282         templateListener
1283       </a>
1284     </li>
1285
1286
1287
1288
1289
1290
1291
1292     <li class="md-nav__item">
1293       <a href="../../package/pip/user-group-option/" class="md-nav__link">
1294         userGroupOption
1295       </a>
1296     </li>
1297
1298
1299
1300
1301
1302
1303
1304     <li class="md-nav__item">
1305       <a href="../../package/pip/user-menu/" class="md-nav__link">
1306         userMenu
1307       </a>
1308     </li>
1309
1310
1311
1312
1313
1314
1315
1316     <li class="md-nav__item">
1317       <a href="../../package/pip/user-notification-event/" class="md-nav__link">
1318         userNotificationEvent
1319       </a>
1320     </li>
1321
1322
1323
1324
1325
1326
1327
1328     <li class="md-nav__item">
1329       <a href="../../package/pip/user-option/" class="md-nav__link">
1330         userOption
1331       </a>
1332     </li>
1333
1334
1335
1336
1337
1338
1339
1340     <li class="md-nav__item">
1341       <a href="../../package/pip/user-profile-menu/" class="md-nav__link">
1342         userProfileMenu
1343       </a>
1344     </li>
1345
1346
1347
1348         </ul>
1349       </nav>
1350     </li>
1351
1352
1353
1354
1355
1356
1357
1358     <li class="md-nav__item">
1359       <a href="../../package/database-php-api/" class="md-nav__link">
1360         Database PHP API
1361       </a>
1362     </li>
1363
1364
1365
1366         </ul>
1367       </nav>
1368     </li>
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380     <li class="md-nav__item md-nav__item--nested">
1381
1382
1383         <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_6" type="checkbox" id="__nav_6" >
1384
1385       <label class="md-nav__link" for="__nav_6">
1386         Migration
1387         <span class="md-nav__icon md-icon"></span>
1388       </label>
1389       <nav class="md-nav" aria-label="Migration" data-md-level="1">
1390         <label class="md-nav__title" for="__nav_6">
1391           <span class="md-nav__icon md-icon"></span>
1392           Migration
1393         </label>
1394         <ul class="md-nav__list" data-md-scrollfix>
1395
1396
1397
1398
1399
1400
1401     <li class="md-nav__item md-nav__item--nested">
1402
1403
1404         <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_6_1" type="checkbox" id="__nav_6_1" >
1405
1406       <label class="md-nav__link" for="__nav_6_1">
1407         Migrating from WSC 5.3
1408         <span class="md-nav__icon md-icon"></span>
1409       </label>
1410       <nav class="md-nav" aria-label="Migrating from WSC 5.3" data-md-level="2">
1411         <label class="md-nav__title" for="__nav_6_1">
1412           <span class="md-nav__icon md-icon"></span>
1413           Migrating from WSC 5.3
1414         </label>
1415         <ul class="md-nav__list" data-md-scrollfix>
1416
1417
1418
1419
1420
1421     <li class="md-nav__item">
1422       <a href="../../migration/wsc53/php/" class="md-nav__link">
1423         PHP API
1424       </a>
1425     </li>
1426
1427
1428
1429
1430
1431
1432
1433     <li class="md-nav__item">
1434       <a href="../../migration/wsc53/session/" class="md-nav__link">
1435         Session Handling and Authentication
1436       </a>
1437     </li>
1438
1439
1440
1441
1442
1443
1444
1445     <li class="md-nav__item">
1446       <a href="../../migration/wsc53/javascript/" class="md-nav__link">
1447         JavaScript
1448       </a>
1449     </li>
1450
1451
1452
1453
1454
1455
1456
1457     <li class="md-nav__item">
1458       <a href="../../migration/wsc53/templates/" class="md-nav__link">
1459         Templates
1460       </a>
1461     </li>
1462
1463
1464
1465
1466
1467
1468
1469     <li class="md-nav__item">
1470       <a href="../../migration/wsc53/libraries/" class="md-nav__link">
1471         Third Party Libraries
1472       </a>
1473     </li>
1474
1475
1476
1477         </ul>
1478       </nav>
1479     </li>
1480
1481
1482
1483
1484
1485
1486
1487
1488     <li class="md-nav__item md-nav__item--nested">
1489
1490
1491         <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_6_2" type="checkbox" id="__nav_6_2" >
1492
1493       <label class="md-nav__link" for="__nav_6_2">
1494         Migrating from WSC 5.2
1495         <span class="md-nav__icon md-icon"></span>
1496       </label>
1497       <nav class="md-nav" aria-label="Migrating from WSC 5.2" data-md-level="2">
1498         <label class="md-nav__title" for="__nav_6_2">
1499           <span class="md-nav__icon md-icon"></span>
1500           Migrating from WSC 5.2
1501         </label>
1502         <ul class="md-nav__list" data-md-scrollfix>
1503
1504
1505
1506
1507
1508     <li class="md-nav__item">
1509       <a href="../../migration/wsc52/php/" class="md-nav__link">
1510         PHP API
1511       </a>
1512     </li>
1513
1514
1515
1516
1517
1518
1519
1520     <li class="md-nav__item">
1521       <a href="../../migration/wsc52/templates/" class="md-nav__link">
1522         Templates and Languages
1523       </a>
1524     </li>
1525
1526
1527
1528
1529
1530
1531
1532     <li class="md-nav__item">
1533       <a href="../../migration/wsc52/libraries/" class="md-nav__link">
1534         Third Party Libraries
1535       </a>
1536     </li>
1537
1538
1539
1540         </ul>
1541       </nav>
1542     </li>
1543
1544
1545
1546
1547
1548
1549
1550
1551     <li class="md-nav__item md-nav__item--nested">
1552
1553
1554         <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_6_3" type="checkbox" id="__nav_6_3" >
1555
1556       <label class="md-nav__link" for="__nav_6_3">
1557         Migrating from WSC 3.1
1558         <span class="md-nav__icon md-icon"></span>
1559       </label>
1560       <nav class="md-nav" aria-label="Migrating from WSC 3.1" data-md-level="2">
1561         <label class="md-nav__title" for="__nav_6_3">
1562           <span class="md-nav__icon md-icon"></span>
1563           Migrating from WSC 3.1
1564         </label>
1565         <ul class="md-nav__list" data-md-scrollfix>
1566
1567
1568
1569
1570
1571     <li class="md-nav__item">
1572       <a href="../../migration/wsc31/php/" class="md-nav__link">
1573         PHP API
1574       </a>
1575     </li>
1576
1577
1578
1579         </ul>
1580       </nav>
1581     </li>
1582
1583
1584
1585
1586
1587
1588
1589
1590     <li class="md-nav__item md-nav__item--nested">
1591
1592
1593         <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_6_4" type="checkbox" id="__nav_6_4" >
1594
1595       <label class="md-nav__link" for="__nav_6_4">
1596         Migrating from WSC 3.0
1597         <span class="md-nav__icon md-icon"></span>
1598       </label>
1599       <nav class="md-nav" aria-label="Migrating from WSC 3.0" data-md-level="2">
1600         <label class="md-nav__title" for="__nav_6_4">
1601           <span class="md-nav__icon md-icon"></span>
1602           Migrating from WSC 3.0
1603         </label>
1604         <ul class="md-nav__list" data-md-scrollfix>
1605
1606
1607
1608
1609
1610     <li class="md-nav__item">
1611       <a href="../../migration/wsc30/php/" class="md-nav__link">
1612         PHP API
1613       </a>
1614     </li>
1615
1616
1617
1618
1619
1620
1621
1622     <li class="md-nav__item">
1623       <a href="../../migration/wsc30/javascript/" class="md-nav__link">
1624         JavaScript API
1625       </a>
1626     </li>
1627
1628
1629
1630
1631
1632
1633
1634     <li class="md-nav__item">
1635       <a href="../../migration/wsc30/templates/" class="md-nav__link">
1636         Templates
1637       </a>
1638     </li>
1639
1640
1641
1642
1643
1644
1645
1646     <li class="md-nav__item">
1647       <a href="../../migration/wsc30/css/" class="md-nav__link">
1648         CSS
1649       </a>
1650     </li>
1651
1652
1653
1654
1655
1656
1657
1658     <li class="md-nav__item">
1659       <a href="../../migration/wsc30/package/" class="md-nav__link">
1660         Package Components
1661       </a>
1662     </li>
1663
1664
1665
1666         </ul>
1667       </nav>
1668     </li>
1669
1670
1671
1672
1673
1674
1675
1676
1677     <li class="md-nav__item md-nav__item--nested">
1678
1679
1680         <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_6_5" type="checkbox" id="__nav_6_5" >
1681
1682       <label class="md-nav__link" for="__nav_6_5">
1683         Migrating from WCF 2.1
1684         <span class="md-nav__icon md-icon"></span>
1685       </label>
1686       <nav class="md-nav" aria-label="Migrating from WCF 2.1" data-md-level="2">
1687         <label class="md-nav__title" for="__nav_6_5">
1688           <span class="md-nav__icon md-icon"></span>
1689           Migrating from WCF 2.1
1690         </label>
1691         <ul class="md-nav__list" data-md-scrollfix>
1692
1693
1694
1695
1696
1697     <li class="md-nav__item">
1698       <a href="../../migration/wcf21/php/" class="md-nav__link">
1699         PHP API
1700       </a>
1701     </li>
1702
1703
1704
1705
1706
1707
1708
1709     <li class="md-nav__item">
1710       <a href="../../migration/wcf21/templates/" class="md-nav__link">
1711         Templates
1712       </a>
1713     </li>
1714
1715
1716
1717
1718
1719
1720
1721     <li class="md-nav__item">
1722       <a href="../../migration/wcf21/css/" class="md-nav__link">
1723         CSS
1724       </a>
1725     </li>
1726
1727
1728
1729
1730
1731
1732
1733     <li class="md-nav__item">
1734       <a href="../../migration/wcf21/package/" class="md-nav__link">
1735         Package Components
1736       </a>
1737     </li>
1738
1739
1740
1741         </ul>
1742       </nav>
1743     </li>
1744
1745
1746
1747         </ul>
1748       </nav>
1749     </li>
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761     <li class="md-nav__item md-nav__item--nested">
1762
1763
1764         <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_7" type="checkbox" id="__nav_7" >
1765
1766       <label class="md-nav__link" for="__nav_7">
1767         Tutorials
1768         <span class="md-nav__icon md-icon"></span>
1769       </label>
1770       <nav class="md-nav" aria-label="Tutorials" data-md-level="1">
1771         <label class="md-nav__title" for="__nav_7">
1772           <span class="md-nav__icon md-icon"></span>
1773           Tutorials
1774         </label>
1775         <ul class="md-nav__list" data-md-scrollfix>
1776
1777
1778
1779
1780
1781
1782     <li class="md-nav__item md-nav__item--nested">
1783
1784
1785         <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_7_1" type="checkbox" id="__nav_7_1" >
1786
1787       <label class="md-nav__link" for="__nav_7_1">
1788         Tutorial Series
1789         <span class="md-nav__icon md-icon"></span>
1790       </label>
1791       <nav class="md-nav" aria-label="Tutorial Series" data-md-level="2">
1792         <label class="md-nav__title" for="__nav_7_1">
1793           <span class="md-nav__icon md-icon"></span>
1794           Tutorial Series
1795         </label>
1796         <ul class="md-nav__list" data-md-scrollfix>
1797
1798
1799
1800
1801
1802     <li class="md-nav__item">
1803       <a href="../../tutorial/series/overview/" class="md-nav__link">
1804         Overview
1805       </a>
1806     </li>
1807
1808
1809
1810
1811
1812
1813
1814     <li class="md-nav__item">
1815       <a href="../../tutorial/series/part_1/" class="md-nav__link">
1816         Part 1
1817       </a>
1818     </li>
1819
1820
1821
1822
1823
1824
1825
1826     <li class="md-nav__item">
1827       <a href="../../tutorial/series/part_2/" class="md-nav__link">
1828         Part 2
1829       </a>
1830     </li>
1831
1832
1833
1834
1835
1836
1837
1838     <li class="md-nav__item">
1839       <a href="../../tutorial/series/part_3/" class="md-nav__link">
1840         Part 3
1841       </a>
1842     </li>
1843
1844
1845
1846         </ul>
1847       </nav>
1848     </li>
1849
1850
1851
1852         </ul>
1853       </nav>
1854     </li>
1855
1856
1857
1858   </ul>
1859 </nav>
1860                   </div>
1861                 </div>
1862               </div>
1863
1864
1865
1866               <div class="md-sidebar md-sidebar--secondary" data-md-component="sidebar" data-md-type="toc" >
1867                 <div class="md-sidebar__scrollwrap">
1868                   <div class="md-sidebar__inner">
1869
1870 <nav class="md-nav md-nav--secondary" aria-label="Table of contents">
1871
1872
1873
1874
1875
1876     <label class="md-nav__title" for="__toc">
1877       <span class="md-nav__icon md-icon"></span>
1878       Table of contents
1879     </label>
1880     <ul class="md-nav__list" data-md-component="toc" data-md-scrollfix>
1881
1882         <li class="md-nav__item">
1883   <a href="#the-preparedstatement-object" class="md-nav__link">
1884     The PreparedStatement Object
1885   </a>
1886
1887     <nav class="md-nav" aria-label="The PreparedStatement Object">
1888       <ul class="md-nav__list">
1889
1890           <li class="md-nav__item">
1891   <a href="#query-parameters" class="md-nav__link">
1892     Query Parameters
1893   </a>
1894
1895 </li>
1896
1897           <li class="md-nav__item">
1898   <a href="#fetching-a-single-result" class="md-nav__link">
1899     Fetching a Single Result
1900   </a>
1901
1902 </li>
1903
1904           <li class="md-nav__item">
1905   <a href="#fetch-by-column" class="md-nav__link">
1906     Fetch by Column
1907   </a>
1908
1909 </li>
1910
1911           <li class="md-nav__item">
1912   <a href="#fetching-all-results" class="md-nav__link">
1913     Fetching All Results
1914   </a>
1915
1916 </li>
1917
1918       </ul>
1919     </nav>
1920
1921 </li>
1922
1923         <li class="md-nav__item">
1924   <a href="#building-complex-conditions" class="md-nav__link">
1925     Building Complex Conditions
1926   </a>
1927
1928 </li>
1929
1930         <li class="md-nav__item">
1931   <a href="#insert-or-update-in-bulk" class="md-nav__link">
1932     INSERT or UPDATE in Bulk
1933   </a>
1934
1935 </li>
1936
1937     </ul>
1938
1939 </nav>
1940                   </div>
1941                 </div>
1942               </div>
1943
1944
1945           <div class="md-content" data-md-component="content">
1946             <article class="md-content__inner md-typeset">
1947
1948
1949                   <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">
1950                     <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>
1951                   </a>
1952
1953
1954                 <h1 id="database-access">Database Access<a class="headerlink" href="#database-access" title="Permanent link">#</a></h1>
1955 <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>
1956 <h2 id="the-preparedstatement-object">The PreparedStatement Object<a class="headerlink" href="#the-preparedstatement-object" title="Permanent link">#</a></h2>
1957 <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>
1958 <div class="highlight"><pre><span></span><code><span class="o">&lt;?</span><span class="nx">php</span>
1959 <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>
1960 <span class="nv">$statement</span><span class="o">-&gt;</span><span class="na">execute</span><span class="p">();</span>
1961 <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>
1962     <span class="c1">// handle result</span>
1963 <span class="p">}</span>
1964 </code></pre></div>
1965 <h3 id="query-parameters">Query Parameters<a class="headerlink" href="#query-parameters" title="Permanent link">#</a></h3>
1966 <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>
1967 <div class="highlight"><pre><span></span><code><span class="o">&lt;?</span><span class="nx">php</span>
1968 <span class="nv">$sql</span> <span class="o">=</span> <span class="s2">&quot;SELECT  *</span>
1969 <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>
1970 <span class="s2">        WHERE   exampleID = ?</span>
1971 <span class="s2">                OR bar IN (?, ?, ?, ?, ?)&quot;</span><span class="p">;</span>
1972 <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>
1973 <span class="nv">$statement</span><span class="o">-&gt;</span><span class="na">execute</span><span class="p">([</span>
1974     <span class="nv">$exampleID</span><span class="p">,</span>
1975     <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>
1976 <span class="p">]);</span>
1977 <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>
1978     <span class="c1">// handle result</span>
1979 <span class="p">}</span>
1980 </code></pre></div>
1981 <h3 id="fetching-a-single-result">Fetching a Single Result<a class="headerlink" href="#fetching-a-single-result" title="Permanent link">#</a></h3>
1982 <div class="admonition danger">
1983 <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>
1984 </div>
1985 <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>
1986 <div class="highlight"><pre><span></span><code><span class="o">&lt;?</span><span class="nx">php</span>
1987 <span class="nv">$sql</span> <span class="o">=</span> <span class="s2">&quot;SELECT  *</span>
1988 <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>
1989 <span class="s2">        WHERE   exampleID = ?&quot;</span><span class="p">;</span>
1990 <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>
1991 <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>
1992 <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>
1993 </code></pre></div>
1994 <p>There are two distinct differences when comparing with the example on query parameters above:</p>
1995 <ol>
1996 <li>The method <code>prepareStatement()</code> receives a secondary parameter that will be appended to the query as <code>LIMIT 1</code>.</li>
1997 <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>
1998 </ol>
1999 <h3 id="fetch-by-column">Fetch by Column<a class="headerlink" href="#fetch-by-column" title="Permanent link">#</a></h3>
2000 <div class="admonition warning">
2001 <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>
2002 </div>
2003 <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>
2004 <div class="highlight"><pre><span></span><code><span class="o">&lt;?</span><span class="nx">php</span>
2005 <span class="nv">$sql</span> <span class="o">=</span> <span class="s2">&quot;SELECT  bar</span>
2006 <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>
2007 <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>
2008 <span class="nv">$statement</span><span class="o">-&gt;</span><span class="na">execute</span><span class="p">();</span>
2009 <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>
2010     <span class="c1">// handle result</span>
2011 <span class="p">}</span>
2012 <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>
2013 </code></pre></div>
2014 <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>
2015 <div class="highlight"><pre><span></span><code><span class="o">&lt;?</span><span class="nx">php</span>
2016 <span class="nv">$sql</span> <span class="o">=</span> <span class="s2">&quot;SELECT  bar</span>
2017 <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>
2018 <span class="s2">        WHERE   exampleID = ?&quot;</span><span class="p">;</span>
2019 <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>
2020 <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>
2021 <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>
2022 </code></pre></div>
2023 <h3 id="fetching-all-results">Fetching All Results<a class="headerlink" href="#fetching-all-results" title="Permanent link">#</a></h3>
2024 <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>
2025 <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>
2026 <div class="highlight"><pre><span></span><code><span class="o">&lt;?</span><span class="nx">php</span>
2027 <span class="nv">$sql</span> <span class="o">=</span> <span class="s2">&quot;SELECT  *</span>
2028 <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>
2029 <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>
2030 <span class="nv">$statement</span><span class="o">-&gt;</span><span class="na">execute</span><span class="p">();</span>
2031 <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>
2032 </code></pre></div>
2033 <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>
2034 <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>
2035 <div class="highlight"><pre><span></span><code><span class="o">&lt;?</span><span class="nx">php</span>
2036 <span class="nv">$sql</span> <span class="o">=</span> <span class="s2">&quot;SELECT  exampleID</span>
2037 <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>
2038 <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>
2039 <span class="nv">$statement</span><span class="o">-&gt;</span><span class="na">execute</span><span class="p">();</span>
2040 <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>
2041 </code></pre></div>
2042 <p>As a result, you get an array with all <code>exampleID</code> values.</p>
2043 <p>The <code>PreparedStatement</code> class adds an additional methods that covers another common use case in our code:
2044 Fetching two columns and using the first column's value as the array key and the second column's value as the array value.
2045 This case is covered by <code>PreparedStatement::fetchMap()</code>:</p>
2046 <div class="highlight"><pre><span></span><code><span class="o">&lt;?</span><span class="nx">php</span>
2047 <span class="nv">$sql</span> <span class="o">=</span> <span class="s2">&quot;SELECT  exampleID, userID</span>
2048 <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>
2049 <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>
2050 <span class="nv">$statement</span><span class="o">-&gt;</span><span class="na">execute</span><span class="p">();</span>
2051 <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>
2052 </code></pre></div>
2053 <p><code>$map</code> is a one-dimensional array where each <code>exampleID</code> value maps to the corresponding <code>userID</code> value.</p>
2054 <div class="admonition warning">
2055 <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>
2056 </div>
2057 <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>
2058 <div class="highlight"><pre><span></span><code><span class="o">&lt;?</span><span class="nx">php</span>
2059 <span class="nv">$sql</span> <span class="o">=</span> <span class="s2">&quot;SELECT  exampleID, userID</span>
2060 <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>
2061 <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>
2062 <span class="nv">$statement</span><span class="o">-&gt;</span><span class="na">execute</span><span class="p">();</span>
2063 <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>
2064 </code></pre></div>
2065 <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>
2066 <h2 id="building-complex-conditions">Building Complex Conditions<a class="headerlink" href="#building-complex-conditions" title="Permanent link">#</a></h2>
2067 <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>
2068 <div class="highlight"><pre><span></span><code><span class="o">&lt;?</span><span class="nx">php</span>
2069 <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>
2070 <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>
2071 <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>
2072     <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>
2073 <span class="p">}</span>
2074 </code></pre></div>
2075 <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>
2076 <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>
2077 <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>
2078 <div class="highlight"><pre><span></span><code><span class="o">&lt;?</span><span class="nx">php</span>
2079 <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>
2080
2081 <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>
2082 <span class="s2">                     (bar)</span>
2083 <span class="s2">        VALUES       (?)&quot;</span><span class="p">;</span>
2084 <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>
2085
2086 <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>
2087 <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>
2088     <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>
2089 <span class="p">}</span>
2090 <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>
2091 </code></pre></div>
2092 <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>
2093 <div class="highlight"><pre><span></span><code><span class="o">&lt;?</span><span class="nx">php</span>
2094 <span class="nv">$data</span> <span class="o">=</span> <span class="p">[</span>
2095     <span class="mi">1</span> <span class="o">=&gt;</span> <span class="s1">&#39;abc&#39;</span><span class="p">,</span>
2096     <span class="mi">3</span> <span class="o">=&gt;</span> <span class="s1">&#39;def&#39;</span><span class="p">,</span>
2097     <span class="mi">4</span> <span class="o">=&gt;</span> <span class="s1">&#39;ghi&#39;</span>
2098 <span class="p">];</span>
2099
2100 <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>
2101 <span class="s2">        SET     bar = ?</span>
2102 <span class="s2">        WHERE   exampleID = ?&quot;</span><span class="p">;</span>
2103 <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>
2104
2105 <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>
2106 <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>
2107     <span class="nv">$statement</span><span class="o">-&gt;</span><span class="na">execute</span><span class="p">([</span>
2108         <span class="nv">$bar</span><span class="p">,</span>
2109         <span class="nv">$exampleID</span>
2110     <span class="p">]);</span>
2111 <span class="p">}</span>
2112 <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>
2113 </code></pre></div>
2114
2115
2116
2117
2118 <hr>
2119 <div class="md-source-date">
2120   <small>
2121
2122       Last update: 2021-01-08
2123
2124   </small>
2125 </div>
2126
2127
2128
2129
2130
2131
2132
2133
2134             </article>
2135           </div>
2136         </div>
2137       </main>
2138
2139
2140 <footer class="md-footer">
2141
2142     <nav class="md-footer__inner md-grid" aria-label="Footer">
2143
2144         <a href="../database-objects/" class="md-footer__link md-footer__link--prev" rel="prev">
2145           <div class="md-footer__button md-icon">
2146             <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>
2147           </div>
2148           <div class="md-footer__title">
2149             <div class="md-ellipsis">
2150               <span class="md-footer__direction">
2151                 Previous
2152               </span>
2153               Database Objects
2154             </div>
2155           </div>
2156         </a>
2157
2158
2159         <a href="../exceptions/" class="md-footer__link md-footer__link--next" rel="next">
2160           <div class="md-footer__title">
2161             <div class="md-ellipsis">
2162               <span class="md-footer__direction">
2163                 Next
2164               </span>
2165               Exceptions
2166             </div>
2167           </div>
2168           <div class="md-footer__button md-icon">
2169             <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>
2170           </div>
2171         </a>
2172
2173     </nav>
2174
2175   <div class="md-footer-meta md-typeset">
2176     <div class="md-footer-meta__inner md-grid">
2177       <div class="md-footer-copyright">
2178
2179           <div class="md-footer-copyright__highlight">
2180             Copyright © 2020 WoltLab GmbH
2181           </div>
2182
2183         Made with
2184         <a href="https://squidfunk.github.io/mkdocs-material/" target="_blank" rel="noopener">
2185           Material for MkDocs
2186         </a>
2187
2188       </div>
2189       <div class="md-footer-copyright">
2190         <a href="https://www.woltlab.com/legal-notice/">Legal Notice</a>
2191         <a href="https://www.woltlab.com/privacy-policy/">Privacy Policy</a>
2192 </div>
2193     </div>
2194   </div>
2195 </footer>
2196
2197     </div>
2198     <div class="md-dialog" data-md-component="dialog">
2199       <div class="md-dialog__inner md-typeset"></div>
2200     </div>
2201     <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.fb4a9340.min.js", "version": {"provider": "mike"}}</script>
2202
2203
2204       <script src="../../assets/javascripts/bundle.5cf3e710.min.js"></script>
2205
2206
2207   </body>
2208 </html>