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

PHP&MySQL--6
一、新增帳號

grant all privileges on *.* to pcschool@localhost identified by 'phpmysql';

grant all privileges on board.* to php1@localhost identified by 'mysqlstart1';

grant select on board2.* to php3@localhost identified by 'mysqlstart3';

二、 PHP連結資料庫語法（db.php，資料庫為pcschool）

〈html〉〈head〉〈meta http-equiv="Content-Type" content="text/html; charset=utf-8"〉
〈title〉資料庫連線〈/title〉〈/head〉〈body〉〈?
$link = @mysql_connect("localhost", "pcschool", "phpmysql");
$link_db = @mysql_select_db("pcschool123446");
if($link) { echo "主機連結OK!....";
if($link_db) { echo "資料庫連結OK!...."; }
if(!$link_db) { echo "資料庫連結失敗!....."; }}
if(!$link) { die( "連結主機失敗!....");}
echo "Hello"; ?〉〈/body〉〈/html〉

1. 問題：如果mysql_connect出錯，會如何？
2. 問題：如果mysql_selsect_db出錯，會如何？
3. 問題：在發生錯誤之後，可否在顯示訊息後跳出網頁？
三、 mysql編碼與傳輸：unicode.php

〈?
mysql_query("SET NAMES utf8");
mysql_query("CHARACTER SET utf8");
mysql_query("SET CHARACTER_SET_CLIENT =utf8");
mysql_query("SET COLLATION_CONNECTION=utf8_general_ci");
mysql_query("SET CHARACTER_SET_RESULTS =utf8");
mysql_query("SET CHARACTER_SET_SERVER = utf8");
mysql_query("SET character_set_connection=utf8"); ?〉

四、 PHP函數--送出執行語法：mysql_query("SQL 語法")
【提供mysql_query1.php】

〈html〉〈head〉
〈meta http-equiv="Content-Type" content="text/html; charset=utf-8"〉
〈title〉mysql_query〈/title〉〈/head〉〈body〉〈?
mysql_connect("localhost", "pcschool", "phpmysql") or die("無法連結主機");
mysql_select_db("pcschool") or die("無法連結資料庫");
//include("unicode.php");
//以下新增語法請做練習
//以下兩行為一行
//$sql = "insert into list (username, email, sex) values('pcschool', 'quota123@ms14.url.com.tw', '男')";
//$sql = "insert into list (email, sex) values('quota123@ms14.url.com.tw', '男')";
//$sql = "insert into list values('quota123@ms14.url.com.tw','pcschool','男')";
$sql = "insert into list values('','pcschool','男')";
mysql_query($sql) or die(mysql_error( ) );
?〉〈/body〉〈/html〉

若沒有加入unicode.php，MySQL內的資料會如何呢？
若資料表名稱錯誤或儲存的欄位數量不對，系統會如何回應呢？
【提供mysql_query01.php】

〈html〉〈head〉
〈meta http-equiv="Content-Type" content="text/html; charset=UTF-8"〉
〈title〉以下的Query是否有錯？〈/title〉〈/head〉〈body〉
〈?php
include("server.php");
$a='test';
$b='test2';
$sql="INSERT INTO orders(CustomerID,EmployeeID)VALUES ($a,$b);";
mysql_query($sql);
//mysql_query($sql) or die(mysql_error( ) );
//echo $sql;
?〉〈/body〉〈/html〉

若兩個query一起執行，但其中一個有問題會如何呢？【提供mysql_query02.php】

〈html〉〈head〉
〈meta http-equiv="Content-Type" content="text/html; charset=UTF-8"〉
〈title〉兩個Query執行會如何？〈/title〉〈/head〉〈body〉
〈?php
include("server.php");
$sql="INSERT INTO orders(CustomerID,EmployeeID)VALUES ('er', '23');";
mysql_query($sql);
$a='test';
$b='test2';
$sql="INSERT INTO orders(CustomerID,EmployeeID)VALUES ($a,$b);";
mysql_query($sql);
//mysql_query($sql) or die(mysql_error( ) );
//echo $sql;//得到與mysql_error( )
?〉〈/body〉〈/html〉

上述兩個練習請修正【修改為mysql_query03.php】：

$a='test';
$b='test2';
$sql="INSERT INTO orders(CustomerID,EmployeeID)VALUES ('$a',$b);";

複製資料表語法

create table insertemployees select * from employees;
create table insertjob select * from job;
create table updateemployees1 select * from employees;
create table updateemployees2 select * from employees;
create table updateemployees3 select * from employees;
create table updatejob select * from job;
create table updateproducts select * from products;
create table delemployees1 select * from employees;
create table delemployees2 select * from employees;
create table delemployees3 select * from employees;
create table deljob select * from job;

新增資料語法

insert into insertjob values (12);

insert into insertjob values ('manager',12);
select * from insertjob;

insert into insertjob values (12,'manager');
select * from insertjob;

insert into employees(lastname,firstname,employeeid) values ('jiannrong','yeh',2009);」
select lastname,firstname,employeeid from employees where employeeid=2009;

insert into insertjob(employeeid,title) select employeeid ,concat(firstname,"-",lastname) from employees;
select * from insertjob

五、資料查詢語法
1.基本查詢

select * from employees;
select firstname, lastname from employees;
select city from employees;
select distinct city from employees;

2.As的使用

select firstname as f, lastname as l from employees;

select productname, unitprice ,unitsinstock ,unitprice * unitsinstock as total from products;

select firstname,hiredate,curdate( ) as nowdate,(year(curdate( ) )-year(hiredate)) as years from employees;

select firstname,lastname,concat(firstname, "-- ",lastname) as yourname from employees;

3.排序

select firstname, lastname, hiredate from employees order by firstname;
select firstname, lastname, hiredate from employees order by firstname desc;
select firstname, lastname, hiredate from employees order by firstname asc;
select firstname, lastname from employees order by hiredate desc;
select firstname, lastname from employees order by firstname desc,lastname asc;
select firstname, lastname from employees order by lastname asc ,firstname desc;

六、 PHP函數-- mysql_num_rows( ) 計算查詢後的筆數【請自行撰寫】

〈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");
$abc="select username from list";
echo $abc."〈br〉";
$sql = mysql_query($abc) or die(mysql_error( ) );;
$rows = mysql_num_rows($sql);
if($rows==""){
echo "查無資料！";
}else{ echo "有 ".$rows." 筆資料喔！";}
?〉〈/body〉〈/html〉

七、回傳欄位資料【請依照講義修改mysql_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";
$sql2=mysql_query($sql) or die(mysql_error( ) );
echo $sql2;
$list1= mysql_fetch_array($sql2);
echo $list1['OrderID'];
?〉
〈/body〉〈/html〉

請修改上例，觀察顯示內容【請將mysql_query04.php另存新檔後依講義修改】

$list1= mysql_fetch_array($sql2);
echo $list1['OrderID']."〈br〉";
echo $list1['OrderID']."〈br〉";
$list1= mysql_fetch_array($sql2);
echo $list1['OrderID']."〈br〉";

八、顯示資料【以array方式設計】【請依照講義修改mysql_query06.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";
$sql2=mysql_query($sql) or die(mysql_error( ) );
while($list1= mysql_fetch_array($sql2))
{
echo $list1['OrderID']." ".$list1['OrderDate']."〈br〉";
}
?〉〈/body〉〈/html〉

九、表單送資料至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;

十一、其他