ダブルパー　【My Script】　SQL_1

■【My Script】データベースに登録されたカテゴリ[SQL]での記事[7]ケの内、[1]番目から[7]ケを表示)

□(1)　文字列からキーを抽出しデータベース(MySQL)のレコードをセレクトする
□(2)　xrea→coreserveへMySQLデータのお引っ越し
□(3)　テーブルを作る(PHPでデータベースからSELECTしたデータによる)
□(4)　データベース(MySQL)の日時データと現在の日時の差を求める
□(5)　データベース(MySQL)からランダムに複数個のレコードデータをセレクトする
□(6)　データベース(MySQL)からセレクトしたデータ取得数をカウントする
□(7)　セレクトフィールドにデータベース(MySQL)からSELECTしたデータをバインドする

【文字列からキーを抽出しデータベース(MySQL)のレコードをセレクトする】

◆概要
※カンマ区切りで接続された複数のデータをキーとしてデータベース(MySQL)からレコードをセレクトする。

◆MySQL+PHP(本文)
  ※テーブル名:tblMySample
  ※フィールド:FILD1,FILD2,FILD3,...
  ※文字列
◆Tips
[PHP]
<?php
  $Data="001,002,005,..."

  $arrData=explode(",",$Data);  //文字列を配列変換
  $QesCnt=count($arrQesNumber);  //要素数
  for($n=0;$n<$QesCnt;$n++){
  if($n==0){
     $sqlWhere=" WHERE DataID='".$arrQesNumber[$n]."' ";
  }else{
     $sqlWhere.=" OR DataID='".$arrQesNumber[$n]."' ";
  }   

  $con=mysql_connect(MYSQL_CONNECT_HOST,MYSQL_CONNECT_USER,MYSQL_CONNECT_PASS);
  mysql_select_db(MYSQL_DB_NAME);

  $sql="SELECT * FROM tblMySample ".$sqlWhere;
  $res=mysql_query($sql);
  ...
  mysql_close($con);  //SQLクローズ
?>

◆MySQL(config.php)---省略

◆メモ
※「データベース(MySQL)からランダムに複数個のレコードデータをセレクトする」で作成したデータの文字列を元に、同じテーブルから改めてレコードを抽出するとき等

[xrea]でmySQLを用いてサイト運営していたが、今回、[coreserver]に引っ越すことにした。htmlやphp等のファイルは簡単にお引っ越しができたが(phpは一部修正が必要なものがあったが、これば別題)、問題はSQL。[xrea]ではmySql4だが[coreserver]はmySql5。バージョンが上がっている。mySql4では文字コードをEUCにしていたが、なにやらmySQLではninicode(UTF-8)とshift-JISしかない(^^;;)。これが原因なのか、普通にお引っ越しをすると、字化けしてなんともならない。悪戦苦闘の末、たどりついたのが下記。問題がすべて解決したわけでなく、理由も全く理解できないのだが、とにかく字化けだけは修正された。

■[xrea](mySQL4)側でデータベースのエクスポート

(1)データベースのトップページからPhpMyAdminのお引っ越しするDBにログイン
(2)「エクスポート」タブを選択
(3)左側のエクスポートするファイルを全選択。
(4)エクスポートの形式は「SQL」
(5)「構造」の「DROP TABLEを追加」にチェック
(6)「ファイルを保存する」をチェック(圧縮なし)
(7)その下の「エンコーディングに変換する」は「non」にチェック
(8)「実行する」ボタンをクリック
(9)「データベース名.sql」というファイルをローカルPCに保存

■[coreserver](mySQL5)側でデータベースを作成

(1)データベーストップページで、新たにDBを作成。
(2)ユーザー名とログインパスワードを入力。文字コードを「unicode」に設定して「作成」ボタンをクリック。

■[coreserver](mySQL5)側で作成したデータベースにデータをインポート

(1)データベースのトップページからPhpMyAdminの新たに作成したDBにログイン。DBを選択すると「このデータベースにはテーブルがありません」と表示される。
(2)「操作」タブを選択し「照合順序」を「latin_general_ci」に設定して「実行する」ボタンをクリック。
(3)「インポート」タブを選択。
(4)「インポートするファイル」で[xrea]側でエクスポートしたファイルのパスを指定。
(5)「ファイルの文字セット」を「latin1」に設定。
(6)「インポートするファイル形式」を「MYSQL4.0」に設定。
(7)「エンコーディングに変換する」は「non」。
(8)「実行する」ボタンをクリック。
(9)「インポートは正常終了しました。」と表示されて左側にテーブルの一覧が表示されれはお引っ越しは終了。

■大問題!!

(1)ホームページ側でデータを表示するは正常だが、PhpMyAdminでテーブルのデータを表示すると、完全に字化けしている。データベースへのデータの入出力はすべてウェブページから実行しているし、これを解決するのはなかなか大変そうなので、いまのところはそのままにしておく。
(2)引っ越した先で新たにデータベースを作成する場合は、全く違うDBを新規に作ることにしよう。

【テーブルを作る(PHPでデータベースMySQLからSELECTしたデータによる)】

◆概要
※MySQLからSELECTしたデータから、テーブルを作成する。
※PHPスクリプトは直接HTMLに書き込めるので、こうした芸当が可能である。このテクの応用範囲は広い。

◆[MySQL]
※テーブル名:tblSample 
※フィールド:FILD1,FILD2,FILD3,FILD4 
※検索条件:FILD1=$strDat1で検索して得られる全フィールドのデータをFILD1昇順にてテーブルに書き出す。1列目は行番号を記述。

◆[XHTML]+[PHP]+[MySQL]
<table id="tblData" cellpadding="1" cellspacing="1">
<colgroup span="5" width="100" align="center" valign="middle" ></colgroup>
<tr>
<th>No</th>
<th>列1</th>
<th>列2</th>
<th>列3</th>
<th>列4</th>
</tr>
<?php
  //※データベース設定 
  require("config.php"); 
  //※SQL接続 
  $con=mysql_connect(MYSQL_CONNECT_HOST,MYSQL_CONNECT_USER,MYSQL_CONNECT_PASS); 
  mysql_select_db(MYSQL_DB_NAME); 
  //※SQL実行 
  $sql="SELECT * FROM tblMySample WHERE FILD1='".$strDat1."' ORDER BY FILD1; 
  $res=mysql_query($sql,$con); 
 
  $n=1;
  while($row=mysql_fetch_array($res)){
      $data1=$row['FILD1'];
      $data2=$row['FILD2'];
      $data3=$row['FILD3'];
      $data4=$row['FILD4'];
?>
<tr>
<td><?php echo $n; ?></td>
<td><?php echo $data1; ?></td>
<td><?php echo $data2; ?></td>
<td><?php echo $data3; ?></td>
<td><?php echo $data4; ?></td>
</tr>
<?php  $n=$n+1;
  }
  mysql_close($con);  //SQLクローズ 
?>
</table>

◆MySQL(config.php)---データベース設定(本文でrequire()) 
<?php 
  define("MYSQL_CONNECT_HOST","localhost"); //DBホスト名   
  define("MYSQL_CONNECT_USER","DBuser"); //DBuser:sample 
  define("MYSQL_CONNECT_PASS","DBpassword"); //DB password:sample 
  define("MYSQL_DB_NAME","DBname"); //DB name:sample 
?> 

◆メモ 
※データが字化けを起こす場合(MySqlとhtmlの文字コードの相違等)、
  $data1=mb_convert_encoding($row['FILD1'],"SJIS",auto);
とエンコードする。

【タイムスタンプによる日時の差の計算】

◆MySQLのフィールドの種別をdatetimeに設定すると、データは
　　1999-99-99 99:99:99(年-月-日 時:分:秒)
という形式で格納される。このデータを取得して現在時刻との差を計算する。

◆PHP Tips
//$strRegDate:MySQLから取得した日時データ
$strTimeStamp=strtotime($strDate); //タイムスタンプに変換
$strTime=time()-$strTimeStamp); //タイムスタンプ差計算

