基本設計

  1. デフォルトでは現在の情報をGETし表示
  2. 更新はform elementにデータを入力
  3. 入力されたデータを元にしてXMLHttpRequestでPHPにPOSTする
  4. PHPがパラメータからSQLを作成して送信
  5. クエリが通ったら、更新されたデータベースの情報を取得
  6. 更新されたデータベースの情報をXML形式でJavaScriptへ渡す
  7. JavaScriptは受け取ったデータをHTMLで表示

SQLスキーマの設計

CREATE TABLE rex( rex_id SMALLINT UNSIGNED AUTO_INCREMENT, rex_year YEAR, rex_month VARCHAR(2), rex_day VARCHAR(2), rex_label TINYTEXT, rex_price VARCHAR(10), CONSTRAINT pk_rex PRIMARY KEY (rex_id) );

このページの上部へ

XMLの設計

<!ELEMENT rex ( rex_item )> <!ELEMENT rex_item ( year, month, day, label, price )> <!ELEMENT year (#PCDATA)> <!ELEMENT month (#PCDATA)> <!ELEMENT day (#PCDATA)> <!ELEMENT label (#PCDATA)> <!ELEMENT price (#PCDATA)>

このページの上部へ

HTML

<input type="text" name="year" id="year" size="4" /><label for="year">年</label> <input type="text" name="month" id="month" size="3" /><label for="month">月</label> <input type="text" name="day" id="day" size="6" /><label for="day">日</label> <label for="label">製品名</label><input type="text" name="label" id="label" /> <label for="price">価格</label><input type="text" name="price" id="price" />円 <input type="button" value="送信" id="q-form" /> <div id="put"></div>

このページの上部へ

JavaScript

dom.event.addEventListener(window,"load",function(){ getQuery(); var qForm = document.getElementById("q-form"); dom.event.addEventListener(qForm,"click",postQuery); }); var getQuery = function (){ sendRequest("res.php",dbDisp,"GET",""); } var postQuery = function (){ var year = document.getElementById("year").value; var month = document.getElementById("month").value; var day = document.getElementById("day").value; var label = document.getElementById("label").value; var price = document.getElementById("price").value; var put = document.getElementById("put"); put.removeChild(put.firstChild); sendRequest("res.php",dbDisp,"POST","&year="+year+"&price="+price); }month="+month+"&day="+day+"&label="+label+"&price="+price); } var dbDisp = function (req){ var put = document.getElementById("put"); var root = req.responseXML.getElementsByTagName("rex")[0]; var items = root.getElementsByTagName("rex_item"); var tableNode = document.createElement("table"); var tbodyNode = document.createElement("tbody"); for(var i=0; i<items.length;i+=1){ var trNode = document.createElement("tr"); //Get node values var nodeYear = items[i].getElementsByTagName("year")[0].firstChild.nodeValue; var nodeMonth = items[i].getElementsByTagName("month")[0].firstChild.nodeValue; var nodeDay = items[i].getElementsByTagName("day")[0].firstChild.nodeValue; var nodeLabel = items[i].getElementsByTagName("label")[0].firstChild.nodeValue; var nodePrice = items[i].getElementsByTagName("price")[0].firstChild.nodeValue; //Create text var text0 = document.createTextNode(nodeYear); var text1 = document.createTextNode(nodeMonth); var text2 = document.createTextNode(nodeDay); var text3 = document.createTextNode(nodeLabel); var text4 = document.createTextNode(nodePrice); var text5 = document.createTextNode("年"); var text6 = document.createTextNode("月"); var text7 = document.createTextNode("日"); //Create HTML node var thNode = document.createElement("th"); thNode.appendChild(text0); thNode.appendChild(text5); thNode.appendChild(text1); thNode.appendChild(text6); thNode.appendChild(text2); thNode.appendChild(text7); trNode.appendChild(thNode); var tdNode0 = document.createElement("td"); tdNode0.appendChild(text3); trNode.appendChild(tdNode0); var tdNode1 = document.createElement("td"); tdNode1.appendChild(text4); trNode.appendChild(tdNode1); tbodyNode.appendChild(trNode); } tableNode.appendChild(tbodyNode); put.appendChild(tableNode); }

このページの上部へ

PHP

header("Content-Type: application/xml"); //********************** //データベースに接続 //********************** $connect = mysql_connect("localhost","id","pass"); if(!$connect){ die('Could not connect'); } $db = mysql_select_db("DBname", $connect); if(!$db){ die('Could not select database'); } //POSTされた場合の処理 if(!is_null($_POST['year'])){ $year = $_POST['year']; $month = $_POST['month']; $day = $_POST['day']; $label = $_POST['label']; $price = $_POST['price']; //SQL作成 $q_insert = "INSERT INTO rex(rex_year,rex_month,rex_day,rex_label,rex_price) VALUES ($year,$month,$day,'$label',$price);"; $insert_query = mysql_query($q_insert); if(!$insert_query){ die('Could not mysql_query'); }else{ //SQL作成 $q_select = "SELECT * FROM rex;"; //送信 $select_arrow = mysql_query($q_select); if(!$select_arrow){ die('Could not mysql_query'); } //受け取ったデータを配列に代入 $wnum = 0; while( $result_row = mysql_fetch_array($select_arrow) ){ $rex_id[$wnum] = $result_row[0]; $rex_year[$wnum] = $result_row[1]; $rex_month[$wnum] = $result_row[2]; $rex_day[$wnum] = $result_row[3]; $rex_label[$wnum] = $result_row[4]; $rex_price[$wnum] = $result_row[5]; $wnum += 1; } //XML生成 echo '<?xml version="1.0" encoding="UTF-8"?>'; echo '<rex>'; for($i=0;$i<count($rex_id);$i+=1){ echo "<rex_item> <year>$rex_year[$i]</year> <month>$rex_month[$i]</month> <day>$rex_day[$i]</day> <label>$rex_label[$i]</label> <price>$rex_price[$i]</price> </rex_item>"; } echo '</rex>'; } }else{//通常の処理 //SQL作成 $q_select = "SELECT * FROM rex;"; //送信 $select_arrow = mysql_query($q_select); if(!$select_arrow){ die('Could not mysql_query'); } //受け取ったデータを配列に代入 $wnum = 0; while( $result_row = mysql_fetch_array($select_arrow) ){ $rex_id[$wnum] = $result_row[0]; $rex_year[$wnum] = $result_row[1]; $rex_month[$wnum] = $result_row[2]; $rex_day[$wnum] = $result_row[3]; $rex_label[$wnum] = $result_row[4]; $rex_price[$wnum] = $result_row[5]; $wnum += 1; } //XML生成 echo '<?xml version="1.0" encoding="UTF-8"?>'; echo '<rex>'; for($i=0;$i<count($rex_id);$i+=1){ echo "<rex_item> <year>$rex_year[$i]</year> <month>$rex_month[$i]</month> <day>$rex_day[$i]</day> <label>$rex_label[$i]</label> <price>$rex_price[$i]</price> </rex_item> "; } echo '</rex>'; }