建榮的資訊筆記: PHP&MySQL--8 2010年11月課程講義預覽

PHP&MySQL--8
一、表單送資料至PHP後查詢資料【提供表單網頁】
1. 練習SQL語法中where語法

select employeeid,firstname from employees where (employeeid 〉= 3);
select employeeid,firstname from employees where (employeeid in(2,4,9));
select employeeid,firstname from employees where (employeeid not in(2,4,9));
select country,companyname from customers where (country in('Argentina','Mexico'));

select region,companyname from customers where (region is null);
select region,companyname from customers where (region is not null);
select firstname from employees where (firstname like 'a%');
select firstname from employees where (firstname like '%a%');
select firstname from employees where (firstname like '%a');
select companyname,contactname,country from customers where (country in('France','Germany') and companyname like 'b%');

select companyname,contactname,country from customers where (country in('France','Germany') or companyname like 'b%');

2. 若直接將post傳送過來的資料送至sql語法？【提供query03.php】

〈html〉〈head〉
〈meta http-equiv="Content-Type" content="text/html; charset=UTF-8"〉
〈title〉接收資料〈/title〉〈/head〉〈body〉
〈?
include("server.php");
$sql="SELECT * FROM orders where CustomerID=".$_POST['text1'];
$sql2=mysql_query($sql) or die(mysql_error());
echo mysql_num_rows($sql2)."〈br〉";
while($list1= mysql_fetch_row($sql2))
{
echo $list1[0]." ".$list1[1]."〈br〉";
}
?〉〈/body〉〈/html〉

3. 上例修改【請依照講義修改query03.php】

//$sql="SELECT * FROM orders where CustomerID=".$_POST['text1'];
$sql="SELECT * FROM orders where CustomerID='".$_POST['text1']."'";

4. 上例修改【請依照講義修改query03.php】

//$sql="SELECT * FROM orders where CustomerID=".$_POST['text1'];
//$sql="SELECT * FROM orders where CustomerID='".$_POST['text1']."'";
$a=$_POST['text1'];
$sql="SELECT * FROM orders where CustomerID='$a'";

5. 測試各種模糊查詢【提供query04.php，但請修改表單網頁路徑】

〈html〉〈head〉
〈meta http-equiv="Content-Type" content="text/html; charset=UTF-8"〉
〈title〉接收資料〈/title〉
〈/head〉〈body〉
〈?php
include("server.php");
//測試各種模糊查詢
$sql="SELECT * FROM orders where CustomerID like '%$a%'";
$sql="SELECT * FROM orders where CustomerID like '$a%'";
$sql="SELECT * FROM orders where CustomerID like '%$a'";
$sql2=mysql_query($sql) or die(mysql_error());
echo mysql_num_rows($sql2)."〈br〉";
while($list1= mysql_fetch_row($sql2))
{
echo $list1[0]." ".$list1[1]."〈br〉";
}
?〉〈/body〉〈/html〉

二、傳遞參數給SQL語法：例如查詢customers筆數與範圍（提供表單）
【請自行撰寫】

〈html〉〈head〉
〈meta http-equiv="Content-Type" content="text/html; charset=utf-8"〉
〈title〉回傳各欄位資料〈/title〉〈/head〉〈body〉〈?
@mysql_connect("localhost", "pcschool", "phpmysql") or die("無法連結主機");
mysql_select_db("pcschool") or die("無法連結資料庫");
include("unicode.php");
echo $_SERVER['HTTP_REFERER']."〈br〉";
//$sql1="select * from customers limit ".$_POST['first'].",".$_POST['total'];
//$sql1="select * from customers limit $_POST['first'],$_POST['total']";

$a=$_POST['first'];
$b=$_POST['total'];
$sql1="select * from customers limit $a,$b";
//echo $sql1;
//$sql = @mysql_query($sql1);
$sql = mysql_query($sql1) or die(mysql_error());
$rows = @mysql_num_rows($sql);
//echo $rows;
if($rows==""){
echo "查無資料！";
}else{
echo "有 ".$rows." 筆資料喔！";
}
?〉〈/body〉〈/html〉

限制筆數之SQL語法：

select employeeid,firstname, lastname from employees ;
select employeeid,firstname, lastname from employees limit 1,30;
select employeeid,firstname, lastname from employees limit 0,5;
select employeeid,firstname, lastname from employees limit 6;

select productid,productname,unitprice from products order by unitprice desc limit 1;

select employeeid,firstname, lastname from employees order by rand() limit 6;

三、人數統計分頁分析【提供display1.php，並請修改網頁內容】

