$ cd ~/www $ cat include/config.php <?php $db_link = mysql_connect('localhost', 'username', 'userpw'); if ( ! $db_link ) { echo "資料連結失敗了！\n"; die ; } else $db_sel = mysql_select_db ('userdb', $db_link); if ( ! $db_sel ) { echo "資料庫選取失敗了！\n"; die; } ?> $ mysql -u username -p Enter password: <==這裡輸入密碼！ Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 4 Server version: 5.5.52-MariaDB MariaDB Server Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | userdb | (我們之前建立的資料庫，應該還沒有忘記吧！) +--------------------+ 2 rows in set (0.00 sec) MariaDB [(none)]> use userdb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [(none)]> show tables; +------------------+ | Tables_in_userdb | +------------------+ | userinfo | (等等要來加入欄位的資料表) +------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> describe userinfo; +------------+--------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+-------------------+-----------------------------+ | uid | int(11) | NO | PRI | NULL | auto_increment | | login_name | varchar(40) | NO | | NULL | | | login_pass | varchar(64) | NO | | NULL | | | realname | varchar(40) | NO | | NULL | | | u_email | varchar(100) | YES | | NULL | | | u_bday | date | NO | | NULL | | | u_sex | tinyint(4) | NO | | 0 | | | u_active | tinyint(4) | NO | | 0 | | | u_regd | timestamp(6) | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +------------+--------------+------+-----+-------------------+-----------------------------+ 9 rows in set (0.00 sec)
userlevel tinyint not null default 0接下來就能夠使用 alter 來直接修改資料表內的欄位 (colume) 了！語法是這樣：
alter table [table name] add column [colume name] [colume describe]; (新增) alter table [table name] drop column [colume name]; (刪除)上面的中括號內的資料就是我們要加入的相關資訊。因此，最終我們這樣來瞧瞧：
MariaDB [(none)]> alter table userinfo add column userlevel tinyint not null default 0; Query OK, 22 rows affected (0.01 sec) Records: 22 Duplicates: 0 Warnings: 0 MariaDB [(none)]> describe userinfo; +------------+--------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+-------------------+-----------------------------+ | uid | int(11) | NO | PRI | NULL | auto_increment | | login_name | varchar(40) | NO | | NULL | | | login_pass | varchar(64) | NO | | NULL | | | realname | varchar(40) | NO | | NULL | | | u_email | varchar(100) | YES | | NULL | | | u_bday | date | NO | | NULL | | | u_sex | tinyint(4) | NO | | 0 | | | u_active | tinyint(4) | NO | | 0 | | | u_regd | timestamp(6) | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | userlevel | tinyint(4) | NO | | 0 | | (新增的項目，檢查看看) +------------+--------------+------+-----+-------------------+-----------------------------+ 10 rows in set (0.00 sec) MariaDB [(none)]> select uid,login_name,userlevel from userinfo; +-----+------------+-----------+ | uid | login_name | userlevel | +-----+------------+-----------+ | 1 | a030cxxx | 0 | | 2 | a030c101 | 0 | | 3 | vbird | 0 | | 4 | vbird | 0 | | 5 | vbird | 0 | | 6 | vbird | 0 | +-----+------------+-----------+ 22 rows in set (0.00 sec)
update [table name] set [column name] = [column content] where [column name] = [column content];同樣的，上述的畫面中中括號內的資料就是你要自己設計的判斷項目。現在我們要讓第一個帳號 (uid=1) 的用戶變成管理員， 那就可以使用如下的方式來處理：
MariaDB [(none)]> update userinfo set userlevel = 1 where uid = 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [(none)]> select uid,login_name,userlevel from userinfo; +-----+------------+-----------+ | uid | login_name | userlevel | +-----+------------+-----------+ | 1 | a030cxxx | 1 | (這個用戶就會變成管理員囉！) | 2 | a030c101 | 0 | | 3 | vbird | 0 | | 4 | vbird | 0 | | 5 | vbird | 0 | +-----+------------+-----------+ 22 rows in set (0.00 sec)
delete from [table] where [column name] = [column content];假設我們想要刪除 uid 為 2 的那筆資料，就可以這樣進行：
MariaDB [(none)]> delete from userinfo where uid = 2; MariaDB [(none)]> select uid,login_name,userlevel from userinfo; (做完一定要檢查一下，是否正確執行完畢？) MariaDB [(none)]> exit最終應該就可以處理好我們所需要的環境了！
# vim include/function.php if ( $_REQUEST['action'] == 'login' ) { // 先取得所有的參數 $checking = $_REQUEST['checking']; $login_name = trim($_REQUEST['login_name']); $login_pass = trim( hash ('sha256', $_REQUEST['login_pass'])); // 先設定一個名為 msg 的變數，這個變數內容如果是空的，代表沒有錯誤，若非為空，則是有錯誤！ $msg = ''; // 先確認是否取得正確的註冊驗證碼 if ( $_SESSION['CAPTCHA'] != $checking ) { $msg = '驗證碼錯誤！請重新取得正確的驗證碼資訊'; } else { // 開始取得帳號與密碼接下來你應該要嘗試登入兩個不同的身份看看，試試看能不能改變導覽列的內容！若可以，就代表你的系統已經具有基礎的會員等級設定了！$sql = "select login_name, realname from userinfo where login_name = '$login_name' and login_pass = '$login_pass'";$sql = "select login_name, realname, uid,userlevel from userinfo where login_name = '$login_name' and login_pass = '$login_pass'"; $result = mysql_query ($sql, $db_link ); if ( ! $result ) { // 找不到資料的結果，就是給予錯誤訊息 $msg = '查不到這個帳號或密碼錯誤'; } else { $row = mysql_fetch_row($result); $_SESSION['username'] = $row[1];$_SESSION['userlevel'] = 'user';if ( $row[3] == 0 ) { $_SESSION['userlevel'] = 'user'; } else if ( $row[2] == 1 ) { $_SESSION['userlevel'] = 'admin'; } $_SESSION['userid'] = $row[2]; } } } if ( $_REQUEST['action'] == 'logout' ) { unset($_SESSION['userid']); unset($_SESSION['username']); unset($_SESSION['userlevel']); $urltmp = explode('?',$_SERVER['PHP_SELF']); $url = $urltmp[0]; echo "<script>window.location = '$url'</script>"; die; }
$ vim include/header.php <li <?php if ( $pagenu == "person" ) echo 'class="active"' ?>><a href="<?php echo $root_web; ?>/member.php"><span class="glyphicon glyphicon-user"></span> 會員資料</a></li> $ vim member.php (大部分的資料來自於 register.php 喔！) <?php $pagenu = 'person'; include ('include/header.php'); $sql = "select login_name, realname, u_email, u_bday, u_sex, date(u_regd) from userinfo where uid = " . $_SESSION['userid'] ; $result = mysql_query ( $sql, $db_link ); $row = mysql_fetch_row($result); $u_login = $row[0]; $u_name = $row[1]; $u_email = $row[2]; $u_bday = $row[3]; $u_sex = $row[4]; $u_regd = $row[5]; ?> <!--中間資料--> <div class="col-md-8"> <div class="panel panel-primary"> <div class="panel-heading">會員基本資料</div> <div class="panel-body" style="min-height:400px;"> <form id="regform" onsubmit="return checkinput()" > <table> <tr> <td>登入帳號：</td> <td><?php echo $u_login; ?></td> <td>不可更改囉！</td> </tr> <tr> <td>真實姓名：</td> <td><input type="text" name="realname" value="<?php echo $u_name; ?>" /></td> <td>填寫註冊者的姓名</td> </tr> <tr> <td>電子郵件：</td> <td><input type="email" name="u_email" value="<?php echo $u_email; ?>" /></td> <td>請填慣用的電子郵件</td> </tr> <tr> <td>生日：</td> <td><input type="date" name="u_bday" required="required" id='bday' value="<?php echo $u_bday; ?>" /></td> <td>請務必使用 YYYY-MM-DD 的格式，如 1990-01-20</td> </tr> <tr> <td>性別：</td> <td><select name="u_sex"> <option value="0" <?php if ( $u_sex == 0 ) echo "selected='selected'"; ?>>女性</option> <option value="1" <?php if ( $u_sex == 1 ) echo "selected='selected'"; ?>>男性</option> </select></td> <td>選擇性別</td> </tr> <tr> <td>註冊日期：</td> <td><?php echo $u_regd; ?></td> <td>不能修改喔！</td> </tr> </table> </form> </div> </div> </div> <!--中間資料結束--> <?php include ('include/footer.php'); ?>
$ vim member.php <?php $pagenu = 'person'; $userlevel = 'user'; (一定要放在 include 之前！否則就沒用了！) include ('include/header.php'); .... $ vim include/function.php (這個檔案的最下方來處理新增程式碼，就 OK 了！) // 使用者等級的權限限制 if ( isset ( $userlevel) ) { if ( $userlevel == 'user' ) { if ( $_SESSION['userlevel'] != 'user' && $_SESSION['userlevel'] != 'admin' ) { echo "你沒有權限瀏覽此頁面<br />"; echo "<a href='" . $root_web . "'>點擊回首頁</a>"; die; } } if ( $userlevel == 'admin' ) { if ( $_SESSION['userlevel'] != 'admin' ) { echo "你沒有權限瀏覽此頁面<br />"; echo "<a href='" . $root_web . "'>點擊回首頁</a>"; die; } } }未來只要你想要處理權限問題，將這個設定值 $userlevel 帶入就可以了！如此才能夠避免系統不小心被攻擊的問題喔！相當重要！
$ vim member.php ..... <form id="regform" onsubmit="return checkinput()" > <table> <tr> <td>登入帳號：</td> <td><?php echo $u_login; ?></td> <td>不可更改囉！</td> </tr> <tr> <td rowspan="3">修改密碼：<br/>可不更動</td> <td><input type="password" name="login_pass0" id='login_pass0' /></td><td>舊的密碼(一定要正確)</td> </tr> <tr><td><input type="password" name="login_pass1" id='login_pass1' /></td><td>舊的密碼(一定要正確)</td></tr> <tr><td><input type="password" name="login_pass2" id='login_pass2' /></td><td>再一次確認新密碼</td></tr> <tr><td colspan="3">----</td> ..... <tr> <td colspan="3" style="text-align:center; "> <input type="hidden" name="action" value="userupdate" /> <input type="submit" value="送出修改" /> </td> </tr> </table> </form> </div> </div> </div> <!--中間資料結束--> <script> function checkinput() { // 檢查登入帳號是否有特殊字元 var re = /[^a-zA-Z0-9.-_]/; var okname = re.exec ( document.getElementById("login_name").value); if ( okname ) { window.alert ( "指允許英文、數字、底線、小數點與減號" ); document.getElementById("login_name").focus(); return false; } // 開始檢查密碼長度是否正確？ var pw1 = document.getElementById("login_pass1"); if ( pw1.value.length < 5 ) { window.alert ( "密碼長度必須要大於 5 個字元以上" ); document.getElementById("login_pass1").focus(); return false; } // 看看兩次密碼是否相同？ var pw2 = document.getElementById("login_pass2"); if ( pw1.value != pw2.value ) { window.alert ( "兩次密碼並不相同！" ); document.getElementById("login_pass1").focus(); return false; } // 檢查生日的格式正確與否 re = /^[0-9]{4}[./-][0-9]{2}[./-][0-9]{2}$/; var okday = re.exec ( document.getElementById("bday").value); if ( ! okday ) { window.alert ( "日期格式為 2011-11-11" ); document.getElementById("bday").focus(); return false; } } </script> ....基本上，我們多了修改密碼的欄位，這個欄位比較特別，需要輸入舊的密碼才能夠填寫新密碼，避免密碼不小心被亂改了。 此外，上傳的 action 我們指定為 userupdate 喔！接下來，要去 function.php 新建一個新的 action 來進行更新的動作才行！
$ vim include/function.php
// 用戶有需要進行更新自己的帳號時，這才處理！
if ( $_REQUEST['action'] == 'userupdate' ) {
if ( $_SESSION['userlevel'] == 'user' || $_SESSION['userlevel'] == 'adamin' ) {
// 先取得所需要的變數資料
$login_pass0 = trim($_REQUEST['login_pass0']);
$login_pass1 = trim($_REQUEST['login_pass1']);
$login_pass2 = trim($_REQUEST['login_pass2']);
$realname = trim($_REQUEST['realname']);
$u_email = trim($_REQUEST['u_email']);
$u_bday = trim($_REQUEST['u_bday']);
$u_sex = trim($_REQUEST['u_sex']);
$pass = '';
// 若有需要進行密碼更新時！
if ( $login_pass0 != '' ) {
$pass_raw = hash ('sha256', $login_pass0);
$sql = 'select login_pass from userinfo where uid = ' . $_SESSION['userid'];
$result = mysql_query ( $sql, $db_link );
$row = mysql_fetch_row($result);
if ( $row[0] != $pass_raw ) {
echo "<script>alert('舊的密碼不正確喔！');history.go(-1);</script>";
die;
} else {
if ( $login_pass1 != $login_pass2 ) {
echo "<script>alert('兩次密碼輸入不相同');history.go(-1);</script>";
die;
}
}
$login_pass = hash ('sha256', $login_pass1);
$pass = "login_pass = '$login_pass', ";
}
// 開始整理相關註冊資訊
$sql = "update userinfo set " .
$pass .
"realname = '$realname', " .
"u_email = '$u_email', " .
"u_bday = '$u_bday', " .
"u_sex = '$u_sex' " ;
"where uid = " . $_SESSION['userid'];
$result = mysql_query ($sql, $db_link );
if ( ! $result ) {
echo "<script>alert('無法更新！！');history.go(-1);</script>";
die;
}
}
}
這樣就能夠更新囉！實驗看看！