Transcript PHP Database Access

PHP & SQL
Xingquan (Hill) Zhu
[email protected]
PHP
1-1
PHP&SQL
 Relational Database systems
 Structured Query Language: SQL
 Access MySQL on Pluto server
 Create table, add records, query, and delete
records
 PHP MySQL database access
 Connect to MySQL server
 Select database
 Query
 Show the results
PHP
1-2
Relational database systems
 A collection of tables of data


Each table can have any number of rows and columns of data
The columns of a table are named
• Attributes

Each row usually contains a value for each column
Rows of a table are often referred to as entries
Primary keys

Both data values and primary key values in a table are called field


• One column which uniquely identify the rows of the table
ID First_name
Last_Name
Lobster Crab
Apple
Orange Comments
1
Hill
Zhu
2
1
2
0
Good
2
Hill
Ford
1
2
0
0
Good
3
Ford
James
0
0
1
1
Super
PHP
1-3
Structured Query Language: SQL
 Language for specifying access and modification to
relational database
 Different from most programming language

More like a structure form of English
 Reserved words are not case sensitive
 SELECT and select are equivalent
 The whitespace separating reserved words and clauses
is ignored

Commands can be spread across several lines
 Simple SQL process
 Create database
 Use database
 Create table
 Add records
 Search records
PHP
1-4
Access SQL on Pluto
 Download putty
(http://www.chiark.greenend.org.uk/~sgtatham/putty/)
 Download -> putty.exe -> open
PHP
1-5
Access SQL on Pluto
Your fau ID
Username: Your fau ID
Password: Your fau ID
PHP
1-6
Access SQL on Pluto
Your FAU ID again
MySql version
PHP
1-7
You are not able to create a database
on Pluto, but select your own database
 Use YourFAUID;
 You are only able to use your
own db, TSG created for you
PHP
1-8
Create a table
Table name, you name it
 create table orderTbl(ID int not null primary key
auto_increment, first_name varchar(30), last_name
varchar(30), lobster int, crab int, apple int, orange
int, comments varchar(30));
PHP
1-9
Insert Records
 Insert into orderTbl(ID, first_name, last_name,
lobster, crab, apple, orange, comments) values (1,
“Hill”, “Zhu”, 2, 1, 2, 0, “Good”);
PHP
1-10
Query the database
 List all the records
 Select * from orderTbl;
PHP
1-11
Query the database
 Select first_name, last_name from orderTbl;
PHP
1-12
Query the database
 Select * from orderTbl where first_name=“Hill”;
PHP
1-13
Delete records
 Delete from table where xx=y
 Delete from orderTbl where last_name=“Ford”;
PHP
1-14
PHP&SQL
 Relational Database systems
 Structured Query Language: SQL
 Access MySQL on Pluto server
 Create table, add records, query, and delete
records
 PHP MySQL database access
 Connect to MySQL server
 Select database
 Query
 Show the results
PHP
1-15
An Important Step
 Login into pluto mySQL server
 Execute the following command

SET PASSWORD FOR ‘yourfauid’@’localhost’ =
OLD_PASSWORD(‘yourfauid’);
 Otherwise, you will not be able to connect to mySQL server


Error message “Client does not support authentication protocol ”
Some sort of protocol problem
PHP
1-16
Php connect to MySQL server
 Connect to a MySQL server
 $db = mysql_connect($hostname, $username, $userpasswd);
 Select database
 $er = mysql_select_db("customer", $db);









$hostname="localhost";
It’s YourFauId if use pluto
$username="hill";
$userpasswd="hill";
$db = mysql_connect($hostname, $username, $userpasswd);
if (!$db)
{
print ("Error - Could not connect to MySQL");
exit;
}
Database.php
PHP
1-17
PHP SQL Query
 $qresult = mysql_query($query);
The query string should not end with a semicolon.
 Return “false” on error
 Return a complex “resource” structure on
success

$num_rows = mysql_num_rows($qresult);
 $num_fields = mysql_num_fields($qresult);
 $row = mysql_fetch_array($qresult);

PHP
1-18
PHP SQL Query
 $row = mysql_fetch_array($qresult);
 Calling Mysql_fetch_array() each time will return one row
of the retrieved records (from the top to the bottom)
 $row is a special array
 It has two elements for each field
 The first element consists of the system assigned key (0,
1, 2…) along with the field value
 The second element uses attribute name as the key
(“first_name”…), along with the field value
 So you can use either of the following forms
• $row[0], $row[1]….
• $row[“first_name”], $row[“last_name”]….
PHP
1-19
PHP SQL Query
 A simple PHP query example
Customer.html accesscustomer.php
PHP
1-20
PHP Insert A record
 $sqlquery = INSERT INTO $table
VALUES($id, $first_name, $last_name,
$lobval, $crbval, $appval,
$orgval,$comments);
 Insert order information into the database
Formselection.php formcheckout.php
formprocesswithDB.php
mysqlDBProcess.inc
PHP
1-21
PHP&SQL
 Relational Database systems
 Structured Query Language: SQL
 Access MySQL on Pluto server
 Create table, add records, query, and delete
records
 PHP MySQL database access
 Connect to MySQL server
 Select database
 Query
 Show the results
PHP
1-22