〈html〉〈head〉
〈meta http-equiv="Content-Type" content="text/html; charset=utf-8"〉
〈title〉人數統計分析〈/title〉〈/head〉
〈body〉〈?
include("server.php");
if( isset($_GET['page']) )
$page = intval( $_GET['page'] );
else{$page = 1;}
$page_size = 10;
$sql = "select count(*) as total from record";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
$total = $row['total'];
if(isset($total))
{
if( $total 〈 $page_size )
$page_count = 1;
if( $total % $page_size )
$page_count = (int)($total / $page_size) + 1;
else
$page_count = $total / $page_size;
}
else
$page_count = 0;
$page_number = '';
if( $page == 1 )
$page_number .= '[第一頁][上一頁] ';
else {
$page_number.='[〈a href='.$_SERVER['PHP_SELF'].'?page=1〉第一頁〈/a〉]';
$page_number.='[〈a href='.$_SERVER['PHP_SELF'].'?page='.($page-1).'〉'."上一頁〈/a〉]";
}
if(($page == $page_count) || ($page_count == 0))
$page_number .= '[下一頁][尾頁]';
else {
$page_number.='[〈a href='.$_SERVER['PHP_SELF'].'?page='.($page+1).'〉'."下一頁〈/a〉]";
$page_number.='[〈a href='.$_SERVER['PHP_SELF'].'?page='.$page_count.'〉'."尾頁〈/a〉]";
}
if(isset($total)) {
$sql = "select * from record limit ". ($page-1)*$page_size .", $page_size";
$result = mysql_query($sql);
echo "〈table border=1〉";
echo "〈tr〉〈td〉時間〈/td〉〈td〉ip〈/td〉〈/tr〉";
while($list1=mysql_fetch_array($result))
{
echo "〈tr〉〈td〉".$list1['visitday']."〈/td〉";
echo "〈td〉".$list1['ip']."〈/td〉〈/tr〉"; }
echo "〈/table〉";
echo $page_number; }
?〉〈/body〉〈/html〉

四、檔案上傳
1. 表單網頁【提供upload.htm】

〈html〉〈head〉〈meta http-equiv="Content-Type" content="text/html; charset=utf-8"〉〈title〉檔案上載〈/title〉〈/head〉〈body〉
〈form action="upload.php" method="post" enctype="multipart/form-data"〉
〈input type="hidden" name="MAX_FILE_SIZE" value="1000000"〉
Send this file: 〈input name="userfile" type="file"〉
〈input type="submit" value="Send File"〉
〈/form〉〈/body〉〈/html〉

2. 接收端網頁【提供upload.php，並請修改網頁內容】

〈html〉
〈head〉〈meta http-equiv="Content-Type" content="text/html; charset=utf-8"〉
〈title〉檔案上傳：接收端網頁〈/title〉〈/head〉〈body〉〈?
$uploaddir='./tmp/';
print "〈pre〉";
$file2=mb_convert_encoding($_FILES["userfile"]["name"],"big5","utf8");
if(move_uploaded_file($_FILES['userfile']['tmp_name'], $uploaddir.$file2))
{ echo "檔名：".$_FILES['userfile']['name']."〈br〉" ;
echo "大小：".$_FILES['userfile']['size']."〈br〉" ;
echo "型態：".$_FILES['userfile']['type']."〈br〉" ; }
else
{
print_r ($_FILES);
$a=$_FILES['userfile']['error'];
echo "a-〉".$a;
if ($a==1)
{ die("檔案大小超出 php.ini:upload_max_filesize 限制"); }
if($a==2)
{ die("檔案大小超出 MAX_FILE_SIZE 限制"); } }
echo $a;
?〉〈/body〉〈/html〉

五、 header( )進階使用
1. 開啟jpg圖檔【提供jpg1.php，並請修改網頁內容】

〈?
$im = imagecreatefromjpeg("test.jpg");
header('Content-type: image/jpg');
imagejpeg($im);
imagedestroy($im); ?〉

2. 與上例有關，增加ip限制【提供lockip.php，並請修改網頁內容】

〈?
function allow_ip($addr)
{
$first = substr($addr,0,(strrpos($addr,".")));
$second = substr($addr,(strrpos($addr,".")+1));
if(($first=="140.111.144") && ($second〉=1 && $second 〈=255))
{ return true; }
else
{ return false; }
}?〉

3. ip限制才能看圖【請自行撰寫】

〈?
require("lockip.php");
$addr_ip=$_SERVER['REMOTE_ADDR'];
$flag=allow_ip($addr_ip);
if(!$flag)
{die("不能看圖，IP位置不合法");}
include('./jpg1.php'); ?〉

4. 連結PHP檔案瀏覽圖片【提供jpg3.php，並請修改網頁內容】

〈html〉〈head〉
〈meta http-equiv="Content-Type" content="text/html; charset=utf-8" /〉
〈title〉檢測縮圖並做連結〈/title〉〈/head〉〈body〉
按下連結可察看原圖〈br〉
〈a href="jpg2.php"〉
〈img src = "jpg1.php" alt="看不到" width="100" height="90" border="0"〉〈/a〉
〈/body〉〈/html〉

六、開啟網頁前的認證【提供auth1.php，並請修改網頁內容】

〈? header("Content-type: text/html; charset=utf-8");
if (empty($_SERVER['PHP_AUTH_USER'])) {
header('WWW-Authenticate: Basic realm="Please input"');
header('HTTP/1.0 401 Unauthorized');
echo '請輸入正確的帳號及密碼, 不可以取消!';
exit;
} else {
$correctName="pcschool";
$correctpwd="mysql" ;
if (($_SERVER['PHP_AUTH_USER'] != $correctName) or
($_SERVER['PHP_AUTH_PW'] !=$correctpwd)){
echo "登入失敗,請開啟新的瀏覽器重新登入"; }} ?〉

七、下載的檔案與實際的檔案不同【請自行撰寫】

〈?php
include("auth1.php"); //可不加上此行
header("Content-type: text/html; charset=utf-8");
$file="./9707.zip"; // 實際檔案的路徑+檔名
$filename="0714.zip"; // 下載的檔名
header("Content-type: ".filetype("$file"));//指定類型
header("Content-Disposition: attachment; filename=".$filename."");
readfile($file); ?〉

八、其它