{"id":1003,"date":"2017-12-16T04:14:58","date_gmt":"2017-12-16T04:14:58","guid":{"rendered":"http:\/\/blog.silverterra.net\/?p=1003"},"modified":"2017-12-16T04:19:15","modified_gmt":"2017-12-16T04:19:15","slug":"pdo-bind-value-list-for-in-statement","status":"publish","type":"post","link":"https:\/\/blog.silverterra.net\/?p=1003","title":{"rendered":"PDO bind value list for IN statement"},"content":{"rendered":"<blockquote><p>Goal: pass value list to IN statement<\/p><\/blockquote>\n<p>The PDO variable binding is easy until you want to use an IN statement like<\/p>\n<pre data-enlighter-language=\"sql\" class=\"EnlighterJSRAW\">\r\n   SELECT *\r\n     FROM table\r\n    WHERE id IN (1,2,3,4)\r\n<\/pre>\n<p>with PDO<\/p>\n<pre data-enlighter-language=\"php\" class=\"EnlighterJSRAW\">\r\n$sql = &lt;&lt;&lt;EOT\r\n  SELECT *\r\n    FROM table\r\n   WHERE id IN (?)\r\nEOT;\r\n<\/pre>\n<p>after the binding this statement is not working because in the background the select statement will be like this:<\/p>\n<pre data-enlighter-language=\"sql\" class=\"EnlighterJSRAW\">\r\n   SELECT *\r\n     FROM table\r\n    WHERE id IN ('1,2,3,4')\r\n<\/pre>\n<p>Luckily the mysql knows the following tricky form<\/p>\n<pre data-enlighter-language=\"sql\" class=\"EnlighterJSRAW\">\r\n   SELECT *\r\n     FROM table\r\n    WHERE find_in_set(cast(ID as char), '1,2,3,4')\r\n<\/pre>\n<p>and the bindable form is the following<\/p>\n<pre data-enlighter-language=\"php\" class=\"EnlighterJSRAW\">\r\n$sql = &lt;&lt;&lt;EOT\r\n  SELECT *\r\n    FROM table\r\n   WHERE find_in_set(cast(ID as char), ? )\r\nEOT;\r\n<\/pre>\n<p>Job done. Enjoy the result&#8230;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Goal: pass value list to IN statement The PDO variable binding is easy until you want to use an IN statement like with PDO after the binding this statement is not working because in the background the select statement will be like this: Luckily the mysql knows the following tricky form and the bindable form [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9,11],"tags":[],"class_list":["post-1003","post","type-post","status-publish","format-standard","hentry","category-mysql","category-tricky"],"_links":{"self":[{"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/posts\/1003","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1003"}],"version-history":[{"count":7,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/posts\/1003\/revisions"}],"predecessor-version":[{"id":1010,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/posts\/1003\/revisions\/1010"}],"wp:attachment":[{"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1003"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1003"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1003"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}