◆MEMO
※現在の日時のタイムスタンプ  time()
※1週間のタイムスタンプ= 7*24*3600
※n日間のタイムスタンプ= n*24*3600

【データベース(MySQL)からランダムに複数個のレコードデータをセレクトする】

◆概要
※データベース(MySQL)のテーブル(tblMySample)から、ランダムにn個のレコードをセレクトする
※セレクトしたデータをカンマで接続して文字列に変換する

◆MySQL+PHP(本文)
  ※テーブル名:tblMySample
  ※フィールド:FILD1,FILD2,FILD3,...
<?php
  //セレクトするレコード数(nは整数)
  $n=n;
  //※データベース設定
  require("config.php");
  //※SQL接続
  $con=mysql_connect(MYSQL_CONNECT_HOST,MYSQL_CONNECT_USER,MYSQL_CONNECT_PASS);
  mysql_select_db(MYSQL_DB_NAME);

  //※SQL実行
  $sql="SELECT FILD1 FROM tblMySample ORDER BY RAND() LIMIT ".$n
  $res=mysql_query($sql,$con);

  $SelCNT=mysql_num_rows($res);  //セレクトしたデータ数
  $j=0;
  while($row=mysql_fetch_array($res,MYSQL_ASSOC)){
      if($j==0){
          $Data=$row['FILD1'];
      }else{
          $Data.=",".$row['FILD1'];  //カンマで接続
      }   
      $n=$n+1;
  }
 
  mysql_close($con);  //SQLクローズ
