課題研究の教材作成 - Mae向きなブログ

午後からは、現在、生徒と取り組んでいる課題研究用の教材を作成。
http://www.atmarkit.co.jp/flinux/rensai/mysql07/mysql07c.html にRubyとMySQL版があったので、SQLite3用に変更しました。

sample1.rb

#!/usr/bin/ruby -Ke
# -*- coding: euc-jp -*-

require 'rubygems'
require 'sqlite3'

object = SQLite3::Database.open("TEST_RUBY")

object.execute('drop table test_table')
object.execute('create table test_table (name char(32),price int)')
object.execute("insert into test_table values ('りんご',300);")
object.execute("insert into test_table values ('みかん',80);")
object.execute("insert into test_table values ('梨',150);")
object.execute("insert into test_table values ('桃',250);")
object.execute("insert into test_table values ('メロン',3450);")
object.execute("insert into test_table values ('いちご',450);")

object.close

sample.html

<html>
  <head>
    <title>Ruby&SQLite3</title>
    <META HTTP-EQUIV="Content-Type" CONTENT="text/html;CHARSET=x-euc-jp">
  </head>
  <body bgcolor="#CCFFCC">
    <center>
      <form action=search.rb method=post>
        <table cellspacing="0" cellpadding="2" width=280 bgcolor="#CCFFCC" border="0">
          <tr><th bgcolor="#FFCCCC" width=50%>名 前</th>
            <td><input type="text" name="name1" size="15"></td>
          </tr>

          <tr><td><input type="radio" name="and_or1" value="AND" checked="checked">AND</td>
            <td><input type="radio" name="and_or1" value="OR">OR</td>
          </tr>

          <tr><th bgcolor="#99FFCC" width=50%>値 段</th>
            <td><input type="text" name="price1" size="15"></td>
            <td><select name="zyoken1">
                <option value=1>一致 
                <option value=2>以上
                <option value=3>以下
            </select></td>
          </tr> 
          
        </table>
        <br>
        <input type=submit value="　検　索　">
    </center>
  </body>
</html>

search.rb

#!/usr/bin/ruby
# -*- coding: euc-jp -*-

require 'rubygems'
require 'sqlite3'
require 'cgi'

cgi = CGI.new

f_name = CGI.escapeHTML(cgi.params['name1'][0])
f_price = CGI.escapeHTML(cgi.params['price1'][0])
f_and_or = cgi.params['and_or1'][0]
f_zyoken = cgi.params['zyoken1'][0].to_i

case f_zyoken
when 1
  zyoken = "="
when 2
  zyoken = ">="
when 3
  zyoken = "<="
end

# フォームの条件よりSQLを組み立てる
if f_name != "" && f_price != ""
  sql = "select name,price from test_table where name='#{f_name}' #{f_and_or} price #{zyoken} '#{f_price}';"
elsif f_name != ""
  sql = "select name,price from test_table where name='#{f_name}';"
elsif f_price != ""
  sql = "select name,price from test_table where price #{zyoken} '#{f_price}';"
else
  sql = "select name,price from test_table;"
end

db = SQLite3::Database.open("TEST_RUBY")

puts cgi.header("type" => "text/html", "charset" => "euc-jp")
puts '<html><head><title>Ruby&MySQL</title> </head><body bgcolor="#CCFFCC"><center>'
puts '<table cellspacing="0" cellpadding="2" width=280 bgcolor="#CCFFCC" border="1">'
db.execute(sql) do |name, price|
  print "<tr><th bgcolor=\"#FFCCCC\" width=50%>#{name}</td>"
  print "<td bgcolor=\"white\">#{price}円</td></tr>"
end
puts '</table></body></html>'

db.close