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> <?php if ($id != '') { echo "Edit Record"; } else { echo "New Record"; } ?> </title> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> </head> <body> <h1><?php if ($id != '') { echo "Edit Record"; } else { echo "New Record"; } ?></h1> <?php if ($error != '') { echo "<div style='padding:4px; border:1px solid red; color:red'>" . $error . "</div>"; } ?> <form action="" method="post"> <div> <?php if ($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/
コメント