I worked with PHP and MySQLi for the last few days and still don’t like it that much but got used to it.<\/p>\n
Something that was annoying me pretty much was the Binding of Parameters in an SQL-Statement.
\nSo I decided to write a little Helper Function for this. <\/p>\n
A normal Binding looks like this:<\/p>\n
\r\n$stmt = $db->prepare('UPDATE SET Name=?, SomeInt=? FROM SomeTable WHERE ID = ?');\r\n$stmt->bind_param('sii', $newName, $newInt, $id);\r\n<\/pre>\nIt’s a shame that it’s not possible to pass an Array of Objects to the bind_param<\/code><\/p>\nAfter some search I found a Function for that problem:<\/p>\n
\r\n\/\/ $stmt = The SQL Statement Object\r\n\/\/ $param = Array of the Parameters\r\nfunction DynamicBindVariables($stmt, $params)\r\n{\r\n\tif ($params != null)\r\n\t{\r\n\t\t\/\/ Generate the Type String (eg: 'issisd')\r\n\t\t$types = '';\r\n\t\tforeach($params as $param)\r\n\t\t{\r\n\t\t\tif(is_int($param)) {\r\n\t\t\t\t\/\/ Integer\r\n\t\t\t\t$types .= 'i';\r\n\t\t\t} elseif (is_float($param)) {\r\n\t\t\t\t\/\/ Double\r\n\t\t\t\t$types .= 'd';\r\n\t\t\t} elseif (is_string($param)) {\r\n\t\t\t\t\/\/ String\r\n\t\t\t\t$types .= 's';\r\n\t\t\t} else {\r\n\t\t\t\t\/\/ Blob and Unknown\r\n\t\t\t\t$types .= 'b';\r\n\t\t\t}\r\n\t\t}\r\n \r\n\t\t\/\/ Add the Type String as the first Parameter\r\n\t\t$bind_names[] = $types;\r\n \r\n\t\t\/\/ Loop thru the given Parameters\r\n\t\tfor ($i=0; $i<count($params);$i++)\r\n\t\t{\r\n\t\t\t\/\/ Create a variable Name\r\n\t\t\t$bind_name = 'bind' . $i;\r\n\t\t\t\/\/ Add the Parameter to the variable Variable\r\n\t\t\t$$bind_name = $params[$i];\r\n\t\t\t\/\/ Associate the Variable as an Element in the Array\r\n\t\t\t$bind_names[] = &$$bind_name;\r\n\t\t}\r\n\t\t\r\n\t\t\/\/ Call the Function bind_param with dynamic Parameters\r\n\t\tcall_user_func_array(array($stmt,'bind_param'), $bind_names);\r\n\t}\r\n\treturn $stmt;\r\n}\r\n<\/pre>\nNow you can Bind the Parameters like this:<\/p>\n
\r\n$paramList = array();\r\n$paramList[] = $newName;\r\n$paramList[] = $newInt;\r\n$paramList[] = $id;\r\n$stmt = $db->prepare('UPDATE SET Name=?, SomeInt=? FROM SomeTable WHERE ID = ?');\r\nDynamicBindVariables($stmt, $paramList);\r\n<\/pre>\nThe Code is a bit longer but we’re now able to easily generate Statements with a dynamic amount of Parameters and bind them correctly.<\/p>\n","protected":false},"excerpt":{"rendered":"
I worked with PHP and MySQLi for the last few days and still don’t like it that much but got used to it. Something that was annoying me pretty much was the Binding of Parameters in an SQL-Statement. So I decided to write a little Helper Function for this.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[6,3],"tags":[91,92,89,90],"class_list":{"0":"entry","1":"post","2":"publish","3":"author-roemer","4":"has-more-link","5":"post-213","7":"format-standard","8":"category-php","9":"category-programming","10":"post_tag-binding","11":"post_tag-dynamic","12":"post_tag-mysqli","13":"post_tag-parameter"},"acf":[],"views":296000,"_links":{"self":[{"href":"http:\/\/dev.flauschig.ch\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/213"}],"collection":[{"href":"http:\/\/dev.flauschig.ch\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dev.flauschig.ch\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dev.flauschig.ch\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/dev.flauschig.ch\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=213"}],"version-history":[{"count":0,"href":"http:\/\/dev.flauschig.ch\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/213\/revisions"}],"wp:attachment":[{"href":"http:\/\/dev.flauschig.ch\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=213"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dev.flauschig.ch\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=213"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dev.flauschig.ch\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=213"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}