I often don’t want to instantiate a whole dbi stack just for some quick work. \u00c2\u00a0So in the include module I tend to haul around, there’s a “return_sql” function. \u00c2\u00a0It takes 5 or 6 arguments (the last being an optional debug): the sql statement itself, db,host,user,pass in that order. \u00c2\u00a0After that, it will execute the sql. \u00c2\u00a0Fun use: \u00c2\u00a0if there’s only one row returned, it just returns that one as a scalar – otherwise it returns an array of comma-seperated values (like I said, this is for small quick stuff).<\/p>\n
sub return_sql()\r\n{\r\n my $sql=shift || return; \u00c2\u00a0#the or is super useful for this!\r\n my $db=shift || return;\r\n my $host=shift || '';\r\n my $user=shift || '';\r\n my $pass=shift || '';\r\n my $debug=shift || 0; #for optional debugging, default is 0\r\n print \"SQL=$sql\\n\" if $debug;\r\n #from here I (depending on the host) either use the dbi, or simply echo to mysql's path and get the result\r\n my $dbh=DBI->connect(\"DBI:mysql:$db:$host\",$user,$pass)\r\n or die \"Couldn't connect to $db:$host,$user: $!\\n\";\r\n my $sth=$dbh->prepare($sql)\r\n or warn \"Couldn't prepare sql: $!\\n\";\r\n $sth->execute()\r\n or warn \"Couldn't execute sql\\n$sql\\nReason: $!\\n\";\r\n my @ret_ary=();\r\n #could do this a bunch of other ways, but this is really really easy to explain. :)\r\n #while you're fetching a row (i.e. for each row you get) that's put into @val\r\n #join it into a temp line seperated by commas, and push that onto an array\r\n while( my @val=$sth->fetchrow_array()){push(@ret_ary,join(\",\",@val));}\r\n #then, if there's only one row returned (if the length of the new array is 1), return only it\r\n return $ret_ary[0] if scalar(@ret_ary)==1;\r\n #otherwise, return the whole array and use away!\r\n return @ret_ary;\r\n}<\/pre>\nUse in practice:<\/p>\n
use include;\r\nmy @db=('db','host','user','pass',$debug); #debug being 0 or non-0 :)\r\n#first, let's try just one row\r\nmy $name=&include::return_sql(\"select name from mytable limit 1\",@db);\r\n#then, let's get a list\r\nmy @names=&include::return_sql(\"select name from mytable where 1\",@db);\r\n#a little more complicated\r\nforeach my $row(&include::return_sql(\"select name,id from mytable where 1\",@db))\r\n{\r\n my ($name,$id)=split(\",\",$row);\r\n print \"name=$name,id=$id\\n\";\r\n}<\/pre>\nAnd that’s my useful snippet for the day!<\/p>\n","protected":false},"excerpt":{"rendered":"
I often don’t want to instantiate a whole dbi stack just for some quick work. \u00c2\u00a0So in the include module I tend to haul around, there’s a “return_sql” function. \u00c2\u00a0It takes 5 or 6 arguments (the last being an optional debug): the sql statement itself, db,host,user,pass in that order. \u00c2\u00a0After that, it will execute the […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[15],"tags":[10],"_links":{"self":[{"href":"http:\/\/www.imaginarybillboards.com\/index.php?rest_route=\/wp\/v2\/posts\/26"}],"collection":[{"href":"http:\/\/www.imaginarybillboards.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.imaginarybillboards.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.imaginarybillboards.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.imaginarybillboards.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=26"}],"version-history":[{"count":4,"href":"http:\/\/www.imaginarybillboards.com\/index.php?rest_route=\/wp\/v2\/posts\/26\/revisions"}],"predecessor-version":[{"id":89,"href":"http:\/\/www.imaginarybillboards.com\/index.php?rest_route=\/wp\/v2\/posts\/26\/revisions\/89"}],"wp:attachment":[{"href":"http:\/\/www.imaginarybillboards.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=26"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.imaginarybillboards.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=26"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.imaginarybillboards.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=26"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}