{"id":230324,"date":"2022-12-08T13:29:00","date_gmt":"2022-12-08T10:29:00","guid":{"rendered":"https:\/\/wordpress.mediadoma.com\/?p=230324"},"modified":"2022-12-07T11:53:22","modified_gmt":"2022-12-07T08:53:22","slug":"tuekk-wordpressis-andmebaasipaeringutega-toeoetamiseks","status":"publish","type":"post","link":"https:\/\/wordpress.mediadoma.com\/et\/tuekk-wordpressis-andmebaasipaeringutega-toeoetamiseks\/","title":{"rendered":"T\u00fckk WordPressis andmebaasip\u00e4ringutega t\u00f6\u00f6tamiseks"},"content":{"rendered":"\n<p>Nii palju kui ma olen teatud WordPressi API-de f\u00e4nn andmebaasi p\u00e4ringute tegemiseks, n\u00e4iteks:<\/p>\n<ul>\n<li><strong><a href=\"https:\/\/codex.wordpress.org\/Class_Reference\/WP_Query\" target=\"_blank\" rel=\"noopener nofollow\" class=\"external external_icon\">WP_Query<\/a><\/strong> ,<\/li>\n<li><strong><a href=\"https:\/\/codex.wordpress.org\/Class_Reference\/WP_User_Query\" target=\"_blank\" rel=\"noopener nofollow\" class=\"external external_icon\">WP_User_Query<\/a><\/strong> ,<\/li>\n<li><strong><a href=\"https:\/\/codex.wordpress.org\/Class_Reference\/WP_Meta_Query#Related\" target=\"_blank\" rel=\"noopener nofollow\" class=\"external external_icon\">WP_Meta_Query<\/a><\/strong> ,<\/li>\n<li>ja nii edasi<\/li>\n<\/ul>\n<p>Samuti armastan ma vajaduse korral otse andmebaasi p\u00e4ringuid teha.<\/p>\n<p>Nagu m\u00e4rkate, on mul seda j\u00e4rjepidevalt tehtud ja kuna on aegu, kus olen seda sagedamini teinud, on siin WordPressi andmebaasip\u00e4ringutega t\u00f6\u00f6tamise p\u00f5hivorming.<\/p>\n<p>Kui ma aga seda teen, pean \u00fcldiselt silmas j\u00e4rgmisi tegureid:<\/p>\n<ol>\n<li>alati veenduge, et p\u00e4ring on parameetritega m\u00e4\u00e4ratud (eriti kasutaja sisendi puhul),<\/li>\n<li>tagastab teabe assotsiatiivse massiivi kujul.<\/li>\n<\/ol>\n<p>Esimene juhtum on turvalisuse huvides; teine \u200b\u200bon enam-v\u00e4hem mugavuse huvides, et muuta andmete itereerimine PHP massiivi funktsioonide kaudu lihtsamaks.<\/p>\n<h2>Andmebaasip\u00e4ringud WordPressis<\/h2>\n<p>Esiteks on oluline m\u00f5ista kahte m\u00f5istet:<\/p>\n<ol>\n<li>parameetrite m\u00e4\u00e4ramine,<\/li>\n<li>massiivi_veerg<\/li>\n<\/ol>\n<p>Need on minu p\u00e4ringute kirjutamise ja nende tulemustega t\u00f6\u00f6tamise p\u00f5hielemendid.<\/p>\n<h3>1 Parameetristamine<\/h3>\n<p>Selle kohta saate rohkem lugeda Codexist, kuid parameetrite m\u00e4\u00e4ramine on viis, kuidas saame kirjutada p\u00e4ringuid, mis takistavad SQL-i s\u00fcstimist.<\/p>\n<p><strong><a href=\"https:\/\/codex.wordpress.org\/Class_Reference\/wpdb#Protect_Queries_Against_SQL_Injection_Attacks\" target=\"_blank\" rel=\"noopener nofollow\" class=\"external external_icon\">Codexi lehelt<\/a><\/strong> :<\/p>\n<blockquote>\n<p>K\u00f5ik SQL-p\u00e4ringute andmed peavad enne SQL-p\u00e4ringu t\u00e4itmist olema SQL-i v\u00e4ltimisega, et v\u00e4ltida SQL-i s\u00fcstimise r\u00fcnnakuid. Meetod <code>prepare<\/code>t\u00e4idab seda funktsiooni WordPressi jaoks, mis toetab nii <a href=\"https:\/\/php.net\/sprintf\" target=\"_blank\" rel=\"noopener nofollow\" class=\"external external_icon\">sprintf()<\/a> &#8211; sarnast kui ka <a href=\"https:\/\/php.net\/vsprintf\" target=\"_blank\" rel=\"noopener nofollow\" class=\"external external_icon\">vsprintf()-<\/a> sarnast s\u00fcntaksit.<\/p>\n<\/blockquote>\n<p>See t\u00e4hendab, et meetod toetab p\u00e4ringusse teabe edastamisel selliseid m\u00e4rke nagu <strong>%s<\/strong>, <strong>%d<\/strong> ja <strong>%f .<\/strong> N\u00e4itan selle kohta hiljem postituses.<\/p>\n<h3>2 T\u00f6\u00f6tamine massiivi veergudega<\/h3>\n<p>Selliste p\u00e4ringute tulemusi saab tagastada mitmel erineval viisil. Nimelt objektina, numbriliselt indekseeritud massiivina v\u00f5i assotsiatiivse massiivina. Enamasti meeldib mulle kasutada massiive (ja ma n\u00e4itan, kuidas seda WordPressis teha hiljem selles postituses).<\/p>\n<p><a href=\"https:\/\/wordpress.mediadoma.com\/wp-content\/uploads\/2022\/01\/post-162387-61e73d7998688.png\" data-rel=\"lightbox\"><img decoding=\"async\" class=\"SDStudio-light-box-enable SDStudio-editor-tools-md-imp\" src=\"https:\/\/wordpress.mediadoma.com\/wp-content\/uploads\/2022\/01\/post-162387-61e73d7998688.png\" alt=\"T\u00fckk WordPressis andmebaasip\u00e4ringutega t\u00f6\u00f6tamiseks\" ><\/a><\/p>\n<p>P\u00f5hjus on aga selles, et saaksin \u00e4ra kasutada funktsiooni <a href=\"https:\/\/php.net\/manual\/en\/function.array-column.php\" target=\"_blank\" rel=\"noopener nofollow\" class=\"external external_icon\">array_column<\/a>. PHP juhendist:<\/p>\n<blockquote>\n<p>array_column() tagastab v\u00e4\u00e4rtused sisendi \u00fchest veerust, mida t\u00e4histab veeru_v\u00f5ti. Valikuliselt v\u00f5idakse ette n\u00e4ha indeksi_v\u00f5ti, et indekseerida tagastatud massiivi v\u00e4\u00e4rtusi sisendmassiivi veerus index_key olevate v\u00e4\u00e4rtustega.<\/p>\n<\/blockquote>\n<p>See t\u00e4hendab, et kui teil on massiiv ja sellel on hunnik tulemusi, millest iga\u00fchel on sama veerg, saate teabe lihtsalt lihtsalt edastada, edastades massiivi meetodile ja seej\u00e4rel veeru nime meetodile.<\/p>\n<p>Tulemus? Numbriliselt indekseeritud v\u00e4\u00e4rtuste massiiv. See muudab andmete itereerimise palju lihtsamaks l\u00e4bi <a href=\"https:\/\/php.net\/manual\/en\/function.array-map.php\" target=\"_blank\" rel=\"noopener nofollow\" class=\"external external_icon\">array_map<\/a>, <strong><a href=\"https:\/\/php.net\/manual\/en\/control-structures.for.php\" target=\"_blank\" rel=\"noopener nofollow\" class=\"external external_icon\">kasutades<\/a> foreach v\u00f5i<\/strong> <strong><a href=\"https:\/\/php.net\/manual\/en\/control-structures.foreach.php\" target=\"_blank\" rel=\"noopener nofollow\" class=\"external external_icon\">foreach<\/a><\/strong>.<\/p>\n<h2>T\u00fcnn andmebaasi p\u00e4ringute tegemiseks<\/h2>\n<p>Kui see p\u00f5hjendus on paigas, <strong><a href=\"https:\/\/gist.github.com\/tommcfarlin\/83522e41b58ba2ee8633282cb03e6a60#file-00-query-stub-php\" target=\"_blank\" rel=\"noopener nofollow\" class=\"external external_icon\">siis siin on t\u00fcnn<\/a><\/strong>, mida ma tavaliselt kasutan alati, kui t\u00f6\u00f6tan otse andmebaasip\u00e4ringutega:<\/p>\n<pre><code>&lt;?php\nglobal $wpdb;\n$results = $wpdb-&gt;get_results(\n    $wpdb-&gt;prepare(\n        \"\n        \",\n        $userId\n    ),\n    ARRAY_A\n);\n\nif (empty($results)) {\n  return;\n}\n$results = array_map($results, 'column_name');<\/code><\/pre>\n<p>Ja siin on n\u00e4ide paari erineva teabega, et saaksite n\u00e4ha, <strong><a href=\"https:\/\/gist.github.com\/tommcfarlin\/83522e41b58ba2ee8633282cb03e6a60#file-01-example-query-php\" target=\"_blank\" rel=\"noopener nofollow\" class=\"external external_icon\">kuidas see t\u00f6\u00f6tab<\/a><\/strong> :<\/p>\n<pre><code>&lt;?php\nglobal $wpdb;\n$results = $wpdb-&gt;get_results(\n    $wpdb-&gt;prepare(\n        \"\n        SELECT\n            user_id,\n            meta_value\n        FROM $wpdb-&gt;usermeta\n        WHERE meta_key LIKE %s\n        AND user_id = %d\n        \",\n        '%wc_authorize_net_cim_customer_profile_id%',\n        wp_get_current_user()-&gt;ID;\n    ),\n    ARRAY_A\n);\n\nif (empty($results)) {\n    return [];\n}\n$results = array_map($results, 'meta_value');<\/code><\/pre>\n<p><strong>P\u00e4ringu esitamine tabeli wp_user_meta<\/strong> vastu v\u00f5ib tunduda rumal, kuna get_user_meta on kehtiv API funktsioon, kuid ma arvasin, et seda on lihtne m\u00f5ista, arvestades, mida ma praegu \u00fcritan selgitada.<\/p>\n<p>Igatahes ei saa ma p\u00e4ringut esitada ega ka juhul, kui tulemused v\u00f5ivad tulla tagasi mitme veeruga (sel juhul peate t\u00f6\u00f6tama millegi muuga kui <strong>array_map<\/strong>, kuid see peaks olema piisavalt lihtne, eks? )<\/p>\n<p><div id=\"PostUnique_PostSource\" style=\"padding-top: 50px\">:  <a target=\"_blank\" rel=\"noopener nofollow\" href=\"\/\/tommcfarlin.com\" class=\"external external_icon\">tommcfarlin.com<\/a><\/div><\/p>\n","protected":false},"excerpt":{"rendered":"<p>WordPressi andmebaasi p\u00e4ringute tegemiseks on j\u00e4rjepidev viis. Kuna on aegu, mil olen seda sagedamini teinud, on siin WordPressis andmebaasip\u00e4ringutega t\u00f6\u00f6tamise p\u00f5hivorming.<\/p>\n","protected":false},"author":1,"featured_media":235713,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_wp_rev_ctl_limit":""},"categories":[718,894,842,863],"tags":[1165],"class_list":["post-230324","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-arendaja","category-kood","category-opetused","category-wordpress-4","tag-affiai-et"],"_links":{"self":[{"href":"https:\/\/wordpress.mediadoma.com\/et\/wp-json\/wp\/v2\/posts\/230324","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wordpress.mediadoma.com\/et\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wordpress.mediadoma.com\/et\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/et\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/et\/wp-json\/wp\/v2\/comments?post=230324"}],"version-history":[{"count":0,"href":"https:\/\/wordpress.mediadoma.com\/et\/wp-json\/wp\/v2\/posts\/230324\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/et\/wp-json\/wp\/v2\/media\/235713"}],"wp:attachment":[{"href":"https:\/\/wordpress.mediadoma.com\/et\/wp-json\/wp\/v2\/media?parent=230324"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/et\/wp-json\/wp\/v2\/categories?post=230324"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/et\/wp-json\/wp\/v2\/tags?post=230324"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}