[Ruby]MySQLからデータ取得

MySQLからデータ取得

サンプル

#!/usr/bin/ruby-1.9.3
# coding: utf-8
require 'json'
require 'mysql'

puts "Content-type: application/json\n\n"

connection = Mysql.connect('host','user','password','db name')
connection.query("set character set utf8") #文字化けするとき

columns=["ID","post_title","post_date"]
result = connection.query("SELECT "+columns.join(",")+" FROM wp_posts WHERE post_status='publish' ORDER BY ID DESC LIMIT 5")
connection.close

results=Array.new
result.each {|record|
    w = {}
    record.each_with_index{|val,i|
        key = columns[i]
        w[key.force_encoding("utf-8")]=val.force_encoding("utf-8") #文字化けするときforce_encoding
    }
    results.push(w)
}
puts JSON.generate({'time'=>Time.now.strftime("%Y-%m-%d %H:%M:%S"), 'data'=>results})