mysql -u root -p
create database players;
use players;
GRANT SELECT,UPDATE,INSERT,DELETE ON players.* TO ‘user’@’localhost’;
CREATE TABLE`players` ( `id` int(11) NOT NULL auto_increment, `firstname` varchar(32) NOT NULL, `lastname` varchar(32) NOT NULL, PRIMARY KEY(`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
INSERT INTO players (firstname,lastname) VALUES(‘孝彦’, ‘藤本’);
INSERT INTO players (firstname,lastname) VALUES(‘かよ子’, ‘藤本’);
INSERT INTO players (firstname,lastname) VALUES(‘太郎’, ‘藤本’);
INSERT INTO players (firstname,lastname) VALUES(‘花子’, ‘藤本’);
mkdir /var/www/html/playes
vi /var/www/html/players/connect-db.php
<?php // mysql server 接続設定情報 $server= 'localhost'; $user= 'user'; $pass= 'パスワード'; $db= 'players'; // データーベース接続手続 $mysqli= new mysqli($server, $user, $pass, $db); // エラーの表示 開発が終了したら削除する mysqli_report(MYSQLI_REPORT_ERROR); ?> vi /var/www/html/players/view.php
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"> <html> <head> <title>View Records</title> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> </head> <body> <h1>View Records</h1> <p><b>View All</b> |<a href="view-paginated.php">View Paginated</a></p> <?php // connect to the database include('connect-db.php'); // get the records from the database //$resultにデーターがあれば if ($result= $mysqli->query("SELECT * FROM players ORDER BY id")) { // display records if there are records to display //レコードが0以上であれば if ($result->num_rows> 0) { // display records in a table // テーブとして表示する echo"<table border='1' cellpadding='10'>"; // set table headers //テーブルのヘッダー表示 echo"<tr><th>ID</th><th>First Name</th><th>Last Name</th><th></th><th></th></tr>"; while ($row= $result->fetch_object()) { // set up a row for each record //テーブルのデーター表示 echo"<tr>"; echo"<td>" . $row->id. "</td>"; echo"<td>" . $row->firstname. "</td>"; echo"<td>" . $row->lastname. "</td>"; echo"<td><a href='records.php?id=" . $row->id. "'>Edit</a></td>"; echo"<td><a href='delete.php?id=" . $row->id. "'>Delete</a></td>"; echo"</tr>"; } echo"</table>"; } // if there are no records in the database, display an alert message else { echo"No results to display!"; } } // show an error if there is an issue with the database query else { echo"Error: " . $mysqli->error; } // close database connection // データーベース接続終了処理 $mysqli->close(); ?> <a href="records.php">Add New Record</a> </body> </html>
vi /var/www/html/players/view-paginated.php
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"> <html> <head> <title>レコードを見る</title> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> </head> <body> <h1>レコードを見る</h1> <?php // connect to the database //データーベース接続処理 include('connect-db.php'); // number of results to show per page //1ページに表示するデーターの行数 $per_page= 3; // figure out the total pages in the database //データベースの総ページ数を把握する if ($result= $mysqli->query("SELECT * FROM players ORDER BY id")) { if ($result->num_rows!= 0) { //全レコードの行数を得る $total_results= $result->num_rows; // ceil() returns the next highest integer value by rounding up value if necessary //ページ数を得る $total_pages= ceil($total_results/ $per_page); // check if the 'page' variable is set in the URL (ex: view-paginated.php?page=1) // 既にpage変数が設定されているかどうかを確認する if (isset($_GET['page']) && is_numeric($_GET['page'])) { //既に設定されている（現在のページ）を$show_page変数に入力 $show_page= $_GET['page']; // make sure the $show_page value is valid //$show_pageの値が有効ならば if ($show_page> 0 && $show_page<= $total_pages) { //現在のページの先頭レコード数を設定 $start= ($show_page-1) * $per_page; //現在のページの最終レコード数を設定 $end= $start+ $per_page; } else { // error - show first set of results //プログラム開始時の設定 $start= 0; $end= $per_page; } } else { // if page isn't set, show first set of results $start= 0; $end= $per_page; } // display pagination echo"<p><a href='view.php'>View All</a> | <b>View Page:</b> "; //ページ数をカウントする ページ数を表示する for ($i= 1; $i<= $total_pages; $i++) { if (isset($_GET['page']) && $_GET['page'] == $i) { //ページ数を順番に水平に表示していく echo $i. " "; } else { //ページ数のリンクを順番に表示する echo"<a href='view-paginated.php?page=$i'>$i</a> "; } } echo"</p>"; // display data in table echo"<table border='1' cellpadding='10'>"; echo"<tr> <th>ID</th> <th>First Name</th> <th>Last Name</th> <th></th> <th></th></tr>"; // loop through results of database query, displaying them in the table //該当レコード表示していく for ($i= $start; $i< $end; $i++) { // make sure that PHP doesn't try to show results that don't exist //該当レコードの最終ならば表示を終了する if ($i== $total_results) { break; } // find specific row //該当レコードのデーターを取得 $result->data_seek($i); $row= $result->fetch_row(); // echo out the contents of each row into a table echo"<tr>"; echo'<td>' . $row[0] . '</td>'; echo'<td>' . $row[1] . '</td>'; echo'<td>' . $row[2] . '</td>'; //編集の場合は該当レコードの編集プログラムに移る echo'<td><a href="records.php?id=' . $row[0] . '">Edit</a></td>'; //削除の場合は該当レコードの削除プログラムに移る
echo'<td><a href="delete.php?id=' . $row[0] . '">Delete</a></td>'; echo"</tr>"; } // close table> echo"</table>"; } else { echo"No results to display!"; } } // error with the query else { echo"Error: " . $mysqli->error; } // close database connection $mysqli->close(); ?> <a href="records.php">Add New Record</a> </body> </html> </html> records.php（新しいレコードの作成/既存のレコードの編集）vi /var/www/html/players/records.php
vi /var/www/html/playes/records.php <?php /* Allows the user to both create new records and edit existing records //ユーザーが新しいレコードを作成して既存のレコードを編集できるようにする */ // connect to the database include("connect-db.php"); // creates the new/edit record form //新しい/編集レコードフォームを作成する // since this form is used multiple times in this file, I have made it a function that is easily reusable //このフォームはこのファイルで複数回使用されているので、簡単に再利用できる関数にしました function renderForm($first= '', $last='', $error= '', $id= '') { ?> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"> <html> <head> <title> <?phpif ($id!= '') { echo"Edit Record"; } else { echo"New Record"; } ?> </title> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> </head> <body> <h1><?phpif ($id!= '') { echo"Edit Record"; } else { echo"New Record"; } ?></h1> <?phpif ($error!= '') { echo"<div style='padding:4px; border:1px solid red; color:red'>" . $error . "</div>"; } ?> <form action="" method="post"> <div> <?phpif ($id!= '') { ?> <input type="hidden" name="id" value="<?php echo $id; ?>" /> <p>ID:<?php echo $id; ?></p> <?php} ?> <strong>First Name: *</strong> <input type="text" name="firstname" value="<?php echo $first; ?>"/><br/> <strong>Last Name: *</strong> <input type="text" name="lastname" value="<?php echo $last; ?>"/> <p>* required</p> <input type="submit" name="submit" value="Submit" /> </div> </form> </body> </html> <?php} /* EDIT RECORD */ // if the 'id' variable is set in the URL, we know that we need to edit a record if (isset($_GET['id'])) { // if the form's submit button is clicked, we need to process the form if (isset($_POST['submit'])) { // make sure the 'id' in the URL is valid if (is_numeric($_POST['id'])) { // get variables from the URL/form $id= $_POST['id']; $firstname= htmlentities($_POST['firstname'], ENT_QUOTES); $lastname= htmlentities($_POST['lastname'], ENT_QUOTES); // check that firstname and lastname are both not empty if ($firstname== '' || $lastname== '') { // if they are empty, show an error message and display the form $error= 'ERROR: Please fill in all required fields!'; renderForm($firstname, $lastname, $error, $id); } else { // if everything is fine, update the record in the database if ($stmt= $mysqli->prepare("UPDATE players SET firstname = ?, lastname = ? WHERE id=?")) { $stmt->bind_param("ssi", $firstname, $lastname, $id); $stmt->execute(); $stmt->close(); } // show an error message if the query has an error else { echo"ERROR: could not prepare SQL statement."; } // redirect the user once the form is updated header("Location: view.php"); } } // if the 'id' variable is not valid, show an error message else { echo"Error!"; } } // if the form hasn't been submitted yet, get the info from the database and show the form else { // make sure the 'id' value is valid if (is_numeric($_GET['id']) && $_GET['id'] > 0) { // get 'id' from URL $id= $_GET['id']; // get the recod from the database if($stmt= $mysqli->prepare("SELECT * FROM players WHERE id=?")) { $stmt->bind_param("i", $id); $stmt->execute(); $stmt->bind_result($id, $firstname, $lastname); $stmt->fetch(); // show the form renderForm($firstname, $lastname, NULL, $id); $stmt->close(); } // show an error if the query has an error else { echo"Error: could not prepare SQL statement"; } } // if the 'id' value is not valid, redirect the user back to the view.php page else { header("Location: view.php"); } } } /* NEW RECORD */ // if the 'id' variable is not set in the URL, we must be creating a new record else { // if the form's submit button is clicked, we need to process the form if (isset($_POST['submit'])) { // get the form data $firstname= htmlentities($_POST['firstname'], ENT_QUOTES); $lastname= htmlentities($_POST['lastname'], ENT_QUOTES); // check that firstname and lastname are both not empty if ($firstname== '' || $lastname== '') { // if they are empty, show an error message and display the form $error= 'ERROR: Please fill in all required fields!'; renderForm($firstname, $lastname, $error); } else { // insert the new record into the database if ($stmt= $mysqli->prepare("INSERT players (firstname, lastname) VALUES (?, ?)")) { $stmt->bind_param("ss", $firstname, $lastname); $stmt->execute(); $stmt->close(); } // show an error if the query has an error else { echo"ERROR: Could not prepare SQL statement."; } // redirec the user header("Location: view.php"); } } // if the form hasn't been submitted yet, show the form else { renderForm(); } } // close the mysqli connection $mysqli->close(); ?> vi /var/www/html/players/delete.php
<?php // connect to the database include('connect-db.php'); // confirm that the 'id' variable has been set if (isset($_GET['id']) && is_numeric($_GET['id'])) { // get the 'id' variable from the URL $id= $_GET['id']; // delete record from database if ($stmt= $mysqli->prepare("DELETE FROM players WHERE id = ? LIMIT 1")) { $stmt->bind_param("i",$id); $stmt->execute(); $stmt->close(); } else { echo"ERROR: could not prepare SQL statement."; } $mysqli->close(); // redirect user after delete is successful header("Location: view.php"); } else // if the 'id' variable isn't set, redirect the user { header("Location: view.php"); } ?> http://localhost/players/view.php 参考 http://www.killersites.com/community/index.php?/topic/3064-basic-php-system-view-edit-add-delete-records-with-mysqli/
コメント