{"id":1697,"date":"2014-10-30T10:00:11","date_gmt":"2014-10-30T01:00:11","guid":{"rendered":"http:\/\/www.skyarch.net\/blog\/?p=1697"},"modified":"2014-10-30T12:11:23","modified_gmt":"2014-10-30T03:11:23","slug":"%e6%ad%a3%e8%a6%8f%e5%8c%96%e7%b8%a6%e6%8c%81%e3%81%a1%e3%80%81%e8%a1%8c%e6%8c%81%e3%81%a1%e3%83%87%e3%83%bc%e3%82%bf%e3%81%ab%e5%af%be%e3%81%99%e3%82%8b%e6%a4%9c%e7%b4%a2%e3%81%ae%e5%ae%9f%e8%a3%85-2","status":"publish","type":"post","link":"https:\/\/www.skyarch.net\/blog\/%e6%ad%a3%e8%a6%8f%e5%8c%96%e7%b8%a6%e6%8c%81%e3%81%a1%e3%80%81%e8%a1%8c%e6%8c%81%e3%81%a1%e3%83%87%e3%83%bc%e3%82%bf%e3%81%ab%e5%af%be%e3%81%99%e3%82%8b%e6%a4%9c%e7%b4%a2%e3%81%ae%e5%ae%9f%e8%a3%85-2\/","title":{"rendered":"\u6b63\u898f\u5316(\u7e26\u6301\u3061\u3001\u884c\u6301\u3061)\u30c7\u30fc\u30bf\u306b\u5bfe\u3059\u308b\u691c\u7d22\u306e\u5b9f\u88c5(PHP\u7de8)"},"content":{"rendered":"

\u524d\u56de\u306b\u5f15\u304d\u7d9a\u304d\u3001\u6b63\u898f\u5316(\u7e26\u6301\u3061\u3001\u884c\u6301\u3061)\u30c7\u30fc\u30bf\u306b\u5bfe\u3059\u308b\u691c\u7d22\u306e\u5b9f\u88c5\u3092\u304a\u3053\u306a\u3044\u307e\u3059\u3002<\/p>\n

\u4eca\u56de\u306fPHP\u3067\u3001\u5b9f\u88c5\u3092\u304a\u3053\u306a\u3044\u307e\u3059\u3002<\/p>\n

\u524d\u56de\u306e\u7d50\u679c\u3068\u3057\u3066\u4ee5\u4e0b\u306e\u3088\u3046\u306aSQL\u6587\u3092\u767a\u884c\u3059\u308c\u3070\u3088\u3044\u3053\u3068\u304c\u308f\u304b\u308a\u307e\u3057\u305f\u3002<\/p>\n

\r\nSELECT opt_1.* FROM test_option_2 opt_1\r\nLEFT JOIN test_option_2 opt_2\r\nON opt_1.name_id = opt_2.name_id\r\nWHERE \r\n(\r\n    opt_1.option_id = 1\r\n    AND \r\n    opt_1.value = "good"\r\n)\r\nAND\r\n(\r\n    opt_2.option_id = 2\r\n    AND \r\n    opt_2.value = "bad"\r\n)\r\n<\/pre>\n
\u3053\u3053\u3067\u3001\u672c\u6a5f\u80fd\u3092PHP\u304a\u3088\u3073PDO\u3092\u7528\u3044\u3066\u5b9f\u88c5\u3057\u307e\u3057\u3087\u3046\u3002PDO\u3092\u306e\u4f7f\u3044\u65b9\u306f\u3001\u5404\u3005\u8abf\u3079\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n
\r\n<?php\r\n\r\nclass SearchSample{\r\n\t\r\n\tprivate $dbname     = "dbname";\r\n\tprivate $user       = "user";\r\n\tprivate $pass       = "pass";\r\n\t\r\n\tprivate $pdo        = null;\r\n\r\n\tpublic function __construct()\r\n    {\r\n        try {\r\n                $this->pdo = new PDO("mysql:dbname={$this->dbname};host=172.16.21.223", $this->user, $this->pass);\r\n        } catch (PDOException $e) {\r\n                exit('\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306b\u63a5\u7d9a\u3067\u304d\u307e\u305b\u3093\u3067\u3057\u305f\u3002' . $e->getMessage());\r\n        }\r\n    }\r\n\r\n    \/\/JOIN\u306e\u30af\u30a8\u30ea\u8ffd\u52a0\u3059\u308b\r\n    public function addJoin(&$join_query , $num){\r\n        $join_query .= " LEFT JOIN test_option_2 opt_${num} ON name.id = opt_${num}.name_id ";\r\n    }\r\n    \/\/WHERE\u30af\u30a8\u30ea\u3092\u8ffd\u52a0\u3059\u308b\r\n    public function addWhere(&$where_query , $option_id , $option_value , $num){\r\n        $where_query .= " (opt_${num}.option_id = ${option_id} AND opt_${num}.value = \\"${option_value}\\") ";\r\n    }\r\n\r\n        \/\/\u691c\u7d22\u30af\u30a8\u30ea\u3092\u767a\u884c\u3059\u308b\r\n    public function search($option_id_arr , $option_value_arr){\r\n        $pdo    = $this->pdo;\r\n        $select = "SELECT name.name FROM test_name name ";\r\n        $join   = "";\r\n        $where  = "";\r\n        $query  = "";\r\n        if(count($option_id_arr) == count($option_value_arr)){\r\n            foreach($option_id_arr as $key => $option_id){\r\n                $option_value = $option_value_arr[$key];\r\n                $this->addJOIN($join , $key);\r\n                if($key == 0){\r\n                    $where .= " WHERE ";\r\n                }\r\n                $this->addWhere($where , $option_id , $option_value , $key);\r\n                if($key != count($option_value_arr) - 1){\r\n                    $where .= " AND ";\r\n                }\r\n            }\r\n        }\r\n        $query = $select . $join . $where;\r\n        $stmt  = $pdo->query($query);\r\n        if (!$stmt) {\r\n                $info = $pdo->errorInfo();\r\n        }\r\n        print($query);\r\n        return $stmt->fetchAll(PDO::FETCH_ASSOC);\r\n    }\r\n}\r\n$Search_obj = new SearchSample();\r\n\r\nprint_r($Search_obj->search(Array(1 , 2) , Array("good" , "bad")));\r\n\r\n?>\r\n<\/pre>\n
\u4ee5\u4e0a\u306e\u3088\u3046\u306aPHP\u3092\u5b9f\u884c\u3059\u308b\u3068<\/p>\n

\n(
\n    [0] => Array
\n        (
\n            [name] => taro
\n        )<\/p>\n
)
\n<\/code>
\n\u306e\u3088\u3046\u306a\u7d50\u679c\u304c\u8fd4\u3063\u3066\u304d\u307e\u3059\u3002\u3053\u308c\u306a\u3089\u3070\u3001\u3044\u304f\u3064\u691c\u7d22\u6761\u4ef6\u304c\u3042\u3063\u3066\u3082\u3001\u554f\u984c\u3042\u308a\u307e\u305b\u3093\u3057\u3001JOIN\u306e\u6570\u3092\u691c\u7d22\u6761\u4ef6\u6570\u306b\u9650\u5b9a\u3057\u3066\u3044\u308b\u305f\u3081\u3001\u4e00\u65e6\u6a2a\u6301\u3061\u30c6\u30fc\u30d6\u30eb\u306b\u5909\u63db\u3059\u308b\u51e6\u7406\u306b\u6bd4\u3079\u308b\u3068\u683c\u6bb5\u306b\u306f\u3084\u304f\u306a\u3063\u3066\u3044\u308b\u306f\u305a\u3067\u3059\u3002<\/p>\n
\u3053\u308c\u306b\u3001And\u691c\u7d22\u3001OR\u691c\u7d22\u3001LIKE\u691c\u7d22\u306a\u3069\u306e\u6a5f\u80fd\u3092\u8ffd\u52a0\u3059\u308c\u3070\u5341\u5206\u5b9f\u7528\u306b\u8010\u3048\u308b\u3082\u306e\u304c\u3067\u304d\u308b\u3068\u601d\u3044\u307e\u3059\u3002\u672c\u5f53\u306f\u3001\u3055\u3089\u306b\u62bd\u8c61\u5316\u3059\u3079\u304d\u3067\u3059\u304c<\/p>\n
\u5f53\u793e\u3067\u306f\u3001\u793e\u5185\u30b7\u30b9\u30c6\u30e0\u306b\u304a\u3044\u3066\u3001\u691c\u7d22\u6a5f\u80fd\u3092Symfony2\u306eDBAL\u30d9\u30fc\u30b9\u3067\u5b9f\u88c5\u3057\u3066\u3044\u307e\u3059\u304c\u3001\u4eca\u56de\u306f\u6c4e\u7528\u6027\u3092\u9451\u307f\u3066PDO\u30d9\u30fc\u30b9\u3067\u5b9f\u88c5\u3057\u3066\u307f\u307e\u3057\u305f\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"
\u524d\u56de\u306b\u5f15\u304d\u7d9a\u304d\u3001\u6b63\u898f\u5316(\u7e26\u6301\u3061\u3001\u884c\u6301\u3061)\u30c7\u30fc\u30bf\u306b\u5bfe\u3059\u308b\u691c\u7d22\u306e\u5b9f\u88c5\u3092\u304a\u3053\u306a\u3044\u307e\u3059\u3002 \u4eca\u56de\u306fPHP\u3067\u3001\u5b9f\u88c5\u3092\u304a\u3053\u306a\u3044\u307e\u3059\u3002 \u524d\u56de\u306e\u7d50\u679c\u3068\u3057\u3066\u4ee5\u4e0b\u306e\u3088\u3046\u306aSQL\u6587\u3092\u767a\u884c\u3059\u308c\u3070\u3088\u3044\u3053\u3068\u304c\u308f\u304b\u308a\u307e\u3057\u305f\u3002 SELECT opt_1….<\/p>\n","protected":false},"author":34,"featured_media":1751,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_locale":"","_original_post":"","footnotes":""},"categories":[24,23],"tags":[],"class_list":{"0":"post-1697","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-mysql","8":"category-23","9":"ja"},"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/posts\/1697","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/users\/34"}],"replies":[{"embeddable":true,"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/comments?post=1697"}],"version-history":[{"count":3,"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/posts\/1697\/revisions"}],"predecessor-version":[{"id":1716,"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/posts\/1697\/revisions\/1716"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/media\/1751"}],"wp:attachment":[{"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/media?parent=1697"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/categories?post=1697"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/tags?post=1697"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}