PHP SQL Xingquan Hill Zhu xqzhucse fau edu

PHP & SQL Xingquan (Hill) Zhu xqzhu@cse. fau. edu PHP 1

PHP&SQL q Relational Database systems q Structured Query Language: SQL q Access My. SQL on Pluto server v Create table, add records, query, and delete records q PHP My. SQL database access v Connect to My. SQL server v Select database v Query v Show the results PHP 2

Relational database systems q A collection of tables of data v Each table can have any number of rows and columns of data v The columns of a table are named • Attributes v Each row usually contains a value for each column Rows of a table are often referred to as entries Primary keys v Both data values and primary key values in a table are called field v v • 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 3

Structured Query Language: SQL q Language for specifying access and modification to relational database q Different from most programming language v More like a structure form of English q Reserved words are not case sensitive v SELECT and select are equivalent q The whitespace separating reserved words and clauses is ignored v Commands can be spread across several lines q Simple SQL process v Create database v Use database v Create table v Add records v Search records PHP 4

Access SQL on Pluto q Download putty (http: //www. chiark. greenend. org. uk/~sgtatham/putty/) q Download -> putty. exe -> open PHP 5

Access SQL on Pluto Your fau ID Username: Your fau ID Password: Your fau ID PHP 6

Access SQL on Pluto Your FAU ID again My. Sql version PHP 7

You are not able to create a database on Pluto, but select your own database q Use Your. FAUID; q You are only able to use your own db, TSG created for you PHP 8

Create a table Table name, you name it q create table order. Tbl(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 9

Insert Records q Insert into order. Tbl(ID, first_name, last_name, lobster, crab, apple, orange, comments) values (1, “Hill”, “Zhu”, 2, 1, 2, 0, “Good”); PHP 10

Query the database q List all the records v Select * from order. Tbl; PHP 11

Query the database q Select first_name, last_name from order. Tbl; PHP 12

Query the database q Select * from order. Tbl where first_name=“Hill”; PHP 13

Delete records q Delete from table where xx=y v Delete from order. Tbl where last_name=“Ford”; PHP 14

PHP&SQL q Relational Database systems q Structured Query Language: SQL q Access My. SQL on Pluto server v Create table, add records, query, and delete records q PHP My. SQL database access v Connect to My. SQL server v Select database v Query v Show the results PHP 15

An Important Step q Login into pluto my. SQL server q Execute the following command v SET PASSWORD FOR ‘yourfauid’@’localhost’ = OLD_PASSWORD(‘yourfauid’); q Otherwise, you will not be able to connect to my. SQL server v Error message “Client does not support authentication protocol ” v Some sort of protocol problem PHP 16

Php connect to My. SQL server q Connect to a My. SQL server v $db = mysql_connect($hostname, $userpasswd); q Select database v $er = mysql_select_db("customer", $db); v v v v v $hostname="localhost"; It’s Your. Fau. Id if use pluto $username="hill"; $userpasswd="hill"; $db = mysql_connect($hostname, $userpasswd); if (!$db) { print ("Error - Could not connect to My. SQL"); exit; } Database. php PHP 17

PHP SQL Query q $qresult = mysql_query($query); v The query string should not end with a semicolon. v Return “false” on error v Return a complex “resource” structure on success $num_rows = mysql_num_rows($qresult); v $num_fields = mysql_num_fields($qresult); v $row = mysql_fetch_array($qresult); v PHP 18

PHP SQL Query q $row = mysql_fetch_array($qresult); v Calling Mysql_fetch_array() each time will return one row of the retrieved records (from the top to the bottom) q $row is a special array v It has two elements for each field v The first element consists of the system assigned key (0, 1, 2…) along with the field value v The second element uses attribute name as the key (“first_name”…), along with the field value v So you can use either of the following forms • $row[0], $row[1]…. • $row[“first_name”], $row[“last_name”]…. PHP 19

PHP SQL Query q A simple PHP query example Customer. html accesscustomer. php PHP 20

PHP Insert A record q $sqlquery = INSERT INTO $table VALUES($id, $first_name, $last_name, $lobval, $crbval, $appval, $orgval, $comments); q Insert order information into the database Formselection. php formcheckout. php formprocesswith. DB. php mysql. DBProcess. inc PHP 21

PHP&SQL q Relational Database systems q Structured Query Language: SQL q Access My. SQL on Pluto server v Create table, add records, query, and delete records q PHP My. SQL database access v Connect to My. SQL server v Select database v Query v Show the results PHP 22