?>

◆MySQL(config.php)---データベース設定(本文でrequire())
<?php
  define("MYSQL_CONNECT_HOST","localhost"); //DBホスト名  
  define("MYSQL_CONNECT_USER","DBuser"); //DBuser:sample
  define("MYSQL_CONNECT_PASS","DBpassword"); //DB password:sample
  define("MYSQL_DB_NAME","DBname"); //DB name:sample
?>

◆メモ
※重複しないレコードをセレクトする。
※$データは 「****,****,****」という文字列になる。

※テーブルのすべてのレコードを参照するので、レコードが多いテーブルでは、かなりサーバーの負荷になる。別の方法を採用する必要がある。

【データベース(MySQL)からセレクトしたデータ取得数をカウントする】

◆MySQL+PHP(本文)
  ※テーブル名:tblMySample
  ※フィールド:FILD1,FILD2,FILD3,...
  ※WHRER句:FILD1=$strDat1
<?php
  //※データベース設定
  require("config.php");
  //※SQL接続
  $con=mysql_connect(MYSQL_CONNECT_HOST,MYSQL_CONNECT_USER,MYSQL_CONNECT_PASS);
  mysql_select_db(MYSQL_DB_NAME);

  //※SQL実行
  $sql="SELECT COUNT(*) AS CNT FROM tblMySample WHERE FILD1='".$strDat1."'";  //COUNT()を別名CNTで
  $res=mysql_query($sql,$con);
  $row=mysql_fetch_array($res,MYSQL_ASSOC); //sqlデータ配列変換     
  $valCNT=$row['CNT']; //取得した値を変数に代入

  //※SQLクローズ
  mysql_close($con);
?>

◆MySQL(config.php)---データベース設定(本文でrequire())
<?php
  define("MYSQL_CONNECT_HOST","localhost"); //DBホスト名  
  define("MYSQL_CONNECT_USER","DBuser"); //DBuser:sample
  define("MYSQL_CONNECT_PASS","DBpassword"); //DB password:sample
  define("MYSQL_DB_NAME","DBname"); //DB name:sample
?>

◆メモ
※COUNT()に*でなく特定のフィールドを指定すると、そのフィールドでの検索条件にヒットした件数となる。但し、NULLはカウントされない。
※SELECT COUNT()...を用いず、SELECT文でデータを検索したあとで
  ...
  $res=mysql_query($sql,$con);
  $valCNT=mysql_num_rows($res);  //セレクトしたデータ数
  ...
として取得数を取得することもできる。

【セレクトフィールドにデータベース(MySQL)からSELECTしたデータをバインドする】

◆概要
※MySQLからSELECTしたデータをセレクトフィールドにバインドして選択できるようにする。

◆[MySQL]
※テーブル名:tblSample 
※フィールド:FILD1,FILD2,FILD3,... 
※WHRER句:FILD1=$strDat1で検索して得られるFILD2のデータをバインドする

◆[XHTML]+[PHP]+[MySQL]
<select>
<option value="0">--ファイル選択</option>
<?phpFILD2
  //※データベース設定 
  require("config.php"); 
  //※SQL接続 
  $con=mysql_connect(MYSQL_CONNECT_HOST,MYSQL_CONNECT_USER,MYSQL_CONNECT_PASS); 
  mysql_select_db(MYSQL_DB_NAME); 

  //※SQL実行 
  $sql="SELECT FILD2 FROM tblMySample WHERE FILD1='".$strDat1."' ORDER BY FILD2; 
  $res=mysql_query($sql,$con); 
 
  while($row=mysql_fetch_array($res)){
      $dd=mb_convert_encoding($row['FILD2'],"SJIS","EUC-JP");
      echo "<option value='".$dd."'>".$dd."</option>";
  }

  //※SQLクローズ 
  mysql_close($con); 
?> 
</select>

◆MySQL(config.php)---データベース設定(本文でrequire()) 
<?php 
  define("MYSQL_CONNECT_HOST","localhost"); //DBホスト名   
  define("MYSQL_CONNECT_USER","DBuser"); //DBuser:sample 
  define("MYSQL_CONNECT_PASS","DBpassword"); //DB password:sample 
  define("MYSQL_DB_NAME","DBname"); //DB name:sample 
?> 
◆メモ 
※$dd=...は字化けを起こす場合(問題なければ不要) 
※上記の例では<option>のvalueと表示されてるTEXTを同じ$ddにしているが、別のデータにすることも可能