3 <html lang=
"en" class=
"no-js">
7 <meta name=
"viewport" content=
"width=device-width,initial-scale=1">
12 <link rel=
"icon" href=
"../../assets/default.favicon.ico">
13 <meta name=
"generator" content=
"mkdocs-1.1.2, mkdocs-material-7.1.0">
17 <title>Database Access - WoltLab Suite Documentation
</title>
21 <link rel=
"stylesheet" href=
"../../assets/stylesheets/main.33e2939f.min.css">
24 <link rel=
"stylesheet" href=
"../../assets/stylesheets/palette.ef6f36e2.min.css">
28 <meta name=
"theme-color" content=
"#009485">
38 <link rel=
"stylesheet" href=
"../../stylesheets/extra.css">
52 <body dir=
"ltr" data-md-color-scheme=
"" data-md-color-primary=
"teal" data-md-color-accent=
"">
55 <script>function __prefix(e){return new URL(
"../..",location).pathname+
"."+e}function __get(e,t=localStorage){return JSON.parse(t.getItem(__prefix(e)))}
</script>
57 <input class=
"md-toggle" data-md-toggle=
"drawer" type=
"checkbox" id=
"__drawer" autocomplete=
"off">
58 <input class=
"md-toggle" data-md-toggle=
"search" type=
"checkbox" id=
"__search" autocomplete=
"off">
59 <label class=
"md-overlay" for=
"__drawer"></label>
60 <div data-md-component=
"skip">
63 <a href=
"#database-access" class=
"md-skip">
68 <div data-md-component=
"announce">
70 <aside class=
"md-announce">
71 <div class=
"md-announce__inner md-grid md-typeset">
73 <a href=
"https://www.woltlab.com">Back to
<strong>woltlab.com
</strong></a>
80 <header class=
"md-header" data-md-component=
"header">
81 <nav class=
"md-header__inner md-grid" aria-label=
"Header">
82 <a href=
"../.." title=
"WoltLab Suite Documentation" class=
"md-header__button md-logo" aria-label=
"WoltLab Suite Documentation" data-md-component=
"logo">
84 <img src=
"../../assets/logo.png" alt=
"logo">
87 <label class=
"md-header__button md-icon" for=
"__drawer">
88 <svg xmlns=
"http://www.w3.org/2000/svg" viewBox=
"0 0 24 24"><path d=
"M3 6h18v2H3V6m0 5h18v2H3v-2m0 5h18v2H3v-2z"/></svg>
90 <div class=
"md-header__title" data-md-component=
"header-title">
91 <div class=
"md-header__ellipsis">
92 <div class=
"md-header__topic">
93 <span class=
"md-ellipsis">
94 WoltLab Suite Documentation
97 <div class=
"md-header__topic" data-md-component=
"header-topic">
98 <span class=
"md-ellipsis">
109 <label class=
"md-header__button md-icon" for=
"__search">
110 <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 <div class=
"md-search" data-md-component=
"search" role=
"dialog">
114 <label class=
"md-search__overlay" for=
"__search"></label>
115 <div class=
"md-search__inner" role=
"search">
116 <form class=
"md-search__form" name=
"search">
117 <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
>
118 <label class=
"md-search__icon md-icon" for=
"__search">
119 <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>
120 <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>
122 <button type=
"reset" class=
"md-search__icon md-icon" aria-label=
"Clear" tabindex=
"-1">
123 <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>
126 <div class=
"md-search__output">
127 <div class=
"md-search__scrollwrap" data-md-scrollfix
>
128 <div class=
"md-search-result" data-md-component=
"search-result">
129 <div class=
"md-search-result__meta">
132 <ol class=
"md-search-result__list"></ol>
140 <div class=
"md-header__source">
142 <a href=
"https://github.com/WoltLab/docs.woltlab.com/" title=
"Go to repository" class=
"md-source" data-md-component=
"source">
143 <div class=
"md-source__icon md-icon">
145 <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>
147 <div class=
"md-source__repository">
156 <div class=
"md-container" data-md-component=
"container">
161 <main class=
"md-main" data-md-component=
"main">
162 <div class=
"md-main__inner md-grid">
166 <div class=
"md-sidebar md-sidebar--primary" data-md-component=
"sidebar" data-md-type=
"navigation" >
167 <div class=
"md-sidebar__scrollwrap">
168 <div class=
"md-sidebar__inner">
172 <nav class=
"md-nav md-nav--primary" aria-label=
"Navigation" data-md-level=
"0">
173 <label class=
"md-nav__title" for=
"__drawer">
174 <a href=
"../.." title=
"WoltLab Suite Documentation" class=
"md-nav__button md-logo" aria-label=
"WoltLab Suite Documentation" data-md-component=
"logo">
176 <img src=
"../../assets/logo.png" alt=
"logo">
179 WoltLab Suite Documentation
182 <div class=
"md-nav__source">
184 <a href=
"https://github.com/WoltLab/docs.woltlab.com/" title=
"Go to repository" class=
"md-source" data-md-component=
"source">
185 <div class=
"md-source__icon md-icon">
187 <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>
189 <div class=
"md-source__repository">
195 <ul class=
"md-nav__list" data-md-scrollfix
>
204 <li class=
"md-nav__item">
205 <a href=
"../../getting-started/" class=
"md-nav__link">
222 <li class=
"md-nav__item md-nav__item--active md-nav__item--nested">
225 <input class=
"md-nav__toggle md-toggle" data-md-toggle=
"__nav_2" type=
"checkbox" id=
"__nav_2" checked
>
227 <label class=
"md-nav__link" for=
"__nav_2">
229 <span class=
"md-nav__icon md-icon"></span>
231 <nav class=
"md-nav" aria-label=
"PHP API" data-md-level=
"1">
232 <label class=
"md-nav__title" for=
"__nav_2">
233 <span class=
"md-nav__icon md-icon"></span>
236 <ul class=
"md-nav__list" data-md-scrollfix
>
242 <li class=
"md-nav__item">
243 <a href=
"../pages/" class=
"md-nav__link">
254 <li class=
"md-nav__item">
255 <a href=
"../database-objects/" class=
"md-nav__link">
268 <li class=
"md-nav__item md-nav__item--active">
270 <input class=
"md-nav__toggle md-toggle" data-md-toggle=
"toc" type=
"checkbox" id=
"__toc">
275 <label class=
"md-nav__link md-nav__link--active" for=
"__toc">
277 <span class=
"md-nav__icon md-icon"></span>
280 <a href=
"./" class=
"md-nav__link md-nav__link--active">
285 <nav class=
"md-nav md-nav--secondary" aria-label=
"Table of contents">
291 <label class=
"md-nav__title" for=
"__toc">
292 <span class=
"md-nav__icon md-icon"></span>
295 <ul class=
"md-nav__list" data-md-component=
"toc" data-md-scrollfix
>
297 <li class=
"md-nav__item">
298 <a href=
"#the-preparedstatement-object" class=
"md-nav__link">
299 The PreparedStatement Object
302 <nav class=
"md-nav" aria-label=
"The PreparedStatement Object">
303 <ul class=
"md-nav__list">
305 <li class=
"md-nav__item">
306 <a href=
"#query-parameters" class=
"md-nav__link">
312 <li class=
"md-nav__item">
313 <a href=
"#fetching-a-single-result" class=
"md-nav__link">
314 Fetching a Single Result
319 <li class=
"md-nav__item">
320 <a href=
"#fetch-by-column" class=
"md-nav__link">
326 <li class=
"md-nav__item">
327 <a href=
"#fetching-all-results" class=
"md-nav__link">
338 <li class=
"md-nav__item">
339 <a href=
"#building-complex-conditions" class=
"md-nav__link">
340 Building Complex Conditions
345 <li class=
"md-nav__item">
346 <a href=
"#insert-or-update-in-bulk" class=
"md-nav__link">
347 INSERT or UPDATE in Bulk
364 <li class=
"md-nav__item">
365 <a href=
"../exceptions/" class=
"md-nav__link">
377 <li class=
"md-nav__item md-nav__item--nested">
380 <input class=
"md-nav__toggle md-toggle" data-md-toggle=
"__nav_2_5" type=
"checkbox" id=
"__nav_2_5" >
382 <label class=
"md-nav__link" for=
"__nav_2_5">
384 <span class=
"md-nav__icon md-icon"></span>
386 <nav class=
"md-nav" aria-label=
"API" data-md-level=
"2">
387 <label class=
"md-nav__title" for=
"__nav_2_5">
388 <span class=
"md-nav__icon md-icon"></span>
391 <ul class=
"md-nav__list" data-md-scrollfix
>
398 <li class=
"md-nav__item md-nav__item--nested">
401 <input class=
"md-nav__toggle md-toggle" data-md-toggle=
"__nav_2_5_1" type=
"checkbox" id=
"__nav_2_5_1" >
403 <label class=
"md-nav__link" for=
"__nav_2_5_1">
405 <span class=
"md-nav__icon md-icon"></span>
407 <nav class=
"md-nav" aria-label=
"Caches" data-md-level=
"3">
408 <label class=
"md-nav__title" for=
"__nav_2_5_1">
409 <span class=
"md-nav__icon md-icon"></span>
412 <ul class=
"md-nav__list" data-md-scrollfix
>
418 <li class=
"md-nav__item">
419 <a href=
"../api/caches/" class=
"md-nav__link">
430 <li class=
"md-nav__item">
431 <a href=
"../api/caches_persistent-caches/" class=
"md-nav__link">
442 <li class=
"md-nav__item">
443 <a href=
"../api/caches_runtime-caches/" class=
"md-nav__link">
460 <li class=
"md-nav__item">
461 <a href=
"../api/comments/" class=
"md-nav__link">
472 <li class=
"md-nav__item">
473 <a href=
"../api/cronjobs/" class=
"md-nav__link">
484 <li class=
"md-nav__item">
485 <a href=
"../api/events/" class=
"md-nav__link">
497 <li class=
"md-nav__item md-nav__item--nested">
500 <input class=
"md-nav__toggle md-toggle" data-md-toggle=
"__nav_2_5_5" type=
"checkbox" id=
"__nav_2_5_5" >
502 <label class=
"md-nav__link" for=
"__nav_2_5_5">
504 <span class=
"md-nav__icon md-icon"></span>
506 <nav class=
"md-nav" aria-label=
"Form Builder" data-md-level=
"3">
507 <label class=
"md-nav__title" for=
"__nav_2_5_5">
508 <span class=
"md-nav__icon md-icon"></span>
511 <ul class=
"md-nav__list" data-md-scrollfix
>
517 <li class=
"md-nav__item">
518 <a href=
"../api/form_builder/overview/" class=
"md-nav__link">
529 <li class=
"md-nav__item">
530 <a href=
"../api/form_builder/structure/" class=
"md-nav__link">
541 <li class=
"md-nav__item">
542 <a href=
"../api/form_builder/form_fields/" class=
"md-nav__link">
553 <li class=
"md-nav__item">
554 <a href=
"../api/form_builder/validation_data/" class=
"md-nav__link">
565 <li class=
"md-nav__item">
566 <a href=
"../api/form_builder/dependencies/" class=
"md-nav__link">
583 <li class=
"md-nav__item">
584 <a href=
"../api/package_installation_plugins/" class=
"md-nav__link">
585 Package Installation Plugins
595 <li class=
"md-nav__item">
596 <a href=
"../api/user_activity_points/" class=
"md-nav__link">
607 <li class=
"md-nav__item">
608 <a href=
"../api/user_notifications/" class=
"md-nav__link">
619 <li class=
"md-nav__item">
620 <a href=
"../api/sitemaps/" class=
"md-nav__link">
637 <li class=
"md-nav__item">
638 <a href=
"../code-style/" class=
"md-nav__link">
649 <li class=
"md-nav__item">
650 <a href=
"../apps/" class=
"md-nav__link">
661 <li class=
"md-nav__item">
662 <a href=
"../gdpr/" class=
"md-nav__link">
683 <li class=
"md-nav__item md-nav__item--nested">
686 <input class=
"md-nav__toggle md-toggle" data-md-toggle=
"__nav_3" type=
"checkbox" id=
"__nav_3" >
688 <label class=
"md-nav__link" for=
"__nav_3">
689 Languages, Templates & CSS
690 <span class=
"md-nav__icon md-icon"></span>
692 <nav class=
"md-nav" aria-label=
"Languages, Templates & CSS" data-md-level=
"1">
693 <label class=
"md-nav__title" for=
"__nav_3">
694 <span class=
"md-nav__icon md-icon"></span>
695 Languages, Templates & CSS
697 <ul class=
"md-nav__list" data-md-scrollfix
>
703 <li class=
"md-nav__item">
704 <a href=
"../../view/languages/" class=
"md-nav__link">
715 <li class=
"md-nav__item">
716 <a href=
"../../view/templates/" class=
"md-nav__link">
727 <li class=
"md-nav__item">
728 <a href=
"../../view/template-plugins/" class=
"md-nav__link">
739 <li class=
"md-nav__item">
740 <a href=
"../../view/css/" class=
"md-nav__link">
761 <li class=
"md-nav__item md-nav__item--nested">
764 <input class=
"md-nav__toggle md-toggle" data-md-toggle=
"__nav_4" type=
"checkbox" id=
"__nav_4" >
766 <label class=
"md-nav__link" for=
"__nav_4">
767 TypeScript and JavaScript API
768 <span class=
"md-nav__icon md-icon"></span>
770 <nav class=
"md-nav" aria-label=
"TypeScript and JavaScript API" data-md-level=
"1">
771 <label class=
"md-nav__title" for=
"__nav_4">
772 <span class=
"md-nav__icon md-icon"></span>
773 TypeScript and JavaScript API
775 <ul class=
"md-nav__list" data-md-scrollfix
>
781 <li class=
"md-nav__item">
782 <a href=
"../../javascript/general-usage/" class=
"md-nav__link">
793 <li class=
"md-nav__item">
794 <a href=
"../../javascript/typescript/" class=
"md-nav__link">
806 <li class=
"md-nav__item md-nav__item--nested">
809 <input class=
"md-nav__toggle md-toggle" data-md-toggle=
"__nav_4_3" type=
"checkbox" id=
"__nav_4_3" >
811 <label class=
"md-nav__link" for=
"__nav_4_3">
813 <span class=
"md-nav__icon md-icon"></span>
815 <nav class=
"md-nav" aria-label=
"New API" data-md-level=
"2">
816 <label class=
"md-nav__title" for=
"__nav_4_3">
817 <span class=
"md-nav__icon md-icon"></span>
820 <ul class=
"md-nav__list" data-md-scrollfix
>
826 <li class=
"md-nav__item">
827 <a href=
"../../javascript/new-api_writing-a-module/" class=
"md-nav__link">
838 <li class=
"md-nav__item">
839 <a href=
"../../javascript/new-api_data-structures/" class=
"md-nav__link">
850 <li class=
"md-nav__item">
851 <a href=
"../../javascript/new-api_core/" class=
"md-nav__link">
862 <li class=
"md-nav__item">
863 <a href=
"../../javascript/new-api_dom/" class=
"md-nav__link">
874 <li class=
"md-nav__item">
875 <a href=
"../../javascript/new-api_events/" class=
"md-nav__link">
886 <li class=
"md-nav__item">
887 <a href=
"../../javascript/new-api_ajax/" class=
"md-nav__link">
898 <li class=
"md-nav__item">
899 <a href=
"../../javascript/new-api_dialogs/" class=
"md-nav__link">
910 <li class=
"md-nav__item">
911 <a href=
"../../javascript/new-api_browser/" class=
"md-nav__link">
912 Browser and Screen Sizes
922 <li class=
"md-nav__item">
923 <a href=
"../../javascript/new-api_ui/" class=
"md-nav__link">
940 <li class=
"md-nav__item">
941 <a href=
"../../javascript/legacy-api/" class=
"md-nav__link">
952 <li class=
"md-nav__item">
953 <a href=
"../../javascript/helper-functions/" class=
"md-nav__link">
964 <li class=
"md-nav__item">
965 <a href=
"../../javascript/code-snippets/" class=
"md-nav__link">
986 <li class=
"md-nav__item md-nav__item--nested">
989 <input class=
"md-nav__toggle md-toggle" data-md-toggle=
"__nav_5" type=
"checkbox" id=
"__nav_5" >
991 <label class=
"md-nav__link" for=
"__nav_5">
993 <span class=
"md-nav__icon md-icon"></span>
995 <nav class=
"md-nav" aria-label=
"Package Components" data-md-level=
"1">
996 <label class=
"md-nav__title" for=
"__nav_5">
997 <span class=
"md-nav__icon md-icon"></span>
1000 <ul class=
"md-nav__list" data-md-scrollfix
>
1006 <li class=
"md-nav__item">
1007 <a href=
"../../package/package-xml/" class=
"md-nav__link">
1019 <li class=
"md-nav__item md-nav__item--nested">
1022 <input class=
"md-nav__toggle md-toggle" data-md-toggle=
"__nav_5_2" type=
"checkbox" id=
"__nav_5_2" >
1024 <label class=
"md-nav__link" for=
"__nav_5_2">
1026 <span class=
"md-nav__icon md-icon"></span>
1028 <nav class=
"md-nav" aria-label=
"PIPs" data-md-level=
"2">
1029 <label class=
"md-nav__title" for=
"__nav_5_2">
1030 <span class=
"md-nav__icon md-icon"></span>
1033 <ul class=
"md-nav__list" data-md-scrollfix
>
1039 <li class=
"md-nav__item">
1040 <a href=
"../../package/pip/" class=
"md-nav__link">
1051 <li class=
"md-nav__item">
1052 <a href=
"../../package/pip/acl-option/" class=
"md-nav__link">
1063 <li class=
"md-nav__item">
1064 <a href=
"../../package/pip/acp-menu/" class=
"md-nav__link">
1075 <li class=
"md-nav__item">
1076 <a href=
"../../package/pip/acp-search-provider/" class=
"md-nav__link">
1087 <li class=
"md-nav__item">
1088 <a href=
"../../package/pip/acp-template/" class=
"md-nav__link">
1099 <li class=
"md-nav__item">
1100 <a href=
"../../package/pip/bbcode/" class=
"md-nav__link">
1111 <li class=
"md-nav__item">
1112 <a href=
"../../package/pip/box/" class=
"md-nav__link">
1123 <li class=
"md-nav__item">
1124 <a href=
"../../package/pip/clipboard-action/" class=
"md-nav__link">
1135 <li class=
"md-nav__item">
1136 <a href=
"../../package/pip/core-object/" class=
"md-nav__link">
1147 <li class=
"md-nav__item">
1148 <a href=
"../../package/pip/cronjob/" class=
"md-nav__link">
1159 <li class=
"md-nav__item">
1160 <a href=
"../../package/pip/database/" class=
"md-nav__link">
1171 <li class=
"md-nav__item">
1172 <a href=
"../../package/pip/event-listener/" class=
"md-nav__link">
1183 <li class=
"md-nav__item">
1184 <a href=
"../../package/pip/file/" class=
"md-nav__link">
1195 <li class=
"md-nav__item">
1196 <a href=
"../../package/pip/language/" class=
"md-nav__link">
1207 <li class=
"md-nav__item">
1208 <a href=
"../../package/pip/media-provider/" class=
"md-nav__link">
1219 <li class=
"md-nav__item">
1220 <a href=
"../../package/pip/menu/" class=
"md-nav__link">
1231 <li class=
"md-nav__item">
1232 <a href=
"../../package/pip/menu-item/" class=
"md-nav__link">
1243 <li class=
"md-nav__item">
1244 <a href=
"../../package/pip/object-type/" class=
"md-nav__link">
1255 <li class=
"md-nav__item">
1256 <a href=
"../../package/pip/object-type-definition/" class=
"md-nav__link">
1257 objectTypeDefinition
1267 <li class=
"md-nav__item">
1268 <a href=
"../../package/pip/option/" class=
"md-nav__link">
1279 <li class=
"md-nav__item">
1280 <a href=
"../../package/pip/page/" class=
"md-nav__link">
1291 <li class=
"md-nav__item">
1292 <a href=
"../../package/pip/pip/" class=
"md-nav__link">
1303 <li class=
"md-nav__item">
1304 <a href=
"../../package/pip/script/" class=
"md-nav__link">
1315 <li class=
"md-nav__item">
1316 <a href=
"../../package/pip/smiley/" class=
"md-nav__link">
1327 <li class=
"md-nav__item">
1328 <a href=
"../../package/pip/sql/" class=
"md-nav__link">
1339 <li class=
"md-nav__item">
1340 <a href=
"../../package/pip/style/" class=
"md-nav__link">
1351 <li class=
"md-nav__item">
1352 <a href=
"../../package/pip/template/" class=
"md-nav__link">
1363 <li class=
"md-nav__item">
1364 <a href=
"../../package/pip/template-listener/" class=
"md-nav__link">
1375 <li class=
"md-nav__item">
1376 <a href=
"../../package/pip/user-group-option/" class=
"md-nav__link">
1387 <li class=
"md-nav__item">
1388 <a href=
"../../package/pip/user-menu/" class=
"md-nav__link">
1399 <li class=
"md-nav__item">
1400 <a href=
"../../package/pip/user-notification-event/" class=
"md-nav__link">
1401 userNotificationEvent
1411 <li class=
"md-nav__item">
1412 <a href=
"../../package/pip/user-option/" class=
"md-nav__link">
1423 <li class=
"md-nav__item">
1424 <a href=
"../../package/pip/user-profile-menu/" class=
"md-nav__link">
1441 <li class=
"md-nav__item">
1442 <a href=
"../../package/database-php-api/" class=
"md-nav__link">
1463 <li class=
"md-nav__item md-nav__item--nested">
1466 <input class=
"md-nav__toggle md-toggle" data-md-toggle=
"__nav_6" type=
"checkbox" id=
"__nav_6" >
1468 <label class=
"md-nav__link" for=
"__nav_6">
1470 <span class=
"md-nav__icon md-icon"></span>
1472 <nav class=
"md-nav" aria-label=
"Migration" data-md-level=
"1">
1473 <label class=
"md-nav__title" for=
"__nav_6">
1474 <span class=
"md-nav__icon md-icon"></span>
1477 <ul class=
"md-nav__list" data-md-scrollfix
>
1484 <li class=
"md-nav__item md-nav__item--nested">
1487 <input class=
"md-nav__toggle md-toggle" data-md-toggle=
"__nav_6_1" type=
"checkbox" id=
"__nav_6_1" >
1489 <label class=
"md-nav__link" for=
"__nav_6_1">
1490 Migrating from WSC
5.3
1491 <span class=
"md-nav__icon md-icon"></span>
1493 <nav class=
"md-nav" aria-label=
"Migrating from WSC 5.3" data-md-level=
"2">
1494 <label class=
"md-nav__title" for=
"__nav_6_1">
1495 <span class=
"md-nav__icon md-icon"></span>
1496 Migrating from WSC
5.3
1498 <ul class=
"md-nav__list" data-md-scrollfix
>
1504 <li class=
"md-nav__item">
1505 <a href=
"../../migration/wsc53/php/" class=
"md-nav__link">
1516 <li class=
"md-nav__item">
1517 <a href=
"../../migration/wsc53/session/" class=
"md-nav__link">
1518 Session Handling and Authentication
1528 <li class=
"md-nav__item">
1529 <a href=
"../../migration/wsc53/javascript/" class=
"md-nav__link">
1530 TypeScript and JavaScript
1540 <li class=
"md-nav__item">
1541 <a href=
"../../migration/wsc53/templates/" class=
"md-nav__link">
1552 <li class=
"md-nav__item">
1553 <a href=
"../../migration/wsc53/libraries/" class=
"md-nav__link">
1554 Third Party Libraries
1571 <li class=
"md-nav__item md-nav__item--nested">
1574 <input class=
"md-nav__toggle md-toggle" data-md-toggle=
"__nav_6_2" type=
"checkbox" id=
"__nav_6_2" >
1576 <label class=
"md-nav__link" for=
"__nav_6_2">
1577 Migrating from WSC
5.2
1578 <span class=
"md-nav__icon md-icon"></span>
1580 <nav class=
"md-nav" aria-label=
"Migrating from WSC 5.2" data-md-level=
"2">
1581 <label class=
"md-nav__title" for=
"__nav_6_2">
1582 <span class=
"md-nav__icon md-icon"></span>
1583 Migrating from WSC
5.2
1585 <ul class=
"md-nav__list" data-md-scrollfix
>
1591 <li class=
"md-nav__item">
1592 <a href=
"../../migration/wsc52/php/" class=
"md-nav__link">
1603 <li class=
"md-nav__item">
1604 <a href=
"../../migration/wsc52/templates/" class=
"md-nav__link">
1605 Templates and Languages
1615 <li class=
"md-nav__item">
1616 <a href=
"../../migration/wsc52/libraries/" class=
"md-nav__link">
1617 Third Party Libraries
1634 <li class=
"md-nav__item md-nav__item--nested">
1637 <input class=
"md-nav__toggle md-toggle" data-md-toggle=
"__nav_6_3" type=
"checkbox" id=
"__nav_6_3" >
1639 <label class=
"md-nav__link" for=
"__nav_6_3">
1640 Migrating from WSC
3.1
1641 <span class=
"md-nav__icon md-icon"></span>
1643 <nav class=
"md-nav" aria-label=
"Migrating from WSC 3.1" data-md-level=
"2">
1644 <label class=
"md-nav__title" for=
"__nav_6_3">
1645 <span class=
"md-nav__icon md-icon"></span>
1646 Migrating from WSC
3.1
1648 <ul class=
"md-nav__list" data-md-scrollfix
>
1654 <li class=
"md-nav__item">
1655 <a href=
"../../migration/wsc31/php/" class=
"md-nav__link">
1673 <li class=
"md-nav__item md-nav__item--nested">
1676 <input class=
"md-nav__toggle md-toggle" data-md-toggle=
"__nav_6_4" type=
"checkbox" id=
"__nav_6_4" >
1678 <label class=
"md-nav__link" for=
"__nav_6_4">
1679 Migrating from WSC
3.0
1680 <span class=
"md-nav__icon md-icon"></span>
1682 <nav class=
"md-nav" aria-label=
"Migrating from WSC 3.0" data-md-level=
"2">
1683 <label class=
"md-nav__title" for=
"__nav_6_4">
1684 <span class=
"md-nav__icon md-icon"></span>
1685 Migrating from WSC
3.0
1687 <ul class=
"md-nav__list" data-md-scrollfix
>
1693 <li class=
"md-nav__item">
1694 <a href=
"../../migration/wsc30/php/" class=
"md-nav__link">
1705 <li class=
"md-nav__item">
1706 <a href=
"../../migration/wsc30/javascript/" class=
"md-nav__link">
1717 <li class=
"md-nav__item">
1718 <a href=
"../../migration/wsc30/templates/" class=
"md-nav__link">
1729 <li class=
"md-nav__item">
1730 <a href=
"../../migration/wsc30/css/" class=
"md-nav__link">
1741 <li class=
"md-nav__item">
1742 <a href=
"../../migration/wsc30/package/" class=
"md-nav__link">
1760 <li class=
"md-nav__item md-nav__item--nested">
1763 <input class=
"md-nav__toggle md-toggle" data-md-toggle=
"__nav_6_5" type=
"checkbox" id=
"__nav_6_5" >
1765 <label class=
"md-nav__link" for=
"__nav_6_5">
1766 Migrating from WCF
2.1
1767 <span class=
"md-nav__icon md-icon"></span>
1769 <nav class=
"md-nav" aria-label=
"Migrating from WCF 2.1" data-md-level=
"2">
1770 <label class=
"md-nav__title" for=
"__nav_6_5">
1771 <span class=
"md-nav__icon md-icon"></span>
1772 Migrating from WCF
2.1
1774 <ul class=
"md-nav__list" data-md-scrollfix
>
1780 <li class=
"md-nav__item">
1781 <a href=
"../../migration/wcf21/php/" class=
"md-nav__link">
1792 <li class=
"md-nav__item">
1793 <a href=
"../../migration/wcf21/templates/" class=
"md-nav__link">
1804 <li class=
"md-nav__item">
1805 <a href=
"../../migration/wcf21/css/" class=
"md-nav__link">
1816 <li class=
"md-nav__item">
1817 <a href=
"../../migration/wcf21/package/" class=
"md-nav__link">
1844 <li class=
"md-nav__item md-nav__item--nested">
1847 <input class=
"md-nav__toggle md-toggle" data-md-toggle=
"__nav_7" type=
"checkbox" id=
"__nav_7" >
1849 <label class=
"md-nav__link" for=
"__nav_7">
1851 <span class=
"md-nav__icon md-icon"></span>
1853 <nav class=
"md-nav" aria-label=
"Tutorials" data-md-level=
"1">
1854 <label class=
"md-nav__title" for=
"__nav_7">
1855 <span class=
"md-nav__icon md-icon"></span>
1858 <ul class=
"md-nav__list" data-md-scrollfix
>
1865 <li class=
"md-nav__item md-nav__item--nested">
1868 <input class=
"md-nav__toggle md-toggle" data-md-toggle=
"__nav_7_1" type=
"checkbox" id=
"__nav_7_1" >
1870 <label class=
"md-nav__link" for=
"__nav_7_1">
1872 <span class=
"md-nav__icon md-icon"></span>
1874 <nav class=
"md-nav" aria-label=
"Tutorial Series" data-md-level=
"2">
1875 <label class=
"md-nav__title" for=
"__nav_7_1">
1876 <span class=
"md-nav__icon md-icon"></span>
1879 <ul class=
"md-nav__list" data-md-scrollfix
>
1885 <li class=
"md-nav__item">
1886 <a href=
"../../tutorial/series/overview/" class=
"md-nav__link">
1897 <li class=
"md-nav__item">
1898 <a href=
"../../tutorial/series/part_1/" class=
"md-nav__link">
1909 <li class=
"md-nav__item">
1910 <a href=
"../../tutorial/series/part_2/" class=
"md-nav__link">
1921 <li class=
"md-nav__item">
1922 <a href=
"../../tutorial/series/part_3/" class=
"md-nav__link">
1949 <div class=
"md-sidebar md-sidebar--secondary" data-md-component=
"sidebar" data-md-type=
"toc" >
1950 <div class=
"md-sidebar__scrollwrap">
1951 <div class=
"md-sidebar__inner">
1953 <nav class=
"md-nav md-nav--secondary" aria-label=
"Table of contents">
1959 <label class=
"md-nav__title" for=
"__toc">
1960 <span class=
"md-nav__icon md-icon"></span>
1963 <ul class=
"md-nav__list" data-md-component=
"toc" data-md-scrollfix
>
1965 <li class=
"md-nav__item">
1966 <a href=
"#the-preparedstatement-object" class=
"md-nav__link">
1967 The PreparedStatement Object
1970 <nav class=
"md-nav" aria-label=
"The PreparedStatement Object">
1971 <ul class=
"md-nav__list">
1973 <li class=
"md-nav__item">
1974 <a href=
"#query-parameters" class=
"md-nav__link">
1980 <li class=
"md-nav__item">
1981 <a href=
"#fetching-a-single-result" class=
"md-nav__link">
1982 Fetching a Single Result
1987 <li class=
"md-nav__item">
1988 <a href=
"#fetch-by-column" class=
"md-nav__link">
1994 <li class=
"md-nav__item">
1995 <a href=
"#fetching-all-results" class=
"md-nav__link">
1996 Fetching All Results
2006 <li class=
"md-nav__item">
2007 <a href=
"#building-complex-conditions" class=
"md-nav__link">
2008 Building Complex Conditions
2013 <li class=
"md-nav__item">
2014 <a href=
"#insert-or-update-in-bulk" class=
"md-nav__link">
2015 INSERT or UPDATE in Bulk
2028 <div class=
"md-content" data-md-component=
"content">
2029 <article class=
"md-content__inner md-typeset">
2032 <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">
2033 <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>
2037 <h1 id=
"database-access">Database Access
<a class=
"headerlink" href=
"#database-access" title=
"Permanent link">#
</a></h1>
2038 <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>
2039 <h2 id=
"the-preparedstatement-object">The PreparedStatement Object
<a class=
"headerlink" href=
"#the-preparedstatement-object" title=
"Permanent link">#
</a></h2>
2040 <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>
2041 <div class=
"highlight"><pre><span></span><code><span class=
"o"><?
</span><span class=
"nx">php
</span>
2042 <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">-
></span><span class=
"na">prepareStatement
</span><span class=
"p">(
</span><span class=
"s2">"SELECT * FROM wcf
"</span><span class=
"o">.
</span><span class=
"nx">WCF_N
</span><span class=
"o">.
</span><span class=
"s2">"_example
"</span><span class=
"p">);
</span>
2043 <span class=
"nv">$statement
</span><span class=
"o">-
></span><span class=
"na">execute
</span><span class=
"p">();
</span>
2044 <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">-
></span><span class=
"na">fetchArray
</span><span class=
"p">())
</span> <span class=
"p">{
</span>
2045 <span class=
"c1">// handle result
</span>
2046 <span class=
"p">}
</span>
2048 <h3 id=
"query-parameters">Query Parameters
<a class=
"headerlink" href=
"#query-parameters" title=
"Permanent link">#
</a></h3>
2049 <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-
>execute()
</code> with a continuous, one-dimensional array that exactly match the number of question marks.
</p>
2050 <div class=
"highlight"><pre><span></span><code><span class=
"o"><?
</span><span class=
"nx">php
</span>
2051 <span class=
"nv">$sql
</span> <span class=
"o">=
</span> <span class=
"s2">"SELECT *
</span>
2052 <span class=
"s2"> FROM wcf
"</span><span class=
"o">.
</span><span class=
"nx">WCF_N
</span><span class=
"o">.
</span><span class=
"s2">"_example
</span>
2053 <span class=
"s2"> WHERE exampleID = ?
</span>
2054 <span class=
"s2"> OR bar IN (?, ?, ?, ?, ?)
"</span><span class=
"p">;
</span>
2055 <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">-
></span><span class=
"na">prepareStatement
</span><span class=
"p">(
</span><span class=
"nv">$sql
</span><span class=
"p">);
</span>
2056 <span class=
"nv">$statement
</span><span class=
"o">-
></span><span class=
"na">execute
</span><span class=
"p">([
</span>
2057 <span class=
"nv">$exampleID
</span><span class=
"p">,
</span>
2058 <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>
2059 <span class=
"p">]);
</span>
2060 <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">-
></span><span class=
"na">fetchArray
</span><span class=
"p">())
</span> <span class=
"p">{
</span>
2061 <span class=
"c1">// handle result
</span>
2062 <span class=
"p">}
</span>
2064 <h3 id=
"fetching-a-single-result">Fetching a Single Result
<a class=
"headerlink" href=
"#fetching-a-single-result" title=
"Permanent link">#
</a></h3>
2065 <div class=
"admonition danger">
2066 <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>
2068 <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>
2069 <div class=
"highlight"><pre><span></span><code><span class=
"o"><?
</span><span class=
"nx">php
</span>
2070 <span class=
"nv">$sql
</span> <span class=
"o">=
</span> <span class=
"s2">"SELECT *
</span>
2071 <span class=
"s2"> FROM wcf
"</span><span class=
"o">.
</span><span class=
"nx">WCF_N
</span><span class=
"o">.
</span><span class=
"s2">"_example
</span>
2072 <span class=
"s2"> WHERE exampleID = ?
"</span><span class=
"p">;
</span>
2073 <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">-
></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>
2074 <span class=
"nv">$statement
</span><span class=
"o">-
></span><span class=
"na">execute
</span><span class=
"p">([
</span><span class=
"nv">$exampleID
</span><span class=
"p">]);
</span>
2075 <span class=
"nv">$row
</span> <span class=
"o">=
</span> <span class=
"nv">$statement
</span><span class=
"o">-
></span><span class=
"na">fetchSingleRow
</span><span class=
"p">();
</span>
2077 <p>There are two distinct differences when comparing with the example on query parameters above:
</p>
2079 <li>The method
<code>prepareStatement()
</code> receives a secondary parameter that will be appended to the query as
<code>LIMIT
1</code>.
</li>
2080 <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>
2082 <h3 id=
"fetch-by-column">Fetch by Column
<a class=
"headerlink" href=
"#fetch-by-column" title=
"Permanent link">#
</a></h3>
2083 <div class=
"admonition warning">
2084 <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>
2086 <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>
2087 <div class=
"highlight"><pre><span></span><code><span class=
"o"><?
</span><span class=
"nx">php
</span>
2088 <span class=
"nv">$sql
</span> <span class=
"o">=
</span> <span class=
"s2">"SELECT bar
</span>
2089 <span class=
"s2"> FROM wcf
"</span><span class=
"o">.
</span><span class=
"nx">WCF_N
</span><span class=
"o">.
</span><span class=
"s2">"_example
"</span><span class=
"p">;
</span>
2090 <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">-
></span><span class=
"na">prepareStatement
</span><span class=
"p">(
</span><span class=
"nv">$sql
</span><span class=
"p">);
</span>
2091 <span class=
"nv">$statement
</span><span class=
"o">-
></span><span class=
"na">execute
</span><span class=
"p">();
</span>
2092 <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">-
></span><span class=
"na">fetchColumn
</span><span class=
"p">())
</span> <span class=
"p">{
</span>
2093 <span class=
"c1">// handle result
</span>
2094 <span class=
"p">}
</span>
2095 <span class=
"nv">$bar
</span> <span class=
"o">=
</span> <span class=
"nv">$statement
</span><span class=
"o">-
></span><span class=
"na">fetchSingleColumn
</span><span class=
"p">();
</span>
2097 <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>
2098 <div class=
"highlight"><pre><span></span><code><span class=
"o"><?
</span><span class=
"nx">php
</span>
2099 <span class=
"nv">$sql
</span> <span class=
"o">=
</span> <span class=
"s2">"SELECT bar
</span>
2100 <span class=
"s2"> FROM wcf
"</span><span class=
"o">.
</span><span class=
"nx">WCF_N
</span><span class=
"o">.
</span><span class=
"s2">"_example
</span>
2101 <span class=
"s2"> WHERE exampleID = ?
"</span><span class=
"p">;
</span>
2102 <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">-
></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>
2103 <span class=
"nv">$statement
</span><span class=
"o">-
></span><span class=
"na">execute
</span><span class=
"p">([
</span><span class=
"nv">$exampleID
</span><span class=
"p">]);
</span>
2104 <span class=
"nv">$bar
</span> <span class=
"o">=
</span> <span class=
"nv">$statement
</span><span class=
"o">-
></span><span class=
"na">fetchSingleColumn
</span><span class=
"p">();
</span>
2106 <h3 id=
"fetching-all-results">Fetching All Results
<a class=
"headerlink" href=
"#fetching-all-results" title=
"Permanent link">#
</a></h3>
2107 <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>
2108 <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>
2109 <div class=
"highlight"><pre><span></span><code><span class=
"o"><?
</span><span class=
"nx">php
</span>
2110 <span class=
"nv">$sql
</span> <span class=
"o">=
</span> <span class=
"s2">"SELECT *
</span>
2111 <span class=
"s2"> FROM wcf
"</span><span class=
"o">.
</span><span class=
"nx">WCF_N
</span><span class=
"o">.
</span><span class=
"s2">"_example
"</span><span class=
"p">;
</span>
2112 <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">-
></span><span class=
"na">prepareStatement
</span><span class=
"p">(
</span><span class=
"nv">$sql
</span><span class=
"p">);
</span>
2113 <span class=
"nv">$statement
</span><span class=
"o">-
></span><span class=
"na">execute
</span><span class=
"p">();
</span>
2114 <span class=
"nv">$rows
</span> <span class=
"o">=
</span> <span class=
"nv">$statement
</span><span class=
"o">-
></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>
2116 <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>
2117 <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>
2118 <div class=
"highlight"><pre><span></span><code><span class=
"o"><?
</span><span class=
"nx">php
</span>
2119 <span class=
"nv">$sql
</span> <span class=
"o">=
</span> <span class=
"s2">"SELECT exampleID
</span>
2120 <span class=
"s2"> FROM wcf
"</span><span class=
"o">.
</span><span class=
"nx">WCF_N
</span><span class=
"o">.
</span><span class=
"s2">"_example
"</span><span class=
"p">;
</span>
2121 <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">-
></span><span class=
"na">prepareStatement
</span><span class=
"p">(
</span><span class=
"nv">$sql
</span><span class=
"p">);
</span>
2122 <span class=
"nv">$statement
</span><span class=
"o">-
></span><span class=
"na">execute
</span><span class=
"p">();
</span>
2123 <span class=
"nv">$exampleIDs
</span> <span class=
"o">=
</span> <span class=
"nv">$statement
</span><span class=
"o">-
></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>
2125 <p>As a result, you get an array with all
<code>exampleID
</code> values.
</p>
2126 <p>The
<code>PreparedStatement
</code> class adds an additional methods that covers another common use case in our code:
2127 Fetching two columns and using the first column's value as the array key and the second column's value as the array value.
2128 This case is covered by
<code>PreparedStatement::fetchMap()
</code>:
</p>
2129 <div class=
"highlight"><pre><span></span><code><span class=
"o"><?
</span><span class=
"nx">php
</span>
2130 <span class=
"nv">$sql
</span> <span class=
"o">=
</span> <span class=
"s2">"SELECT exampleID, userID
</span>
2131 <span class=
"s2"> FROM wcf
"</span><span class=
"o">.
</span><span class=
"nx">WCF_N
</span><span class=
"o">.
</span><span class=
"s2">"_example_mapping
"</span><span class=
"p">;
</span>
2132 <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">-
></span><span class=
"na">prepareStatement
</span><span class=
"p">(
</span><span class=
"nv">$sql
</span><span class=
"p">);
</span>
2133 <span class=
"nv">$statement
</span><span class=
"o">-
></span><span class=
"na">execute
</span><span class=
"p">();
</span>
2134 <span class=
"nv">$map
</span> <span class=
"o">=
</span> <span class=
"nv">$statement
</span><span class=
"o">-
></span><span class=
"na">fetchMap
</span><span class=
"p">(
</span><span class=
"s1">'exampleID
'</span><span class=
"p">,
</span> <span class=
"s1">'userID
'</span><span class=
"p">);
</span>
2136 <p><code>$map
</code> is a one-dimensional array where each
<code>exampleID
</code> value maps to the corresponding
<code>userID
</code> value.
</p>
2137 <div class=
"admonition warning">
2138 <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>
2140 <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>
2141 <div class=
"highlight"><pre><span></span><code><span class=
"o"><?
</span><span class=
"nx">php
</span>
2142 <span class=
"nv">$sql
</span> <span class=
"o">=
</span> <span class=
"s2">"SELECT exampleID, userID
</span>
2143 <span class=
"s2"> FROM wcf
"</span><span class=
"o">.
</span><span class=
"nx">WCF_N
</span><span class=
"o">.
</span><span class=
"s2">"_example_mapping
"</span><span class=
"p">;
</span>
2144 <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">-
></span><span class=
"na">prepareStatement
</span><span class=
"p">(
</span><span class=
"nv">$sql
</span><span class=
"p">);
</span>
2145 <span class=
"nv">$statement
</span><span class=
"o">-
></span><span class=
"na">execute
</span><span class=
"p">();
</span>
2146 <span class=
"nv">$map
</span> <span class=
"o">=
</span> <span class=
"nv">$statement
</span><span class=
"o">-
></span><span class=
"na">fetchMap
</span><span class=
"p">(
</span><span class=
"s1">'exampleID
'</span><span class=
"p">,
</span> <span class=
"s1">'userID
'</span><span class=
"p">,
</span> <span class=
"k">false
</span><span class=
"p">);
</span>
2148 <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>
2149 <h2 id=
"building-complex-conditions">Building Complex Conditions
<a class=
"headerlink" href=
"#building-complex-conditions" title=
"Permanent link">#
</a></h2>
2150 <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>
2151 <div class=
"highlight"><pre><span></span><code><span class=
"o"><?
</span><span class=
"nx">php
</span>
2152 <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>
2153 <span class=
"nv">$conditions
</span><span class=
"o">-
></span><span class=
"na">add
</span><span class=
"p">(
</span><span class=
"s2">"exampleID = ?
"</span><span class=
"p">,
</span> <span class=
"p">[
</span><span class=
"nv">$exampleID
</span><span class=
"p">]);
</span>
2154 <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>
2155 <span class=
"nv">$conditions
</span><span class=
"o">-
></span><span class=
"na">add
</span><span class=
"p">(
</span><span class=
"s2">"(bar IN (?) OR baz = ?)
"</span><span class=
"p">,
</span> <span class=
"p">[
</span><span class=
"nv">$valuesForBar
</span><span class=
"p">,
</span> <span class=
"nv">$baz
</span><span class=
"p">]);
</span>
2156 <span class=
"p">}
</span>
2158 <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>
2159 <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>
2160 <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>
2161 <div class=
"highlight"><pre><span></span><code><span class=
"o"><?
</span><span class=
"nx">php
</span>
2162 <span class=
"nv">$data
</span> <span class=
"o">=
</span> <span class=
"p">[
</span><span class=
"s1">'abc
'</span><span class=
"p">,
</span> <span class=
"s1">'def
'</span><span class=
"p">,
</span> <span class=
"s1">'ghi
'</span><span class=
"p">];
</span>
2164 <span class=
"nv">$sql
</span> <span class=
"o">=
</span> <span class=
"s2">"INSERT INTO wcf
"</span><span class=
"o">.
</span><span class=
"nx">WCF_N
</span><span class=
"o">.
</span><span class=
"s2">"_example
</span>
2165 <span class=
"s2"> (bar)
</span>
2166 <span class=
"s2"> VALUES (?)
"</span><span class=
"p">;
</span>
2167 <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">-
></span><span class=
"na">prepareStatement
</span><span class=
"p">(
</span><span class=
"nv">$sql
</span><span class=
"p">);
</span>
2169 <span class=
"nx">\wcf\system\WCF
</span><span class=
"o">::
</span><span class=
"na">getDB
</span><span class=
"p">()
</span><span class=
"o">-
></span><span class=
"na">beginTransaction
</span><span class=
"p">();
</span>
2170 <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>
2171 <span class=
"nv">$statement
</span><span class=
"o">-
></span><span class=
"na">execute
</span><span class=
"p">([
</span><span class=
"nv">$bar
</span><span class=
"p">]);
</span>
2172 <span class=
"p">}
</span>
2173 <span class=
"nx">\wcf\system\WCF
</span><span class=
"o">::
</span><span class=
"na">getDB
</span><span class=
"p">()
</span><span class=
"o">-
></span><span class=
"na">commitTransaction
</span><span class=
"p">();
</span>
2175 <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>
2176 <div class=
"highlight"><pre><span></span><code><span class=
"o"><?
</span><span class=
"nx">php
</span>
2177 <span class=
"nv">$data
</span> <span class=
"o">=
</span> <span class=
"p">[
</span>
2178 <span class=
"mi">1</span> <span class=
"o">=
></span> <span class=
"s1">'abc
'</span><span class=
"p">,
</span>
2179 <span class=
"mi">3</span> <span class=
"o">=
></span> <span class=
"s1">'def
'</span><span class=
"p">,
</span>
2180 <span class=
"mi">4</span> <span class=
"o">=
></span> <span class=
"s1">'ghi
'</span>
2181 <span class=
"p">];
</span>
2183 <span class=
"nv">$sql
</span> <span class=
"o">=
</span> <span class=
"s2">"UPDATE wcf
"</span><span class=
"o">.
</span><span class=
"nx">WCF_N
</span><span class=
"o">.
</span><span class=
"s2">"_example
</span>
2184 <span class=
"s2"> SET bar = ?
</span>
2185 <span class=
"s2"> WHERE exampleID = ?
"</span><span class=
"p">;
</span>
2186 <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">-
></span><span class=
"na">prepareStatement
</span><span class=
"p">(
</span><span class=
"nv">$sql
</span><span class=
"p">);
</span>
2188 <span class=
"nx">\wcf\system\WCF
</span><span class=
"o">::
</span><span class=
"na">getDB
</span><span class=
"p">()
</span><span class=
"o">-
></span><span class=
"na">beginTransaction
</span><span class=
"p">();
</span>
2189 <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">=
></span> <span class=
"nv">$bar
</span><span class=
"p">)
</span> <span class=
"p">{
</span>
2190 <span class=
"nv">$statement
</span><span class=
"o">-
></span><span class=
"na">execute
</span><span class=
"p">([
</span>
2191 <span class=
"nv">$bar
</span><span class=
"p">,
</span>
2192 <span class=
"nv">$exampleID
</span>
2193 <span class=
"p">]);
</span>
2194 <span class=
"p">}
</span>
2195 <span class=
"nx">\wcf\system\WCF
</span><span class=
"o">::
</span><span class=
"na">getDB
</span><span class=
"p">()
</span><span class=
"o">-
></span><span class=
"na">commitTransaction
</span><span class=
"p">();
</span>
2202 <div class=
"md-source-date">
2205 Last update:
2021-
01-
08
2224 <footer class=
"md-footer">
2226 <nav class=
"md-footer__inner md-grid" aria-label=
"Footer">
2228 <a href=
"../database-objects/" class=
"md-footer__link md-footer__link--prev" rel=
"prev">
2229 <div class=
"md-footer__button md-icon">
2230 <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>
2232 <div class=
"md-footer__title">
2233 <div class=
"md-ellipsis">
2234 <span class=
"md-footer__direction">
2243 <a href=
"../exceptions/" class=
"md-footer__link md-footer__link--next" rel=
"next">
2244 <div class=
"md-footer__title">
2245 <div class=
"md-ellipsis">
2246 <span class=
"md-footer__direction">
2252 <div class=
"md-footer__button md-icon">
2253 <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>
2259 <div class=
"md-footer-meta md-typeset">
2260 <div class=
"md-footer-meta__inner md-grid">
2261 <div class=
"md-footer-copyright">
2263 <div class=
"md-footer-copyright__highlight">
2264 Copyright ©
2020 WoltLab GmbH
2268 <a href=
"https://squidfunk.github.io/mkdocs-material/" target=
"_blank" rel=
"noopener">
2273 <div class=
"md-footer-copyright">
2274 <a href=
"https://www.woltlab.com/legal-notice/">Legal Notice
</a>
2275 <a href=
"https://www.woltlab.com/privacy-policy/">Privacy Policy
</a>
2282 <div class=
"md-dialog" data-md-component=
"dialog">
2283 <div class=
"md-dialog__inner md-typeset"></div>
2285 <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>
2288 <script src=
"../../assets/javascripts/bundle.d892486b.min.js"></script>